Dynmaic Cascading Prompts - Populate parameter prompt with only values in date range

[Former Member]

Hi Everyone,

I want to create a dynamic cascading prompt in CR 2011. I should have the 'date Range' and then the 'Test List' as prompts. Theres's a column "Date" in the table- which has the date values. I want to create a prompt where I can set up a Date Range(TO and FROM date from CalendaR) and Then according to that Date Range I should get the Particular Test Lists ranging in that date range. How do I achieve that? Please help me out.

Thank you.

[patrick_genest · Accepted solution]

Hi,

To have a list of values for the "Particular Tests: based on a date range you can create a date range in the Business View, then create a List of Values (LOV) of that Business View for the "Particluar Tests"

Below are the general steps on how to do so:

== In the Business View Manager ==

1. In the Business View Manager, create a Data Connection to your data source.

2. Create a Data Foundation based off the Data Connection created in step 1.

3. In the Data Foundation, create a data range parameter.

4. Then create a filter on the data range.

5. Apply rights to the filter. ( If you do not, then the filter will not apply, and therefore you will not be prompted by the data range )

6. Create a Business Element on the Data Foundation

7. Create a Business View off the Business Element created.

8. Create a List of Values off the Business View, and select the database fields that you want a List of Values.

== In Crystal Reports ==

9. In Crystal Reports, create a report

10. Connect to the BusinessObjects Enterprise repository.

11. Create a new parameter, and set your parameter to be dynamic.

12. In the parameter window, then select the list of values (LOV) you created.

13. Add your parameter to the report Record Selection formula.

Now, when refreshing the report it will first prompt you for the data range, which is coming from the filter in the data foundation, then the second prompt will be your parameter with a dynamic list of values based on that date range.

Hope this help!

Patrick

[abhilash_kumar · Accepted solution]

Oh yes, achievable using Business View Manager too !

- Abhilash

[Former Member · Accepted solution]

Thanks a lot Patrick !

[Former Member · Accepted solution]

Abhilash,

I had to use the method of having the  Date Range parameter in the SQL command ,as we wer building reports from a SQL command and not a Data Source. But the prob is its prompting for the Date Ranges twice. One separately and one again with the Test List. Is that the way it does or is there a way to make it prompt only once for the Date Range?

Thanks

[abhilash_kumar · Accepted solution]

Hi Sridhar,

Yes, it does prompt for the datasource twice, unfortunately.

This is the only workaround I've been able to find until now.

- Abhilash

[Former Member · Accepted solution]

Thanks for the response Abhilash! Hope they do get in some built in options.

[Former Member · Accepted solution]

Hi Abhilash,

I am sorry. I think theres a typo in ur reply so am confused here. Are you telling it will prompt for Date Ranges twice ? One separately and one again with the TestList prompt ? Because I have to enter the Date Ranges twice.

Thanks

[abhilash_kumar · Accepted solution]

Hi Sridhar,

I'm sorry, I did not mean to say 'datasource'.

The point is, you'll get prompted for the 'Date Ranges' twice and I've tried every way to see if something else works, unfortunately that's the closest I've come so far.

Have you put in the Enhancement at the Ideas Place yet?

- Abhilash

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member · Accepted solution]

Abhilash,

Thanks for clearing that up for me. And Yes Now I am sure going to put up that Enhancement Request.

[abhilash_kumar]

Hi Sridhar,

You cannot create Dynamic Cascading Prompts on Date Ranges.

The only workaround is to create a command object with the 'start date' and 'end date' as parameters.

This will bring in a filtered result set in CR. Then, you can create a dynamic parameter for the 'TestList' from the Field Explorer.

So, everytime you refresh the report, you will first be prompted for the dates and then you'll get the option to select the TestList. This is not exactly as you would like it to be, however it's still another way to achieve this.

I would also suggest that you create an Enhancement Request at http://ideas.sap.com.

- Abhilash

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member]

Hi Abhilash,

Thanks a lot for the reply. By "create a command object with the 'start date' and 'end date' as parameters." do you mean something like this

Select table.`TestList`

From table

where table.`date` >= {?start} and

table.`date` < {?end}+1.

If not, Can you be more specific as to what I should create as Command object please?

Thanks.

[abhilash_kumar]

Hi Sridhar,

Yes, that is correct.

And, {?start} and {?end} are the parameters created in the Command Object too.

- Abhilash

[Former Member]

Thanks for your inputs too Abhilash.

[nscheaffer]

Abhilash,

I am trying to do this same sort of thing.  I put the begin and end date parameters in the command as you suggested.

I have DCP based upon the Branch Number (BRNCH_NBR - first screenshot below) and then Account Number (ACCT_NBR - second screen shot below).

The problem is that I seem to be getting prompted for the dates twice.  First I think for the DCP given the "BRNCH_NBR > ACCT_NBR" labels on the right side...

And then I get prompted for the dates again which are think are for the SQL in the command...

Is there a way to avoid this duplicate prompt for the dates?  From a technical standpoint I can understand what is going on, but I don't think my users will be so understanding.  Any suggestions?

Thanks,

Noel

[patrick_genest]

Hi,

It will prompt you twice if you use any of the command object database fields on the report.

The command object should only be used to generated the List of Values, and therfore, it should only be used in the dynamic parameter.

To only get prompted once, make sure:

1.The command object is not linked to other tables on the report

2. No objects from the command object should be on the report.

3. The parameter of the command object should not be on the report eirther.

4. Ensure you have the latest update of the following versions of Crystal Reports

    - XI R2,

    - 2008

    or Crystal Reports 2011

Once all of the above condition are met, it will only prompt you once.

Note that if you are going to use the report in Crystal Reports, then this is the way to go.

But if you are going to use the report in BusinessObjects Enterprise, then create the List of Values in the Business View Manager, like I described in my other post.

Hope this help!

Patrick

Patrick

[abhilash_kumar]

Hi Noel,

Yes, that is what happens with me too.

In fact, it's the command object that prompts first; first to bring in records for the Prompt and then to get the records for the report.

I have not found a way around this from the reporting side yet.

If Sridhar has put in an Enhancement Request, please support it by Voting Up!

- Abhilash

[Former Member]

Hi Noel,

Atlast found a way to do it. Used Patricks idea to do it. Create the Dynamic Cascading prompts without having the date parameters created in the report(remove them even from the command) and publish the report it into the BI server. Once you do this all the elements like Dataconnection, datafoundation, business view, etc will be created for that particular Dynamic prompt automatically when you publish the report to BI server.

Close the report(very important).

Log into the Business view manager. Go and find the DataFoundation element for the Dynamic Cascading prompt you created.

Open the data foundation, create a Date Range Parameter.

create a filter to that date range something like this {Command.field}={?Date Range}. Save it.

Right click the filter and say Add rights. Like Patrick said this is very important orlese it wont prompt for date range.

After adding that , save it again it will update all the elements for that dynamic cascading prompt. Now go and run the report and make sure the parameter in the report is pointing out to the same prompt which you updated now. And you will see it working like a magic.

Hope it helps.

--TC--