Blog

C# Scripting the report layer with Tabular Editor

Hello!

Today’s post is quite special for me as I wished that this day would come. When I wrote the article to generate the measures for a dynamic data problems button there was a section that read «So, let’s build this button then. This part, unfortunately cannot be scripted, so we’ll need to work a little bit.». Well, maybe this is a thing of the past. Let’s see how can we script this part too. Before going diving in though I want to thank Xavi Paterna for helping me out with his tremendous knowledge of C# programming.

TL;DR it’s easier to modify the report layer programmatically!

Last month Microsoft launched the public preview of «Power BI enhanced report format (PBIR) in Power BI Desktop developer mode». This is one step forward on the Power BI Project format that saves all the little bits of the pbix in different files. This is good for version control, but also allows to do a lot more things than we could do connected to PBIX files. First they developed TMDL to split out the model in different little files. Next they did something similar for the report layer. PBIR files are still JSON files, but very granular, meaning that each page is a folder, each visual has its own folder with a single json file and so on. Microsoft has published the schema for all these files so you can do things with some confidence that it will not blow up. That being said, at the time of this writing there still plenty of limitations on reports using PBIR format, so don’t even think on using it for anything in production. But it’s clear that this is the future, so the earlier we get to know it, the better.

Let’s start easy

I thought it would be fun to develop some script that created something on the report layer. Something that I may do more than once and that is boring to build because it’s always the same. And it came to my mind that one such candidate would be the «data problems button» that I was mentioning at the beginning. As I did not want to break anything I decided that my first iteration would add a new page to the target report and add the button already configured with all the measures required, and any extra formatting.

Step 1: Identify the measures in the old script

Since PBIR is nowhere near mainstream, I decided to keep the two scripts separated. One script would generate the measures and the other one would build the page with the button. The idea is that then the user would move the button elsewhere and build all the visuals showing the data problems of the model there. Scripting all those visuals might be the topic of another article. Anyway, in order to build the button creation script I did not want to have to ask the user to point out the measures created by the first script, so I did some modifications to it. Using the trick I mentioned on this other article object annotations can be very useful. Are sort of breadcrumbs to find stuff later on, even if they used a different name, or moved the measure to a different table for example.

To the code of the original script I added 4 variables:

string annotationLabel = "DataProblemsMeasures";
string annotationValueNavigation = "ButtonNavigationMeasure";
string annotationValueText = "ButtonTextMeasure";
string annotationValueBackground = "ButtonBackgroundMeasure";

I reuse the one for annotation label, and then I use a different annotation value for each measure. Then, when I create each of the measures, I add the annotation.

buttonBackgroundMeasure.SetAnnotation(annotationLabel, annotationValueBackground);
...
buttonNavigationMeasure.SetAnnotation(annotationLabel, annotationValueNavigation);
...
buttonTextMeasure.SetAnnotation(annotationLabel, annotationValueText);

Step 2: At the beginning of the new script, make sure that all measures exist!

It’s best to do all checks before anything else, so in the new script I replicated the variables and added some code to check that indeed there’s a measure for each pair of annotation label and value.

string annotationLabel = "DataProblemsMeasures";
string annotationValueNavigation = "ButtonNavigationMeasure";
string annotationValueText = "ButtonTextMeasure";
string annotationValueBackground = "ButtonBackgroundMeasure";
string[] annotationArray = new string[3] { annotationValueNavigation, annotationValueText, annotationValueBackground };
foreach(string annotation in annotationArray)
{
    if(!Model.AllMeasures.Any(m =﹥ m.GetAnnotation(annotationLabel) == annotation))
    {
        Error(String.Format("No measure found with annotation {0} = {1} ", annotationLabel, annotationValueNavigation));
        return;
    }
}

Step 3: Make the user point out to the PBIR file

With Tabular Editor we are connected to the analysis services instance, but there’s no way (that I know of) to trace back from which file or folder structure this has been done. Therefore it’s even more impossible to find the report that we want to modify out of thin air. We need to ask the user. That’s something I had not done in the past, but with some ChatGPT+ it was a no-brainer. Of course if the user cancelled, it’s time to stop. I just made up the file filter, but apparently it works!

// Create an instance of the OpenFileDialog
OpenFileDialog openFileDialog = new OpenFileDialog();

openFileDialog.Title = "Please select definition.pbir file of the target report";
// Set filter options and filter index.
openFileDialog.Filter = "PBIR Files (*.pbir)|*.pbir";
openFileDialog.FilterIndex = 1;

// Call the ShowDialog method to show the dialog box.
DialogResult result = openFileDialog.ShowDialog();

