Skip to Content
avatar image
Former Member

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

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Aug 30, 2013 at 06:24 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Abhilash Kumar

      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

  • avatar image
    Former Member
    Aug 30, 2013 at 08:08 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • avatar image
    Former Member
    Aug 30, 2013 at 09:00 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member 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?