Recently I got an opportunity to embed power BI reports in ColdFusion Application. Please find the steps and requirements below for implementation of Power BI using ColdFusion. Please let me know if you face any issues in implementing the details below.
NOTE: If you are applying report filtration then ensure you are checking the case of table and field in power BI. Its case sensitive. Value passed is not case sensitive.
Requirements from POWER BI:
- reportId,
- GroupId,
- clientId (ApplicationID),
- username and
- password .
Steps to Follow
1. Collect access token from Power BI
To collect access token we need to make REST API call with few parameters. Here goes the code below. Only modify the green highlighted part of the code as per your power BI settings. Rest part does not need any change.
Upon successful completion of the REST API call, you would be receiving below details in JSON format. Use DeSerializationJSON method to convert this to ColdFusion Structure.
"token_type": "Bearer",
"scope": "",
"expires_in": "",
"ext_expires_in": "",
"expires_on": "",
"not_before": "",
"resource": "https://analysis.windows.net/powerbi/api",
"access_token": "",
"refresh_token": ""
2. Collect Embed URL details
Out of above received details, we are going to use access_token part to get the embed URL. Below is the REST API Call to get embed URL. As you can see here, we are requesting to an URL having our groupId and reportId. Also as cfhttp parameter we are sending header name: Authorization and value: "Bearer #receivedAccesstoken#"
<cfset variables.embedURLRequestURL = "https://api.powerbi.com/v1.0/myorg/groups/#variables.groupId#/reports/#variables.reportId#">
<!--- Getting embed string details from power bi passing access token --->
<CFHTTP result="embedURLDetails" Url="#variables.embedURLRequestURL#" method="get">
<cfhttpparam type="header" name="Authorization" value="Bearer #variables.access_token#">
</CFHTTP>
Upon successful completion we would be receiving below details in JSON format. Use DeSerializationJSON method to convert this to ColdFusion Structure.
"@odata.context": "",
"id": "",
"reportType": "PowerBIReport",
"name": "",
"webUrl": "",
"embedUrl": "",
"isFromPbix": true,
"isOwnedByMe": true,
"datasetId": ""
3. Rendering report using PowerBI.js
Use PowerBI.JS to render report using the embedURL received in above step. You can download the powerBI.js and use it from you local or can use CDN. Along with powerbi.js you will need jquery plugin as well.PowerBI CDN link: https://cdn.jsdelivr.net/npm/powerbi-client@2.10.2/dist/powerbi.min.js
Here I have created a method named updateEmbedReport that takes embedURL and accessToken collected in above steps. Here I have added code for filtering report data. In case you don't want to apply filtration, then you can remove below marked code in red. In case you wish to hide on screen filter, then you can take off below code in blue color.
JavaScript Code for Report Rending:
<script type="text/javascript">
jQuery(document).ready(function () {
updateEmbedReport(embedUrl = '#variables.embedURL#', accessToken = '#variables.access_token#');
});
// update embed report
function updateEmbedReport(embedUrl, accessToken) {
// check if the embed url was selected
if (embedUrl === "")
return;
const basicFilter = {
$schema: "http://powerbi.com/product/schema##basic",
target: {
table: "Filters",
column: "memberID"
},
operator: "eq",
values: ['#USERAUTH.user_id#'],
filterType: 1 // pbi.models.FilterType.BasicFilter
}
// Embed configuration used to describe the what and how to embed.
// This object is used when calling powerbi.embed.
// You can find more information at https://github.com/Microsoft/PowerBI-JavaScript/wiki/Embed-Configuration-Details.
var config = {
type: 'report',
accessToken: accessToken,
filters: [basicFilter],
embedUrl: embedUrl,
settings: {
filterPaneEnabled: false,
navContentPaneEnabled: false
}
};
// Grab the reference to the div HTML element that will host the report.
var reportContainer = document.getElementById('reportContainer');
// Embed the report and display it within the div container.
var report = powerbi.embed(reportContainer, config);
// report.on will add an event handler which prints to Log window.
report.on("error", function (event) {
var logView = document.getElementById('logView');
logView.innerHTML = logView.innerHTML + "Error<br/>";
logView.innerHTML = logView.innerHTML + JSON.stringify(event.detail, null, " ") + "<br/>";
logView.innerHTML = logView.innerHTML + "---------<br/>";
});
}
</script>
4. Add container for report and report error to display
Add reportContainer div any where in page body, where you wish to display the report. And logView div is to show error log if any thing goes wrong during report rendering.<div id="logView"></div>
POWER BI REPORT SCREENSHOT
