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.