One Prompt for three data items

[Cognos91]

Hi All,

I am working on a report that requires me to build a single prompt that references three other data items, namely variances,

Eg: Three data items are variance 1, variance 2, and variance 3

Now, the prompt should contain values from the above three variances, so when the report is run and a value is selected , say 20%, then it should filter all the three variance values and only those records(for whom variance 1 or 2 or 3 is 20%) would be shown on the report.

Also,if for the same record, two variances contain the same value selected, then that record should be displayed only once and not twice.

Any guidance on this matter would be appreciated.

Thanks.

[MFGF]

Hi,

Try the following:

Code a filter for your report along these lines:

[Variance1] = ?var? or [Variance2] = ?var? or [Variance3] = ?var?

To get a value prompt showing values from all three variance items, do the following.  Add a second query to your report in the Query Explorer and bring in [Variance1], add a third query and bring in [Variance2], and bring in a fourth query, but leave it empty for the moment.  From the Query Explorer, drop a Union object onto the fourth query, then drag Query 2 and Query 3 to feed into the Union.  Then go into Query 4 and bring in [Variance1] from the Union (which will now contain Variance 1 and Variance 2 values).  Repeat this process to union Query 4 with a new query containing [Variance3].  Name the final query 'AllVariances'

Finally, add a prompt page to your report, drag in a value prompt, associate it with the var parameter on the first page of the prompt wizard, then press Finish.  In the properties of the Value prompt, select AllVariances as the query, and the item from this query as the 'Values to use' property value.

Regards,

MF.

[Cognos91]

Hi,

Thanks for your time.

But I still have doubts on using the union queries...

Reason being the three variances are calculated data items associated with one query.
To created different queries for each variance calculated item and then unioning them, would be kind of tedious.

I am not sure how this would be possible or is there any other way that the prompt could be associated with the three variances altogether?

DCognos

[rockytopmark]

Value Prompts get their values from a Query, or static list of values.

If you want values from 3 sources, UNION query is your answer.

Are you sure a Value prompt is really the best solution for what you want?  I mean, think about it... lets say you have a lot of data and there are 60, 70 distinct variance values... you will have a HUGE list to traverse in the prompt control.

Maybe an Edit Box prompt, where user simply types in the variance they wish to run the report with would be more usable?

[MFGF]

Hi,

I'd have to agree with Mark here - especially if you don't want to have to Union queries together.  Is a value prompt absolutely necessary?

Regards,

MF.

[Cognos91]

Nope...A Value prompt isnt really necessary....

thanks for your suggestion...

will try it...

[Cognos91]

Hi Mark and MF,

Mark....I tried using the Edit Box Prompt and it worked without having to union the queries.

But my only question here would be , why cannot we link an edit box prompt to a query as we can do to a value prompt?

Also,

I would like for users to be able to select the values as in 'less than 10%' or 'between 15% and 20%' and so on....and this would have been possible with a value prompt (i tried with value prompt and it took forever to run)....

so in this case, users would be able to input values like -0.5 for -50%, 0.15 for 15%....i want more added functionality ....

how will i be able to use the edit prompt box to add more functionality and give users more options to select prompt values ....

thanks a ton guys!