Sum Of Group In SSRS Report

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.
LVL 8
Naitik GamitSoftware DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Arifhusen AnsariBusiness Intelligence Developer and AnalystCommented:
Could you please provide the expect output ??

It's not clear from your question.
Naitik GamitSoftware DeveloperAuthor 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 EngineerCommented:
 write it likr this
SUM(Fields!ACHIVEMENT.Value/Fields!target.Value) 
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Naitik GamitSoftware DeveloperAuthor 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 AnalystCommented:
Are you using matrix?
Naitik GamitSoftware DeveloperAuthor Commented:
No I am using tabular report and have column group..
Arifhusen AnsariBusiness Intelligence Developer and AnalystCommented:
Can you please post the screen shot of your report?
Naitik GamitSoftware DeveloperAuthor 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 EngineerCommented:
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 AnalystCommented:
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 DeveloperAuthor 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 DeveloperAuthor 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 AnalystCommented:
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 DeveloperAuthor Commented:
Arif it working only for sum , if we are doing division it show wrong result
Arifhusen AnsariBusiness Intelligence Developer and AnalystCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Naitik GamitSoftware DeveloperAuthor Commented:
How to declare Reportvariable1 ?? and where ?? I cant find
Arifhusen AnsariBusiness Intelligence Developer and AnalystCommented:
In report property you can declare the variable.

Refer the screenshot.

2016-07-22_15-45-38.png
Naitik GamitSoftware DeveloperAuthor 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.