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:
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'
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.
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.
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.
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?
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,
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)...
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.