Solved

# Sum Of Group In SSRS Report

Posted on 2016-07-21
59 Views
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 .
0
Question by:Naitik Gamit
• 9
• 7
• 2

LVL 12

Expert Comment

ID: 41724135
Could you please provide the expect output ??

It's not clear from your question.
0

LVL 8

Author Comment

ID: 41724139
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
0

LVL 142

Expert Comment

ID: 41724142
Â write it likr this
SUM(Fields!ACHIVEMENT.Value/Fields!target.Value)Â
0

LVL 8

Author Comment

ID: 41724144
Guy Hengel [angelIII / a3]

It show same result.... as 5+15+10+20/10+20+30+40  not as (5/10)+(15/20)..
0

LVL 12

Expert Comment

ID: 41724145
Are you using matrix?
0

LVL 8

Author Comment

ID: 41724147
No I am using tabular report and have column group..
0

LVL 12

Expert Comment

ID: 41724149
0

LVL 8

Author Comment

ID: 41724156

As per image I want sum of 0.5+0+1.33+2 in red colour textbox.
0

LVL 142

Expert Comment

ID: 41724159
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
0

LVL 12

Expert Comment

ID: 41724161
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
0

LVL 8

Author Comment

ID: 41724162
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.
0

LVL 8

Author Comment

ID: 41724164
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
0

LVL 12

Expert Comment

ID: 41724184
Hi,

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

Report5.rdl
2016-07-22_13-13-09.png
0

LVL 8

Author Comment

ID: 41724212
Arif it working only for sum , if we are doing division it show wrong result
0

LVL 12

Accepted Solution

Arifhusen Ansari earned 500 total points
ID: 41724429
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
``````

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.

Report5.rdl
1

LVL 8

Author Comment

ID: 41724446
How to declare Reportvariable1 ?? and where ?? I cant find
0

LVL 12

Expert Comment

ID: 41724457
In report property you can declare the variable.

Refer the screenshot.

0

LVL 8

Author Comment

ID: 41725792
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.
0

## Featured Post

### Suggested Solutions

It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only seeâ€¦
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you sâ€¦
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custoâ€¦
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final pâ€¦