Cognos Macro - How to get the right results

[morgface]

Hi!

I need a bit of help. I have recently written a Cognos Macro which acts as a prompt for a Impromptu report.

The prompt has six main elements to it. Firstly, it asks for a start date and an end date for the period for which you require the report to be produced for.

Next, the user is expected to chose a value from one (and only one!) dropdown menu.

I have no problem getting the variables into the report itself, it is just that I am not sure how to filter the report based on the value they have chosen from the dropdown menu.

I would really welcome any advice on this, as this is the last element to my report.

Kind regards, and I look forward to hearing from someone soon.

Sarah

 

[morgface]

Hmmmnnnn... just as an aside I think I missed two major points.

"Next, the user is expected to chose a value from one (and only one!) dropdown menu." At this point, I should have added that the user is to chose a value from one (and only one) of four dropdown menus.

These dropdown menus contain values from four different tables that the report can possibly report/filter on. Obviously, this is where my problems are occurring, as I am not sure how to filter?

Sorry, and thanks Sarah

 

[griffindm]

Sarah,

Make sure each of the 4 drop down lists has a default of something like &quot;<None>&quot;. If the user does not select from the list, the return value from the listbox should be 0 or -1. Use a determination of which listbox was used to pass the selected choice to the report, and do an error loop if the user selects data from more than a single listbox.

Hope this helps,

Dave Griffin


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

http://www.decisionsupportgroup.com

 

[dennydm]

hi,
what i would suggest is that you dont try to capture user requirements in the Macro.

What i would do is make an Impromptu report with in-built impromptu promts and run this report via the Cognos Macro.

So what would happen is that a user invokes the macro which in turn triggers off the Impromptu report which in turn prompts the user for Start and Finish dates and also a prompts the user for the other input parameter by displaying a combo box as a picklist. The values in the picklist could either be hard-coded or could be distinct values from a database column.

Denny

 

[CognosProfessional]

How to filter? Just put your prompt in your Filter tab of the report using your Prompt Variables. I dont' understand your quesion of how to filter. Put them in one prompt for each. Right?

CP

 

[griffindm]

Sarah,

I think I understand what you are trying to do, but just to be sure, give us some example of what's IN the four listboxes and how you have the report setup to use them. If you are not sure how to use them, that's fine, and we can give you some suggestions.

Regards,

Dave Griffin


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

http://www.decisionsupportgroup.com

 

[morgface]

Hello,

Thanks for all your responses.

Right, I guess what I am trying to do is... I have a prompt , which I have created within a Cognos Macro.The report is a schedule report that allows the user to see what jobs have been allocated to crews for a certain day.

As part of the filtering, I need the user to be able to select whether they would like to see crews for a particular &quot;District&quot;, OR a particular &quot;Area&quot;, OR a particular &quot;Crew Headquarters&quot; OR a particular &quot;Crew Class&quot;.

The user can only select ONE of these four filter values, which all come from separate tables. I have put in all the error handling, what I am not sure about is how Cognos Impromptu knows which value to filter on? i.e. that the user has selected a District???

Is there anyway, that I can I can specify that if they chose a district then ?districtpromptvalue?=District, and if they chose an Area, that ?Areapromptvalue?=Area etc??

Does that make more sense?

Thanks in advance...

Kind regards,

Sarah

 

[CognosProfessional]

Yes.

First you would have a prompt in your one data item
created for the Prompt column results in your report.
Then, in your query you would have a couple of
prompts that you can use for the second prompts.
Maybe this could work for you...just a thought.
If you need to have it more involved, maybe Sub Reports
work better in a hidden fashion.

if (Prompt column = District) then ( ?DPV? ) else
if (Prompt column = Area ) then ( ?APV? ) else
ect...



CP