Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Report totals in SQL Server Reporting Services 2005

Posted on 2008-06-11
26
Medium Priority
?
491 Views
Last Modified: 2011-10-19
I'm trying to total a number of items in SSRS 2005. I'm putting my totals in the page as indicated here http://msdn.microsoft.com/en-us/libr...93(VS.80).aspx. I'm also using a visibility expression to ensure the totals only appear on the final page. My problem is that my totaling expression only totals for the last page as I have a number of page breaks in the report. Any suggestions as to how to force a total for the whole report and ignore the page breaks?

At the end of the report in the footer I'm using:
=Sum(ReportItems!OwnerGrossIncome.Value)

The problem is, it does not grab values from previous pages before the page breaks.  Microsoft mentions this in the MSDN guide and I wanted to know if someone has a workaround.
0
Comment
Question by:BillSinc
[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
  • 13
  • 13
26 Comments
 
LVL 12

Expert Comment

by:jgv
ID: 21761895
Do your totaling in the table footer (assuming you are using a table). The table footer will be displayed on the last page of the report and will be a sum over all groups regardless of page breaks.
=Sum(Fields!OwnerGrossIncome.Value)

If you want to see the total in the report footer on the last page you can refer to the textbox in the table footer.
0
 

Author Comment

by:BillSinc
ID: 21762924
A report may have dozens of tables. I already have a total for each table and I need need to total all of them.  
0
 
LVL 12

Expert Comment

by:jgv
ID: 21763326
I'm talking about table objects, not database tables. I don't recall ever using more than 3 or 4 table objects in any report let alone dozens. How many table objects do you have in this particular report?
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:BillSinc
ID: 21763605
Depending on the state a user selects it could be dozens.  There is one table per property.

Body--------------------------------------------------------------------------------------------------------------------------------------------------------------------
                   Table
                       Header1                                       Header2                                             HeaderX
                       Data                                            Data                                                   Data
                       Data                                            Data                                                   Data
                       =SUM(Fields!Data1.Value)            =SUM(Fields!Data2.Value)                    =SUM(Fields!DataX.Value)

Report Footer--------------------------------------------------------------------------------------------------------------------------------------------------------

                      Header1                                        Header2                                             HeaderX
                      =SUM(ReportItems!Data1.Value)     =SUM(ReportItems!Data2.Value)         =SUM(ReportItems!DataX.Value)
0
 
LVL 12

Expert Comment

by:jgv
ID: 21764194
There seems to be a disconnect on the terminology and I'm not sure how to continue. Before I can help further, I need to ask if you're new to SSRS. If you just started working with it I will try to give a more detailed explanation with what I'm asking.
0
 

Author Comment

by:BillSinc
ID: 21764206
Yes, I am.  Trying to learn as I go along.  I do know the difference between database tables and table objects however.
0
 
LVL 12

Expert Comment

by:jgv
ID: 21764285
So let's start here then. How many tables have you actually added to the report body? You mention that the tables are determined by the user's input (state?) and that there is 1 per property (what is a property?). Are you changing the Hidden property on these tables to show or hide them based on the input?
0
 

Author Comment

by:BillSinc
ID: 21764342
There is just one table in the report body.  There are actually about 3 variables that can determine how many actually show up on a report.  For the most part a user picks a state and each table correlates to a property in that state and includes line by line income from that property over a given date range.  I'm not hiding any tables but if you pick a date range and a property does not have any income during that time, it won't display, or if the user does not have access to revenue information for the given property.
0
 
LVL 12

Expert Comment

by:jgv
ID: 21764600
If you only have 1 table in the body of the report then you only have 1 table...no matter what the user provides as input. You are probably thinking it's multiple tables because there is a group within the table and when the report is generated it appears like the table is repeating for the different groups (state, property, whatever the grouping is on).

Knowing that you only have 1 table in the body, I'm going back to my first answer. You can get a grand total for a field if you sum it in the table footer.
-Make sure that the table footer row is showing (screenshot).
-If the table footer is not showing, right click the details row header and select "Table Footer" from the popup menu (screenshot).
-In one of the table footer cells, sum the field that you want a grand total for (screenshot).
-Run the report and verify that the table footer is showing the grand total

report-footer.JPG
0
 

Author Comment

by:BillSinc
ID: 21764757
I already have a grand total for each table.  I need to total those totals in the footer.
fields.bmp
0
 
LVL 12

Expert Comment

by:jgv
ID: 21765073
Can you do another screen shot that shows the left side of the table. I want to see the row where you've placed the subtotals.  
0
 
LVL 12

Expert Comment

by:jgv
ID: 21765079
...and make sure you click on a cell in the table before taking the screenshot so that the the icons beside each row are shown
0
 

Author Comment

by:BillSinc
ID: 21765099
The developer before me did that.  It's not in the footer.
fields.bmp
0
 

Author Comment

by:BillSinc
ID: 21765107
I did however put it in the footer myself earlier and the problem still remained.
0
 
LVL 12

Expert Comment

by:jgv
ID: 21765181
Let's forget the report footer for the moment and focus on getting the total in the table footer.

Copy one of the group totals and paste it onto the table footer row (use the one in the screenshot). Just do one for testing. If it is not providing a grand total for all the groups then take a screenshot so that I can clearly see the full expression you've entered into the cell on the table footer row (I need to see the *entire* expression).
0
 
LVL 12

Expert Comment

by:jgv
ID: 21765187
...and the screenshot...
fields.bmp
0
 

Author Comment

by:BillSinc
ID: 21765566
That works as it should.
report.bmp
0
 

Author Comment

by:BillSinc
ID: 21765572
I used the same formula as the field above, however.
0
 
LVL 12

Expert Comment

by:jgv
ID: 21765644
Is that table sitting on a List object? If it is, that would explain why the table footer sum is matching each of the group footers. Click just above the table and look at the properties window. Is it referencing the report body or a list object?
0
 

Author Comment

by:BillSinc
ID: 21765665
It is a list.  It looks like there might be one on top of another.
0
 
LVL 12

Accepted Solution

by:
jgv earned 1000 total points
ID: 21765896
Put a text box below the list but make sure it is outside of the list. Copy the same sum expression from the group footer cell in the table and put it into the textbox. When you run the report that textbox should contain the overall sum and that's what you reference from the page footer.
0
 
LVL 12

Expert Comment

by:jgv
ID: 21765934
and if there is a list within a list then you might want to put the textbox inside of the first list but outside of the second. You will have to test to see what the appropriate location is for the textbox.
0
 

Author Comment

by:BillSinc
ID: 21771192
Okay, we're getting close.  I found that there are three lists.  I played around with putting the new text box in each of them and it looks like outside of all of them is what I want.  However, when I reference =Sum(ReportItems!OwnerGrossIncome.Value) the value is blank in the footer and instead appears below the body on the last page.
0
 
LVL 12

Expert Comment

by:jgv
ID: 21771480
Is the name of the textbox "OwnerGrossIncome"? Click on the textbox and check the value of "Name" in the properties window.
0
 

Author Comment

by:BillSinc
ID: 21771522
Oh wow, I really have been staring at this too long.  That worked!!!  I really can't thank you enough.
0
 

Author Closing Comment

by:BillSinc
ID: 31466210
You are a lifesaver!
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

705 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