Export Filtered Dataset From Power BI Report To SQL Server Programmatically Using API And Power BI Embedded - Part One

Overview

 
In this article series, we will learn how we can export the filtered data from Power BI report and insert the data back to the SQL Server.
 
Power BI Service doesn’t provide such customization. However, we can export the data with the selected filters with the help of the Power BI Embedded concept.
 
In order to achieve this functionality, I believe you already have embedded the Power BI report using .NET SDK. If you haven’t embedded the report, please rede the following article or steps are mentioned to this article or you can visit the vLog Series.
First, we need to embed the Power BI report. Once our report is embedded, we will add custom button “Export Data” to export the data from the specific table visual using API. Power BI API provide us the data in a form of the string. We will parse the string and insert the record back to SQL Server database.
 
So, I have divided this article series into two parts,
  • In the first part, we will add the UI element to export the data and talk about the API part to export the data programmatically. 
  • In the second part, we will talk about the controller method and SQL Server Stored Procedure part. 
Download the Code
 
For your convenience, I have uploaded the code solution files to my GitHub account. You can directly download from below link.
The same article series available in a form of a video which can be viewed using this URL.
 
So, now let’s get started!
 
Step 1
  • Open the solution file.
  • Below is a structure of the code files.

    Export Filtered Dataset From Power BI Report To SQL Server Programmatically Using API And Power BI Embedded

  • We need to focus on the following two files.

    • HomeController
    • EmbedReport.cshtml
Step 2
 
Add a button in the EmbedReport.cshtml file.
 
Below is the code,
  1. <div>  
  2.   
  3. <input type="button" id="exportdata" value="Export Data">  
  4.   
  5. </div>  
Export Filtered Dataset From Power BI Report To SQL Server Programmatically Using API And Power BI Embedded
 
Step 3
 
Publish your report in a workspace.
 
I have following things in my report.
  • One table visual with Product and Profit Column.

    Export Filtered Dataset From Power BI Report To SQL Server Programmatically Using API And Power BI Embedded 
Step 4
 
Now, I believe, you have embedded the report already and when you run your solution it will show the embedded report showing in the below screen.
 
Export Filtered Dataset From Power BI Report To SQL Server Programmatically Using API And Power BI Embedded
 
Now, let’s find out the Page name and Visual name from the console which we need to pass in the export data API Call.
 
Run the below code in the browser console.
  1. report.getPages()  
Export Filtered Dataset From Power BI Report To SQL Server Programmatically Using API And Power BI Embedded
 
This will show you the following result. Our Active page is Page 1.
 
Execute the following code. So, you will get the visual name. 
  1. report.page("ReportSection""Page 1"true).getVisuals()  
Export Filtered Dataset From Power BI Report To SQL Server Programmatically Using API And Power BI Embedded
 
This will return all available visuals.
 
Copy the name of the visual as highlighted below.
 
Add Click event for the Export Data button and Call API to export the data.
  1. $("#exportdata").click(function() {  
  2.     report.page("ReportSection""Page 1"true).getVisuals().then(function(visuals) {  
  3.         return visuals.find(function(visual) {  
  4.             return visual.name === "73a25d3154275e964d4e"  
  5.         })  
  6.     }).then(function(emailVisual) {  
  7.         return emailVisual.exportData(models.ExportDataType.Summarized)  
  8.     }).then(function(result) {  
  9.         console.log(result);  
  10.         console.log(result.data);  
  11.         console.log(result.data.length)  
  12.     });  
  13. })   
Here, below is the breakdown of the code.
 
In the first method we need pass the parameter of Page name of your report.
 
Using getVisuals() method you will find the all available visual from the page. From there you can find the ID of the visual from console and pass that name as visual.name value.
 
Export Filtered Dataset From Power BI Report To SQL Server Programmatically Using API And Power BI Embedded
 
In the second method, we will call exportData mehod and in the result we will log the data.
 
Export Filtered Dataset From Power BI Report To SQL Server Programmatically Using API And Power BI Embedded
 
We will get the data in a string format.
 
Step 5
 
Let’s run the solution and check the console for the output.
 
This will show the report. Click on Export data button.
 
Export Filtered Dataset From Power BI Report To SQL Server Programmatically Using API And Power BI Embedded
 
In console you will find the following export data string
 
Export Filtered Dataset From Power BI Report To SQL Server Programmatically Using API And Power BI Embedded
 
In the next video, we will call the controller method and insert the data to SQL Server.
 

Conclusion

 
This is how export data API work with .Net SDK. Stay connected with me for the amazing articles!
 
Happy reporting!!