Embedding Power BI Report Using ColdFusion

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:

1. reportId, 
2. GroupId, 
3. clientId (ApplicationID), 
4. username and 
5. 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.

<!--- Getting power BI access token ---> <CFHTTP result="accessTokenDetails" Url="https://login.microsoftonline.com/common/oauth2/token" method="post"> <cfhttpparam type="header" name="Content-Type" value="application/x-www-form-urlencoded"> <cfhttpparam type="formfield" encoded="no" name="grant_type" value="password" /> <cfhttpparam type="formfield" encoded="no" name="client_id" value="#variables.clientId#" /> <cfhttpparam type="formfield" encoded="no" name="resource" value="https://analysis.windows.net/powerbi/api" /> <cfhttpparam type="formfield" encoded="no" name="username" value="#variables.PBIUserName#" /> <cfhttpparam type="formfield" encoded="no" name="password" value="#variables.pbiPassword#" /> </CFHTTP>

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="reportContainer" style="width :1080px; height: 760px;"  ></div>
<div id="logView"></div>

POWER BI REPORT SCREENSHOT

Issues with Like operator in ColdFusion Query of Query and solution

This post is regarding one of my recent findings on LIKE operator in ColdFusion query of query(QoQ). Please find the details below. Hope this helps.

1.       What is Query of Query(QoQ) in ColdFusion?

a.       When you run a query on another query, we call it QoQ. This base query could be obtained from Database or may be from cfdirectory or any other source.

2.       Issue with Like Operator and QoQ

a.      In SQL server, Like operator makes Case Insensitive search. So writing column_name Like ‘%abc%’ or ‘%ABC%’ returns the same result.

b.      This same principle does not work when you write Like operator, in QoQ. QoQ makes case sensitive search. This will result in wrong result set.

3.       Addressing issue with Like operator in QoQ

a.       To address this issue, we need to compare both sides of Like operator in same case.

b.      Ex. WHERE UPPER(item.item_name) LIKE ‘%#UCase(itemName)#%’

c.       WHERE LOWER(item.item_name) LIKE ‘%#LCase(itemName)#%’

HOW TO RUN FILES THAT TAKES VERY LONG TIME TO RUN?

You might have encountered some situations when your file runs for very long time and it results in timeout in browser. This happens when browsers don’t get response within some stipulated time frame. Few examples of such situations are,

1. generating report that needs lot many db operations,
2. reading or writing many files(I/O Operations)
3. long running data base operation through CF file etc.
4.  A page request that has lot many back end jobs to complete. There could be many examples as such.

Here is the suggested solution for this type of situations.

1. Set big requestTimeout value in the pages that are expected to run for very long time. Remember, here the value is in milliseconds.
2. In place of directly calling the files in browser, create a scheduled task in CF Admin and there you set the file URL(see highlights in second screenshot). Mention timeout value to a bigger amount to ensure successful completion of the task.
3. Here, to see what output you got from the page, you need to  check Publish checkbox(see highlights in second screenshot) and set a file path to write the output. Its recommended to set the output file extension to .html for better readability of output.
4. Once you are done with all these setting and done with the scheduled task creation, run the scheduled task, by clicking on green icon, to run the file immediately(screenshot three).

Hope, these details will be of help. Any suggestion or question is most welcome.

Converting time to 12 or 24 hr format

As per my recent task in my project I had to convert time to 24 hours format. The time was in format of 12:30 am or 2:30 pm. Hence I thought of writing a function that will convert as per requested formats. Please find the function below. I hope this will help in saving a good amount of time in finding the logic to convert time format.

