Dynamic and Prompted Dates in Central Management Console/BI Launchpad

[Former Member]

Hi,

I'm not sure if this is the right forum for this discussion, so please let me know if I should move it.

The reports where I work are currently with an Oracle database, using SQL Commands or stored procedures and temp tables, run through a custom Internet interface or scheduled through Central Management Console or BI Launchpad.

The reports in question that I'm being asked about are desired to have both dynamic and user-entered date parameters.

When they are scheduled on a recurring basis, the user wants to put in a formula or sysdate (see attached) so that can be used to complete the date parameters, BUT if the report is run ad-hoc or through the Internet interface, the user can still enter the date parameters and have it run to those specifications.

Is this possible?  I would normally have two reports - one that runs the desired date range and set that one to recurring and one for ad-hoc with date parameters.

Would anyone happen to know how to do this?  If it's possible?  A work-around?

Thank you,

Erin

[DellSC · Accepted solution]

Which version of BusinessObjects/Crystal Reports Server are you using?  Which version of Crystal?

There is probably a way to do this, but it's dependent on the version you're using.

-Dell

[Former Member · Accepted solution]

Hi Dell,

Crystal Reports 2013 Support Pack 5 Patch 2

Version 14.1.5.15.68

SAP Business Objects BI Platform 4.1 Support Pack 5 Patch 2

Version 14.1.5.15.68

Thank you,

Erin

[DellSC · Accepted solution]

Cool.  Here's what you'll do:

1.  Create a parameter where the user can select which set of dates to use - the automatic ones for scheduling or the actual dates for on-demand reports.  I'll call this {?DateType} and it has two values - "Scheduled" and "User Defined".

2.  Create two Date parameters - {?Start Date} and {?End Date}.  Make both of them Optional in the parameter definition.

3.  Update the formula in the Select Expert.  It should look something like this:

(

({?DateType} = 'User Defined' and HasValue({?Start Date}) and HasValue ({?End Date}) and {MyTable.DateField} >= {?Start Date} and {MyTable.Date Field} <= {?EndDate})

OR

({?DateType} = 'Scheduled' and <whatever date criteria you use for scheduled reports)

OR

false

)

Note that the parentheses are VERY important for getting this to work right.

The "false" value at the end will cause it to return no data if either date parameter has no values.

-Dell

[Former Member · Accepted solution]

Hi Dell,

Although I didn't put it as eloquently as you, this was my solution to the user, as well.  Or I also suggested having two reports - one that pulls the dynamic date range and one that is ad-hoc - I've done both solutions before.

Unfortunately, the user wants to affect the Crystal Report as little as possible and not add a second parameter.  They believe a formula in the CMC/BI Launchpad prompt should be possible.

I really appreciate you getting back to me so quickly.

And I get the parentheses being important - when I first learned Crystal - and's, or's, and parentheses made me struggle for days, maybe a few weeks until I got the hang of it.

Again - thank you.

Take care,

Erin

[DellSC · Accepted solution]

It is NOT possible in the CMC or BI Launchpad.  The only way to do this is with a second parameter or a second report.

However, in my original response, I didn't take into account that you're working with a command or stored proc.  The problem with that is that you can't make parameters for those optional.  So, it looks like you would have to use two separate reports anyways.  Or you could set a default dates of 1/1/1900 for both date params in formulas, make what's now your main report a subreport, and use the formulas as links to the date params in the subreport.  This will only work if there are no subreports in the existing report.

-Dell

[DellSC · Accepted solution]

Actually, thinking about it, there is a way to change the selection criteria for a report when scheduling it in the CMC.  This may be what the user is looking at.  However, this does NOT work for stored procedures or commands, only when you're joining tables together.

-Dell

[Former Member · Accepted solution]

Hi Dell,

Thanks for giving it more thought and providing a definitive answer.

Take care,

Erin