Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

need to total a subtotal column within a SSRS tablix

Posted on 2009-05-05
6
Medium Priority
?
5,980 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 2000 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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 …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

604 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