09-09-2015 01:04 PM
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.
Solved! Go to Solution.
09-11-2015 08:41 AM
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
09-09-2015 01:17 PM
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
09-09-2015 02:26 PM
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.
09-09-2015 02:33 PM
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
09-10-2015 01:13 PM
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
09-11-2015 08:41 AM
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
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide