Crystal not displaying stored procedure results when dealing with null date values.

[Former Member]

I am sorry if this has already been addressed. I have searched to exhaustion.

I have an oracle stored procedure that can handle null date values being passed to it. It just defaults the value to the system date. It works like a champ every where except in crystal reports. I have looked and changed all the apparent null value options inside Crystal reports with no changes in behavior. I even modified my stored procedure to write out the values being passed in to a temp table and all appear to be normal. All values look perfect. The only problem is, crystal doesn't show the expected rows.

If I pass dates dates, as expected, all works fine inside of crystal. It is only if either date value is null where I have the problem.

While on the subject, is there anyway to NOT have the "Set to Null" check-box in the parameters dialog box? It seems as though there is very limited flexibility in the stored procedure parameters dialog boxes. One other problem I have with it, I would like to just have the user enter a date and not a date/time value. Oracle doesn't give you the option to just accept a date value (even though it will), it seems as if Crystal thinks it can only accept a date/time value and you can't change it in the parameters dialog box.

Sorry for all the question, I am a newbie with crystal reports.

[abhilash_kumar · Accepted solution]

Hi Brad,

Let's take this one question at a time.

Are you trying to pass NULL Dates from the prompt screen in crystal reports?

P.S: For the other two questions I would recommend posting a new discussion.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

Thanks for the quick response!

I would actually like for NULL values not to be passed at all, but I don't know how to suppress that check box from coming up in the default parameter dialog box from crystal. That is the only reason I have the stored procedure looking for and acting upon the NULL values.

To answer your question, I am assuming the NULL values from the default dialog box DO get passed to the stored procedure, just the same as when an actual date gets passed in. Is there a way to control that behavior?

Good suggestion on creating separate threads for each question. Sorry, I am still trying to get familiar with the wonderful SCN community.

[abhilash_kumar · Accepted solution]

Hi Brad,

Here are two workarounds to remove the 'set to Null' option on the prompt screen:

Workaround 1:

1) Create a report with any datasource (you do not need to use the fields in this source)

2) Create a parameter in this report with the same datatype as the sub-report's parameter.

3) Insert a sub-report (Insert > Subreport) and select the stored procedure as the datasource. It will prompt you for a value once. Place the subreport on the Report Header.

4) Suppress all other sections of the Main-report except the Report Header

5) Right-click the Sub-report and select 'Change sub-report links' > Move the Main report parameter to the 'Fields to Link to' area and from the drop-down that says 'Subreport parameter field to use' choose the sub-report's Date parameter

Workaround 2:

Another way to do this is by calling the stored procedure via a command object. So, once the connection to the database has been established from the Database Expert, you should see an option called 'Add Command'.

Write a command similar to this:

Exec Procedure_Name {?Parameter_name}

Here, {?Parameter_name} is the prompt created at the command level and you can create it like this:

On the right-hand side where it says Parameter List, click on the 'Create' button and create the parameter with the same datatype as the stored procedure's. Give it a name and use the same name in the Command Object.

Hope this helps!

-Abhilash

[Former Member · Accepted solution]

Abhilash,

Thanks again for the quick response!

I have tried the sub-report method, but when I ultimately have to connect to the stored procedure, I run in the same problem with connecting a DATE paramter with a DATETIME parameter. I saw a suggestion of creating a function value and surrounding it with DATEVALUE but I didn't have luck with that, but I will try again.

I will try your "Workaround 2" this technique is new to me.

I really appreciate your help on this!

~brad

[abhilash_kumar · Accepted solution]

Are you trying to say that CR converts the Date type prompt in Oracle to DateTime?

If you've still got the Subreport in there, you can do the following:

1) Create a formula in the Main Report with this code:

CdateTime({date_prompt_in_main_report}, Time(0,0,0))

2) You can then link this formula field to the Subreport DateTime prompt by following Step 5 in my previous reply.

-Abhilash

[Former Member · Accepted solution]

Abhilash,

Okay, I finally figured it out. It appears that Crystal is passing the date/time in as yyyy/mm/dd hh24:mi:ss. Inside of oracle, I was just using the default format, in my case of mm/dd/yyyy hh:mi:ss.

So, if I force my oracle stored procedure to expect the data to come in as the Crystal format everything works like a champ!

I really appreciate your help and insights on new techniques. I really want to just use DATE instead of the DATETIME. I will try your options as suggested.

Thanks again for all of your help on this!

~brad