Creating a Power BI report for specific sites

What are the advantages to creating reports for a specific site or group of sites? 

There are a few reasons why you may want to create reports for a specific or group of sites:

• Site-specific report data downloads faster, resulting in reports that perform better.
• Producing separate, specific reports avoids the risk of sharing data with the wrong parties, if you’re sharing the reports with different team members or clients.
• You can maintain different report branches for different reporting requirements across customers or sites.
• Site-specific reports can help if you’re having trouble with Power BI authentication issues. (See Troubleshooting authentication issues in Power BI.)

Note: Throughout this article we use the terms “clients,” “sites,” and “tenants” interchangeably. You’ll see “sites” and “clients/sites” on the dashboard when creating new reports. The Auvik API uses the word “tenants.”

Why do we use IDs rather than domain prefixes to identify sites?

Auvik creates unique identifiers (UIDs) for the purpose of differentiating elements in our database. Devices, interfaces, and alerts are common examples of where UIDs are used. Sites will also have their own UIDs, which helps us keep track of them even if the company name or the domain for the site changes.

You’ll need to know the tenant UID to create client-specific reports. 

How to find your tenant UID master table

Using our default Power BI template, you’ll see a table with the names and details of all your sites and their corresponding tenant UIDs. To run this report: 

1. Navigate to the Power Query Editor.

2. In the Power Query editor, select  Client / Sites Details.

You’ll see the following table:

The table shows you each tenant unique ID. The client name and/or domain prefix columns will help you further identify the sites.

3. Copy this table and paste it into a spreadsheet where you can go back to it for future reference.

Two methods for passing the tenant UID as a filter in each query

For each query, you can specify one or more tenants you’d like to get data for. See our API documentation for full details.

To specify tenants, use the: “tenants=” filter and include the UIDs for the tenants you’d like to filter on: 

tenants=199762235015168516,199762235015168004 

The default Power BI template needs to be modified to add the tenant filter to each individual query. There are two methods for doing that, each with its pros and cons.  

Method 1: Using parameters to create site-specific reports

When first creating a report using our Power BI template, you’ll see this prompt:

By default, you’re asked for reporting period dates, server cluster, and domain prefix. By adding a parameter for site-specific reports, you’ll get a new box where you can add the sites every time you create a new report.

Pros

• This method lets you use a single template (.pbit file) to quickly create the same report for specific sites. 
• You can quickly iterate with other customers to create and save different reports, while only having to maintain a single master template. 

Cons

• By using parameters, you won’t be able to use the report in Power BI Pro. 
• You’ll need to define a default tenant UID. If you share the report with someone else, they’ll be able to see that UID. This could cause confusion. 

1. Navigate to the Power Query Editor.

2. Create a new parameter.

• Use a name that you and other report users can easily identify.
• Set type as text.
• Provide a default tenant UID. Note that any UID entered here is visible to anyone with access to the report.

3. Click OK to save.

Note: Repeat this process in all of the tables that are dynamic queries—in other words, all the tables that aren’t static. If the new parameter isn’t added to every one of the queries, the entire report may fail.

We called our parameter Client / Site ID. We refer to this parameter in the steps below. You can use any name you prefer.  

After you’ve created the parameter, you need to embed it in each of the queries in the report.

1. Start by selecting one of the tables. Right-click to open the drop-down and select Advanced Editor.

2. You’ll see the code editor. Locate the part of the code that builds the URL for the query. The main line often looks similar to this:

   startUrl = UrlPrefix & timefilter & pagination, 

    This is where the report builds its query from different components. 

3. Add the parameter you created directly on this line. Since we named our Client / Site ID, our code looks like this:
   

   startUrl = UrlPrefix & timefilter & pagination & "&tenants=" & #"Client / Site ID",

   The ”&tenants=” code refers to the static part of the filter. The #"Client / Site ID" part is dynamic. It’s where Power BI automatically inserts the UID you provide when you fill in the parameters prompt at the time of creating a new report.

Repeat this process in all of the tables that are dynamic queries—in other words, all the tables that aren’t static. If the new parameter isn’t added to every one of the queries, the entire report may fail. 

Time and Date are static tables and don’t require that you add parameters. Some of the items in the Queries column, such as Server Cluster and Report Dates, are other parameters and also don’t require editing. The diagram below shows you which tables require editing because they’re dynamic.

After you make your changes to the report, we recommend saving your edits as a new version of the template file. Then, every time you go to create a report for those customers or sites, you can open the new template rather than the default template from Auvik.

With this new template, every time you want to create a new report, the initial prompt will look like this:

In the Client / Site ID field,  you’ll need to copy the tenant UIDs from the spreadsheet you exported them to. You can create a report for more than one site by adding commas (without any spaces) between the tenant UIDs.

Method 2: Adding fixed clients to each individual report

Through this approach, you’ll generate a new copy of the report for each client or set of clients as needed. There’s no limit to the number of clients you can include on a report.

This method is compatible with publishing in Power BI Pro. 

1. Navigate to the Power Query Editor.

2. For each table, right-click to open the dropdown and select Advanced Editor.
   
   

You’ll see the code editor. Locate the part of the code that builds the URL for the query. It looks similar to this:

startUrl = UrlPrefix & timefilter & pagination,

Add the &tenants= filter along with the UIDs you’d like to report on. It will end up looking like this: 

 startUrl = UrlPrefix & timefilter & pagination & "&tenants=507799876112,50779987611",

Some dynamic tables have slightly different code, so for these you'll need to append the &tenants=<tenantID> filter to the query.

For example, this is what the Device Inventory table (along with Warranty Data, Device inventory Details, Device Configuration) will initially look like:

startUrl = "https://auvikapi." & #"Auvik Server Cluster" & ".my.auvik.com/v1/inventory/device/info?page[first]=50"

Once you add the &tenants= filter, it will end up looking similar to this:

startUrl = "https://auvikapi." & #"Auvik Server Cluster" & ".my.auvik.com/v1/inventory/device/info?page[first]=50" & "&tenants=507799876112,50779987611",

Where the numbers after &tenants= are the tenant IDs from the sites that you would like to report on in this file. 

Note: Repeat this process for all of the dynamic tables.

Save this report as something like Report for Clients X and Y, or whatever name makes sense for you.

After you make your changes to the report, we recommend saving your edits as a new version of the template file. Then, every time you go to create a report for those customers or sites, you can open the new template rather than the default template from Auvik.