?
Solved

Sum Of Group In SSRS Report

Posted on 2016-07-21
18
Medium Priority
?
304 Views
Last Modified: 2016-08-05
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.
0
Comment
Question by:Naitik Gamit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
  • 2
18 Comments
 
LVL 13

Expert Comment

by:Arifhusen Ansari
ID: 41724135
Could you please provide the expect output ??

It's not clear from your question.
0
 
LVL 8

Author Comment

by:Naitik Gamit
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41724142
 write it likr this
SUM(Fields!ACHIVEMENT.Value/Fields!target.Value) 
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Author Comment

by:Naitik Gamit
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 13

Expert Comment

by:Arifhusen Ansari
ID: 41724145
Are you using matrix?
0
 
LVL 8

Author Comment

by:Naitik Gamit
ID: 41724147
No I am using tabular report and have column group..
0
 
LVL 13

Expert Comment

by:Arifhusen Ansari
ID: 41724149
Can you please post the screen shot of your report?
0
 
LVL 8

Author Comment

by:Naitik Gamit
ID: 41724156
tst.png
As per image I want sum of 0.5+0+1.33+2 in red colour textbox.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
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 13

Expert Comment

by:Arifhusen Ansari
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

by:Naitik Gamit
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

by:Naitik Gamit
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 13

Expert Comment

by:Arifhusen Ansari
ID: 41724184
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
0
 
LVL 8

Author Comment

by:Naitik Gamit
ID: 41724212
Arif it working only for sum , if we are doing division it show wrong result
0
 
LVL 13

Accepted Solution

by:
Arifhusen Ansari earned 2000 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

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
1
 
LVL 8

Author Comment

by:Naitik Gamit
ID: 41724446
How to declare Reportvariable1 ?? and where ?? I cant find
0
 
LVL 13

Expert Comment

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

Refer the screenshot.

2016-07-22_15-45-38.png
0
 
LVL 8

Author Comment

by:Naitik Gamit
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question