Forum Discussion
Seamless Access Token Refresh for PowerBI Reports using PowerBI Rest API
"I'm currently integrating PowerBI reports into my project and utilizing the PowerBI Rest API to apply filters dynamically. However, the access token expires every hour, causing disruptions for the report viewer. I attempted to update the access token every 45 minutes using setInterval, but it didn't yield the desired result. It is still throwing errors of Token Expiration.
I'm seeking a solution that provides a seamless experience for the report viewer, allowing them to interact with the report without any interruptions due to token expiration where the access token will automatically gets updated. Are there any effective strategies or techniques to handle this scenario?"
This is the code I am using to load Powerbi Report in my Laravel Web Application.
Backend code
public function view_powerbi_reports(){
$data = null;
if (session('user.userrole_id') == '3') {
$district = array();
$projectFilter = getPartnerProjects(session('user.id'));
unset($projectFilter['']);
$projects = array_values($projectFilter);
$partnerId = getValue('kjs.lk_partner_user', 'partner_id', 'user_id',session('user.id'))->getData();
$partnerId = $partnerId->data;
$partnerName = getValue('kjl_shared.partner', 'name', 'id', $partnerId)->getData();
$partnerFilter = [$partnerName->data];
$data['partnerFilterValues'] = json_encode($partnerFilter, true);
$data['projectFilterValues'] = json_encode($projects, true);
}
$accessToken = $this->getAccessToken()->getData();
$data['access_Token'] = json_encode($accessToken, true);
// dd($data);
return view('reports.power-bi-reports.index')->with('data', $data);
}
public function getAccessToken(){
try{
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, 'https://login.microsoftonline.com/organizations/oauth2/v2.0/token');
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, http_build_query([
'client_id' => 'CLIENT_ID',
'scope' => 'https://analysis.windows.net/powerbi/api/Report.Read.All',
'client_secret' => 'CLIENT_SECRET',
'grant_type' => 'password',
'username' => 'MY_EMAIL', //replace username
'password' => '' //replace password
]));
$headers = array();
$headers[] = 'Content-Type: application/x-www-form-urlencoded';
curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);
$response = curl_exec($ch);
if (curl_errno($ch)) {
Session::flash('message', 'Some error occurred.');
return redirect('/projects');
}
curl_close($ch);
} catch (Exception $e){
$data = $e->getMessage();
Session::flash('message', $data);
return redirect('/projects');
}
$response = json_decode($response, true);
$accessToken = null;
if (isset($response['access_token'])) {
$accessToken = $response['access_token'];
} else {
Session::flash('message', 'Some error occurred.');
return redirect('/projects');
}
return response()->json($accessToken);
}And this is how I am loading and filtering report in frontend with the help of access token. Please let me know what I can improve in this flow and how can I achieve the seamless expirence for the user when Access Token gets expired after one hour.
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/powerbi-client/2.22.4/powerbi.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/powerbi-client/2.22.4/powerbi.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/powerbi-client/2.22.4/powerbi.min.js" integrity="sha512-ZMmyGjAz4cSAlJZVBrJnpcoWv2KVhPpH6H738XBYnPHY66xUC7QgmZNqBymp2WSAL3hZmdpFpVRkoB3mgET6jQ==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
<div id="embedContainer" style="display:none;"></div>
<script>
let loadedResolve, reportLoaded = new Promise((res, rej) => { loadedResolve = res; });
let renderedResolve, reportRendered = new Promise((res, rej) => { renderedResolve = res; });
let report;
const models = window['powerbi-client'].models;
let accessToken;
IF(session('user.userrole_id') != '1')
const partnerFiltersVal = {!! $data['partnerFilterValues'] !!};
const projectFiltersVal = {!! $data['projectFilterValues'] !!};
@endif
accessToken = {!! $data['access_Token'] !!};
function embedPowerBIReport() {
const embedUrl = 'https://app.powerbi.com/reportEmbed?reportId=xxxxxxx&config=xxxxxxxxxxxx';
const embedReportId = 'xxxxxxxxxxxxxxxxx';
const permissions = models.Permissions.Read;
const config = {
type: 'report',
tokenType: models.TokenType.Aad,
// tokenType: tokenType == '0' ? models.TokenType.Aad :models.TokenType.Embed,
accessToken: accessToken,
embedUrl: embedUrl,
id: embedReportId,
permissions: permissions,
settings: {
panes: {
filters: {
visible: false
},
pageNavigation: {
visible: true
}
}
}
};
const embedContainer = $('#embedContainer')[0];
report = powerbi.embed(embedContainer, config);
report.off("loaded");
report.on("loaded", function () {
loadedResolve();
IF(session('user.userrole_id') == '1')
var e = document.getElementById('embedContainer');
document.getElementById('loader-container').style.display="none";
e.style.display = 'block';
@endif
IF(session('user.userrole_id') != '1')
setTimeout(applyFilter);
@endif
report.off("loaded");
});
report.off("error");
report.on("error", function (event) {
console.log(event.detail.message);
embedPowerBIReport();
});
report.off("rendered");
report.on("rendered", function () {
renderedResolve();
report.off("rendered");
});
}
IF(session('user.userrole_id') != '1')
async function applyFilter() {
console.log("ApplyFilter");
const filter = {
$schema: "http://powerbi.com/product/schema#Advanced",
target: {
table: "KJL-AllReviews-Export Less Records",
column: "Partner Name"
},
operator: "In",
values: partnerFiltersVal
};
const filterSheet1 = {
$schema: "http://powerbi.com/product/schema#Advanced",
target: {
table: "KJL-AllReviews-Export Less Records",
column: "Project Name"
},
operator: "In",
values: projectFiltersVal
};
const filterSheet2 = {
$schema: "http://powerbi.com/product/schema#Advanced",
target: {
table: "Rating",
column: "Project Name"
},
operator: "In",
values: projectFiltersVal
};
await reportLoaded;
await reportRendered;
partnerFiltersFlag = projectFiltersFlag1 = projectFiltersFlag2 = false;
await report.updateFilters(models.FiltersOperations.Add, [filter])
.then(result => {
partnerFiltersFlag = true;
// console.log("Filter applied successfully", result);
})
.catch(error => {
console.error("Error applying filter", error);
});
await report.updateFilters(models.FiltersOperations.Add, [filterSheet1])
.then(result => {
projectFiltersFlag1 = true;
console.log("Filter applied successfully", result);
})
.catch(error => {
console.error("Error applying filter", error);
});
await report.updateFilters(models.FiltersOperations.Add, [filterSheet2])
.then(result => {
projectFiltersFlag2 = true;
console.log("Filter applied successfully", result);
})
.catch(error => {
console.error("Error applying filter", error);
});
if(partnerFiltersFlag && projectFiltersFlag1 && projectFiltersFlag2){
var e = document.getElementById('embedContainer');
document.getElementById('loader-container').style.display="none";
e.style.display = 'block';
} else {
console.log("Filters are not applied as expected");
}
}
startInterval();
function loadReport(){
try {
setTimeout(applyFilter);
} catch (errors) {
var e = document.getElementById('embedContainer');
document.getElementById('loader-container').style.display="none";
e.style.display = 'block';
console.log(errors);
}
}
@endif
embedPowerBIReport();
</script>
</body>
</html>
1 Reply
You need a Power BI App Registration. Please watch this video
