Currently Being Moderated

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 :

me.jpg

 

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.

1.jpeg

A wizard will pop up asking you create new connection.

Under new connection, select ODBC(RDO)

1.jpeg

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

1.jpeg

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.

1.jpeg

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.1.jpeg

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 1.jpeg
  • 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

      1.jpeg

Defining Formula

Create a formula which will give the size of Parameter’s array.

Right Click on Formula Fields and select New

1.jpeg

Formula, which gives the size of array

1.jpeg

Create another formula which will maintain the order of the select characteristic information

1.jpeg

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

1.jpeg

Do the grouping in the following way

1.jpeg

Design will become like this

1.jpeg

Aggregation

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

Formula for the 1st Group

1.jpeg

Formula for the Second Group

1.jpeg

Formula for the third Group

1.jpeg

Formula for fourth Group

1.jpeg

Headers

Create formula for Header Characteristics and Key figures

Header for First Group

1.jpeg

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

1.jpeg

Output

Output of the crystal report will be:-

Comments

Actions

Filter Blog

By author:
By date:
By tag: