Data Filtering

[SergioRota]

I followed the Trond’s guide and I see my Power BI report in Dynamics 365 dashboards and forms 🙂

I have some issues about data filtering: following your guide I created the web resource for Account filtering (newaccountfilter.js); I adding this in his/powerbi/scripts/config.js:

is this correct?

After in the form I modified the web resource properties adding &customFn=Samples.Filters.filterOnAccount and selected the option to pass the record Id

Thanks Sergio

[taarskog]

No it is not correct - I assume you followed the guide on http://crm-powerbi-viewer.heiigjen.com/pages/advanced-config.html?

If you look at the last of the 2 script references you see the one you should use when referencing a web resource: "../../prefix_/scripts/eventhandlers.js?ver=1"

So in your case it will be: "../../new_/accountfilter.js?ver=1"

Don't forget the last double quote (").

[SergioRota]

Thanks! Unfortunately I don't see data filtered yet in my account form. I explain my steps:

1) I have a Report in PowerBI (I copy It)

P.S. in my report I have insert a table about accountid

2) I created new JS Web Resource: new_accountfilter

window.Samples = window.Samples || {};
Samples.Filters = Samples.Filters || {};
/**
 * Filter on user by name whenever a specific page is shown.
 * 
 * Note: This resets the filter whenever the user navigates to the specific page. 
 * Remove the event logic and set the filter once to avoid resetting on every navigation.
 * 
 * Suggested modifications:
 * - In your own code you should check against page name and not displayname to avoid 
 *   breaking the code if someone changes the displayname
 * - Filters should be on ID and not name
 * - Using Xrm get name/id from current user or owner if view is embedded to a form.
 */
Samples.Filters.filterOnAccount = function (report) {
    report.on("pageChanged", function(event) {
        let page = event.detail.newPage;
        console.log(`Page changed to '${page.displayName}' (${page.name})`);

        if (page.displayName === "Pagina") {
var accId = window.parent.Xrm.Page.data.entity.getId();
accId = accId.replace('{','').replace('}','');
            const filter = {
                $schema: "http://powerbi.com/product/schema#basic",
                target: {
                    table: "accounts",
                    column: "accountid"
                },
                operator: "In",
                values: [accId]
            };

            page.setFilters([filter]);
        }
    });
}

3) in Web Resource his_/powerbi/scripts/config.js I added the reference in custom_scripts

var customConfig = {
    ///////////////////////////////
    //
    // ITEMS THAT MUST BE CHANGED
    //
    ///////////////////////////////

    // See documentation on how to register the application in Azure AD and get a client id.
    auth_client_id: "3acf6c02-83c9-49d7-8dd0-6ea3c1511035",

    ///////////////////////////////////////////
    //
    // CAN USUALLY BE LEFT UNCHANGED 
    // (unless specified in the documentation)
    //
    ///////////////////////////////////////////

    // How to perform authentication - valid values are "inline" or "popup". Default is "inline".
    // You should typically use "inline" on Dynamics 365 Online with users located in your Azure AD and "popup" when on-premise (might also be required if you have invited external parties to your Azure AD).
    auth_mode: null,

    // Auto-refresh access - may cause page reload if token cannot be updated silently. Default is true.
    auto_refresh_token: null,

    // Where to cache tokens. Valid values are 'sessionStorage' and 'localStorage'. Default is 'sessionStorage'.
    auth_cache_location: null,

    // Logging of Adal authentication process [Valid values are 0-3] (0=ERROR, 1=WARNING, 2=INFO, 3=VERBOSE). Default is 0.
    auth_log_level: null,

    // Log level for Power BI Viewer [Valid values are 0-3] (0=ERROR, 1=WARNING, 2=INFO, 3=DEBUG). Default is 0.
    // (is not affected by and does not affect auth_log_level)
    log_level: null,

    // Array of custom scripts to load. Functions in these scripts can be referenced for report filtering etc.
    custom_scripts: "../../new_/accountfilter?ver=1"
};

4) in Account Form I added a existing Web Resource his_/powerbi/viewer.html and in Custom parameter I paste PowerBI report and I added the function from Web Resource named new_accountfilter

5) Save and Public All

In my Account Form I see PowerBI report but Account table isn't filtered by accountid

Thanks Sergio

[taarskog]

I may have mislead you. You config should maybe be "../../new_accountfilter.js?ver=1". Verify by checking the url of your web resource and adjust as needed.

If you still have trouble please take a look at the debug console (F12). Errors should be logged there. If you are not able to find the issue I suggest you post a screenshot here...

Also verify that the table name match what you have in your report (including casing).

[SergioRota]

The Web Resource with Account Filter function is: new_accountfilter The reference in customscript about his/powerbi/scripts/config.js is "../../new_accountfilter.js?ver=1"

.js isn't a part of name of my web resource but have I to insert it in the reference?

About the table name in PowerBI:

Here's the console errors:

Thanks Sergio

[taarskog]

No in that case you should leave it out. I use .js as a convention on all my JavaScript resources.

[SergioRota]

Ok, I corrected the web resource.

now these error occur:

[taarskog]

Try with the browser in incognito mode (may be a caching issue)… Also try to bump the version ?ver=2

[SergioRota]

Always the same

Does the Web Resource about account filtering correct?

window.Samples = window.Samples || {};
Samples.Filters = Samples.Filters || {};
/**
 * Filter on user by name whenever a specific page is shown.
 * 
 * Note: This resets the filter whenever the user navigates to the specific page. 
 * Remove the event logic and set the filter once to avoid resetting on every navigation.
 * 
 * Suggested modifications:
 * - In your own code you should check against page name and not displayname to avoid 
 *   breaking the code if someone changes the displayname
 * - Filters should be on ID and not name
 * - Using Xrm get name/id from current user or owner if view is embedded to a form.
 */
Samples.Filters.filterOnAccount = function (report) {
    report.on("pageChanged", function(event) {
        let page = event.detail.newPage;
        console.log(`Page changed to '${page.displayName}' (${page.name})`);

        if (page.displayName === "Pagina") {
var accId = window.parent.Xrm.Page.data.entity.getId();
accId = accId.replace('{','').replace('}','');
            const filter = {
                $schema: "http://powerbi.com/product/schema#basic",
                target: {
                    table: "accounts",
                    column: "accountid"
                },
                operator: "In",
                values: [accId]
            };

            page.setFilters([filter]);
        }
    });
}

My goal is open account form and view a report with only the information about this account (account id)

Thanks Sergio

[taarskog]

Think I found your bug. Just went through the process and it is working well for me.

The issue is with your config. crm-powerbi-viewer supports multiple custom scripts. Thus the value custom_scripts is expected to be an array.

Yours:

    // Array of custom scripts to load. Functions in these scripts can be referenced for report filtering etc.
    custom_scripts: "../../new_/accountfilter?ver=1"

Should be:

    // Array of custom scripts to load. Functions in these scripts can be referenced for report filtering etc.
    custom_scripts: ["../../new_/accountfilter?ver=1"]

May consider adding support for a string here, but for now you need to add the brackets [ ].

[SergioRota]

It works =D

Thanks you!