Sum Of Group In SSRS Report

Naitik Gamit
Naitik Gamit used Ask the Experts™
on
I have data like

target   Achieve
10            5
20            10
30            20

I want to sum in group as (5/10)+(10/20)+(20/30).
when I am summing it give as  5+10+20/10+20+30 .
Please provide me solution.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Arifhusen AnsariBusiness Intelligence Developer and Analyst
Top Expert 2015

Commented:
Could you please provide the expect output ??

It's not clear from your question.
Naitik GamitSoftware Developer
Top Expert 2015

Author

Commented:
Hi,Arif

In my report I have column group A,B,C,D......Report look as

                       
                A      B      C      D
 target    10    20   30   40

Achieve   5      15   10    20

I want Sum(Achieve/target).................. but individually I mean 5/10+15/20 etc
when I write sUM(Fields!ACHIVEMENT.Value)/sUM(Fields!target.Value) it gives result of 5+15+10+20/10+20+30+40
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
 write it likr this
SUM(Fields!ACHIVEMENT.Value/Fields!target.Value) 
Naitik GamitSoftware Developer
Top Expert 2015

Author

Commented:
Guy Hengel [angelIII / a3]

It show same result.... as 5+15+10+20/10+20+30+40  not as (5/10)+(15/20)..
Arifhusen AnsariBusiness Intelligence Developer and Analyst
Top Expert 2015

Commented:
Are you using matrix?
Naitik GamitSoftware Developer
Top Expert 2015

Author

Commented:
No I am using tabular report and have column group..
Arifhusen AnsariBusiness Intelligence Developer and Analyst
Top Expert 2015

Commented:
Can you please post the screen shot of your report?
Naitik GamitSoftware Developer
Top Expert 2015

Author

Commented:
tst.png
As per image I want sum of 0.5+0+1.33+2 in red colour textbox.
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
The 0 should be 1, but that apart...
Do you have an expression for that total line already? If yes use that one
You may also need to define scope
https://msdn.microsoft.com/en-us/library/dd255256.aspx
Arifhusen AnsariBusiness Intelligence Developer and Analyst
Top Expert 2015

Commented:
Use the same expression as you have used in text box in green back ground.

Refer the screen shot.
2016-07-22_12-46-44.png
Naitik GamitSoftware Developer
Top Expert 2015

Author

Commented:
Guy Hengel [angelIII / a3] I am using expression as , sUM(Fields!ACHIVEMENT.Value)/sUM(Fields!target.Value) I am trying to give column group scope but it gives me error...can you please provide me example.
Naitik GamitSoftware Developer
Top Expert 2015

Author

Commented:
Arif I  already use it but it gives all total not individually .......... it gives me result of 17/14 = 1.21 but I want 0.5+1+1.33+2=4.83
Arifhusen AnsariBusiness Intelligence Developer and Analyst
Top Expert 2015

Commented:
Hi,

I have attache the screen shot and the sample rdl as well.

Please have a look.
Report5.rdl
2016-07-22_13-13-09.png
Naitik GamitSoftware Developer
Top Expert 2015

Author

Commented:
Arif it working only for sum , if we are doing division it show wrong result
Business Intelligence Developer and Analyst
Top Expert 2015
Commented:
It's like tricky. You can do this but you have to add some custom code.

Add this code in the Report.

Public Function SetVariableValue(val as Microsoft.ReportingServices.ReportProcessing.OnDemandReportObjectModel.Variable, newval As Decimal ) as Decimal

val.Value = val.Value + newval 

Return Val.Value

End Function

Open in new window


Call this function from the Text box for total and in the last filed where you want the finale total.
just show the variable value.

Find the attached rdl.

Refer the below screenshot.

2016-07-22_15-09-22.png2016-07-22_15-15-51.pngReport5.rdl
Naitik GamitSoftware Developer
Top Expert 2015

Author

Commented:
How to declare Reportvariable1 ?? and where ?? I cant find
Arifhusen AnsariBusiness Intelligence Developer and Analyst
Top Expert 2015

Commented:
In report property you can declare the variable.

Refer the screenshot.

2016-07-22_15-45-38.png
Naitik GamitSoftware Developer
Top Expert 2015

Author

Commented:
Arif I am solving problem in sql so, thanks for your solution.............

can u help me to sum at group level.....I am trying as  Sum(Fields!WEIGHTAGE_ACHIVEMENT.Value,"Group_Name") but it gives error.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial