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.

• Embed Power BI Report Using ASP.NET SDK - Power BI Embedded - Step By Step - Part Six

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.

• GitHub 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.
  
  
  
  
• 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,

<div>

<input type="button" id="exportdata" value="Export Data">

</div>

 

Step 3

 

Publish your report in a workspace.

 

I have following things in my report.

• One table visual with Product and Profit Column.
  
   

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.

 

 

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.

report.getPages()

 

This will show you the following result. Our Active page is Page 1.

 

Execute the following code. So, you will get the visual name. 

report.page("ReportSection", "Page 1", true).getVisuals()

 

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.

$("#exportdata").click(function() {
    report.page("ReportSection", "Page 1", true).getVisuals().then(function(visuals) {
        return visuals.find(function(visual) {
            return visual.name === "73a25d3154275e964d4e"
        })
    }).then(function(emailVisual) {
        return emailVisual.exportData(models.ExportDataType.Summarized)
    }).then(function(result) {
        console.log(result);
        console.log(result.data);
        console.log(result.data.length)
    });
})

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.

 

 

In the second method, we will call exportData mehod and in the result we will log the data.

 

 

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.

 

 

In console you will find the following export data string

 

 

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!!