February 8, 2006 at 12:57 pm
.rdl is here: http://www.photopizzaz.biz/dcr.zip
I don't know how best to explain this but will try.
In my SSRS 2005 report, i have one group in my table. In that group, I have a field that for each company record in my dataset, this field value is repeated for each record. This is a transactions table so you'd expect several records in the dataset for each company..that's not the problem.
example data from dataset:
TransID CompanyID FeeGoal
1 1000 100
2 1000 100
3 1000 100
4 2000 400
5 2000 400
My SSRS 2005 Report has:
Group1 fields:
CompanyID FeeGoal
=Fields!CustomerNumber.Value =Fields!FeeGoal.Value
The output when previewed looks like this:
Company FeeGoal
1000 100
2000 400
Footer 209409730.83 (totals up all feegoals! not just unique instances!)
I have a footer, and this is where the problem comes in. I am not able to sum 100 + 400 because if I do a SUM(=Fields!FeeGoal.Value) It doesn't just sume up 100 + 400 but rather (100 + 100 + 100) + (400 + 400)
I can't find a way to sum up basically the top values for FeeGoal which is really what the Group Field is doing since I get 100 and 400 in my group field for feeGoal. I should end up with a total of 500 for my footer total for Fee Goal but not sure how to get this to work in this unique situation.
FYI: FeeGoal is an input from an ASP.NET form to allow managers to update that one column in my report so that other calculations can rely on it in my group fields. Once the user finishes, I run a stored proc to insert that fee goal in every customer transaction record / feegoal field
I guess is there a way to do some sort of Distinct SUM in an expression? I also tried:
=SUM((Max(Fields!FeeGoal_AZ.Value)) / Fields!FeeSchedule.Value) * 100)
but you can't have an aggregate function like this, I get this error:
[rsAggregateofAggregate] The Value expression for the textbox 'GrossGoal_gt' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions.
Build complete -- 1 errors, 0 warnings
February 8, 2006 at 11:43 pm
Try implementing using custome code.
February 9, 2006 at 6:26 am
Use this format:
SUM(Fields!FeeGoal.Value,"Group1Name")
February 9, 2006 at 7:38 am
Tried that already. When I tried this:
SUM(Fields!GrossGoal1.Value,"CustomerNumber_Grp")
it tells me that I cannot reference a field that is not in the footer scope
See my other 2 posts (read all the replies), you can't reference a group field in a footer like that, I tried
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=240606&SiteID=1
http://groups.ittoolbox.com/archives/archives.asp?l=sql-server-l&i=922461
February 9, 2006 at 8:54 pm
rdl is here, the 2nd InHouse footeter field
February 24, 2006 at 4:22 am
am facing the same problem, has anyone been able to find a solution to the same.
is it a known bug with SRS ?
February 24, 2006 at 7:50 am
try adding a table grouped by companyid and goal. Sum in the group header the max value for the group. Stick the table in a footer field, hiding blank fields. link sub table to main tabble via filter i.e customerid = customerid
Phil Nicholas
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply