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 charts - To show the data initially and after drill down
2. 2 Combo boxes or 2 label bases menus - To filter the data
3. 2 Push buttons - For mobility between charts
4. 3 Grids - To 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
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.
- SAP Managed Tags:
