CUIC - Reporting

[Kristopher Tan]

I'm trying to create a report that has three columns and the third column indicates a percentage of total calls:

This is the query I'm using:

select customvariable5,

          count(customvariable5) as calls,

          round(cast(count(customvariable3) as float)/cast((select count(customvariable3) from contactcalldetail where customvariable3 like 'CustomerGroup5') as float) * 100,2) as 'percent'

from contactcalldetail

where customvariable3 like 'CustomerGroup5'

group by customvariable5

Now the problem I'm running into is the creation of the Percent column. I don't know how to make that a selectable option in CUIC. Is there a way to substitute a variable into the select part of the statement in CUIC? Sorry if there is an obvious answer to this, I understand SQL syntax reasonably well, but I don't quite understand the limitations of CUIC in regards to building a query for reports.

[jacparke]

That should be fine.  When you get it in CUIC, go to the fields tab (in report definition), edit formatting for the field, and set it to the percent mask.

Regards,

Jack Parker

[Kristopher Tan]

Sorry Jack,

I should qualify that I need the piece after the where clause to be a variable like:

where customvariable3 like X

Such that i can compare customvariable3 to anything that is selected or typed by the user when generating the report.

[jacparke]

You can do a value list, to present the user a restricted set of values to select from, or (if not using an anonymous block), at run time you can go to advanced filters, select whatever field and indicate that it has to be equal to whatever,

Do you need help with either of those?

Regards,

Jack Parker

[Kristopher Tan]

Sure Jack, that'd be great if you can.

The funny thing, is i understand how the advance filter can be used equivalent to the outer WHERE clause (the part highlighted in red) but how do I do that with the WHERE clause that is in the return columns of the SELECT? (the part highlighted in green)

I believe I just delete the part in red... but what do with the part in green?

Thanks again for your help,

Kris

[jacparke · Accepted solution]

Unsure why you have two distinct selects for the same criteria.  Can you not just:

select customvariable5, count(*) as calls, round(cast(count(customvariable3) as float)/cast(customvariable3)...

from contactcalldetail

where customvariable3 like (:cust)

group by customvariable5

Quick test of that:

create table foo2 (col1 integer, col2 integer);

insert into foo2 values(1,1);

insert into foo2 (col1) values(1);

insert into foo2 values(4,0);

select count(col1), COUNT(col2) from foo2

You would do this as an anonymous block, click the button that says "Create Parameters" then specify what kind of datatype the parameter is. 

When you run the report, it will prompt for the value of "cust".

If that isn't what you're looking for, we can try again.

Regards,

Jack Parker