Solved

SSRS - Ratio not calculating correctly

Posted on 2010-08-12
2
1,448 Views
Last Modified: 2012-06-21
I have a drilldown report and Group results for ELA/MHG are not calculating RATIO separately....  Any idea what I am doing wrong?

Ratio=Sum(Fields!ccSTA_MED.Value, "ReportDB")/(Sum(Fields!ccSTA_MED.Value, "ReportDB")+Sum(Fields!ccSTA_TELE.Value, "ReportDB")+Sum(Fields!ccSTA_ICU.Value, "ReportDB"))

Attached is an Excel Sample of SSRS report.

 Report2.xls
0
Comment
Question by:epicazo
2 Comments
 
LVL 21

Expert Comment

by:Alfred1
ID: 33426542
It is possible that you might be missing a group if you have nested groupings (parent/child groupings) since you mentioned that you have a drilldown report.  
For example if you have four groups nested in hierarchical fashion (e.g.  Group1 -> Group2 -> Group3 -> Group4) and by chance your columns only accommodate three groups (e.g. Group1 -> Group2 -> Group4) for example, then the values would be different and your result would not calculate correctly.


Also, based on your Excel Spreadsheet, you need to provide a condition in your expression (e.g. using IIF) to determine if the values in the row are ELA or MHG in order to calculate the correct ratio for each row.  It looks like your aggregate functions is aggregating for both ELA and MHG.
0
 

Accepted Solution

by:
epicazo earned 0 total points
ID: 33433440
I really wouldn't want to put an iff condition, then otherwise it wouldn't be dynamically calculating groups.  I figured out the problem.   There I was using the "ReportDB"

Sum(Fields!ccSTA_MED.Value, "ReportDB")/(Sum(Fields!ccSTA_MED.Value, "ReportDB")+Sum(Fields!ccSTA_TELE.Value, "ReportDB")+Sum(Fields!ccSTA_ICU.Value, "ReportDB"))

This solved the problem...
Sum(Fields!ccSTA_MED.Value)/(Sum(Fields!ccSTA_MED.Value)+Sum(Fields!ccSTA_TELE.Value)+Sum(Fields!ccSTA_ICU.Value))

0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Email Notifications for SQL 2005 9 27
Help Required 3 90
Inserting oldest record into new table. 5 22
Show Results for Latest DateTime in a View 27 24
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

813 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now