Using case statement in series of a bar chart

[nancysinghal]

Hi All,

Basically, I have 3 cascading prompts in my dashboard: Region, County and Office.
Based on user selection, it shows 4 charts:
1)Statewide, ie. default selection as u suggested which will display all regions on the chart.
2) If user selects some particular region, chart should display data for that region only and legend should show all counties corresponding to that region.
3) If user selects some particular county, chart should display data for that county only and legend should show all offices corresponding to that county.
4)If user selects some office, that office data should be displayed on chart.

I have achieved this using four charts and nested data decks. But performance has got really poor due to multiple charts.

I am trying to use 2 charts instead of four to achieve this:
I used a DI in series with a case stmt as below:
CASE WHEN
?pRegion? IS NOT NULL AND ?pCounty? is null
THEN
[County]
WHEN 
?pCounty? IS NOT NULL and ?pOffice? is null
THEN
[Office]
END

This throws an error that says: case statement should return a single value , not a set of members.

Please suggest some way around, or is this scenario is feasible at all.

Regards,
Nancy

[MFGF]

Hi All,

Basically, I have 3 cascading prompts in my dashboard: Region, County and Office.
Based on user selection, it shows 4 charts:
1)Statewide, ie. default selection as u suggested which will display all regions on the chart.
2) If user selects some particular region, chart should display data for that region only and legend should show all counties corresponding to that region.
3) If user selects some particular county, chart should display data for that county only and legend should show all offices corresponding to that county.
4)If user selects some office, that office data should be displayed on chart.

I have achieved this using four charts and nested data decks. But performance has got really poor due to multiple charts.

I am trying to use 2 charts instead of four to achieve this:
I used a DI in series with a case stmt as below:
CASE WHEN
?pRegion? IS NOT NULL AND ?pCounty? is null
THEN
[County]
WHEN 
?pCounty? IS NOT NULL and ?pOffice? is null
THEN
[Office]
END

This throws an error that says: case statement should return a single value , not a set of members.

Please suggest some way around, or is this scenario is feasible at all.

Regards,
Nancy

Hi,

Is this an active report? You're posting in the Active Reports board, so I assume it is? In which case, are you really using prompts or are you using Active Report Controls? Referring to parameters such as ?pRegion? would pertain to prompts but not to Active Report Controls, so I'm a little confused about what exactly you're doing here. While you *can* add prompts to an Active Report, they are not utilised within the rendered report - they are fulfilled when the report output is being generated, and would limit the data being included in the rendered report.

Moving on... Why are you nesting data decks? Wouldn't you just have a single data deck driven by region, county and office? Is there a reason for nesting?

An alternative to consider is using visualizations rather than charts, and filtering the visualizations, perhaps? When you use charts they are pre-rendered as static png images within the output, so can quickly bloat up the rendered report size and make it laggy. Visualizations can be re-rendered (and therefore interactively filtered) within the active report output, so can reduce the size of the output compared with charts.

Cheers!

MF.

[nancysinghal]

Hi MFGF,

Thanks for the reply.

1)Yes, it is an active report, and  I have used Data drop down list, where  ?pRegion? being the parameter in the main query. Prompts are not used at all.
2)I have attached the screenshot of the requirement.
As shown in image,
by default, the chart should display all regions data (Statewide view).
On selecting a particular region, chart should display all the counties corresponding to that particular region.
On selecting a particular county, chart should display all the offices corresponding to that particular county.
On selecting a particular office, data should be displayed only for that office on the chart.

Also, only one chart should be displayed at a time, that is why, I used nested decks.

3) I am afraid, I do not know about visualisations thing. Can you please throw some light on that, what is it basically, and, how we can use it?

Thanks & Regards,
Nancy Singhal

[MFGF]

Hi MFGF,

Thanks for the reply.

1)Yes, it is an active report, and  I have used Data drop down list, where  ?pRegion? being the parameter in the main query. Prompts are not used at all.
2)I have attached the screenshot of the requirement.
As shown in image,
by default, the chart should display all regions data (Statewide view).
On selecting a particular region, chart should display all the counties corresponding to that particular region.
On selecting a particular county, chart should display all the offices corresponding to that particular county.
On selecting a particular office, data should be displayed only for that office on the chart.

Also, only one chart should be displayed at a time, that is why, I used nested decks.

3) I am afraid, I do not know about visualisations thing. Can you please throw some light on that, what is it basically, and, how we can use it?

Thanks & Regards,
Nancy Singhal

You need to be very careful here with how you name your active report variables. Anything in a report that is prefixed and suffixed with ? (eg ?pRegion?) would be interpreted by the report as a prompt parameter in many circumstances. I would strongly suggest you remove all ?s from your variable names.

If you are using a data deck, only one card is ever displayed at any one moment. As long as you have one chart defined and master/detailed, you will only see that one chart on the selected card. You can drive a data deck on multiple items, and end up with cards that correspond to each data intersection.

Visualizations are RAVE technology. As long as you are on Cognos BI 10.2.1 and above, you can download and import them into your Cognos instance and use them in reports, including Active Reports.

https://www.ironsidegroup.com/2013/07/09/introduction-to-using-rave-visualizations-in-cognos-10-2-1-active-report/

Cheers!

MF.

[nancysinghal]

Sure MFGF, I will take care of that going forward.
Thankyou so much :)

Regards,
Nancy