2

I currently work at a company where I design PowerBI reports for our clients. Having a large number of clients, I would like to automate the process of creating and maintaining the reports with code.

In idea, I would like to be able to get the code for a "master report" so that we can modify it directly with code without having to open PowerBI. This would not only automate the process of creating a report by duplicating the master report and making all the necessary modifications with code, but also its maintenance. Indeed, when we make a change in a report, we must currently do it manually for all other clients which is very time consuming.

The structure of the reports is identical for each of the clients except for a few details:

  • The SQL query to connect to BigQuery to get the client's data
  • The pages that are shown or hidden (depending on the client, the same number of pages are not shown)
  • The title of 2 slicers
  • The presence or not of 2 slicers depending on the presence of attributes associated with the slicer in the database.
  • The title of some pages

I have done a lot of research on how to do this, I have done some tests but I can't really find a solution that works. Here are the tracks I explored:

  • Power BI Rest API, which allows you to duplicate a report, change the parameters of a report (so potentially the SQL query) but not change the visual of the report.
  • PowerBI Javascript API, which obviously allows you to direct the visual of the report with code by embedding it in an application, but the visual changes do not seem to apply directly to the report in PowerBI service but just in the application. Also, I don't see any methods to change the title of a visual or hide a page.
  • Extract the code from the .pbix file by converting it to .zip. However, when I modify for example the displayname attribute of a slicer in the Layout file and convert the .zip file back to .pbix, an error message appears indicating that the file is corrupted.

Do you know if one of the tracks I mentioned can achieve what I want to do? Is there any other way to automate the creation and maintenance of reports?

Thank you in advance for your advice!

Melvin

Melvin Moriniere
  • 21
  • 2
  • What you can do programatically with a .pbix's contents is a bit of a black box, and until they add documented support for something like this, anything you could come up with would be a hacky, unreliable solution. There are a couple options out there for more dynamic content (ex: custom python visuals), but imo the best option rn for this is creating your own 'reports' containing all embedded content and build a template around that using the framework of your choosing. – trenton-ftw Mar 31 '23 at 04:48
  • Thank you for your reply @trenton-ftw ! Just to be clear, What do you mean by "creating your own 'reports' containing all embedded content and build a template around that using the framework of your choosing." ? What framework are you talking about, a PowerBI framework or a python framework for PowerBI ? When you talk about embed content, do you talk about Javascript API ? – Melvin Moriniere Mar 31 '23 at 13:59
  • Make a web app that uses templates you build to compile all the pieces you want for a report and then embed them all in a single view together. ex: instead of looking for a way to generate a single report with a pie chart and bar graph, save reports that contain the pie chart and bar graph separately and then use the embed APIs (via your web app) to embed them into a single web page together, aka your new 'report'. Your web app can handle things like updating parameters, datasources etc. I say framework meaning frameworks like react or angular but really could be any dev framework you want. – trenton-ftw Mar 31 '23 at 16:00
  • for the record, i don't actually think thats a good idea. but if someone really wanted to pursue something along the lines of what you are describing, i'm not sure a more reliable method exists today. hopefully someone else has ideas for you. – trenton-ftw Mar 31 '23 at 16:01

0 Answers0