PIPware Cumulative benefits report show rolled up benefit delivery for an organisation and can be filtered down to department level.


PIPware APIs can be used to build an interactive pipeline drill down report in PowerBI.

Instructions

Follow these steps to import data into PowerBI via the PiPware APIs


Step 1: Generate an API key

To access data from PiPware an unique API key must be generated for the user that will be accessing the data. This can be done by following the steps in this article.


Step 2: Make sure the user account has the correct permissions

Make sure that the role for the user account you are using have the Export Cumulative Benefits permission.


Step 3: Create a web query to get Ideas from PiPware

  • Open Power BI and click on Get Data, then select Web from the drop down list.


  • Add the Ideas API URL from your site for getting all the Ideas

                        Note: Replace "Identifier" with your site identifier


  • If it is a new data source, you will be prompted for authentication information. Click on Basic and then enter api for the username and your API Key for the password.


  • Click on Connect when all information has been entered.
  • PowerBI will now connect to PiPware and download the data. When completed, it should open the Power Query Editor window. If it does not do so, Click on Transform data and select Transform data.


  • Rename the Query by right-clicking and choosing Rename.
  • Click on the two arrows icon in the Ideas column and choose Expand to new rows.
  • Click on the two arrows icon in the Ideas column again and choose OK to expand the data into table format.


  • The columns will all be in the text data type now. To get them to the right data type, click on each date column and set it to Date/Time and each currency column and set it to Fixed decimal number data types. This is done via the Data Type menu item in the toolbar.


  • Save your work by clicking on the Close & Apply drop down and choosing Apply.


Step 4: Create PowerBI Parameters

  • Create three parameters by clicking on Manage Parameters and then New Parameter. Configure them as shown in the image below. Replace Identifier with your site identifier.


Step 5: Create a web query to get cumulative benefit data from PiPware


  • In the Power Query Editor window, click on New Source and then Web.



  • In the From Web dialog, enter the URL as indicated in the image below.


  • Click on OK to load the data.


  • As above, rename the query and ensure that the columns have the right datatype.
  • Remember to save your changes by clicking on Close & Apply.



You will now have the required data in PowerBI and can go ahead and create your report.