// Process input if the user clicked OK.
if (result != DialogResult.OK)
{
    Error("You cancelled");
    return;
}

// Get the file name.
string pbirFilePath = openFileDialog.FileName;

Step 4: Create a couple of unique identifiers

Since we are adding a page with a visual, we’ll need a unique identifier for each of them (just in case).  This is pretty straight forward once  you see how it’s done:

string newPageId = Guid.NewGuid().ToString();
string newVisualId = Guid.NewGuid().ToString();

Step 5: Get references to each of the measures

This is also pointed out in my article on how to write c# scripts. To retrieve the object with certain combination of annotation label and value, just filter all the objects of the model for that condition and pick the first one. Well, in 99% of the cases there should only be one. Why would you add such annotation to another measure? Or why would you create copies of it? I mean, you could check the number, and if it’s more than one ask the user to choose and so on, but why bother for such a small use case?

The code looks like this:

Measure navigationMeasure = 
    Model.AllMeasures
        .Where(m =﹥ m.GetAnnotation(annotationLabel) == annotationValueNavigation)
        .FirstOrDefault();
Measure textMeasure = 
    Model.AllMeasures
        .Where(m =﹥ m.GetAnnotation(annotationLabel) == annotationValueText)
        .FirstOrDefault();
Measure backgroundMeasure = 
    Model.AllMeasures
        .Where(m =﹥ m.GetAnnotation(annotationLabel) == annotationValueBackground)
        .FirstOrDefault();

Step 6: Define the template for the page and the visual

I did not want to have to download any extra files along with the script so instead we just build a pretty long string variables with the contents of the JSON files that we want, but with some placeholdrers that need to be replaced. To create these strings is enough to build whatever we want manually, save in the PBIR format and have a look at the JSON files. The page.json file is quite easy. There are some properties, includeing a «name» property that must match the name of the folder where the json file is located, under the «pages» folder. In our script this will look like this:

string newPageContent =@"
{
  ""$schema"": ""https://developer.microsoft.com/json-schemas/fabric/item/report/definition/page/1.0.0/schema.json"",
  ""name"": ""{{newPageId}}"",
  ""displayName"": ""Problems Button"",
  ""displayOption"": ""FitToPage"",
  ""height"": 720,
  ""width"": 1280
}";

You see we need to double our double quotes and stuff, but if you work on visual studio and paste whatever text inside the quotes of a string with this value @»» it will take care of everything, new lines, double quotes etc. As you can see I already entered the place holder {{newPageId}} to make it easy to replace it later. For now that’s all I need to change, but you see that any property could be customized during the creation process.

The visual template is way longer, but it follows the same structure. Interestingly the measure name and the name of the table hosting the measure are different properties. Only a fraction is pasted here for brevity

string newVisualContent = @"{
	""$schema"": ""https://developer.microsoft.com/json-schemas/fabric/item/report/definition/visualContainer/1.0.0/schema.json"",
	""name"": ""{{newVisualId}}"",
	""position"": {
		""x"": 510.44776119402987,
		""y"": 256.1194029850746,
		""z"": 0,
		""width"": 188.0597014925373,
		""height"": 50.14925373134328
	},
	""visual"": {
		""visualType"": ""actionButton"",
		""objects"": {
			""icon"": [...],
			""outline"": [...],
			""text"": [
				{...},
				{
					""properties"": {
						""text"": {
							""expr"": {
								""Measure"": {
									""Expression"": {
										""SourceRef"": {
											""Entity"": ""{{textMeasureTable}}""
										}
									},
									""Property"": ""{{textMeasureName}}""
								}
							}
						},
                                                ...
						}
					},
					...
				}
			],
			...
		},
		""visualContainerObjects"": {
			""visualLink"": [...]
		},
		""drillFilterOtherVisuals"": true
	},
	""howCreated"": ""InsertVisualButton""
}";

Here, the point is that no matter how complex the object is, you can always find the little things that you need to modify to make it work on the target data model.

Step 7: Build the dictionary to replace placeholders

I would have gone with probably a less sophisticated approach, but Xavi told me to build a dictionary so we can encapsulate the code of replacing all place holders in a separate function.

Building the dictionary is rather straightforward:

Dictionary﹤string,string﹥ placeholders = new Dictionary﹤string,string﹥();
placeholders.Add("{{newPageId}}", newPageId);
placeholders.Add("{{newVisualId}}", newVisualId);
placeholders.Add("{{textMeasureTable}}",textMeasure.Table.Name);
placeholders.Add("{{textMeasureName}}",textMeasure.Name);
placeholders.Add("{{backgroundMeasureTable}}",backgroundMeasure.Table.Name);
placeholders.Add("{{backgroundMeasureName}}",backgroundMeasure.Name);
placeholders.Add("{{navigationMeasureTable}}",navigationMeasure.Table.Name);
placeholders.Add("{{navigationMeasureName}}",navigationMeasure.Name);

