Jump to content


Willkommen im Coresystems Community Forum

Dieses Forum wird nicht mehr weiter betrieben.
Sollten Sie eine Lösung für eine spezifische Aufgabenstellung suchen wenden Sie sich bitte an unseren Support: helpdesk.coresystems.ch.
Für allgemeine Produktinformationen kontaktieren Sie unser Sales-Team: sales@coresystems.ch

Welcome at the coresystems community forum

This forum is no longer being maintained.
If you need a specific solution please contact our support: helpdesk.coresystems.ch.
For general product information do not hesitate to contact our sales team: sales@coresystems.ch

Build personalized Excel Lists with coresuite cube

coresuite cube personalized Excel Lists coresuite country package

  • Please log in to reply
No replies to this topic

#1 Guest_kau_*

Guest_kau_*
  • Guests

Posted 7 May 2015 - 09:35

(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.

What_it_could_look_like_3.png
Prerequisites
  • coresuite cube
  • Microsoft Excel 2007 or later
Step 1: Establish a connection to coresuite cube

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
Connect_to_SSAS_1.png
Step 2: Choose attributes and measures and add them to report

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:

  • Customer
  • 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:

Add_more_fields_Filters_Measures.png

You are now ready to navigate to the Design tab which can be found in the PIVOTTABLE TOOLS section in the Excel main toolbar.

Switch_to_Design.png
Step 3: Transform your pivot table report to a tabluar format

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.

  • Subtotals
  • Grand totals
  • Report Layout

Left-click the Report Layout button and choose Show in Tabular Form.

Show_Report_in_Tabular_Form.png

This is what the report should currently look like:

Step_1_-_Report_in_Tabular_Form_cut_1.pn
Step 4: Show all item labels

Navigate back to PIVOTTABLE TOOLS and left-click the Report Layout button and choose Repeat all Item Labels.

Repeat_all_Item_Labels.png

This is the expected outcome:

Step_2_-_All_Item_Labels_repeated_cut.pn
Step 5: Disable Totals and Sub Totals

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).

Disable_Grand_Totals_and_Subtotals.png

You are almost done. The report has finally gotten its intended layout. This is what it should look like:

Step_3_-_Report_without_totals.png
Step 5: Format your report, add or remove attributes

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.

What_it_could_look_like_2.png
Conclusion

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
  • etc.

We hope that our tips and tricks help you to satisfy your customers. Stay tuned.

 

 







Also tagged with one or more of these keywords: coresuite cube, personalized Excel Lists, coresuite country package

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users