Solved

need to total a subtotal column within a SSRS tablix

Posted on 2009-05-05
6
5,605 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
  • 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

746 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

8 Experts available now in Live!

Get 1:1 Help Now