How to build a custom report for Lync/Skype for Business

Recently I’ve dealt a lot with building SQL queries for Lync/Skype for Business for different purposes, such as queries for: Calls Duration, Network type usages, Amount of poor calls per site etc..

One of the requirements that seems to keep repeating itself is if it is possible to have the same query build into a custom report in which can later be translated into a scheduled email or exported to different formats.

The short answer is YES! it is possible and even relatively easy to create.

Building the Query

The first step before creating the report is to have the right query. I’m usually taking a lot of ideas out of the CQM queries and also from looking into the existing Views for LcsCDR and QOEMetrics databases which contain a whole lot of information that can be used.

For the example below I’ve created a custom query that provide a list of top callers based on a given week from the QoEMetrics database.

The query has some built-in filters to remove any numbers or synthetic transaction users and display only active users with Lync accounts in the callers list

Creating the Custom Report

Once we have the Query, we are ready to build our custom report.

1) Open the SQL Reporting URL base, by default it is under the http://ReportServerName/Reports (the actual location can be taken from the Reporting Service Configuration tool).

image

2) Under the given menu, choose “Report Builder” option which will download the Microsoft Report builder tool and install it on your computer.

3) Once the Report Builder tool is opened, you will prompt for a wizard, choose Blank to create your own custom based report.

image

4) Now we need to insert our SQL query into the report, to do so, open the navigation pane, expand Dataset and choose “Add Dataset”:

image

5) Within the Dataset Properties windows, select “Use a dataset embedded in my report”.

image

6) On the Data source, click on “New” in order to select the existing data source which were created as part of the Lync/SfB reports installation. You can also change the name of the Data Source to indicated to which DB it connects to in case you need to use it for more dataset later on.
For our example we’ll use the QMSDB which contain the SQL view the query is based upon.

image

7) Once selected, paste the SQL query in the “Query” section and Click “OK”.

image

8) Upon creating the Dataset, the Reporting Builder tool will automatically translate the query into actual Fields and Parameters.

image

9) Those Parameters are the variables the user can put as Input when the report is being loaded as can seen in the below example:

image

10) Please note that we also need to modify the settings for the parameters to match the type of “Data type” they are used for, for instance date/Time, number etc.
This can be done by right click the parameter and selecting “Properties”.

image

11) It is also possible to set Default value when the report first loads, to generate expressions such as today date, or set the begin time to be set initially to last week.

image

imageimage

12) Once we have the dataset and all parameters configured, we need to start creating the actual report. On the Insert tab, select Table and choose “Table Wizard”. The table wizard is a really nice guided design which help at first creating the report in a convenient view.

image

13) On the “Choose a dataset” window, select the existing dataset which we created earlier as the source:

image

14) On the “Arrange Fields” window, select the required column and details you would like to view. In our example and based on the Query, I would like to get a column for the actual caller URI and another column for the total amount of calls that person had within the given date/time range.

image

15) On the “Choose the layout” window, select the required layout and style for the report and click on “Finish”.

image

image

16) Upon completion of the table wizard, the report is ready and the required table will populate on the design menu.
You can also customize the report in terms of fonts, color and anything else you can think of.

image

17) Now click on the “Run” button on the top left navigation menu in order to run the report and specify the required parameters needed and click on the “Run Report” button.

image

18) Once the query finishes to calculate the results, the table will appear with the results of the query.

image

19) Now all left is saving the report to the actual repository and it will be accessible to all.

image

20) To modify an existing report, open the “Report_Content” web URL, hoover over one of the report and select the drop down arrow. within the drop down menu, select “Edit In Report Builder” to customize any existing or new reports created.

image

image

5 Comments

  1. Pingback: How to build a custom report for Lync/Skype for Business | Just a Lync Guy | JC's Blog-O-Gibberish

  2. Pingback: Weekly IT Newsletter – August 10-14, 2015 | Just a Lync Guy

  3. Pingback: Skype CDR Daten mit Splunk auswerten - Merkbar.

  4. Chad

    What is the query you used for this example?

    Reply
    1. Guy Bachar (Post author)

      Hey,
      I used the same query at the top, but unfortunately it has HTML elements integrated into it.
      Once you remove the HTML elements you can use the query as is.

      Reply

Leave a Comment

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.