Good Practice: Always Display the Reporting Environment

Good Practice: Always Display the Reporting Environment

When you work on real-world projects in power BI, you would probably have different environments Like DEV, UAT, Pre-Prod and Prod. It is important for you and your audience to know what the data is coming from. Am I looking at Dev or UAT data or I am actually looking at real data in Production environment. You may have asked or been asked with a question like “Where the data is coming from?”. It is important to know how trustworthy the data you’re analysing is. In this post I show you an easy way to show the environment your Power BI report is connected to.

How It Works

To display the environment name you use query parameters, then you reference that parameter, turn it to a table and add columns to show the environments accordingly. Easy right?

Read more about query parameters from a list output here.

Depending on your scenario the implementation might be slightly different, but the principals are the same. In this post I use a SQL server database. Therefore I need to Parameterise server name. in real world you may also need to parameterise the database name. Again, if your case is quite different, like if you get data from Excel, then the Excel path can be different for different environments. Let’s dig-in.

  • Open Query Editor
  • Click “Manage Parameters”
  • Click “New”
    • Enter “Name” and “description”
    • Tick “Required”
    • Select “Text” in “Type”
    • Select “List of values” in “Suggested Values” and type in server names for different environments
    • pick a “Default Value” and “Current Value”
    • Click OK
Creating new parameter in Power BI Query Editor

So far you created a new parameter that can be used to get data from a SQL Server data source.The next steps show you how to use that parameter to show the environments in your reports.

  • Right click on the “Servers” parameter and select “Reference”
Referencing query parameters in Power BI Query Editor
  • This creates a new query referencing the parameter’s “Current Value”
  • Rename the query to Environments
Current Value of a Query Parameter in Power BI Desktop
  • Click “To Table” button from “Convert” section from the ribbon
Convert Query Parameter value to table in Power BI
  • Rename the “Column1” column to “Server”
  • from “Add Column” tab from the ribbon click “Custom Column”
  • Type in a name for the new column
  • in the formula type something the following (the formula might be different in your case)
if [Server] = ".\SQL2017" then "" else if [Server] = ".\SQL2019" then "UAT" else "Dev"
  • Click OK
Add a new custom column to the query in Power BI Query Editor

I don’t want to show anything when I switch to production server so I put “null” for my production server.

We’re almost done. The only remaining piece of the puzzle would be applying the changes and put the environment in a Card visual in our report.

  • Click “Close & Apply” from “Home” tab from the ribbon
Apply changes to the query in Query Editor in Power BI
  • Now put a Card visual on the report canvas
  • Select “Environment” column
  • Disable “Category Label” from the Card

All done!

Using Card Visual in Power BI Desktop

Now let’s test it.

  • From “Edit Queries” button from “Home” tab click “Edit Parameters”
Edit prarameters' values in Power BI Desktop
  • Switch parameter value by selecting a different value from the list
  • Click OK
Switch parameter value by selecting a different value in Power BI Desktop
  • Click “Apply Changes”
Applying changes when editing query parameter value in Power BI Desktop

There we go. It is working perfectly.

Changing query parameters value in Power BI Desktop

Here is a screenshot of a report built with the same technique that clearly shows the environment the report is connected to. When I select a production server then the card shows nothing so it is would be invisible in production environment while in other environments it is quite clear which environment my report is connected to.

Displaying Environment in Power BI

Have you ever used this technique? Have you faced any challenges? Have you done it differently? Please share with us your thoughts in the comment section below.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.