16 Replies Latest reply: May 4, 2012 10:37 PM by Brian Dong RSS

Urgent: How to remove time part of a dynamic parameter

Kingson Lee
Currently Being Moderated

Hi there,

 

I have an urgent question for removing the time part of a dynamic cascading parameter.

 

I created a dynamic cascading parameter in Crystal Report 2008. In this parameter I retrieve the billing start date and end date from the Oracle database. The type of the field in the Oracle table I retrieve the data from is Oracle default Date type, which contains date and time. I want to only show the date in my parameter without showing time. How can I do it? I cannot change the type of the field in database.

 

The following is the screenshot of my dynamic cascading parameter. I want remove the time parts with black circles. Thanks in advance!

Dynamic_Parameter.JPG

  • Re: Urgent: How to remove time part of a dynamic parameter
    Andrew Baines
    Currently Being Moderated

    It's unusual, and (normally) unnecessary to create a dynamic date parameter - can't you just create a normal date parameter and allow the user to select from a calendar?

    Failing that, check if your database supports date types and use a view instead.

    • Re: Urgent: How to remove time part of a dynamic parameter
      Kingson Lee
      Currently Being Moderated

      Hi Andrew,

       

      Thank you for your reply. The reseason I am using dynamic date parameter is that in our billing period table the billing period information is always changing and the report viewers don't know exact billing period start and end date for a selected customer. I am using cascading parameter to retrieve the up to date billing period dates for the selected customer. Is there any way I can format the date that showing in the date drop down list?

       

      Thank you very much!

       

      Kingson

  • Re: Urgent: How to remove time part of a dynamic parameter
    Nrupali Thakre
    Currently Being Moderated

    Hi Kingson,

     

    The time portion in dynamic parameter based on Oracle is a default behaviour of CR 2008.

    As a workaround you can create a date parameter.

     

    Thanks,

    Nrupal

  • Re: Urgent: How to remove time part of a dynamic parameter
    Abhilash Kumar
    Currently Being Moderated

    Hi Kingson,

     

    Are you sure the field values contain the timestamp as well? You said they're stored as 'Date' in Oracle isn't it?

     

    If they're stored as Date and if you still see the timestamp, then the reason for that, as Nrupal said, is because Crystal Reports adds it for some reason when the datasource is Oracle.

     

    The resolution involves adding a String value in the registry. So, here's where you need to be in the registry:

     

    HKEY_LOCAL_MACHINE/Software/Business Objects/Suite 12.0/Crystal Reports/Database

     

    Once you're here, create a new String Value called 'useDateInsteadOfDateTime'.

     

    Double-click this string and put in the name of the Database or the DSN name as its value.

     

    Restart Crystal Reports and see if the timestamp is still there.

     

    -Abhilash

    • Re: Urgent: How to remove time part of a dynamic parameter
      Kingson Lee
      Currently Being Moderated

      Hi Abhilash,

       

      Thank you very much for your help! I really appreciate it.

       

      For Oracle, the default Date type contains Date and timestamp as well. Crystal Report brought the correct raw data from the database. That's why your solution to change the registry won't help my case. Is there anyway that I can format the display of parameter when it is populated from the database table field or I can modify the query that the cascading parameter used to retrieve values?

       

      Regards,

       

      Kingson

      • Re: Urgent: How to remove time part of a dynamic parameter
        Abhilash Kumar
        Currently Being Moderated

        Hi Kingson,

         

        Although I'm not an Oracle guy, it sounds odd to me that Oracle stores timestamps in Date fields too. So, why would they have DateTime then?

         

        Anyway, since you said the datetime values keep on changing and because this is a cascading parameter, creating a static prompt might not be of great help.

         

        If the data is coming via a command object, you could perhaps convert this Date value to string or somehow (using Oracle functions) remove the timestamp before CR gets the records. This way you can create a dynamic cascading prompt as well.

         

        I would still suggest you to try the registry keys on a test machine and see if it works!

         

        -Abhilash

        • Re: Urgent: How to remove time part of a dynamic parameter
          Kingson Lee
          Currently Being Moderated

          Hi Abhilash,

           

          I used Oracle Client to creat a TNS for Crystal Report to connection to the database. The following is my TNS configuration file:

          CR_CONNECTION =

            (DESCRIPTION =

              (ADDRESS_LIST =

                (ADDRESS = (PROTOCOL = TCP)(HOST = 10.xx.xx.xx)(PORT = 1521))

              )

              (CONNECT_DATA =

                (SERVICE_NAME = Test)

              )

            )

           

          My database name is TestDB. I tried to add the registry key using Registry Editor and put either TNS name or database name as the key value, but neither of them worked. I am not so sure what I need to put as the registry.

           

          For the cascading parameter, I used the table field directly instead of Command. Now I am trying to create a command objects to convert date to string. The following is my command query:

          SELECT SUBSCRIPTION_ID, SUBSCRIPTION_VERSN_START_DATE, BILLING_PERIOD_ID, TO_CHAR(START_DATE,'DD/MM/YYYY'), TO_CHAR(END_DATE,'DD/MM/YYYY') FROM NCSQL.SUBSCRIPTION_BILLING_PERIOD

           

          Then I use the command along with two other tables to create cascading parameter, but the cascading parameter breaks when I select the start date. Please see the following to screenshots for before and after start date is selected.

           

          Before.JPG

          after.JPG

           

          Thank you very much for your time and help!

           

          Best regards,

           

          Kingson

          • Re: Urgent: How to remove time part of a dynamic parameter
            Abhilash Kumar
            Currently Being Moderated

            Hi Kingson,

             

            I'm sorry I forgot to mention that the registry key needs to be added to the HKEY_CURRENT_USER hive as well.

             

            So, when you go to HKEY_CURRENT_USER, you'll find the same folder structure.

             

            And, could you also attach a screenshot of the registry keys.

             

            -Abhilash

            • Re: Urgent: How to remove time part of a dynamic parameter
              Kingson Lee
              Currently Being Moderated

              Hi Abhilash,

               

              I tried to set the key value as CR_Connection, CR_Connection\TestDB, and TestDB, still no luck. Followings are the screenshots for my registry key, one for HKEY_LOCAL_MACHINE and another one for HKEY_CURRENT_USER.Registry_Key_Local_Machine.JPGThankRegistry_Key_Current_User.JPG

               

              Thanks!

               

              Kingson

              • Re: Urgent: How to remove time part of a dynamic parameter
                Prathamesh Acharya
                Currently Being Moderated

                Hi,

                 

                Modifying the java script files that ship with installation is not recommended and supported..

                 

                However, if it is too critical then you can test the following:

                 

                Note: take a backup of the .js file before changing and restart Crystal Reports after making any changes

                 

                1. Open the following JavaScript file in Notepad:
                C:\Program Files\Business Objects\Common\4.0\crystalreportviewers1\prompting\js\promptengine_calendar2.js

                 

                2. Change the line

                 

                this.dateFormat = dateFormat;
                to
                this.dateFormat = "yyyy-MM-dd";

                 

                 

                3. If that does not have any impact then change

                 

                this.dateTimeFormat = dateFormat + " " + "H:mm:ss";

                to

                this.dateTimeFormat = "yyyy-MM-dd";

                 

                Thanks,

                Prathamesh

              • Re: Urgent: How to remove time part of a dynamic parameter
                Abhilash Kumar
                Currently Being Moderated

                Hi Kingson,

                 

                Thanks for the screenshots!

                 

                Apparently, you're adding the key at the 'wrong' place.

                 

                'useDateInsteadOfDateTime' should be a 'string value' for the Database Key. You have created a new 'Key' under Database.

                 

                Here's a screenshot of how it should look.registry.JPG

                 

                Hope this helps!

                 

                -Abhilash

                • Re: Urgent: How to remove time part of a dynamic parameter
                  Kingson Lee
                  Currently Being Moderated

                  Hi Abhilash,

                   

                  I followed you instruction and added the string value for Database key. I tired the value as CR_Connection, CR_Connection\TestDB, and TestDB, but it still didn't work. I also tried command object to preprocess the date values, but I got the issue (the cascading prompt broke) I mentioned above. Could you please help me out?

                   

                  Many thanks!

                   

                  Regards,

                   

                  Kingson

                  • Re: Urgent: How to remove time part of a dynamic parameter
                    Brian Dong
                    Currently Being Moderated

                    Hi Kingson,

                     

                    Changing the registry is really just a hack because you will have to also set the same registry entries on any machine that will be running the report.  What happens if you actually need a DateTime parameter? 

                     

                    I can understand why you want the date as a cascading parameter it won't really do based on your environment.  When you create a parameter, Crystal goes and checks the format of the field and automatically selects the datatype based on what it retrieves. 

                     

                    Because of this I would recommend either having a view created that will link the billing periods with your clients and possibly have the date come back as a string.  Then in your report have the Selection Formula convert the parameters to a date and filter your records. 

                     

                    If Crystal can't see an Oracle Date as just a date then this really is the best suggestion.  The other suggestion would be to remove the dates from the cascading parmeter and have the users select the dates themselves but you mentioned it may not be possible for your users to know what dates to select. 

                     

                    Good luck,

                    Brian

Actions