Group Measures Under Headings for Export

NatalieAnne_Botha
1 yr ago
2replies
Answered

Good afternoon,

We have a scenario where a client is exporting a grid to excel. The real scenario has many dimensions and measures on the grid. I have created a small example using the sample demo data:

Our client would like to have a heading that groups some of the measures together when exporting to excel. So in this example, we would like Cost & Expenses grouped under a heading called "Expense Measures" and Sales & Quantity under "Sales Measures":

I know the above looks minor, but imagine we had at least 20 measures on the grid. They are able to do this in their current tool by adding a text field that also gets exported to excel.

I've tried measure lists, but I'm unable to get the name of the measure lists to show in the discovery above the measures.

Does anyone have a creative or out the box idea for this?

Thanks,
Natalie

export

- Senior Director of Product
- Ian_Macdonald
- 1 yr ago
- Reported - view
Hi Natalie,

This is a creative, out of the box hack, but that's what you asked for!

Find a dimension in your model that has few members, in this case I'm using the Channel dimension.

Create two custom members, one called Expense Measures and one called Sales Measures. Make them the aggregate of all the members of the host dimension, i.e., the aggregate in this case of Distributor, Online and Store:

Construct your grid using the Channel dimension in the columns above the Measures and selecting the two custom members from above:

Use Eliminations to remove Sales and Units from the Expense Measures member and Cost and Expense from the Sales Measures member:

Then Export to Excel, checking the "Merge Common Cells" option:

Resulting in:

Hope that helps.

Ian
- NatalieAnne_Botha
- 1 yr ago
- Reported - view
Thanks Ian - that is a very creative solution. I will give it a try.

Thanks,

Natalie