Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Power BI Desktop Report and Panel Design


By:   |   Read Comments   |   Related Tips: More > Power BI

Problem

Can I really make great data visualization reports with Power BI Desktop? What visualizations are available? How can I access my data? How difficult is it to make changes to my reports to make them more user friendly?

Solution

In a previous tip, Getting Started with Power BI Desktop, we described in detail the new Power BI Desktop (AKA Power BI Designer or PBID) that Microsoft has added to the Power BI tool set. That tip was followed by a second tip which reviewed some of the query methods available in Power BI Desktop. Finally, we provided a great tip on extracting data from a website. The great news is that getting PBID is easy as downloading it at: https://powerbi.microsoft.com/downloads.

The current edition of the software is available free of charge and Microsoft has indicated that the tool will continue to be free. Of course that could change. Furthermore to publish the PBID reports to the web, you must have an account setup on the Power BI site and currently sign up for the web publishing only available to commercial email addresses. Before I go too far I need to warn you that the tool is still in its early stages. Sure it works well from my testing, but features are being added and removed every week and every month. Be sure you temper any move to production with the fact the tool set may not scale as anticipated. Enough said. With all these changes and updates, Microsoft is currently releasing an update version of PBID at least once per month, if not more often. You can see the latest version information on the Microsoft Power BI Support page.

Working with the Power BI Desktop Report Design Panel

When you open PBID, the start screen, as shown below, allows you to start the query process via clicking on Get Data. Alternately, if you bypass the start screen, you can click on the Get Data button in the ribbon.

start menu

Once you have your data (see previously mentioned tip about querying data sources), you are ready to start designing your report grid or panel. The panel takes on a drop and pin structure which allows a report designer to quickly drop a visualization onto the dashboard / report design panel. These visualizations act as the model and structure for the dashboard based on the data retrieved in the query. As we will show in this tip, these visualizations are NOT your 'father's" Reporting Services report parts, tablixes / matrixes, or charts. These visualization are updated, interactive and their color palette and style are more modern. Furthermore, the module nature of the visualizations allows for customized visualizations (including ones designed by yourself or others). The selection of customized visualizations seems much like an app store effect.

Blank Grid

Let us get started with creating our first report. To frame our example, we are using the AdventureWorksDW database as the basis for our queries; these queries have already been completed, and we are now ready to add a visualization. We actually have two methods of adding a visualization. We can either select a visualization from the list of available items, as shown below or we can just start selecting field which will be shown later in our tip.

PickVisual

After selecting the visualization, a blank version of the chart is added to the design grid.

Added Visual

Alternately, the report designer can actually just start selecting the fields that are needed for the visualization. As illustrated next, under the Fields window, all the tables and fields from the query are shown. Simply, click the "> " to the left of the table name to expand the field list. You simply check the box to the left of the field name. You may notice that some fields have a sigma sign, , to the left of the name. That means that based on the query, that field is considered to be measure field and can be aggregated in various ways. In the below example, we selected SalesAmount, TotalProductCost, and EnglishProductCategoryName.

select values 1

select values

These selections immediately generate a clustered column chart without us even selecting the visualization type.

Default Visualization selected values

Pretty cool so far! If we do not like the selected visualization, we can just select the visualization on the design grid, and then click the desired visualization in the visualization gallery that we want to switch to.

change visualization

Switching is that easy with the results shown below.

change visual

Now we have some of the basics out of the way, let us talk about some more of the features of the design space. First if your query returns many tables and fields, you can use the Filter content feature to search for only the specific fields.

filter content

filter content result

Furthermore, as shown next, we can add a second visualization, this time a line chart, using the same fields as before. Unfortunately, the line chart does not lend itself well to the just Sales Amount and Product Cost by Product Category. The illustration below displays two neat triangles, but they are not meaningful.

Line Chart

It would be best if we would add a date field, such as year, to display a trend over time. Here is where just "checking" the field may not work as intended. As shown in the next illustration, just clicking on Fiscal Year adds a new value of "count of Fiscal Year". Definitely, this change is not what we wanted.

add year

To adjust these items, we can actually use the visualization properties panel to make the appropriate adjustments. As displayed below, we first click on the X next to the EnglishProductCategoryName file to remove it from the Axis property. Then we drag the Fiscal Year field to Axis property. We also removed the TotalProductCost field from the values section.

Remove CategoryAdd Year

Now we have a simple line chart which shows the sales by year.

simple line chart

From there we can add back the product category to the legend property and now, as illustrated in the below screen print, we have a line graph showing each of the three major product categories by year.

ProducttoLegend

One of the neat things about the pinned visualizations is that by default all the visualizations are linked together. That means when you highlight / drill down on one visualization, the same drill down or highlighting effect is applied to all visualizations. Thus, as shown in the below illustration, when I click on the red line in the right hand graph that is for Clothing, the left hand chart actually also dims the Accessories and Bikes columns in the chart while highlighting the clothing columns.

Filter applied

This same functionality follows through to any chart added to the same page only. We can easily add a second page, as illustrated next, which is a completely separate report grid. However, the filtering and highlighting impact only those visualizations on the same page; the visualizations on the separate pages will not be changed based on the page filtering and highlighting on another page.

New Page

The concept we have been displaying above is called "highlighting"; as you may have noticed, the clicking effect "dims" the non-selected items while highlighting the selected items. In addition to highlighting, we can also filter our visualizations at three different levels:

  1. Visual level filters - these filters limit the data shown only within the selected visualization
  2. Page level filters - all the visualizations on a particular page have their results limited
  3. Report level filters - all visualizations on the report have their data restricted by the filter criteria

To apply a visual level filter, we simply select our visualization as shown below and then under the Visual level filter area, click just to the right of the field we want to filter. When hovering to the right of the field, a down arrow appears; click on the down arrow.

 
filter visual

Two filter options are available, basic and advanced. The basic filtering allows you to quickly "check" the values you would like to filter by whereas the advanced area allows for setting up complex filtering including "and / or" functionality.

basic filteringadvanced filtering

Notice in the below results that only the visualization on the right is filtered by accessories.

visual filter results

To remove the filter, we simply click the eraser button on the filter. Alternately, you can select the "All" checkbox and the filter will be eliminated.

erase

To apply a page or report filter, basically both are applied in the same way, we first must drag a filter into the filter area.

drag filter

Now when we select Accessories, both visuals on the design grid now are filtered to only include Accessories. Notice how the other product lines disappear completely. I would like to also note one other option with the page filter; just below the list of available items, you see "Require Single Selection". This option will thus only allow a single item in the filter list to be selected (note if none are selected, all items appear on the page). The report level filter works in the same way, so we will not cover that item in this tip.

page filter result

Next we have the ability to change several page formatting items. In order to change the page formats, you first must make sure you do not have any of the visualizations selected. As shown below, you click on the paint brush, step 1, to adjust the page formats. We can change the name of the page tab, as displayed in step 2. Finally as shown in part 3, the background color of the page can be changed based on the selection in the color palette.

page formatting

Finally, we are going to finish up our tip discussion with some format changes that can be made to each visualization. Similar to the page formatting, we must first select the visualization we are working with, and then click the paint brush. In the below example, we clicked on the down arrow next to the Legend heading; then the position of the legend was changed to Bottom and we added a Legend Name of "Figures: ". Notice how the legend now appears on the bottom of the visualization and is prefixed with "Figures:".

visual formats

We can also adjust the visualization's title, title font color, title background color, alignment, and entire background color, among other items.

title and background

Data labels can also be added to chart, and as illustrated below, the labels can be customized for color, display units (thousand, millions, etc.) and precision.

data labels

Finally, and probably my favorite formatting option, is the ability to customize the color of each display instrument (bars on bar chart, lines on line chart, etc.). First, we click on the Data Colors open to open up the properties; next, we can hand pick the color we would like for each data item on our visualization.

data color

This last customization is a great way to help you make your visualization stand out, although the colors I picked above may not be the best examples of "good colors" on charts and graphs. In a future tip or two, I am hoping to cover the publishing process and also cover what you can do once a report is deployed. I am also planning a tip on customized visualizations.

Conclusion

In this tip we continued our review of the new Power BI Desktop Tool. The tool is fun to use and with almost monthly releases, many new and valuable features are to come. The report or dashboard grid allows you to easily drag and drop visualizations onto the design grid. Once, the visualization is added, you can quickly drag fields into the various areas of the visualizations, such as the axis, values (measures ), or legends sections. Furthermore, you can select a particular item on a grid, and PBID highlights that item on the selected visualization and dims any other item on the visualization. The neat thing is that the highlighting and dimming effect is then applied to all the other visualizations on the same page. In addition to highlighting, you can also add report level, page level, and visual level filter; these filters apply only to the appropriate level that was filtered (different from the highlighting effect). Finally, various items at the page level can be formatted to your liking; similarly, each visualization can be custom formatted for background color, legend placement and labels, data labels, and even data color.

Next Steps


Last Update:






About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools