Manual Cross tab reports using SAP Crystal reports for enterprise

[nikhil_joy2]

Applies to:

SAP BO crystal reports developers

Summary

This document explains a way to create manual cross tab reports using SAP crystal reports for enterprise. Normal cross tab reports created from cross tab expert lack some features like alerts, sort, drill down etc. So in scenarios where cross tab reports with such features are required, we can go for manual cross tab reports.

Authors: Gayathri Ramanarayanan, Nikhil Joy

Company: Infosys Limited

Created on: 09-Oct-2012

Author's Bio

Gayathri Ramanarayanan:

Gayathri has around 4 years of professional experience in the IT industry and has rich experience in SAP BO suite of products. She is a Technology Analyst working in Infosys

Nikhil Joy:

Nikhil has 2 years of professional experience and has expertise in SAP BW and SAP BO. He is a Systems Engineer in Infosys and currently implementing POCs on SAP HANA.

Table of Contents

• Introduction
• Steps to create a Manual Cross-Tab report
• Why do we need manual cross tab reports? - An example
• Challenges with manual cross tab report
• References

• Introduction

SAP Crystal reports for Enterprise already has a cross tab expert using which we can create well formatted cross tab reports. But some features will not be available while we create cross tab reports through the expert. For example, in scenarios like if we need to highlight a particular cell having value greater than a particular amount (alerts) or if we want to alter the order of the columns by a value selected at runtime (sorting). Some other necessary functionality like ‘Drill down’ is also not possible with the cross tab expert.

In such scenarios we will go for Manual cross tab also called as mock cross tab.

• Steps to create a Manual Cross-Tab report

Let’s create a simple manual cross tab report using efashion data. The below table illustration explains the crosstab report that we want. 

The requirement is to find the sum of sales revenue for a particular store for particular months selected by the user.

	Sales revenue(KF)  for User Selected  Month Name	Sales revenue(KF) for User (Selected  Month + 1) Name	Sales revenue(KF) for User (Selected  Month + 2) Name
Store name 1
Store name 2

Create the first column by displaying the store name.  

  

Td  This will be the data in row for our cross tab report.

       

Create a prompt ‘Month Selection’ to accept user selection of month.

  

Then create a formula ‘Column 1’ to populate Key figure (Here sales revenue of store) Corresponding to the User selected month.

Use the below formula in formula editor of ‘Column 1’.

     if {eFashion.Time period\Month}= Tonumber({?Month selection}) then {eFashion.Measures\Sales revenue}

   

Similarly create further formulas for the other columns as well. Here we are showing three columns.So we have created three formulas. 'Column 1','Column 2' and 'Column 3' to populate those columns. In manual cross tab reports, the number of columns would be fixed.

Drag and drop them to the 'Body' section of the report one beside the other as columns.

  

Now the report will look like below screenshot according to the prompt value(Here - Month value) selected by the user. Here we have selected January . So according to the formula we have defined, sales revenue for selected month and next two months will be displayed. Here sales revenue for January, February and March will be displayed.

  

Here the columns have formula name as the default header . For creating dynamic headers for each column according to the month selected by the user , we use separate formulas.

For e.g.: Heading column 1= "Sales Revenue for " +  MonthName (Tonumber({?Month selection}))

Similary create further formulas for other headings also. Here we have created formulas 'heading column 1', 'heading column 2' and 'heading column 3' for three columns.

Delete the existing headers in the 'Page Header' section of the report. Drag and drop the corresponding heading formulas to 'page header' of the report.

   

        Now group the report by Store name as shown below.

Then add the sum of each column for each group to the group footer. This is done by 'Insert'->Total

Hide group header, body and other irrelevant sections and drag and drop store name to the group footer.

Add lines from insert tab to make the manual cross tab report bordered. Preview report. You can see your manual cross tab report showing sales revenue for January, February and March for each store.

• Why do we need manual cross tab reports? - An example

As mentioned in the introduction, reports created using cross-tab expert cannot display alerts, cannot be used for drill down and cannot be sorted dynamically. Here we are giving a particular condition in our manual cross tab report to show alert when the key figure value is less than a particular value.

Here we have given a condition to highlight sales revenue values less than 95,000.

        

These features are not available in normal cross tab reports. So in scenarios to use cross tab reports where highlighting values, drilling down, sorting etc are important, we can go for manual cross tab reports.

•      Challenges with manual cross tab reports

• This technique is simpler when used for reports having lesser columns. When the numbers of columns are large, it becomes very tedious to design and maintain them
• Also, since the numbers of columns are fixed, any changes caused because of the user selection will leave unwanted blank spaces which become difficult to handle.
• In order to develop a cross-tab like report using ‘Manual crosstab or Mock crosstab’, we need to manually add the required number of columns (that holds key figures) beside the main column (that holds the characteristic).

•       References

• scn.sap.com