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!
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.
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
You need to create a view that converts the datetime to a date, then use the date as the dynamic part of the parameter - that's assuming Oracle has a date (rather than date-time) data type. I work mostly with MS SQL, and DATE was only added in a fairly recent release.
It all hinges on whether or not your DBA allows this, I'm afraid.
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
Hi Nrupal,
Thank you very much for your help!
You mean for Oracle there is no way I can get rid of the time portion in Dynamic parameter? For the workaround, you mean I just create a static date parameter to let the user to pick the date?
Best regards,
Kingson
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
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
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
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.
Thank you very much for your time and help!
Best regards,
Kingson
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
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.
Thank
Thanks!
Kingson
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
Hi Prathamesh,
Thank you very much for your reply. I tried your solution, but it didn't work for me. As I mentioned above, I tried to use Command as well, but the cascading prompt broke when I select start date. Could you provide me any other solutions?
Thanks in advance!
Regards,
Kingson
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.
Hope this helps!
-Abhilash
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
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
