[DATASET] & Prompts 1

[DrSimon]

Dear All
Using a macro, I need to use a report with a prompt as part of the filter ('not in [DATASET]') in another report with a prompt.

The only way I can see to do it is to open the 1st report with its prompt and save it as a hotfile. Then use the hotfile as the source for an intermediary report and use that as the [DATASET] for the final report.

Is this over complicated and is there a simpler way of doing it?

Thanks

Simon Rouse

 

[griffindm]

Simon,

Why not set the first report up as a report picklist prompt? It can have it's own prompts, which will be seen first, and then filter out in the final report still using a 'Not In' clause. This avoids hotfiles and macro calls entirely. The technique is called a cascading report picklist, and it works well.

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"

http://www.decisionsupportgroup.com

 

[DrSimon]

Thanks Dave
I had played with that idea but the problem is that I need to pass the same prompt data to the second report as the first.
What I'm doing is to find staff who have not attended certain training courses. The first report finds those who have attended in the appropriate timeframe, the second identifies those who should have attended and uses 'not in [DATASET]' to exclude those who have. Once that has been done there is no longer a way to identify the course that they didn't attend (there can't be a join to nothing!), so I was using prompts within a macro to insert the heading.

I've used cascading prompts, but couldn't see how it would work in this case. I could of course make the user enter the second report heading, but I like things to be more robust than that and cope with people whose IQs don't always get into treble figures.

Simon

 

[griffindm]

Simon,

You might want to have the first report appear a second time in the filter, filtering the second report by the class taken. The filter would look like:

Class = ?GetClass[fromRpt1]? and User Not In (?GetUser[fromRpt1]? ...

This should work, but may have two issues: The prompt may appear a second time (I don't think it will, but I haven't checked), and more importantly, if the first report returns no rows (i.e. no one has already taken the class), there would be no data returned for the Class filter from the first report either.

Let me know if this might work. I will continue to ruminate on the issues in the meantime.

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"

http://www.decisionsupportgroup.com

 

[DrSimon]

Dave

I think I did it right but I got 2 prompts as you thought. Please don't spend too long thinking about it unless you're really interested. I have a solution of sorts.

Thanks

Simon