Drill down functionality and RRI from Design studio to Webi Report

Scenario: We have a business requirement that customer wants to visualize his spend data for preferred and non-preferred supplier on a dashboard and he wants to see detail lines of purchases for these suppliers.

Reporting Solution:So we opted to create a drill down chart from the preferred and non-preferred suppliers to supplier spend on selection, and I would interface from design studio report to webi report for details of purchases made from supplier.

Backend data source: HANA

Reporting tools: Design studio, Webi report

Below is the illustration to implement the above scenario in a design studio and webi report.

In Design Studio, firstly drag and drop the chart in design panel/shared area and set the properties as given below. Add data source from your source system and connect the data source DS_Preferred_Spend to fetch the data from HANA backend.

The 1st chart looks as below after assigning the data source and defining the properties..

Similarly drag and drop the second chart in design panel/shared area and set the properties as given below .

Add data source from your source system and connect the data source DS_Preferred_vendor to fetch the data from HANA backend.

The 2nd chart looks as below after assigning the data source and defining the properties..

Now I should create drill down functionality from chart 1 to chart 2.So,functionality is when I click on preferred column of 1st chart the second chart should show only preferred vendors in the pie chart and when I click on non-preferred vendors the pie chart should show only non-preferred vendors .

I can achieve this functionality by writing a statement in the on select of events component of chart 1 as below.

Now the drill down functionality will be like this. When I click on green column for preferred vendors the second chart shows the list of preferred spend by suppliers as below in 2nd chart(pie chart).

Now my second task is to create a report to report interface to see the details for each supplier spend details lines in webi report .ie.when I click on each legend /vendor of second chart it drills down to the webi report showing the corresponding line items of the preferred vendor.

For this, we should write an on click event on second chart.

Before that we need to create webi report for linking with design studio application.

I created a report with required objects and pulled onto my query design panel and input prompts for supplier name and preferred/non preferred type in webi.

The details of supplier detail report structure created.

Now save the webi report Global spend by supplier in your BI Launchpad ->documents->folders ->Public folders->your project folder->in you reporting application folder.

Now I  can get the document link when you right click on webi report as below.

Click on the document link

We need to use this document link to drill down from design studio to webi report by passing the input parameters dynamically based on the selection made on chart 2.

Create 1st variable “a” which gets the selected member from chart 2 (i.e the selected vendor)

Create 2nd variable “b” which gets the selected member from chart1 (i.e selected spend type(preferred/non preferred) as below.

Then call the webi report using the doucment  link of the webi report created passing input parameters supplier and preferred/non preferred spend type at runtime.

Save the below piece of code  at on click event of the chart2.

Now click on the second chart for preferred vendor “Republic steel” the detail lines are shown in following webi report as follows.