September 21, 2010 at 8:22 am
I have the scenario below to calculate percentage.
(A) 100 33.33%
300 42%
(B) 200 66.66%
400 58%
(A+B) 300
700
The 1st column has (A),(B),(A+B)
The second column has the values 100,300,200.400 and so on.
I need to calculate the 3rd column i.e. the percentages.
The 1st row of (A) is divided by the 1st row of(A+B),The 2nd row of (A) is divided by the 2nd row of(A+B) and so on
Can anyone help how this can be done.
September 21, 2010 at 9:00 am
It looks like you need to group the data by row numbers in order to get the percentages. Something like this:
[font="Courier New"]
1 A 100 33%
1 B 200 67%
2 A 300 42%
2 B 400 58%
[/font]
For the percentage column, you could divide the value by the group sum (you can define the scope to just the group in the expression.) Do you need to show the A+B row, or was that simply to help calculate the %? If you don't, you can either omit the A+B rows, or calculate using them but set visibility = false.
September 21, 2010 at 9:32 am
I do need to display the (A+B) group too. So I am using that to calculate the percentage. However the problem I face when i do that is for each entry in (A) and (B) the percentage is getting calculated with only the first value in (a+b) i.e. 300 which should not happen. For the second row in (A) and (B) the percentage should be calculated using 700. but instead 300 is being used.
Is this problem anyways related to defining the scope?
I am using reportitems in the expression to locate the values in each box.
September 21, 2010 at 10:08 am
I think the problem may be what data is in a given row. Can you give me a sample row from the finished report as you'd like it to be?
September 21, 2010 at 10:18 am
I want the report to be seen like this:
(A) 100 33.33%
(A) 300 42%
(B) 200 66.66%
(B) 400 58%
(A+B) 300
(A+B) 700
But what i am getting right now is:
(A) 100 33.33%
(A) 300 100% (wrong value - as it is being divided by 300 instead of 700)
(B) 200 66.66%
(B) 400 133% (wrong value - as it is being divided by 300 instead of 700)
(A+B) 300
(A+B) 700
Let me know if I need to clafiry more. Thanks.
September 21, 2010 at 10:59 am
blue.aqua.43 (9/21/2010)
I want the report to be seen like this:(A) 100 33.33%
(A) 300 42%
(B) 200 66.66%
(B) 400 58%
(A+B) 300
(A+B) 700
That's what I was afraid of. It's an issue of scope. When it comes to values, RS is only aware of the row that it's on and the group(s) that row is a part of. There's no way (that I know of, anyway) to hop across rows to get another row's value. (The closest thing RS has to that is a running total but that also requires the values to be grouped together.)
You'd run into the same problem even if you grouped them because grouped items have to be kept together -- all the A's must appear before the B's can be rendered, and once the A's are rendered, RS can't look back at them. Here's one thing you can do:
[font="Courier New"]
A B A+B %A %B
100 200 300 33% 67%
[/font]
That will keep all the values on the same row and the % should calculate right. That may not satisfy the req's for the report, but it's about as close as you can get.
September 21, 2010 at 12:12 pm
I am not sure I understand your data as you are showing it. But you can add scoping information to fields in a calculation. Generally it is something like this:
fieldname.value, "scopename"
Your scope can be your dataset or one of the grouping names.
September 22, 2010 at 1:52 am
Hi,
My advice is if your using the 2008 then use the group variables.
Thanks,
Veeren.
chk the below blog on regarding report variables and group variables in SSRS 2008.
Thanks & Regards,
Veeren.
Ignore this if you feel i am Wrong. 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply