Dashboard for Top-N values using BI Web service:
As we know that BEx query condition is not working in dashboard report, then how condition can put on keyfigure to get Top-N or Bottom-N values, for this webi report can be created for Top-N and Bottom-N No of values with rank formula and same can access in dashboard using web service. Below is the step by step process.
1) Create BEx query report as per requirement.
2) Create webi report on Bex query for Top 15 Customer by using Rank function.
a) Create webi report on Bex query(in my example it consist of Customer id, name and there turnover of the month)
b) Right click on turnover column and Add Ranking.
c) Now desire output of Top Ten Customer will get as below, saved the webi report on BI platform.
3) Create web service on webi report.
a) Select report table properly and right click then ‘Publish as Web Service’ then Next
b) Click on next and check for Filter prompt which we use for Filter in dashboard.
c) Define Publish content(Name and description) for web service
d) Create or select existing web service to publish new content to as web service.
e) Now web service has been created successfully, you can see it in ‘Web Service Publisher’ Panel at the left side. The content of web service would access through WSDL URL Link refer below screen shot.
4) Open dashboard report and Add connection (Query web service) to web service.(Click on Data and then Add Connection in menu bar or Click on manage connection in tool bar and then select Web service query(Query as web service). In the WSDL URL box, Paste WSDL URL and click on import button.
5) As per below screen shot, two block Input Values and Output Values are shown we need to provide values in ‘Input values block’ and take output values from ‘output value block’, refer below screen shots and bind input block with excel cell in which required value is given like login id, password, required prompt value, resetState (resetState and refresh are used for refresh data on selection prompt values) and Also bind output block with Excel cell to taking web service values like Header, footer and Table values.
6) Please check ‘Refresh Before Component are Loaded’ check box and check when value changes option button and bind Trigger cell with Excel, so whenever the value is changed new data is refresh from Web service.
7) Drag and Drop Dashboard components and bind it with Excel cell as per requirement and execute.
Kashif Ali Khan