To prompt or not to prompt

[Former Member]

To let you know from the beginning, I'm a newbie to anything but a straight forward report. I've used version 10 in the past and now I'm using XI. I'm mainly a VB6 programmer and an Oracle and SQL Server DBA.

I've been running reports for management for a while that generate ticket data from our Oracle DB Call Tracking system for the previous month. It's not problem for me to run since I just go in each month and change the selection criteria and the heading to match the previous month. Now, I've created a VB6 application that will allow management to run these reports without having to bother me.

The question is, should I set these reports up, somehow hard coded, to pull the data from the previous month or prompt the user for the date range?

The problem using a date range prompt is that there is around 5 years of data in the DB and the pull down the prompt uses doesn't list anywhere near the previous month. It stops about 3 years ago and I can't figure out how to let the user type in the date or to get the pull down to go past 2005.

If I hard code the report to always pull the previous month (which I think would be best) how do I go about that. I did create a formula field "monthname(month(currentdate)-1)" to do the heading of the report and I'm working on putting the year in the heading but need to get it to work when the report is run in January.

What would be the best why to do the selection for ticket to grab all tickets open from the first day to the last day of last month.

Thanks for helping out a newbie..

Mike Godwin

[Former Member]

I ended up using Dateadd("m",-1,currentdate) for the heading and found LastFullMonth works great in the selection criteria.

Messing with the reg to increase the entries in the drop down wouldn't work anyway since there are over 350,000 records in the DB.

Thanks for the help.

Mike Godwin

[Former Member]

Hi Mike Godwin

If the user wants to get all the values under prompt please edit the registry setting :

1. Create a registry key HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 11.0\Crystal Reports\DatabaseOptions\LOV.

NOTE Alternatively, you can create the registry key HKEY_CURRENT_USER\SOFTWARE\Business Objects\Suite 11.0\Crystal Reports\DatabaseOptions\LOV and that key will override the settings of the HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 11.0\Crystal Reports\DatabaseOptions\LOV key.

2. Add a string value "MaxRowsetRecords" and set the value to the maximum number of values that you desire for your report. For example, a value of 2000 will return up to 2000 values in the lowest level of a cascading parameter. NOTE: The value 0 (Unlimited) will not work with BusinessObjects Enterprise XI or Crystal Reports Server XI, you must specify another value.

NOTE The higher the number of values is, the longer it will take the Enter Values dialog box to populate with values.

3. After making changes to the registry, restart the affected service or application as required.

This will allow the user the select last month.

Then create a record selection likeu2026.

Databasefield >= ParameterFrom and Databasefield <= ParameterTo

You can get the current heading by applying the following formula :

Dateadd(u2018mu2019,-1,currentdate)

This will give the last month details on your heading

Hope this will resolve the issue

Regards,

Sastry

[Former Member]

you should remove the hardcoded dates from the stored procedure. i would then create parameters either in the sp or the report. if its a datetime stamp convert it to a date.

if it is in the sp it will prompt the user for the parameters.

if it is in the report you need to place the parameters in the record selection

if cdate>=@par1 and cdate(datefield<=@par2