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

1. Introduction.
2. Crystal Report on SQL Database Table.
3. Designing Crystal Report
4. Defining Parameter
5. Defining Formula.
6. Group Expert
7. Aggregation.
8. Create Formula for aggregating the key figure values depending on the group.
9. Formula for the Second Group.
10. Formula for the third Group.
11. Headers 
12. Run Crystal Report
13. Output
14. Related Content

Introduction

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

It will show all the table present in that database. Select the table and click on arrow button (>) to move it to selected table area.

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.

Defining Parameter

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

Defining Formula

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 )

Group Expert

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

Aggregation

Create Formula for aggregating the key figure values depending on the group.

Formula for the 1^st Group

Formula for the Second Group

Formula for the third Group

Formula for fourth Group

Headers

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

Output of the crystal report will be:-