Cascading prompts 1

[ChillAPAC]

I have a request to produce a large number of reports and the user's need the ability to choose which date field the report is run for (loss_date, invoice_date, install_date, etc). Without having separate versions of each report is there a way to use cascading prompts to first have the user choose the date field to use then to enter the date range? I've reviewed cascading prompts and I don't see a way to allow them to choose which date field to use. I am currently running Impromptu 6.0 but we are planning on upgrading to 7.0 in the next few months.

Thanks in advance,

Bill

 

[griffindm]

Bill,

Actually, cascading prompts may not be the approach to go for this. What I would recommend is to use a calculated column that contains different database columns based on user prompt input. If Oracle, use the Decode function. Otherwise use the If-Then-Else logic.

In this example, the prompt is called ?filttype?, as is defined as a type-in string prompt with the following message text:

"Enter (L)oss, in(V)oice, or (I)nstall date for filter:"

The comparison date column (filtkey in this example) would be defined as either:

Oracle:

Decode(Upper(?filttype?),'L',loss_dt,'V',invc_dt,'I',inst_dt) ...

Other:

If (Upper(?filttype?) = 'L') then (loss_dt) else if Upper(?filttype?) = 'V') then (invc_dt) else (inst_dt) ...

In the filter of the report, compare the calculated column filtkey to a second type-in date prompt to complete the flexible filter.

Hope this helps,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com

 

[ChillAPAC]

Dave,
It works fine,
Thanks!
Bill

 

[Coglearner]

Hi,

I have a cascading prompt based on the following columns
1.Brand
2.Flavor.

Examples of the values for these columns..

Brand Flavor
------- --------
Coca Cola Diet
Coca Cola Normal
Pepsi Normal
Pepsi Diet


In my report I have filter for flavor,so when I run the report, first it is asking Brand , if I select the brand then it will ask the flavor ..if I select the flavor
it should display only for the brand and flavor which I selected, but it is giving the all the brands which is having the same flavor since I am using only flavor in my filter defn.

Can I capture the brand which I am selecting in the Cascading prompt also in my filter.


In simple, I want to use the Brand also in my filter condition.The value for this Brand will be the same as the cascading prompt Brand.


Please let me know if any work arounds for this..


Thanks
Coglearner

 

[griffindm]

See my response in your primary thread.

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com