Solved

need to total a subtotal column within a SSRS tablix

Posted on 2009-05-05
6
5,711 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

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 …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

839 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