I could create a different dictionary for the page and the visual contents, but hey, I rather that the machine works, not me.

Step 8: A function to replace all placeholders

Probably influenced by Xavi, a proper C# programmer, this part of the code is done in a custom class at the end of the actual script. This is the first method of the static class (static because you can’t create objects out of it, there’s one one instance). It’s really just like a function we can call. It looks like this:

public static class ReportManager
{

    public static string ReplacePlaceholders(string jsonContents, Dictionary﹤string,string﹥placeholders)
    {
        foreach(string placeholder in placeholders.Keys)
        {
            string valueToReplace = placeholders[placeholder];

            jsonContents = jsonContents.Replace(placeholder, valueToReplace);

        }

        return jsonContents;
    }
    ...
}
    

Basically you pass a string and a dictionary and it will replace all placeholders by their value and return the string to you. Nice, isn’t it? And it’s good it was a function because we have to do it twice, one time for the page.json and one time for the visual.json. And probably any object I want to create programatically on the report layer from now on. To call this method we need to do it like this:

newPageContent = ReportManager.ReplacePlaceholders(newPageContent,placeholders);
newVisualContent = ReportManager.ReplacePlaceholders(newVisualContent, placeholders);

So we need to use the Class name as prefix. Not too bad.

But you know what? At this point NOTHING has changed on the report yet. Sad isn’t it? But hey, things are about to change. Now we have all the pieces we need, to create files, create folders, and modify stuff.

Step 9: Creating the files & folders for page and visual

With the help of Xavi the code is rather simple. This is the second method of our ReportManager class. Let’s have a look at it first:

public static void AddNewPage(string pageContents, string visualContents, string pbirFilePath, string newPageId, string newVisualId)
    {
        
        FileInfo pbirFileInfo = new FileInfo(pbirFilePath);

        string pbirFolder = pbirFileInfo.Directory.FullName;
        string pagesFolder = Path.Combine(pbirFolder, "definition", "pages");
        string pagesFilePath = Path.Combine(pagesFolder, "pages.json");

        string newPageFolder = Path.Combine(pagesFolder, newPageId);

        Directory.CreateDirectory(newPageFolder);

        string newPageFilePath = Path.Combine(newPageFolder, "page.json");
        File.WriteAllText(newPageFilePath, pageContents);

        string visualsFolder = Path.Combine(newPageFolder,"visuals");
        Directory.CreateDirectory(visualsFolder);

        string newVisualFolder = Path.Combine(visualsFolder,newVisualId);
        Directory.CreateDirectory(newVisualFolder); 

        string newVisualFilePath = Path.Combine(newVisualFolder,"visual.json"); 
        File.WriteAllText(newVisualFilePath,visualContents); 

        AddPageIdToPages(pagesFilePath, newPageId);
    }

Here remember we only have the path to PBIR file (that’s already the report, not the pbip file!). From there we need to move in a couple folder. C# has some nice library to extract the folder from a full path so why not use it? From there you can add extra folders without worrying if its a forward or backward slash (or an upslash or downlsash as Johnny Winter might say). It’s interesting that the name of the folder where the PBIR file sits depends on the name of the report, but from there on the structure is fixed. Foldes have either fixed names or really random id names. Let’s play along. PBIR file –> PBIR folder –> Pages Folder –> Create new folder for the new page using our newPageId. And the same story with the visuals.  Important to mention that we create the actual json files with File.WriteAllText function, providing the path and the contents. That’s it.

And you might see a mysterious AddPageIdToPages. That’s the topic of the next point…

Step 10: Modifying the pages.json file

Well, yes, we need to modify one single existing json file. But is a rather simple one. Let’s look at it.

{
  "$schema": "https://developer.microsoft.com/json-schemas/fabric/item/report/definition/pagesMetadata/1.0.0/schema.json",
  "pageOrder": [
    "8f8b3c965803c00bd659",
    "ReportSectiond1ce3549abc27981e560",
    "a36d42b2-0299-4e60-ab2c-64f474c275c0",
    "21131eef0eacd4b2c0b4",
    "860592e4586102e3e663",
    "32898807-5f8b-4c38-9479-07a2510b3130",
    "38ea85b8-de93-40d4-8eb0-a66a7175d28c"
  ],
  "activePageName": "8f8b3c965803c00bd659"
}

