(Original Blog Post from Florian Glanzmannr, coresuite cube expert, posted at the 11.09.2013)
In the past months coresuite cube has been enhanced with useful content such as customer contact details like addresses, phone numbers, e-mail addresses. Furthermore we added the possibility to include UDF's to your cube reports.
Since these details are not included in the cube's default drill through actions and their usage in normal pivot table reports might not be ideal, we would like to present you an alternative approach which enables you to build personalized lists (e.g. customer mailing lists) or even lists which contain UDF's as an alternative to Excel's standard drill through results.
- coresuite cube
- Microsoft Excel 2007 or later
You can either use a previously created Excel report and add another tab which is connected to the cube or you can start from scratch.
If you whish to create a new report proceed as follows:
- Open a new Microsoft Excel worksheet and go to Data > From Other Sources > Microsoft Analysis Services
- Follow the wizard which connects your worksheet to coresuite cube
First of all add the required fields to the pivot table. This example was built using the coresuite sales cube. We added the following regular dimension attributes:
- Contact Person
- Contact Person E-Mail
- Contact Person Phone Number
So far, this is nothing else than a standard pivot table report. Feel free to add additional attributes, filters or even measures such as totals, cost, etc.
Your final pivot table should look like this:
You are now ready to navigate to the Design tab which can be found in the PIVOTTABLE TOOLS section in the Excel main toolbar.
Navigate to the section PIVOTTABLE TOOLS. On the left hand corner of the Excel toolbar you can find three sections which are relevant for your list report.
- Grand totals
- Report Layout
Left-click the Report Layout button and choose Show in Tabular Form.
This is what the report should currently look like:
Navigate back to PIVOTTABLE TOOLS and left-click the Report Layout button and choose Repeat all Item Labels.
This is the expected outcome:
Navigate back to PIVOTTABLE TOOLS and left-click the Grand Totals button and choose Off for Rows and Columns (left hand picture).
Afterwards left-click the Subtotals button and choose Do not show Subtotals (right hand picture).
You are almost done. The report has finally gotten its intended layout. This is what it should look like:
Once converted to a tabular layout, the report can be modified according to your personal desires.
It is for example possible to add or remove fields (e.g. UDF Premium BP in the example underneath) or to change the table's color scheme, etc. without messing up the report's general layout.
Feel free to experiment and find the final look and feel of your report.
The possibility to transform standard pivot table reports to a tabular format does once more prove the unreached flexibility of coresuite cube in combination with Microsoft Excel.
Possible fields of use:
- Mailing lists
- Top / Bottom n customer lists
- Personalized drill trough actions which include UDF's
We hope that our tips and tricks help you to satisfy your customers. Stay tuned.