Skip to Content
author's profile photo Former Member
Former Member

To prompt or not to prompt

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Sep 22, 2008 at 02:15 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 22, 2008 at 02:40 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 22, 2008 at 03:42 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.