it has just 3 things:

  • An schema, which is defined by microsoft, don’t even think about changing this one
  • An array of ids called pageOrder
  • a property called activePageName that is the id of the page you’ll see when you open your report.

We need to change only the second one and add (at the end is fine) our page with our button. Files are already there so it’s just a matter of adding the id in the array. But how can we do it? This time we cannot go the placeholder route because this is already a file of the report. Well, for this we’ll use yet another custom class.

Wait, what? I have no idea on how to define a c# class! Well, me neither. But for both of us, the internet was born. Head over to https://json2csharp.com/ and paste the contents of pages.json. Just like that. Click convert and boom, you have a C# class you can use.

 

Nice, but what to I do with it?? Just paste this after the ReportManager class at the very end and you’ll be fine. But instead of calling it Root, change the name to something fancier, like PagesDto. Dto stands for «Data transformation object». This class will allow us to read the Json into an object with the different properties and so on. We’ll do that in the third and last method of our ReportManager class. Since this method is only called from another method of the class we’ll define it as private.

private static void AddPageIdToPages(string pagesFilePath, string pageId)
    {
        string pagesFileContents = File.ReadAllText(pagesFilePath);
        PagesDto pagesDto = JsonConvert.DeserializeObject﹤PagesDto﹥(pagesFileContents);
        if(pagesDto.pageOrder == null)
        {
            pagesDto.pageOrder = new List﹤string﹥();
        }
        

        if (!pagesDto.pageOrder.Contains(pageId)) { 

            pagesDto.pageOrder.Add(pageId);
            string resultFile = JsonConvert.SerializeObject(pagesDto, Formatting.Indented);
            File.WriteAllText(pagesFilePath, resultFile);
        }
    }

As you can see, the code first reads the contents of the file and stores it into string variable. And then the magic happens, it converts the string into an object with the properties and attributes we just defined using the contents of the actual pages.json. As you can see in the following lines we can refer directly to pagesDto.pagesOrder and that’s indeed the object we saw on the json file. Xavi wanted to be extra careful checking if the array was there and so on, and even checking that the id was not yet in the array. Better safe than sorry they say. And if everything is fine, then it’s the time to finally add the id to the array, and transform back the pages c# object into a json-shaped string variable using JsonConvert.SerializeObject. Oh please use the Formatting.Indented, otherwise it puts it all in one line. And last but not least, writes the string variable back into the actual pages.json file.

Step 11: A little bit of house-keeping

This script is a bit weird because you need to open you pbip project in Power BI desktop*, but also you don’t want that the user saves the file after running the script because that effectively deletes the page again. So let’s ask if they saved the file before running the script and inform them that they need to close Power BI desktop without saving changes and opening again to see the new page.

(* well you could connect to the model metadata folder instead, but then you cannot do that with the TE3 desktop version. )

ScriptHelper.WaitFormVisible = false;

bool waitCursor = Application.UseWaitCursor;
Application.UseWaitCursor = false;

DialogResult dialogResult = MessageBox.Show(text:"Did you save your model changes before running this macro?", caption:"Saved changes?", buttons:MessageBoxButtons.YesNo);

if(dialogResult != DialogResult.Yes){
    Info("Please save your changes first and then run this macro"); 
    return; 
};

The first line is important to avoid seeing a message in TE3 telling you to «wait while executing macro» IN FRONT of the dialog boxes. In TE2 it should not cause any problem, I’ve been told. The following two lines are a bit more treacherous. You need them in TE3 if you don’t want to operate all dialogs with your mouse being a spinning wheel. Yet keep them in TE2 and you’ll get an error… Oh well. From there it’s quite straight forward. We ask a question and if the answer is not yes, it’s time to wrap up.

However… in order to be able to execute this code (and I think the Path thing  we saw on the previous step) we need some external references in our code. From TE3 at least, we need the following two at the very beginning of the script.

using System.Windows.Forms;
using System.IO;

And to let the user know that all is good and that needs to close PBI desktop without saving changes,  let’s show a nice info box and return the cursor to it’s original form:

Info("New page added successfully. Close your PBIP project on Power BI desktop *without saving changes* and open again to see the new page with the button.");
Application.UseWaitCursor = waitCursor;

Conclusion

But does it actually work?? Try it by yourself!

You’ll find the updated version of the two scripts here:

By iterating this approach, the «atomic design» that GOAT Kurt Buhler explained in a recent video is one step closer. Remember that this is still preview and you should not use it for anything else than testing while waiting for GA, or at least some of the limitations lifted. (turns out some limitations have been lifted in July 2024, but others remain!)

If you enjoyed this post, you might like the follow-up I wrote with a more complex use case.

Follow the conversation on Twitter and LinkedIn