cancel
Showing results for 
Search instead for 
Did you mean: 

How suppress criteria tabs when exporting reports to Excel?

cgwaters
Participant
0 Kudos

My users have many AOLAP reports that use HANA calculation views as their data source. When these users export the reports to Excel, the resulting Excel file contains twice as many tabs as are present on the reports. For each tab on the AOLAP report, the Excel file contains an accompanying tab with a "+" suffix in the tab name (e.g., "Customers", "Customers+", "Vendors", "Vendors+", etc.); this accompanying tab lists the criteria (e.g., start date, end date, etc.) the user selected upon loading the report. Is there a way to suppress these criteria tabs...or perhaps include only one such tab, since the criteria is the same for the entire report and, therefore, doesn't need to be repeated for every tab?

Also, the 'Export to XLSX' dialog box that appears when the user presses the 'Export to Excel' button is confusing:

  • It displays the names of all the tabs in the report twice, in a parent/child relationship. When either the parent or child checkbox is selected or de-selected, the child or parent is also selected or de-selected. What is the purpose of this?
  • Selecting or de-selecting the 'Repeat outer header members' checkbox doesn't seem to have any effect upon the resulting export....or the selection and de-selection described above. What is its purpose?

Accepted Solutions (0)

Answers (1)

Answers (1)

I_MCA
Employee
Employee
0 Kudos

Hello Chris

Thanks for the interesting questions.

The sheets with the "+" suffix indicate the prompt values (variables) that are used for the analysis. For some customers there are important to provide context, for other customers they are irrelevant. This is why we export this information to a separate sheet. You can now optionally copy/paste the prompt values to the sheet containing the analysis, or you can simply right click and delete the "+" sheet. (<CTRL>+click will allow you to select multiple sheets and delete them all at once).

From your screenshot it appears that all the sheets are shown twice in a parent/child relationship, but that is only because how you have chosen to name your analyses and sheets.

In your example your AOLAP workspace has 4 separate sheets, each one containing a single crosstab (analysis). The analysis and sheet have both been given the same name.

AOLAP can have up to four analyses (crosstabs and charts) per sheet and as many sheets as you want. Below is a typical example (with generic names):

One thing you might consider doing is moving all four analyses onto one sheet in AOLAP. You can easily do this using the Copy and Paste options. Thus you have one sheet called "Organization" (for example) which contains 4 crosstabs; WW, Business, Business Region and Country. Now you get to see all information at a glance on one sheet, plus each crosstab can be expanded to full screen for closer viewing then minimised again. When you export to Excel, clicking the "Organization" sheet will select all 4 crosstabs whereas currently you have to select 4 sheets to get all four crosstabs. The actual export to excel however will still be exactly the same, with each crosstab displayed on a separate Excel sheet.

The "Repeat Outer Headers" option only makes a difference when you have stacked hierarchies. For example, this is the default output...

...and this is the output when "Repeat Outer Headers" is checked...

The former is easier to read, but the latter is useful if your export is then being used as the data source for another tool.

I hope this helps.

Regards