Power BI: Managing report pages

12 Nov, 2024

One data model, many reports

Sometimes it is necessary to have many reports that link to a single data model. While there may be good reasons to do so, there is one major difficulty that is presented by working in this mode - that is, updating report pages.

Things that are in code (e.g. Power Query changes, DAX changes, new measures) will propagate through all of the reports once the main data model is published to the workspace (they will even propagate to published Apps without needed to update the App).

However, anything that is "one the page", like visuals, which fields are in the field well, on-page filtering, bookmarks does not. This is relevant when we have a case where the same pages used across multiple reports, such as inthe example below. In this example, the data model .pbip file has all of the pages to be used across the children reports, and these pages are selectively shown in the children reports. Let's call this the "Shared Pages" structure. For the rest of this post, let's assume this is the structure we're working with.

Shared Pages

Here, we can see the the pages Projects and Hours are used in two different reports, and they should be the same across both reports. When a change is made to the Projects page in the Project Manager's Report, we will need to see that same change appear in the Staff Manager's report as well.

Doing this by hand will take a long time and will be prone to errors and veriations. To get around this, let's try getting the compter to do the work for us.

Exclusive Pages

The file with the main data model is not displayed to the end users. This makes it a good spot to house diagnostic pages for quick troubleshooting (e.g. number of records, overall sums, overall trends).

Approach

We want to solve the following problems:

1. How can we make just one change, in one place?
2. How can we make that change appear in all of the other places?

One change, one place

The natural place to make changes is in the main data model file. This houses all of the report pages.

Cloning pages

Once the change is made to the master file, the report page needs to be copied multiple times, as many as there are reports. Then, for for each report, we need to show/hide all of the relevant pages for that particular report.

Setup

Get the pages of the reports

In the first instance, every report needs to be set manually. Once the pages have been set, we will generate a file called pages.json which lists what the page is, whether it's shown or not, and its position in the array determines its order.

For the Project Manager's Report, we want to generate a pages.json that looks like this.

[
   {"pages": "Customers", "show": true},
   {"pages": "Projects", "show": true},
   {"pages": "Hours", "show": true},
   {"pages": "Employees", "show": false}
]

Note that some pages are shown and other are hidden, and that the ordering of the array is the same as the order of pages we want to see.

How I do this is that I manually arrange the .pbip file, save it, and then use some PowerShell to rip out a pages.json.

using namespace System.Collections.Generic; # for List
function ripPages ($dest, $report) {
    [List[PSCustomObject]] $pages = @()
    $report.sections | 
        ForEach-Object {
            # the first page does not have an "ordinal" property, so need to manually add it in so we can sort properly in the sort step
            if ($null -eq $_.ordinal) {
                $_ | Add-Member -NotePropertyName ordinal -NotePropertyValue 0
                $_.ordinal = [int] 0
            }
            # convert the data type to integer - originally text
            try { $_.ordinal = [int] $_.ordinal } catch { }
            $_
        } | 
        Sort-Object ordinal |
        ForEach-Object {
            $config = $_.config | ConvertFrom-Json -Depth 20
            $pages.Add(
                [ordered] @{
                    page = $_.displayName
                    # ordinal = $_.ordinal
                    show = ($config.visibility -eq 0) -or ($config.visibility -eq $null)
                }
            )
        }
    $pages | ConvertTo-Json > "$dest\pages.json" -Compress

Set the pages

Every .pbip project has a file called report.json that has all of the visual information required to generate the on-screen reports. This file includes what pages are shown and hidden, and what order they are in. We will use pages.json to programmatically set reports.json to suit out requirements.

function pbi-setPages ($path) {
    $project = $path.split('\')[-1]
    $reportFile = ".\$project\$project.Report\report.json"
    $report = Get-Content $reportFile | ConvertFrom-Json -Depth 20 
    $pagesFile = ".\$project\$project.CodeFiles\pages.json"
    if (Test-Path -Path $pagesFile) {
        $pages = Get-Content $pagesFile | ConvertFrom-Json
        $pages |
            ForEach-Object {
                $_ | Add-Member -NotePropertyName ordinal -NotePropertyValue $pages.IndexOf($_)
                $_
            }
            $show = ($pages | Where-Object { $_.show }).page
            $report.sections | 
                ForEach-Object {
                    # set hide/show
                    $config = $_.config | ConvertFrom-Json -Depth 20
                    if ($_.displayName -in $show) {
                        Write-Host $_.displayName
                        # 0 is show, 1 is hide
                        if ($config.visibility -eq $null) {
                            $config | Add-Member -NotePropertyName visibility -NotePropertyValue ""
                        }
                        $config.visibility = 0
                    }
                    else {
                        $config.visibility = 1
                    }
                    $_.config = $config | ConvertTo-Json -Depth 20 -Compress
                    # set page order
                    $displayName = $_.displayName
                    $ordinal = ($pages | Where-Object { $_.page -eq $displayName })[0].ordinal
                    # Write-Host "$displayName $ordinal"
                    if ($null -eq $_.ordinal) {
                        $_ | Add-Member -NotePropertyName ordinal -NotePropertyValue $ordinal
                    }
                    $_.ordinal = $ordinal
                }
                $report | convertto-json -Depth 10 > $reportFile
    } else {
        Write-Host "No pages.json file found. Rip it out first using pbi-rip"
    }
}

Propagating

What I do is the following:

1. Make changes in main data model
2. Publish to the workspace
3. Download the file as just a report (linked to data)
4. Save-As the file as each of the children report
5. Run pbi-setPages on all of them

Can't do report-level measures

One thnig we haven't addressed is the fact that you can have measures in the report, but not the main data model. Sometimes it makes sense to do this, but in the Shared Pages structure, you can't have report-level measures, because they will be overwritten every time.

Report-level measures are only really viable if you have an Exclusive Pages structure, where the report pages will never be overwritten by another file.

#powerbi #powershell #reports