November 18, 2010 at 9:13 am
Hi there,
I am a relative newbie to SSRS, but been doing ok so far. However I am having an issue with percentages. I have had a search around and tried several things to no avail.
I am trying to output the following. Basically we have a series of codes that defines the type of work done in a specific area (ie client, candidate, tech) and within each of these there are further breakdowns such as client email, client login issue etc. A spreadsheet is created which includes total number of calls broke down by each of these codes and the percentage overall.
It looks like this:
Total Emails% emails
Candidate - Login 3823 18.52%
Candidate - Help 514 2.49%
Candidate - Queries 337 1.63%
Got no problem creating the first 2 parts but the percentage is driving me nuts. I know logically it's the total emails per category/total emails *100, but all that does is keeps giving me a value of 100% for each and ever line.
Query is this:
select distinct pcdesc.category, count(*)
from callref
left join pcdesc
on callref.code=pcdesc.category
where costcenter not in ('email.co.uk')
and YEAR(date_time) = '2010'
and status <> 'SPAM'
and owner in ('JSmith')
group by category
order by category desc
I have played around with a few things using the expressions and even trying an additional dataset within the expression. At the moment I am using this:
=sum(Fields!ID.Value, "DataSet1")/sum(Fields!ID.Value, "DataSet2")
Second dataset is purely count of total calls.
Can anyone help before my brain fries? Many thanks!
November 18, 2010 at 9:34 am
What you are facing is a scope issue. Using a second dataset to solve the problem wont really work right. When the expression is evaluated, it is being evaluated within the given tablix's current group. If you click on the cell in which the expression resides, you will see the row group that it belongs to at the bottom of the report under "row groups" (assuming you are using report builder).
Fortunately in aggregate functions, you can specify the group you wanted the aggregate evaluated for, by passing the group's name in double quotes. So, lets say for row groups you have "group 1" and under that you have "worktype". and your expression exists in the "worktype" group, you would want to write your expression like this -
SUM(<value_to_be_summed)/SUM(<value_to_be_summed>, "Group 1")
So the first SUM would happen at the row group that the expression is in, then the second would sum at the entire tablix level.
Hope that makes sense. if you want me to write a more specific expression, please post back with the row group names in your report.
November 18, 2010 at 9:36 am
actually looking at your post again, you did have it pretty close.
This should work -
=sum(Fields!ID.Value)/sum(Fields!ID.Value, "DataSet1")
If it doesn't, use the highest parent group name from your tablix.
Hope that makes sense.
p.s. you sure you don't wanna use a count() expression instead of a sum() here?
November 18, 2010 at 9:44 am
GetOffMyfoot, thank you so much I clearly was looking at things too closely (overthinking as per usual!).
It worked first time with your suggestion, I really appreciate it.
November 18, 2010 at 9:44 am
I've frequently had trouble with percentages in the past because the query returns an int for that field. Make sure the query is returning a decimal and not an int. You can either cast the result as a decimal in the query or multiply one of the numbers by 1.0 so SQL will cast it for you. Also, if you want to format it as a percentage, you don't need to multiply by 100 as you are in your formula.
If you're getting all 100% or 0%'s, it's a strong hint that you're dealing with integers.
November 18, 2010 at 9:45 am
Doug I had a whole week of getting my head round that last week, it's tricky. Determined to do it plus I am a firm fan of SSRS.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply