Yep, today it’s reporting. Lately I’m trying to give some shape to the governance of an organization, and well, reports having a unified look it’s a good thing. At the very least for those supposed to be «Tier 1» or «The reports that really matter to top brass people». As with business logic, I’m all in when it’s about centralizing stuff in one place, and report themes are indeed this: a file that stores the default configuration for pretty much any setting of your visual objects, page and so on. However, Power BI Desktop does not really allow you to setup everything in the theme, and the alternatives were either editing a huge json file or using tools that were not quite up to the task until not long ago. This has changed and I’m completely addicted to the theme generator that Mike Carlo has put together in Powerbi.tips. IT’S AWESOME.
Disclaimer: I’m not getting paid to write this post, I just think it’s a great tool and has helped me (which I’m not quite a design guy to put it mildly) make decent reports (which was my goal). Let it also be known that Mike is kind enough to provide premium licenses to MVPs like myself, as other companies do. I think the license price (3.15$ a month or 25 a year) is very reasonable. The premium has 2 killer features, saving files so that you can keep tuning them (there’s no upload file so otherwise you need to start from scratch every time) and see how the theme looks on some visuals that show up with dummy data. Not all visuals are supported at the moment — I miss matrix the most.
Now that we’ve put this out of the way, I want to show not just the tool, which by itself is already great, but specifically how you can set up the background image. For a more general overview of the tool Mike himself will show the tool in a couple days. So I’ll jump straight into the background thing.
Before we jump into the tool, we do need a background image. The easiest way you can go about it is to create it with PowerPoint. And the easiest design you can go with is just a light gray background with some brand color for the top bar with a logo on the left hand side.
Now we can save this as svg so that the shapes stay nice and clean ( I haven’t tested alternatives, this is just my happy path ). For some reason in my first tries I selected all images rightclicked and selected save as image. Don’t do that. It adds some margin around that destroys the whole purpose. Instead just go to file –> Save as –> and select «svg» on the file type dropdown and say «just this one» on the pop-up question
Ok. So now we have the background image. If we are not ready for themes, we can load this manually to Power BI Desktop, and it will look nice. HOWEVER: we’ll have to do that for each and every report sheet, and it’s not a 1 click operation:
- Go to page properties (2 clicks)
- Navigate to the file (at least 2-3 clicks)
- Reduce transparency to 0% (why on earth is set by default to 100%??) 1 click + drag
- Make it fit the whole page (2 clicks)
Multiply this for all sheets in all your reports and you end up with a «f-it I’ll just leave it white» situation.
So how can the Theme Generator help us with that? If you really wanted to do that, actually you could long long time ago. Indeed more that 2 years ago (time flies!) good twitter friend Johnny Winter did this nice video where he went in detail on how to spice up your custom theme file to include the background. Lucky for all of us, this is now waaaay easier.
Go to powerbi.tips, and go for «Tools» –> «THEMES – NEW»
By default your are brought to the «Palette» type where you define the colors of your theme, but on the top you can switch to the «Properties» which is where we want to go now. You should definitely set up the color scheme you want to use. I used coolors.co website to help me pick. It’s a bit tricky that the free version only allows you to pick up to 5 colors, but you can remove some and keep picking so hopefully the colors will play well together. I think there are plenty of other sites that do similar stuff.
Anyway, so we go into properties, and we look for «page» on the «Chart picker» box and then on «Page Background»
Here we see that if we want to use an image, we need to provide an URL (of course). And this is basically what I want to talk about: This URL cannot be from SharePoint, oneDrive or Google Drive. Even if you set the file to be publicly available to anyone with the link, it just does not work. It would be nice if it did, but it does not. Deal with it. So what is to be done?
We have basically 2 alternatives. Or at least I only know 2 alternatives.
UPDATE: After posting this on LinkedIn several people have told me that it’s possible to hardcode an image in base 64 instead of providing a URL (see this, or this or this for details on how to get the base64 image, shout out to Injae Park, Johnny Winter and Argenis Chaffardet for the feedback ) ! I’ll discuss the pro’s and cons at the very end of this post. Let’s just say that instead of a URL of an image you can use some long code that is read as an image.
One is to put the file along with any other image files that are being used in the company website, if you can get somebody to put it there. If you are using wordpress, it’s weird that it does not really allow you to upload svg files saying you don’t have enough permissions (even as an admin!). But I did manage to get it though an ftp service that our host provides along with the domain and the wordpress thing. We are using Dreamhost, but probably others do similar stuff.
First thing is to look for the wp-content / uploads folder
If you grab the URL of any of the images of the website, the chances are that you will see the folder as part of the URL
So from here it’s easy to figure out what will be the public URL of the images you upload through the file manager. And yes, this URL will play fine with Power BI.
I know, this is a pretty rough approach, and it might be a no-go in your organization. So isn’t there a better way?
Once again Johnny Winter gave the the tip: «You can use a blob storage to store the file and create secure a link to it».
I wanted to do that in one of my clients, and linking to Esbrina’s website is not serious and I had no way of uploading files to their public website. But I did have a blob storage at hand — the one that I used to store all the JSON’s of metadata (see this post). So I googled a bit and gave it a try.
Not sure if there’s a better way, but this is how I did it. To upload the file to the blob storage I used Azure Storage Explorer and uploaded the file to my blob storage. (Nevermind the container, you should put it where it’s right)
Now to get that secure URL I don’t think it can be done from here, so we need to go into the Azure Portal and look for the same file there. Once you find it right click on it and select «Generate SAS» (yes, trust me!) Aparently SAS stands for «Shared Access Signature»
And then we need to set up an expiry date well into the future (I don’t think you can set forever links this way — and for some reason it did not allow me to change the date using the keyboard, only through the date picker, so weird). When you click «Generate SAS token and URL», the token and the URL boxes appear. We want to copy the contents of the 2nd one which are indeed the URL that we can use in the theme.
So with either approach now you have a URL. Time to go back to the theme generator and use it!
Make sure to set up all 4 fields! For the name you can put anything you like, but you need to provide a name. (UPDATE: If you are using a base64 image, use the long code here instead of the URL)
But does it work? Let’s check it out!
Click download and get the JSON file (the downward arrow, save is to store it in power.tips, requires premium license)
Now go into Power BI Desktop –> View –> Themes –> Browse for themes and boom!
A background for all your pages! create a page and the background is already there!! Sweet huh? I love it!
Of course setting up titles, headers and so on is also huge, but I wanted to focus on the background part.
Hope you liked it! It has definitely upped my report game
I hope at some point PowerBI.tips offers the possibility of duplicating a theme, since you might have a common style for tables and charts but a different background for each department for example. If you also want this to happen, let Mike Carlo know here!
Follow the conversation on twitter and linkedin!
PS. Of course if you save this as a Power BI Template (.pbit) it will use the theme every time but it’s nice that you can also use the JSON to update the look of an older report that needs to come in line with the requirements of a corporate report 🙂 Oh, and the image is called every time, so don’t delete it from the blob storage!
UPDATE: So can we skip the whole part of generating the URLs and go the Base64 image path instead? Absolutely. In many situations this might be your only way if you don’t have either a public storage like a public facing website, or a blob storage. However, the URL path does have a merit I forgot to bring up in the original post, and that merit is that if the company makes a Logo-redesign (and the rest of the theme is not affected) you can simply replace the image with the new one and then all reports will be automatically updated, which is nice.