Toggle between Measures on a Dashboard

[adam_mc]

CA 11.1.2
Chrome

I am building a dashboard based on at least one data set and I would to create a visualization that allows "toggling" between a number of measures such that the business user won't have to see a distinct visualization for each measure.

In my current data set I have columns like the following:
COL1   COL2   COL3   TOTAL$   TOTAL_UNITS   COUNT

I would like to create a single Pie Chart which would display TOTAL$ by COL1 and then allow toggle to show TOTAL_UNITS by COL1 and then COUNT by COL1.
Is  this possible?

As a work around I have built another data set with columns:
COL1   COL2   COL3   MEASURE_TYPE   TOTAL$   TOTAL_UNITS   COUNT

Where MEASURE_TYPE indicates which measure is populated in this row.
I then use this MEASURE_TYPE as a selector in a linked table visualization.

The problem with this is that I now have to have distinct rows for each measure which is potentially increasing the number of rows in the data set by the number of measures I wish to to this functionality with.

Any thoughts would be gratefully appreciated.
Thanks in advance,
Adam.

[VizWiz]

Hi Adam,

I think that was an interesting idea adding a column for the measure type. Having said that, are you using a spreadsheet? If so and you're worried about the data growing too large, then you may consider moving to a database and modelling this out differently instead. I personally think spreadsheets are okay for smaller sets of data, but using a database is really a better long-term solution. This is probably not the answer you wanted, but just my thoughts.

Thanks!
VizWiz

[adam_mc]

VizWiz...
Thanks for your feedback.

It's not using a spreadsheet.
I am creating a Cognos Data Set that is produced in a separate process with a scheduled refresh - I am then using that as the source for the Dashboard.
I want to use a Data Set because I can control and schedule a refresh of the data directly via Cognos without any DBA involvement and using my approved Cognos packages.

My concerns about the size of the Data Set multiplying is management of the Content Store (where Data Sets are stored) and performance of the Dashboard itself when using a Data Set with many more rows.

Thanks again,
Adam. 

[srijack1102]

Hi
I ve the same requirement and reached the same conclusion that measure columns needs to be converted to rows. ( Unpivot/denormalize).

But with this table is going to grow unexpectedly big

let say that there are 15 measures , each row will now be 15 rows so that table is 15X , dont think this is a good solution for table with many measures.

Please let us know if there is any way for dynamically changing measures in a dashboard

thanks

[adam_mc]

I have create an IBM Idea (previously RFE) for this:

https://ibm-data-and-ai.ideas.aha.io/ideas/CAOP-I-2305

Please vote for this if you think it is relevant to you.
Thanks in advance,
Adam.