Microsoft Power BI enables users of other Microsoft services – including all Microsoft Dynamics solutions – to connect to any on-prem or cloud data using a unified, scalable platform for self-service and enterprise business intelligence (BI). This powerful set of business analytics tools is easy to use and helps you to gain deeper data insights with personalized, vibrant dashboards.

For users of Microsoft Dynamics SL 2015 or 2018, reports that used to take hours or even days to design and visualize can be completed in minutes with Power BI.

Learn more below about how to create insightful reports in Microsoft Dynamics SL with Power BI in the latest in our Queue-Tips series.

1. Understand your Dynamics SL data

Use customization manager or queries behind crystal reports to determine what data to query for your Power BI report.

• Customization manager

• Queries behind crystal reports

2. Summarize your Dynamics SL data set

Summarize your data set to optimize your Power BI report visuals and your required drilldown capability.

The below query shows general ledger transactions and enables you to drill down to the transaction level. If you want a higher level of summarization, you could use a group by clause.

ALTER view [dbo].[xqv_DemoIncomeStatement]
as
select
sub
,left(perpost,4) + '-' + right(perpost,2) Fiscal_Period 
,t.Acct
,a.Descr GL_Account
,sd1ru.Description Reporting_Unit

,sd2cmp.Description Company_Unit

,sd3loc.Description Location

,c.CpnyName Company

,case when right(a.AcctType,1) = 'I' then t.CrAmt - t.DrAmt else 0 end Revenue

,case when right(a.AcctType,1) = 'E' then t.DrAmt - t.CrAmt else 0 end Expense

,t.CrAmt - t.DrAmt Income

,t.Crtd_DateTime

from

gltran t

inner join

Account a on t.Acct = a.Acct

and right(a.AcctType,1) in ('I', 'E')

inner join

vs_company c

on t.CpnyID = c.CpnyID

inner join

segdef sd1ru

on sd1ru.FieldClassName = 'SUBACCOUNT'

and sd1ru.SegNumber = 1

and left(t.sub,2) = sd1ru.ID

inner join

segdef sd2cmp

on sd2cmp.FieldClassName = 'SUBACCOUNT'

and sd2cmp.SegNumber = 2

and substring(t.sub,3,3) = sd2cmp.ID

inner join

segdef sd3loc

on sd3loc.FieldClassName = 'SUBACCOUNT'

and sd3loc.SegNumber = 3

and substring(t.sub,6,2) = sd3loc.ID

GO

3. Develop a rough draft Power BI report

• Develop a draft report and show it to your users before finalizing all of the visuals.

 

4. Knowledge of SQL is beneficial in writing Power BI reports

• This is our favorite SQL tutorial site: https://www.w3schools.com/sql/sql_null_values.asp

 

5. Using the Power BI reporting tool is free if you have the skills to execute the above

• Download the Power BI desktop: https://powerbi.microsoft.com/en-us/downloads/

 

---

Post by Ferdi Hintze

Ferdi Hintze serves as Senior Software Developer and Microsoft Dynamics 365 Implementation Specialist for our team. He has been with Queue Associates for 23 years, and his favorite thing is problem-solving.

 

Contact Queue Associates

Please contact Queue Associates (a Microsoft Dynamics Gold Partner) to learn more about our services surrounding Microsoft Dynamics 365 and other complimentary solutions (including upgrades from legacy Microsoft platforms). You can reach us by completing the form on our CONTACT page, or by calling us at the Queue office phone number for your local region (U.S. and Global locations).