Saturday, December 3, 2022
HomeBusiness IntelligenceConstruct your First Report in Microsoft Energy BI Designer 2

Construct your First Report in Microsoft Energy BI Designer 2

PBID

On this submit I wish to clarify extra particulars about Energy BI Designer options. Within the earlier submit you learnt learn how to create some quite simple stories. Nevertheless, these stories had been only for testing basic options of the software. For example we didn’t even play with quite simple options like renaming the dimension or truth tables and members to consumer pleasant names. On this article not solely do I clarify a few of the easy ones, but in addition I’ll undergo a few of the extra superior ones.

Once more, as per my earlier submit, I’m utilizing AdventureWorksDW2012 as a supply database.  We imported “Web Gross sales” into the designer and we created some stories and one new web page and we saved the stories on disc. So we’ve all the necessities readily available. Let’s go…

Making names extra consumer pleasant:

  • Open the *.pbix report file in Energy BI Designer

  • Double click on on “DimCurrency” to rename it to “Forex”. We have to do the identical for all different objects

image

image

  • You are able to do the identical in “Question” part as effectively. To take action, click on on “Question”  then click on on the queries on the suitable pane . Then  on the “Question Settings” pane you may rename the question (Energy Question makes use of the identify “Question” for all imported objects which may very well be tables, views and so on.).

image

Hiding Unused Fields:

There are at all times some fields that shouldn’t be hidden from the top consumer sight. For example, all ID fields ought to be hidden. You are able to do very simply from the “Report” part.

image

image

image

Managing relationships:

We’re going to take a step additional and take a look how we are able to handle the relationships between the queries. Click on on “Handle” from “Relationships” part on the ribbon.

image

As you may see Energy BI Designer mechanically detected some relationships. Wanting on the relationships extra exactly, you’ll see that there are some lacking relationships. For example, the connection between “Web Gross sales” and “Web Gross sales Motive” is missed. It is because composite relationship will not be supported in Energy BI Designer. As the identical in reminiscence expertise as Tabular Fashions and PowerPivot is used it’s not a shock that composite keys usually are not supported within the software. I’ve a submit relating to composite keys in tabular mannequin that you may want to take a look.

Now we need to create a brand new relationship between “Web Gross sales” and “Web Gross sales Motive” queries. We’ll undergo the identical process as I defined on composite keys in tabular mannequin.

We need to concatenate each key columns from each “Web Gross sales” and “Web Gross sales Motive” queries to make a brand new key column.

image

  • Click on “Add Customized Column” from “Add Column” tab from the ribbon

  • Title the Column as “ID”

  • Concatenate “SalesOrderNumber” and “SalesOrderLineNumber” columns utilizing “&” operator then click on OK.

image

image

image

  • Go to “Report” part and click on “Handle” from “Relationships” part on the ribbon

  • Click on “New”

  • From the primary drop down listing choose “Web Gross sales”

  • Scroll to the top tight of the desk and choose “ID” column

  • From the second drop down listing choose “web Gross sales Motive”

  • The “ID” column ought to be highlighted. If it’s not, simply choose it.

  • Click on OK

image

image

Broaden Document Column:

Should you go to “Question” part and click on on “Web Gross sales Motive”, do not forget that we renamed it earlier than to make it extra consumer pleasant, you’ll see two extra columns added to the question that don’t exist within the unique tables. The designer intelligently added the associated columns from different associated tables. Be aware that we didn’t import “DimSalesReason” desk into the mannequin, however the designer detected the connection and added the associated column to the question fields. This is rather like what occurs in Energy Question once you’re going by means of the identical course of.

image

On this pattern, we don’t need to import “DimSalesReason” desk into the mannequin. So we’re going to make use of an superior function which known as “ExpandRecordColumn”. Utilizing this function we’ll broaden the “DimSalesReason” column so we’ll have some chosen columns of “DimSalesReason” side-by-side the “Web Gross sales Motive” columns.

To take action, simply merely click on on the broaden button to the to the “DimSalesReason” column,  tick “SalesReasonName” and “SalesReasonReasonType” the press OK.

image

As you may the 2 chosen columns are added to the question.

image

Now rename the columns to consumer pleasant names. To take action, simply merely double click on on the column identify and rename it.

image

Create a New Reviews Utilizing the New Relationship Created:

Navigate to web page 2 of the report and add the next stories to the web page. You must be capable to create the stories now Smile:

    1. Web Gross sales Quantity by Gross sales Motive Kind

    2. Web Gross sales Quantity by Gross sales Motive

image

Now click on on promotion bar on the “Web Gross sales Quantity by Gross sales Motive Kind” visualisation and as you’ll see it impacts the opposite visualisasion as effectively.

image

Within the subsequent article I’ll clarify learn how to make unbelievable dashboards utilizing the report we created up to now.

Take pleasure in.

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments