Utilizing JavaScript For Energy BI Filters in PBRS

Some of the helpful options of PowerBI Reviews is the flexibility to set report-level filters in order that the data displayed inside it may be related to the buyer.

What are Filters in Energy BI?

Filters in Energy BI distill and kind information and knowledge primarily based on a particular standards. That’s, you’ll be able to choose specific fields or values inside fields and think about solely the data associated to that. As an illustration, in case you have a dataset associated to gross sales of a retailer, you need to use Filters to view a report having solely the info for the chosen elements.

For instance, in the event you solely wish to see the gross sales info within the Charlotte space within the 12 months 2019, all it’s important to do is to place the filtering criterion as 2019 and Charlotte of their respective fields. The report of gross sales will instantly make adjustments accordingly and solely present the graphs and visuals particular to gross sales statistics in Charlotte and within the 12 months 2019.

How Does PBRS Assist Report-Stage Energy BI Filters?

PBRS has at all times supported passing report-level Filters to Energy BI reviews in order that when the report is routinely generated, it’s delivered with simply the correct info. In reality, PBRS customers love utilizing Information-Pushed subscription schedules to run a number of cases of Energy BI reviews, with filters configured through the data-driver after which delivered to information pushed recipients, the ensuing exported output file containing solely the data related to the recipient.

One problem that has at all times plagued the extra superior consumer, nonetheless, is when a Energy BI report comprises greater than a few filters to configure. Because the variety of report filters will increase, the method of organising the PBRS schedule shortly turns into tedious and vulnerable to errors.

Within the September 2020 launch of PBRS, we addressed this drawback by permitting our superior customers to configure their Energy BI Filters through JavaScript.

Utilizing Javascript To Configure PBRS Filters

As an alternative of spending a very long time manually coming into a single filter at a time, PBRS customers can now merely write the JavaScript operate of their favourite code editor (or in PBRS itself) after which put it aside as a part of the schedule. Moreover, the JavaScript operate might be saved in the long run consumer’s database and pulled into the schedule utilizing a data-driver insert.

Assemble Energy BI Filters With JavaScript

Microsoft has detailed info on tips on how to create Filter objects in JavaScript and this may be discovered right here:

https://github.com/Microsoft/PowerBI-JavaScript/wiki/Filters#contructingfilters

As soon as the filters have been created, the one requirement from PBRS is that it’s positioned inside a JavaScript operate that returns an array of all of the filters. An instance of this may be seen under.

Perform getFilters(){
let basicCustomerFilter = …..;
let advancedRegionFilter = ……;
let employeesFilter = …..;
return [basicCompanyFilter, advancedRegionFilter, employeesFilter]
}

As a way to streamline the creation of the filter operate for PBRS data-driven schedules, it is suggested to construct a SQL operate or Saved Process that accepts a key identifier and returns the total operate. That is so that each one the logic might be modified in a single place and doesn’t need to be created in an inline SQL question.

For instance:

SELECT dbo.Custumer.CustomerId, dbo.Buyer.EmailAddress, dbo.GetPowerBiFilterFunction(Buyer.CustomerId) AS PowerBiFilter FROM dbo.Buyer

With this question because the data-driver, the PowerBi Filter column can merely be dropped into the Filter JavaScript subject.

Tell us in case you have any questions or strategies about this characteristic.

Tutorial: Information Pushed Subscription Scheduling for Energy BI

Get Options PDFBegin Free Trial