Conditional Columns

[stewm]

Hello,

I have to convert several reports that users currently get as Excel type reports. The user is currently given a series of prompts which determine the report they will get. This is done in Primis Report Manager. Some prompts are are straight forward such as simple data range prompts that could be done in Impromptu. However some prompts actually prompt for a particular column that will exist or not exist in the report.

How do you prompt a user for columns to be included or not included in Impromptu?

Mark

 

[drlex]

Mark,
You could use conditional formatting to hide columns that are not required, using the prompt value as the condition. (for the column headings, it would seem that rather than Hidden, the format needs to be white text). However, this just blanks the columns and does not remove them from the report, which could cause some layout complaints from the users.
If the range of column options do not result in too many permutations, could you not run a specific report determined by user choice?
lex

 

[griffindm]

Mark,

Actually, you can do this with straight conditional statements in the column calculation. I create reports with multiple columns defined in the query. I then create 'display columns', determined by prompt responses from the end user. It can get tricky and performance can suffer if the column choices cross multiple tables. By in large the technique works well.

You can also use this technique to have a single report show different information on different sections of the report. The calculation can be driven by the value of higher-level groupings. I create single project support reports that show hours by employee in the labor section of the report, but show transaction detail and cost in the 'Other Direct Cost' section. You can control column content and formatting. The only limitation I can't work around is to change column size, as I do not use dynamic sizing for reports destined for printed output.

Lastly, you can also create 'pockets' for selective display of information based on higher-level groupings by using dummy group footers, that only appear for certain values. This avoids excessive 'blank space' in the report where you suppress display, but the objects still take up space in the output.

Hope this helps,

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

 

[stewm]

Drlex, in a couple of instances there is too many permutations it would seem. One report, the user currently can pick up to 15 different dates to include in the report. It would be unfortunate to have to write 15 of almost the same report.

Mark

 

[drlex]

Mark,
Dave's suggestion sounds best for your needs, but note both his comment about performance hits and also impromptu's propensity to "bomb-out" when grouping, totalling and formatting gets involved. I experimented with the high & low groupings and formatting on the relevant headers and footers, but found I could live with the basic version versus the slow-down. Dave's method does allow for dynamic column headers, which may be of value to you and your audience.
The multiple report suggestion would seem reasonable only if you could manage with around a dozen reports; after all, they are only versions of one main report which you already have. I assumed you had some users who wanted to see a date column, for example, so that a consideration of dates was the presence or absence of a column (with any necessary date-trapping in the filter being switched on or off according to prompt).
lex