Applies to: SAP BO (Crystal Report)
Summary : This Article demonstrates, how to provide Drill down feature in SAP BO Crystal Report and also provide dynamic grouping in Crystal Report.
Author(s): Obaidullah Shaikh
Company: AG Technologies
Created on: 21 March 2012
Author Bio :
Obaidullah shaikh is a SAP BI/BOBJ Consultant with AG Technologies. He has good skill in technical areas (ABAP) and he has experience of multiple custome reports development and has experience of Migration/upgradation/Audit projects as well.
Table of Contents
- Crystal Report on SQL Database Table.
- Designing Crystal Report
- Defining Parameter
- Defining Formula.
- Group Expert
- Create Formula for aggregating the key figure values depending on the group.
- Formula for the Second Group.
- Formula for the third Group.
- Run Crystal Report
- Related Content
This Article demonstrates, how to remove or insert the characteristic information (Material, Customer, Vendor, Plant, Date…etc) in the Crystal Report at run time. And also how to change the order of those characteristics information.
Every SAP BI consultant knows the dynamic drilldown feature of SAP BI Reporting tool (Business Explorer). That same feature we can implement in Crystal report as well using Dynamic grouping. We can also do the aggregation of KPIs according to Characteristics Objects.
Crystal Report on SQL Database Table
Here I will create a crystal based on SQL Table using ODBC Connection.
Open the Crystal Report & go to
- Menu File
- Standard Report
Or press Ctrl + N for creating new report.
A wizard will pop up asking you create new connection.
Under new connection, select ODBC(RDO)
Once you click on (+), it will show all the available ODBC connection. Make sure, you need to create the ODBC connection first before doing this from Data source (ODBC) from Administrative tool in Control panel
I have created a DSN SQL, Select the data source name (DSN) and click on Next
It will ask you the /username/password/Database
Enter the details and click on next.
Click on Next and then click on finish
Designing Crystal Report
Here in this table, I have characteristics information like Debitor, Company code, Document date, and Document number. So I will drilldown these characteristics in report.
First define static parameter for dynamic selection of characteristic information
- Go to Field Explorer
- Right click on Parameter fields and select New
- Enter the name of the parameter and values (Doc_num, Doc_date, Debitor, Comp_code) manually and also select True for allow multiple value from value option below
Create a formula which will give the size of Parameter’s array.
Right Click on Formula Fields and select New
Formula, which gives the size of array
Create another formula which will maintain the order of the select characteristic information
Similarly create formula for every index of array(in this case I have maximum four characteristic, so I need to create four formula )
Then do the grouping of characteristics
Go to Menu Report and select Group Expert
Do the grouping in the following way
Design will become like this
Create Formula for aggregating the key figure values depending on the group.
Formula for the 1st Group
Formula for the Second Group
Formula for the third Group
Formula for fourth Group
Create formula for Header Characteristics and Key figures
Header for First Group
Similarly for second . third and fourth, Just index will change (instead of 1, index will be 2 , 3 , 4…) and arraysize also.
Run Crystal Report
Run the Crystal Report (Refresh the Report) and select Characteristic info object you want in the Report
Output of the crystal report will be:-