Reporting against stored procedures: Crystal shows nulls, should have data
First off, the requirements for this report are non-negotiable. I've emplored the requestor, telling them that this should be two reports, not one, to no avail...
I have a report based on an oracle stored procedure.
The stored procedure is controlled by a parameter. Let's call it 'P_TYPE' for sake of argument.
Regardless the value of P_TYPE the names of fields that are returned are identical, but the process to derrive the values is different.
In the stored procedure:
IF P_TYPE = '<value>' THEN OPEN P_CURSOR FOR fields go here.... IF P_TYPE = '<another value>' THEN OPEN P_CURSOR FOR same fields
So far, so good. P_TYPE can have two values, 'Personal' and 'Company', When personal is selected, then a number of company specific fields returned in the dataset are set to 'null' in the stored procedure (select null as fieldName). The same is true of company. Though the majority of the fields are common between the two.
When I run the report for 'Personal' all of the data returned is correct. (yay!)
When I run the report for 'Company' a number of fields are null where they SHOULD have data. Fields common to both data sets are correct. Fields set to null for personal, but populated in the company data set are null.
Now, if I perform a 'verify database' operation, and supply the 'Company' parameter, crystal gives me an 'updating' message, and when I re-run the report... voila... the fields are populated. But NOW, if I run the report for personal, the personal specific fields are... you guessed it... null.
If I put the report into our environment to run in the HTML viewer, the behavior remains the same as the last time I set the datasource location, or verified the database.
- I have verified in oracle that the stored procedure returns the correct and anticiapted data when run directly, without Crystal.
It gets more bizzare...
I suspected that for some reason Crystal was caching something about the fields it was expecting to return from the stored procedure, so I changed the null values for these fields with a string literal ('N/A'). I did a verify database, changes recognized, ran the report for personal, all is well. Ran the report for company, and now... instead of nothing at all in these fields, I am now seeing the first three characters of the data in the field. (noting that 'N/A' is three characters)
So... here's what I don't want to do... I don't want to create a string literal that isn't as long as the field may possibly be. That's a little silly.
Can I select fields, and define thier datatype and size in the select statement?
Edited by: Ryan Fry on Sep 17, 2008 8:58 PM