Solved

need to total a subtotal column within a SSRS tablix

Posted on 2009-05-05
6
5,744 Views
Last Modified: 2012-05-06
I can't seem to figure out how to total a subtotal column within a tablix on my report.

The column I want to sum merely multiplies two other tablix fields on the same line:
=ReportItems("CSTFLD6").Value * ReportItems("CSTFLD7").Value

I want a Total at the bottom of this column.

I'm missing something simple here.
The sum feature says it wont work outside a header/footer.
The aggregate feature says it wont work outside a header/footer.
I see a TOTAL option in the Tablix Field but it's greyed out.
I don't see a TOTAL option in the Tablix Row Group.
I don't see a TOTAL option in the Tablix Properties.

help guys...
0
Comment
Question by:Baltar
[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
  • 2
  • 2
  • 2
6 Comments
 
LVL 14

Expert Comment

by:Emes
ID: 24307353
need to add a report footer and place the sum in there.

=Sum(Fields!ShipCnt.Value)

0
 
LVL 14

Assisted Solution

by:Emes
Emes earned 500 total points
ID: 24307446
this is done in 2008 by adding a row outside the group


samp.bmp
0
 

Author Comment

by:Baltar
ID: 24316621
Neither one of those helped.

The Detail Row cell I'm trying to reference is a computational cell.  I can't reference the source data because the detail row contains cells that have 10 line long switch statements in them, the source data is useless for aggregation.  I can never get SSRS to allow me to reference the data cell I want to aggregate.  For all other purposes I have to use the syntax:     ReportItems("CSTFLDxyx").Value     not data source fields like:     Fields!DESCR.Value

I've tried adding the TOTAL row to the tablix but it still doesn't allow me to reference the detail fields.

I don't know what I'm doing wrong here.

I've attached a few screen prints to help.
ScreenShot1092.jpg
ScreenShot1093.jpg
ScreenShot1094.jpg
ScreenShot1095.jpg
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Expert Comment

by:SamRunyon
ID: 24343767
Baltar:, did you find a answer?
I am having the same problem.

Sam
0
 

Accepted Solution

by:
Baltar earned 0 total points
ID: 24356684
It seems like a simple feature but you can NOT reference an iteration of fields (to get a total) within Reporting Services if the referenced field has any calculations within it.  I had to push all the simple calculations back to the view and then reference the single field within SSRS.  Only as a single field can you subtotal and reference elsewhere.

This was not the solution I was looking for and it seems odd that such a powerful program can't easily be used for this purpose.  I can imagine other situations where the code couldn't be pushed back to the view at which point you would be unable to provide a total for the data.

There may be some way still around this with more complicated SQL code physically embedded within the report code but neither here nor at Microsoft's SSRS blog site was I able to get any suggestions regarding this alternative approach.  I'm begrudgingly closing this string though not answered to the extent I had hoped.

Thanks for everyone's help though.

Baltar
0
 

Expert Comment

by:SamRunyon
ID: 24363118
Baltar, figure out a better way.. put the sum(reportitems!textbox.value) in the page footer not the table footer. I had to convert my fields into boolean. I used. cint(field!Name.Value) and it works just fine.

I hope this help.
Sam
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

730 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