How to start with SAP Contact Center custom reports
Introduction
Besides of standard reports, there is possibility to create custom reports to meet customer needs. SAP Contact Center “CCtr” (AKA BCM) reports are based on Microsoft Reporting and Analysis services, so you will find lot of information about these from Internet, but this guide provides simple steps how to create custom reports for SAP Contact Center system.
Tools
For creating custom reports you can use powerful tool SQL Server Business Intelligence Development Studio (BIDS). It is included already in Microsoft SQL 2008 R2 installation package and you can install it by adding features to your SQL server installation. You can use one BIDS for creating reports for multiple systems (for example development and production), so you don’t need to install BIDS multiple times.
I would say that BIDS is a tool for SAP CCtr administrators/developers. Supervisors/managers could use more simple tool called Report Builder 3.0. Look at “Creating CCtr custom reports in Report Builder” and “How to let SAP Contact Center reports to work for you“.
Starting a project
In BIDS you need to create a project first. It is like a container to hold all your settings and custom reports. As you can use one BIDS for multiple systems, it is wise to separate custom reports by creating projects for each CCtr system.
Open BIDS and from menu choose File > New > Project > Report Server Project
Next step is to configure some basic settings for the project. On the right side of the BIDS windows there is Solution explorer. Click with right mouse button on the project name and select Properties.
You need to configure three settings TargetServerURL, TargetDataSourceFolder and TargetReportFolder. It helps you to deploy new reports to your reporting website much faster. TargetServerURL is basically your reporting website address and from your Standard Reports Virtual Unit. You need to copy “Reporting Services Report Server Site Address” to that field. If you want to add custom reports to same folder, where standard reports are installed, you need to copy from your Standard Reports Virtual Unit “Report Site Name on Reporting Services” to TargetReportFolder and TargetDataSourceFolder field. If you want to add custom reports to separate folder, then just insert folder names you want.
Creating data sources
Data sources include information on where to get data for report. Usually you need to create two data sources, one to get information from Analysis database and one to get information from VWU SQL database.
Click with right mouse button on the Shared Data Sources and select Add New Data Source.
It is wise to insert same names as you have for standard reports data sources.
WDU 2005 OLAP is for connecting with Analysis services OLAP database.
Click Edit. Insert your Analysis services instance name and pick the right database name.
WDU SQL is connecting with SQL server and access VWU database. Data source Type must be Microsoft SQL Server and with Edit button you need to also pick your SQL server name and database name.
If you don’t deploy custom reports to standard reports folder, then click with right mouse button on data source name and choose Deploy. This will upload your data sources to your reporting server. If you deploy custom reports to standard reports folder and data source names are exactly the same, report will be deployed without this step.
Creating reports
VWU database (uses WDU SQL data source) – basically includes raw data to make calculations to OLAP database.
There are lists about queues, users, IVR-s etc and information about every call, e-mail, chat etc. Like who answered the call, in which queue, what time. You can query data from VWU with standard Microsoft SQL query.
OLAP database (uses WDU 2005 OLAP data source) – includes already calculated statistics. For instance, in Service queue there was 110 calls on 08/30. Agent John Doe answered 110 calls in August. You can query data from OLAP database with MDX query but I recommend to use query designer with drag and drop possibility.
To create a new report, click with right mouse button on Reports and choose Add New Report.
Choose WDU 2005 OLAP data source to create report based on OLAP data.
Click on Query Builder.
Choose one cube to query data from.
CubCSSAgents includes calculated statistics from Agent perspective. Such as agent John Doe answered 500 calls in this week.
CubCSSContacts includes calculated statistics from Contact perspective. Such as there was 300 calls with Service skill 5 in Service queue last week and John Doe answered 147 of these calls.
CubCSSQueues includes calculated statistics from Queue perspective. Such as average agent serving count last month in Service queue was 5.
Metadata windows shows you the contents of the cube. Measures include statistical data like Count of handled and answered calls. Dimensions (little arrows and yellow cube) include informative data like agent name, team name, days, month. Expand these items and you will see more detailed data.
From Metadata drag and drop data items to right top window to create report parameters. You can then use the parameters to filter statistics in your report. For example you can sort data by day and agent name etc.
Drag and drop data items to right bottom window, which you want to see in your report. Like report will show agent names, handled call count etc.
After you have chosen right data items, click OK and continue. Next design the table as you like. You will find the best format for you by testing different designs. Note that the Measures data usually goes to Details section.
After designing, your report should be ready. You can edit the design again from Design tab and preview data from Preview tab. You will see that we have same parameters here, which were added before.
Choose WDU SQL data source to create report based on VWU data.
I recommend to already create SQL query in SQL Managment Studio and copy it to Query Designer. After clicking OK in this Query Designer the same table design wizard will start as for OLAP report.
If you reports are ready for a customer, click with right mouse button on your report name and choose Deploy.
Editing standard report – Volume 7 – Contact Log
By default free last numbers for Source and Destination is hidden and it is possible to remove them.
Download standard report from your reporting website.
Click with right mouse button on Reports and choose Add existing item and add Volume 7 report.
Hoover with mouse over the column next o [EndTime] to activate that little icon and click on it.
Menu will pop up and choose Source. Next column from Source is Destination. This replaces special expression with real Source and Destination.
You cannot preview and deploy this report by default, because of the standard reports special configuration (Wicomtranslator.dll is used to translate report to your system language). However it is quite easy to fix that.
From SAP CCtr Install folder find WicomTranslator.dll and copy this to Visual Studio PrivateAssemblies folder.
For example C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies
Keep in mind that every time you upgrade the Standard Reports Virtual Unit, this report will be overwritten. One solution is to rename your report file and deploy it to reporting website. Remember standard reports could be changed with a new service pack and you will need to edit/replace a newer version of this report anyway.
Great Work Agur !! Many more to Go !!!!! 🙂
Thanks Agur for Sharing this valuable information !!! 🙂
It is will really help us in facing our client demands regardind the Reporting Part !!
Thanks Once Again !!1
Could you outline the functional differences between Visual Studio (+ integration addons = BIDS) and Report Builder 3 when creating and editing reports?
The thing is: based on my experience they are functionally the same when creating reports.At least I haven't felt I was missing anything when using RB3.
Hello.
Actually I was also looking for differences last week and I think you are not missing anything, if you create simple custom reports time to time. My intention was to create BIDS guide for IT people, because I have seen lot of people using Visual Studio already for other projects, so they are familiar with this tool.
For me it becomes useful if I have multiple reports and multiple systems (dev, pre-live, live), then I can copy and deploy these reports faster. For example I can import all 20 reports from dev project to pre-live project with some clicks (Add existing item - select all - OK), change some information if needed and select all reports - deploy.
There are lot of information in Internet regarding BIDS vs RB 3.0:
http://technet.microsoft.com/en-us/library/ms159253%28v=sql.105%29.aspx
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/ba05beb2-acac-4890-a649-e76b689cdaad/difference-between-report-builder-30-and-bids
http://www.wiseowl.co.uk/blog/s188/report-builder-differences.htm
Actually if your dev, pre-live and live reporting websites have exactly the same folder structure/names, you only need to change TargetServerURL in one project and select all reports - deploy. 🙂
Oh, about the folder structure:
I didn't find a way to recreate the folder structure of my SSRS instance in Visual Studio.
I did work around it by creating a solution and configuring different folders as different projects (with appropriate TargetReportFolder).
Considering the amount of reports I have to manage and create this seems to add too much overhead.
Is there a simpler way to keep a folder structure in one project?
Hello.
Good question, I have usually deployed custom reports to one folder, but I found a solution from Google and got also smarter.
http://www.bidn.com/blogs/DevinKnight/ssis/430/development-lifecycle-deployment-ssas-ssrs-ssis
Basically you need to create different Configuration profiles and then you can toggle between them to deploy different reports to different folders.
This also means that you can have only one project for client and you can use as many Solution Configurations as you want. One for dev Sales folder, one for dev Marketing folder, one for live Sales folder etc.
In my opinion, the biggest difference between Visual Studio and Report Builder is on their user experience. If you are a developer/administrator or engineer - it's better to use Visual Studio, like it's more powerful, but requires more knowledge and practical experience on creating reports. You can work with multiple systems (prod | non-prod) and multiple clients on the same time.
BUT, if you are a supervisor or someone from business and SSAS|SSRS is not your profile - it's better to take in use something simple like Report Builder. Commonly, such users only need to change some headings or apply some static filter (to see only their department on report) and maybe save it with some other name. For that purpose it's better to use Report Builder. In other words, it's better to show Report Builder to end users or supervisors and Visual Studio for IT people responsible for BCM platform.
This is really valuable information to many SAP BCM partners and customers.
Good post Agur.
Thanks for this post, Agur!
Best regards,
Sasha
Dear Group,
How I can get or create a historical report of the daily movement of the states of presence of the agents ?.
What table does contain the presence status changes of CDT?
Thank you very much for your answers.
Regards,
Juan Carlos
Inicio > ACME BCM Reporting > Agent - Utilization 3 - Daily
Is th e correct report.
Regards.
Juan Carlos