<script type="text/javascript">
 function timeFormat(format, str){

     var timeParts = str.split(":");
     var fstPart = timeParts[0];
     var sndPart = timeParts[1];
  var sndPartNum = sndPart.match(/\d+/)[0];
  if(sndPart.match(/[a-zA-Z]+/)){
   var sndPartStr = sndPart.match(/[a-zA-Z]+/)[0];
  }else{
   var sndPartStr = '';
  }

     if(format==12){
   if(sndPart.match(/[a-zA-Z]+/) 
    && sndPart.match(/[a-zA-Z]+/).length 
    && fstPart <= 12){

     return fstPart + ":" + sndPartNum + " " + sndPartStr;
    }
   else if(!sndPart.match(/[a-zA-Z]+/)){
    if(fstPart <= 12){ // AM part
     return fstPart + ":" + sndPartNum + " am";
    }else if(fstPart > 12 && fstPart < 24){ // PM Part
     return parseInt(fstPart) - 12 + ":" + sndPartNum + " pm";
    }else{
     alert('the input value is not in proper format.');
     return false;
    }
   }else{
    alert('the input value is not in proper format.');
    return false;
   }
     }else if(format == 24){
   if(fstPart < 24){
    var hours = parseInt(fstPart);

         if(sndPartStr.toLowerCase() == "pm"){
           hours += 12;
         }

         return hours + ":" + sndPartNum;
   }else{
    alert('the input value is not in proper format.');
    return false;
   }
     }
 }


 // Calling the method with different type of values
 console.log('input: 2:30 pm. Convert to 24 hours format');
 console.log(timeFormat(24, '2:30 pm'));

 console.log('input: 2:30 am. Convert to 24 hours format');
 console.log(timeFormat(24, '2:30 am'));

 console.log('input: 22:30. Convert to 12 hours format');
 console.log(timeFormat(12, '22:30'));

 console.log('input: 2:30. Convert to 12 hours format');
 console.log(timeFormat(12, '2:30'));

 console.log('input: 2:30 am. Convert to 12 hours format');
 console.log(timeFormat(12, '2:30 am'));

 console.log('input: 18:00. Convert to 12 hours format');
 console.log(timeFormat(12, '18:00 am'));
</script>

Move options from one dropdown to other using jQuery

Today while looking for a way to move options from one dropdown to other dropdown, onclick of some link or button, I discovered one easy and you can say most optimized way to do so. Please follow the same below.

Scenario:
Here suppose we have two dropdowns having id as dropdown1 and dropdown2. I want to move options from dropdown1 to dropdown2 onclick of a link as add and in reverse way on click on remove link I want to take options out from dropdown2 and put the same in dropdown1.

How to do it?
On click of add link we can give call to method as: moveOptions(dropdown1, dropdown2) and similarly on click of remove link we can call to method moveOptions(dropdown2, dropdown1). Mark here I just altered the position of the dropdowns in the method call.

So here goes the method.

function moveOptions(sourceId, sinkId){

     $("#" + sinkId).append($("#" + sourceId + " option:selected"));

}

This single line method will be enough to add and remove options from a dropdown and remove and add in other.
Calling the method:

<!-- First dropdown -->

<select id="dropdown1">

<option value="1">one</option>

<option value="2">two</option>

<option value="3">three</option>

</select>



<!-- Second dropdown -->

<select id="dropdown2">



</select>



<!-- Links to add and remove options -->

<p onclick=" moveOptions(dropdown1, dropdown2)">Add</p>

<p onclick=" moveOptions(dropdown2, dropdown1)">Remove</p>

Object Creation and init() method in ColdFusion

-- In ColdFusion we can create objects in three ways as: cfobject, createObject() and new().

-- The syntax for all three is as below:

1.

<cfobject component=”componentName” name=”objectName”>

2.

<cfscript>

                Variables.objectName = CreateObject(“component”, “componentName”);

</cfscript>

3.

<cfscript>

                Variables.objectName = New componentName(param1, param2,…);

</cfscript>

** Please Note **

The important thing to mention here is the first two methods don’t give call to init() method automatically if not called explicitly, whereas the last one looks for the existence of init() method in the component automatically during object creation and if found then it calls it and returns as per the returnvalue of init() method and if returntype is set to void then the object for the component is returned.

