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

[Former Member]

Hi Brad, the data type of the parameter in Crystal report is dependent on the data type of the parameter on the stored procedure that you are using. 

For Example:

CREATE PROCEDURE [dbo].[Stored_Proc_Name]

-- parameters   

@DateFrom as DATETIME,

@DateTo as DATETIME

-- parameters   

AS

BEGIN

Your crystal report would require you to input date with time. But if you declare your stored procedure as

CREATE PROCEDURE [dbo].[Stored_Proc_Name]

-- parameters   

@DateFrom as DATE,

@DateTo as DATE

-- parameters   

AS

BEGIN

your crystal report will not require to input time. BUT the calendar functionality will not be available (calendar icon).

As for the 'set to null' option in crystal report, when a user ticks the set to null option crystal report will pass a "NULL" value to the stored procedure.  Your stored procedure should have checking whether a parameter has passed a "NULL" value or not.

For the example below i will pass a NULL value on both of the parameters.

exec [Stored_Proc_Name] NULL,NULL

CREATE PROCEDURE [dbo].[Stored_Proc_Name]

-- parameters   

@DateFrom as DATETIME,

@DateTo as DATETIME

-- parameters   

AS

BEGIN

IF @DateFrom IS NULL or @DateFrom = ''  SET @DateFrom = '19000101'

IF @DateTo IS NULL or @DateTo = '' SET @DateTo = getdate()

The SQL code on bold checks if the parameter is null or not. If it's NULL code will set it to a specific date or to the current date.

Hope this helps, I am not that familiar with oracle so I used MSSQL samples.

Thanks

- Marc

[Former Member]

Hi Marc,

In Oracle, there is only a DATE datatype. There is no DATETIME datatype. I know that is part of my problem.  I am sure crystal is helping out by limiting my options to only use the DATETIME datatype in crystal.

As for the NULL values once received, I am looking for NULLs, empty strings and I even tried passing in a default date of 1/1/1900 00.00.00 and all behave the same way. I get no results inside of crystal.

I created temporary table that tracks what comes in the stored procedure, how it gets converted and passed to the actual query that returns the results. All look perfect on the backend. All behave perfectly on the backend. It is just crystal that won't give me the results back correctly.

My oracle equivalent code looks like this:

create or replace procedure umn_rpt_trx_adjustments(in_start in date, in_end in date, p_result out umn_cursor.t_cursor) as

          StartDate date;

          tStart date;

          EndDate date;

          tEnd date;

begin

  insert into bstemp2(date1, date2) values(to_char(in_start),to_char(in_end));

          if ((in_start is null) or (trim(in_start) = '') or (trunc(in_start) = to_date('1/1/1900 00.00.00'))) then

                         tStart := trunc(sysdate);

          else

                 tStart := trunc(in_start);

          end if;

          if ((in_end is null) or (trim(in_end) = '') or (trunc(in_end) = to_date('1/1/1900 00.00.00'))) then

                         tEnd := trunc(sysdate);

          else

                         tEnd := trunc(in_end);

          end if;

  if tStart <= tEnd then

                         StartDate := tStart;

       EndDate := tEnd;

          else

                         StartDate := tEnd;

                         EndDate := tStart;

          end if;

  insert into bstemp2(date1, date2) values(to_char(StartDate),to_char(EndDate));

          commit;

          open p_result for

                    select

                              t."Total"

                              ,t."Code"

                              ,t."Id"

                              ,t."Procedure"

                              ,p."Chart"

                              ,p."First"

                              ,p."Last"

                              ,pn."Date"

                              ,pn."NoteCode"

                              ,pn."Text"

                              ,t."TreatmentDate"

                              ,u."First"

                              ,u."Name"

                    from patient p

                                   inner join trx t

                                                  on t."Patient" = p."Patient"

                                                  and t."Deleted" = 0

                                   inner join umn_rpt_adj_trx_codes ur

                                                  on trim(t."Code") = ur.trx_code

                                   inner join users u

                                                  on u."User" = t."User"

                                   left join ptnote pn

                                                  on pn."Patient" = p."Patient"

                    where 1=1

                    and t."Deleted" = 0

                    and instr(pn."Text",t."Id")>0

                    AND          pn."Date" between trunc(StartDate) AND trunc(EndDate)

                    order by pn."Date", t."Code", p."Chart", t."Id";

end;

Sample records in the bstemp2 table:

DATE1	DATE2	WHEN
08/27/2013 13.01.57		08/30/2013 07:36:57
08/27/2013 00.00.00	08/30/2013 00.00.00	08/30/2013 07:36:57
	08/27/2013 13.01.57	08/30/2013 07:36:41
08/27/2013 00.00.00	08/30/2013 00.00.00	08/30/2013 07:36:41
	08/27/2013 13.01.57	08/30/2013 07:36:13
08/27/2013 00.00.00	08/30/2013 00.00.00	08/30/2013 07:36:13

Sorry, it's not very clear, but the NULL date vales are going in, then converted to actual dates for the "even" numbered records. The matching records have the identical WHEN time stamps.

[Former Member]

I have the same question regarding this

I have a stored procedure with customized ordering / sorting in the order by clause, I use this as data source of my crystal report file.

The problem is, crystal report does not load the same query result in the sql stored procedure with a specified ORDERING. Why is that so?

[Former Member]

Hi Brad, do you keep the 'set to null' checkbox checked while refreshing the report?

This should pass the null value to the stored procedure and has a fair chance to show the same output as you observe in the back-end.

-Prathamesh

[Former Member]

Hi Prathemesh,

I am using the default crystal provided dialog box that my stored procedure. I am so new, I am not sure how to control it very well yet. Maybe there are some settings to help, but in my environment, if I the check box for "set to null" is not checked, it won't allow me to click the Okay button. It seems I can either put a date/time value in or check the "set to null" box.

~brad