Designer Report in Avaya CMS Supervisor 13.0

In using Avaya CMS Supervisor 13.0, for non IT people, sometimes quite frustrating. Based on my personal experience, we have to define all the report during the initial period of contract with the vendor. Being a human, most of the time, we dont know all things we're going to need in the next 1 or 2 years, trends could have been changed. Therefore, we have to get used to the designer area in CMS report. We could define almost all report we need.

The center I've been working are now having 4 skills under 1 management, so the manager sometimes need a summarized report from all 4 skills, and sometimes needs the detail. Vendor only provided us with the detailed report, so if we need it to be summarized, we have to export it into MS Excel and then make the manipulation there. Quite tedious for me so I decided to play a lil bit with the designer area.



The above picture is the one provided by the vendor, so everytime I want a summarized report from all the 4 skills, I have to export it into MS Excel and combine it together using Pivot Table.

If we need to make a summarized report template like below:


Use the following steps:
1. Run New Report Wizard from CMS
2. Choose the correct table/database, be it Real Time or Historical Report and when you arrive at the Data Group window, choose No Pre-Selection. This will allow you to select only the data that you need.
3. To make the wizard more effective, dont choose all the data field at the Select Data Items window at once, choose only ROW_DATE and one field that is the most easiest to calculate, it will facilitate you in backtracking if there is any problem or error occured.
4. Choose to edit in designer.
5. To make a summarized report from all the 4 skills, you have to select the data field in sum. So, instead of only selecting CALLSOFFERED, you have to use sum(CALLSOFFERED). It is applied to all data that needed to be sum. For data that need to be displayed in average, you have to add another mathematical calculation, for example, I use (sum(ACCEPTABLE) / (sum(CALLSOFFERED) - sum(ABNCALLS1))) * 100 to display the Service Level. Play your logic here.
6. The tricky part is the total or summary row. Before you add many fields in the table, make sure the total or the summary row displays the correct amount. To display the summary row, first, copy the query. Then from the table format window, select to display the summary row and choose the new query.
7. The total query basically is the same query as the data query. The only difference is there are any reference data, such as date, start time and skill, you have to give it a constant value instead of the data query. In my example above, in the date column, the data will contain ROW_DATE, and the total will contain "ROW DATE", this text function only as header so you have to put double quotes, otherwise, the total value will only display the corresponding date.

Note : sometimes it is not enough for you to just restart the template to check is it displaying the report correctly or not. You have to close it and then choose the template over again from the Report menu in CMS, so dont panic if after updating, you still have the old form.

Now, if you already update and ensure the template has the right query , it is time to add more data to the template.

Mostly, the problem that are found are related with the database table. Just remember, the group by clause only applied to the data, not to total query.
The AND clause withing the WHERE, is matched to the Input. It looks like this:
ACD=$acd and SPLIT = [Splits/Skills:] and ROW_DATE = [Dates:]

The ACD, SPLIT and ROW_DATE are the database field, and the one within [] are the captions of the Input button.

Another problem I found was finding the average value of the incoming calls during certain range of date, displayed per interval.
CMS does not allow count clause, so you can not put "COUNT (CALLSOFFERED)" in the select query. As the workaround, I use this query:
sum(CALLSOFFERED) / (max(ROW_DATE) - min(ROW_DATE))
That way, I can now generate an average value per day for incoming calls to the center.

One last thing, more than never, it will tell you that something must be in the GROUP BY list without letting you know what is actually needed. If that's the case, make sure you have the correct GROUP BY clause in the query. To show the where clause, click on the WHERE button withing the corresponding query.

Well, that's it for now. Hope I'm finding something else to write here. Just let me know if you have any questions. Enjoy designing.