If you read my post about RGS monitoring you know that there is an extra database created with the name gngacd. Here we created 2 new views and 1 function which should be added to this database.
The next step is to add a database source in Excel.
!! You need to install Power Pivot for Excel because there is too much data fetched from the database what is out of range for Excel. www.powerpivot.com
Start Excel and go to the Power Pivot tab.
Enter your Lync SQL Backend Server Name and select the gngacd database, this database contains the created views and function.
Select the first option.
After fetching data it should said succeed.
|Here an example of the CDR data imported in Power Pivot for Excel.|
Now you need to add 3 extra colums with the folowing information:
1th column name = TariefMobiel (Per-Minute Rate for GSM talk)
2th column name = TariefNationaal (Per-Minute Rate for National Talk)
3th column name = Kosten (Total Cost)
The next step is to create a new pivot table in Excel.
Create a Pivot Table.
As you can see the Pivot Table is using Power Pivot as soure.
Configure the Power Pivot fields as I did.
The column with the green box contains outgoing National calls, format is Number of calls - Number of seconds - Costs
There are also 3 other types of calls like Free Service Numbers, International Calls and Mobile Calls.
So enjoy this selfmade reporting tool.
The created a SQL query which actually runs a LDAP query to get all Enterprise Voice enabled users from Active Directory needs some improvement.