Creating a multy Locale site in ColdFusion

 -- To create sites that would show date, time, currency and number as per the localization, it may be bit tricky. We may have to write logic for each nation separately. But it really not that easy. To ease this process, we can play a small trick in ColdFusion.
 

-- ColdFusion provides a function named ‘SetLocale()’.  This function sets the current session to some particular localization as per your request. And once you set the locale, now you can get the time, date, currency and number in that particular local format also you can verify whether some value is properly formatted or not.

-- To get all those above values we have some predefined methods in ColdFusion as: LSIsCurrency, LSCurrencyFormat, LSDateFormat, LSEuroCurrencyFormat, LSIsDate, LSParseDateTime, LSIsNumeric, LSNumberFormat, LSParseCurrency, LSParseEuroCurrency, LSParseNumber, LSTimeFormat.

-- This setting of localization stays as such for the current session only and once the session times out, the localization is set to default localization as per it’s set in ColdFusion server. This value can also be overridden using 'SetLocale()' function.

-- So the idea for creating a multiple localized site is: for each session, you can fetch the localization of the user in OnSessionStart method, may be using some API or having a database that would consist of user IPs and their localization names. Or simply we can have a dropdown in home page, where the user would pick the localization as per his/her choice.

-- Once you get the localization details, you supposed to have another look up table for localization and locale name. Available locale names that can be used in ColdFusion are:

Chinese (China)	French (Belgian)	Korean
Chinese (Hong Kong)	French (Canadian)	Norwegian (Bokmal)
Chinese (Taiwan)	French (Standard)	Norwegian (Nynorsk)
Dutch (Belgian)	French (Swiss)	Portuguese (Brazilian)
Dutch (Standard)	German (Austrian)	Portuguese (Standard)
English (Australian)	German (Standard)	Spanish (Modern)
English (Canadian)	German (Swiss)	Spanish (Standard)
English (New Zealand)	Italian (Standard)	Swedish
English (UK)	Italian (Swiss)
English (US)	Japanese

-- So once you get the locale name, now you can use SetLocale(locale_name) method to set the locale in OnSessionStart method. So this will be set just once and will stay effective for entire session. Hence I think this is the right place to set this.

-- Now as we have already set the locale, so by using above mentioned methods(Ls….), we would be able to deal with the locale specific formats easily.

Here is a Small example to show how to set locale and use time, date, currency and number as per the locale.

<cfoutput>
    <cfif StructKeyExists(form, 'localeText')>\
        <cfif Len(Trim(form.localeText))>\
            <cfset setLocale(form.localeText)>
        <cfelse>
            <b>Please select one language</b>
        </cfif>
    </cfif>

    <form method="post" action="">
        Enter your local:
        <select type="text" name="localeText" id="text" onchange="javascript: this.form.submit()">
            <option value="">-- Select Language --</option>
            <option value="Chinese (China)" >Chinese (China)</option>
            <option value="Chinese (Hong Kong)">Chinese (Hong Kong)</option>
            <option value="French (Belgian)">French (Belgian)</option>
            <option value="Dutch (Belgian)">Dutch (Belgian)</option>
            <option value="English (Australian)">English (Australian)</option>
            <option value="English (Canadian)">English (Canadian)</option>
            <option value="English (New Zealand)">English (New Zealand)</option>
            <option value="English (UK)">English (UK)</option>
            <option value="English (US)">English (US)</option>
            <option value="Japanese">Japanese</option>
            <option value="Swedish">Swedish</option>
        </select>
        <input type="submit" style="display: none;">
    </form>


    <p>
        The locale is now <b> #GetLocale()#</b><br />
        Your Date Now: #LsDateFormat(now())#<br />
        Your Time Now: #LsTimeFormat(now())#<br />
        Your Currency: #LsCurrencyFormat(350)#<br />
        Your Number View: #LsNumberFormat(350.12, '.__')#<br />
    </P>
</cfoutput>

Initial Form Look(Taking default US Locale):

So on choosing Chinese (China), the output would be:

So on choosing Japanese, the output would be:

So on choosing Swedish, the output would be: