Implementing Drill Down...

[former_member189979]

Dear All,

I'm working on SAP Crystal Reports for Enterprise 4.0 SP 6 Patch 2 and my goal is to design a Sales Review report using this tool. My Data Source for the analysis will be Universe.unx. Following is my scenario for the report:

1. First Level: National Sale: ( it can be visualized using Column Chart (Quantity will be analysed against time (Calendar year/month))

2. Second Level: Regional Sales: (Quantity will be analysed against time (Calendar year/month))

3. Third Level: Zonal Sales: (Quantity will be analysed against time (Calendar year/month))

4. Fourth Level: Customer Sales: (Quantity will be analysed against time (Calendar year/month))

National Sale and Regional Sales should show on first page and when a user click on any Regional Sales Chart it will show next level Zonal Sales of the Regions and on Zonal Sales Chart when user click on any chart of Zonal Sale it should take the user to final level of Customer Sales of selected Zone.

1. How can I accomplish this goal?

2. Do I need to use drill down? if yes then how?

3. How the queries with prompts or same prompts be linked or provided with prompts values?

4. Should I use column chart for this analysis or should other objects be used?

I will appreciate your reply a lot!

Many Thanks!!!

Tariq Ashraf

[Former Member · Accepted solution]

Hi Tariq Ashraf,

Actually you will achieved all the above but you need to click on Group text in spite of Graph.

But, put your Graph according to Grouping Level.

Regards,

Raghwendra

[former_member189979 · Accepted solution]

Dear Raghwendra,

Thank you so much for your reply. The drill down has been implemented successfully into my report. But on clicking NationalSale "Heading" it takes to next level "RegionSale" but the graph does not change its values. What I want on clicking onto NationalSale "Heading" the next level should show 6 charts of different Regional Sales with their names. When a user clicks on the any name "Heading" of the RegionSale it should go to next level of "ZoneSale" of the selected or clicked "RegionSale" Heading. This drill down should be continue till the Customers Level.

See below screen shot:

1. What should I do to accomplish it?

2. Where should I make changes into formula or else where to complete it?

3. See in the screen shot above, on the drill down again the same chart is repeating instead there should be 6 different charts of all Regions with their sales. How to resolve this issue?

Many thanks!

Tariq Ashraf

[Former Member]

Hi Tariq Ashraf,

You can achieve your goal by following the below steps in Enterprise 4.0 SP 6 Patch 2:-

1. You need to make a dummy group(*) in hierarchy.

    First Group: National Sale.

   Second Group: Regional Sales.

   Third Group: Zonal Sales.

   Fourth Group: Customer Sales.

2.Now, place your chart according accordingly in Group Header.

3.Now check to option 'Show on Drill Down' in General tab of Group Header from the second group.

(*)-->For creating Dummy Group you need to create a formula and then group on that formula:-

1st formula:

First Group: National Sale.

Whilereadingrecords;

2st formula:

Second Group: Regional Sales.

Whilereadingrecords;      like this.............................

Regards,

Raghwendra

[former_member189979]

Hello Raghwendra,

Thank you so much for your quick reply. Would you please elaborate more on how to create groups and formulas for your suggested answer. I have not created any group or formula before. How will I provide (prompts/parameter) values to different queries. Suppose, if user click on a Region chart then its Zones should be shown on next level.

Many Thanks!!!

Tariq Ashraf

[Former Member]

Hi,

Follow the following steps:-

1.Create new formula say ' National Sale'.

and place the below code:-

Whilereadingrecords;

'National Sale.'

2.Create new formula say ' Regional Sales'

Whilereadingrecords;

'Regional Sales.'

Similarly for others two also.

Now, place the formula(Drag & Drop) in Body part and right click on it and group on it.

If you are completely unaware to how to create formula and groups then just googled it

there is lot of links available and then follow my process.

Regards,

Raghwendra

[former_member189979]

Hello Raghwendra,

Once more thanks a lot for your prompt reply. As per your suggestion, firstly I've created a query and selected all the dimensions/objects which I need for analysis. After giving prompts/parameters value to the query it executed successfully and brought data into columns. Then, I created formulas with your code and dragged it to the body and right clicked it and selected group on it. Similarly, I did so for all the formulas. But when I right clicked on the second group it did not have/show drill on option.

See below screen shots:

1. Have I missed any step?

2. How can I get rid of the data which is being shown on in columns?

3. How should I link the groups together?

I will appreciate your reply.

Many thanks!!!

Tariq Ashraf

[Former Member]

Hi Tariq Ashraf,

Good Work.

You have almost reached, but you need to right click on Group Head part.

I am just adding a snapshot for you:-

Regards,

Raghwendra

[former_member189979]

Hello Raghwendra,

Once again, thanks a lot for your quick reply. According to your suggested solution, firstly I created a query and had my result objects Calendar Year/Month and Sales Qty for all chart display data. Then I created formulas for all groups starting from NationalSale to CustomerSale and then I dragged these formulas into body and clicked on Group on it for all formulas. Then I inserted column charts for all group into their headers and provided data from Data Explorer tab to the charts. Since, I've selected Calendar Year/Month on X-Axis for all charts and Sold Qty on Y-Axis for all chart, I could not find any data difference, even after going to "Format Group Header (Option)" and selecting option "show on drill down only". I did select this option "show on drill down only" for all groups/charts after second group. When I saved it on BO portal/server and executed it on the portal it did not show drill down.

What I want is that:

1. The first Chart should show "National Sales" and when a user click on this chart it should show 6 "Regions Sales" in different charts. How could it be possible?

2. Next level, when a user click on any Region Sale Chart it should lead to or drill down to relevant Region's Zones Sales into different charts. How could it be achieved?

3. Similarly, when a user clicks on any Zone Sale chart it should take to its Belt sales charts. How could it be done?

4. Lastly, when a user clicks on any Belt Chart then its respective "Customer Sales' should be shown in charts. How could it be possible?

Please see below screen shot as well.

Many Thanks!!!

Tariq Ashraf