Few days back I had a requirement from client which needed two level drill down in SAP BO Dashboard. I thought it would be easy but it took a day for me to achieve it. And yeah, it is easy but some issues still existing.

I’m going to use Pie charts in this document, you may apply the same logic for line charts. But, one will face some difficulties while applying the same on a line chart, so more logic should be used. Anyway, I will share another document for that soon.

The components we need are

1. 3 Pie chartsTo show the data initially and after drill down

2. 2 Combo boxes or 2 label bases menusTo filter the data

3. 2 Push buttonsFor mobility between charts

4. 3 GridsTo observe the logical data and filtered data mobility

This is the sample data we are going to use in this document. Country level-> City level-> Area level

     A               B          C          D          E               F     G          H          I               J               K          L     M

Data for two level drill down in Pie charts.PNG

Pie chart 1:
Labels: Country column

Values: Value column

Insertion type: Row

Source data: Country column

Destination: C1 Output

Initially Pie chart 1 shows the data on country level and when we click on any country area it pushes that country name to the destination cell.

Combo box 1 or label based menu 1:

Labels: Orange colored country column

Insertion type: Filtered rows

Source data: City and value columns

Destination: Filtered data columns

Selected Item type: Label

Selected Item: C1 Output

Combo box 1 selects the value came from C1 as label among the labels given and pushes the rows of selected label to it’s destination. Indirectly, it filtered the data.

Pie chart 2:

Labels: Filtered city column

Values: Filtered value column

Insertion type: Row

Source data: Filtered city column

Destination: C2 Output

Combo box 2 or label based menu 2:

Labels: Orange colored city column

Insertion type: Filtered rows

Source data: Area and value columns

Destination: Filtered data columns

Selected Item type: Label

Selected Item: C2 Output

Pie chart 3:

Labels: Filtered area column

Values: Filtered value column

Grid 1:

Data: filtered data 1 (Green colored part 1)

Grid 2:

Data: filtered data 2 (Green colored part 2)

Grid 3:

Data: Logical data (Yellow colored part)

If we preview the dashboard at this stage, all the pie charts should be working properly. Also observe how data is changing using grids.

Now, place one push button each on chart 2 and chart 3.

The function of these push buttons is to clear the output given by the previous chart, which in turn triggers the logical cells and brings the previous chart visible.

Now, we have to implement mobility between charts.

1. Click on chart 1 -> chart 2

2. Click on chart 2 -> chart 3

3. Click on ‘back’ on chart 3 -> chart 2

4. Click on ‘back’ on chart 2 -> chart 1

Logic involved:

Chart 1 should be visible only when ‘C1 output’ is blank.

Chart 2 should be visible only when ‘C1 output’ is not blank and ‘C2 output’ is blank.

Chart 3 should be visible only when ‘C1 output’ is not blank and ‘C2 output’ is also not blank.

Formulas for the yellow colored cells are given below:

C1

=ISBLANK(A7)

C2 =ISBLANK(B7)
C2 & B2 =AND(B9=FALSE,B10=TRUE)
C3 & B3

=AND(B9=FALSE,B10=FALSE)

Now, bind dynamic visibility accordingly:

Pie chart 1: C1

Pie chart 2 and Push button 2: C2 & B2

Pie chart 3 and Push button 3: C3 & B3

Now, our drill down dashboard is ready. Let’s go for preview.

Issue No.1: Observe that, it works on it’s first journey but not one second. I mean, chart 1 -> chart 2 -> chart 3-> chart 2 -> chart 1 will work fine. If we click on chart 1 after this journey, this time chart 2 will appear and disappears instantly and chart 3 will come on the screen.

Resolution: We may use reset button to come from chart 2 to chart 1. But, the problem is it resets the entire dashboard. This is not a problem in our case but this may be a problem in a dashboard with other set of components.

Issue No. 2: If you delete all the grids, the dashboard will not function properly.

Resolution: You may delete the grid 1 and grid 2, but a grid 3 is must. You may change the data of grid 3 to just output of C1 and C2, and to only C2 if in case you use reset button for chart 2 to chart 1.

Issue No. 3: Combo box 1 is visible on top of chart 3 even I move them to top in object browser. You may not face this issue, if the problem is in my dashboard application only.

Resolution: I have used a background and pushed the combo boxes and the grid behind it.

Doubt No. 1: If I hide combo boxes using dynamic visibility, the dashboard will not function properly. If dynamic visibility also stops functionality of the component, why is it selecting the label and pushing into a cell as output, but only not filtering the data.

Doubt No. 2: What grid is doing in case of Issue No. 2

I’m sharing the google drive link for the above discussed sample dashboard. Feel free to download and ask if you have any questions and concerns.

I would like you all to rate and give feedback so that I can write my further documents in a better way.

Also please help me to solve the issues mentioned above.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply