Using Power BI with Skype for Business - Part IV

This week I'll wrap up the series on using Skype for Business and Power BI together.  Today we are going to focus on some call reports that can be easily generated.  To begin let's assume all of our company calls hit an IVR, and we know that IVR's uri.

For this graph we are going to output the number of calls per week to our IVR this year.  Our SQL statement will look something like this;

select top 10000 DATEPART(wk,SessionIdTime) as [Week], count(*) as Calls from VoipDetailsView where year(getdate()) = year(SessionIdTime) and (ToUri = 'uri@domain.com') group by DATEPART(wk,SessionIdTime) order by DATEPART(wk,SessionIdTime)

Once we have our date let's create a simple area visualization with the following attributes.

You can see we will use the Week as the Axis.


We will use the number of Calls as our value































The end result should look something like this, as I have added data labels and a trend line.



Let's say we want to create a set of data for a particular user's calls.  We are going to show a 3 month time period.   We can generate a simple query in SQL to make report with 3 visualizations;


select top 1000 Convert(date, SessionIdTime) as [Date], SessionIdTime,ToPhone as [To Phone], FromPhone as [From Phone], ResponseTime as [Start Time], EndTime as [End Time], DATEDIFF(mi,ResponseTime, EndTime) as [Minutes] from VoipDetailsView where InviteTime > getdate()-93 and InviteTime < getdate()+1 and (ToUri = 'user@domain' or FromUri = 'user@domain') order by SessionIdTime desc

For this report we will create a data table with our results on the left, and 2 area visualizations, ones for calls and one for minutes on the phone.

You can pin these to any dashboard individually or as an entire page.  This will wrap my series on using Power BI to generate live SQL based report from Skype for Business.  I hope you found this series useful.  As always if you have any questions feel free to contact me.

Doug Routledge, C# Lync, Skype for Business, SQL, Exchange, UC, Full Stack Developer  BridgeOC Twitter - @droutledge @ndbridge