Solved

Table Footer Prints Label Even Though Table is Empty SRS (Filter Causes no data to be returned)

Posted on 2010-11-27
6
565 Views
Last Modified: 2012-05-10
I have 5 tables placed in a report body (SSRS 2005)

The tables point to the same Dataset

The tables have differing columns base upon the record type in the data set

The Tables are individually filtered for a [recordtype]
Then tables are grouped by [ContractType] with a subtotal at change in [ContractType] – In one column I placed [“Total “ + Fields!ContractType.Value] in the far right field I place Sum(Fields!amount.Value).

All seemed good, if there were no records that met the filter criteria for the report section - [recordtype], nothing would print on the report for that section.

I then added a Table Footer so I could show the totals for all contract types within that record type.

In one column I placed [“Combined “ + Fields!RecordType.Value] in the far right field I place Sum(Fields!amount.Value).

Now when there is a section without a qualifying record type, instead of printing nothing,  it prints “Combined” and nothing else.

Note that [Fields!RecordType.Value] is only referenced in the Filter for the table and the Footer, it does not appear anywhere in the grouping or in the “Table Details”

Why is this printing (the “Combined” in the group footer) when there is no qualifying data in the table that meets the filter criteria of the table?


0
Comment
Question by:eholleman
[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
  • 3
6 Comments
 
LVL 5

Expert Comment

by:adamsjs
ID: 34223794
Do you have any conditional hiding of the the tables when there are no qualifying records for the table? If so, it sounds like this did not get applied to the table footer.  If this is the case, try reapplying the condition for hiding the table(s). If you don't already have some conditional hiding of the tables, I think you'll want to look at adding this.

The concatenation of "Combined" and the non-existent data value does not yield a "null" in this scenario.  The non-existent value concatenates as if it were an empty string, so you end up with just "Combined".  Without the conditional hiding, the footer will show.

If you not familiar with hiding report items, look at the Visibility property for the items.  It can be set to False (not hidden), True (hidden), or can use an expression to conditionally determine True or False.  You can also toggle visibility using another report item.
0
 

Author Comment

by:eholleman
ID: 34225561
What I don't understand is that I also have a concatenation of "Total" and a non- existent data value in the group footer row directly above the page footer row yet when I remove the page footer it does not print the "Total" in the group footer nor does it print when I add the footer back.  

It may be academic at this point as I will revert to onditional visibility but I want to understand if this is default behavior of a footer or if I have done something self destructive to cause this aggravation.  
0
 
LVL 5

Accepted Solution

by:
adamsjs earned 500 total points
ID: 34225747
I haven't written a report exactly as you describe, but think I can explain what's happening.  I also admit I'm not an expert in SSRS, so if someone sees my logic here is wrong, I'll hope they'll provide the correct response.

The group footer corresponds to data rows.  That is, if no rows are returned, there is no group, and no group footer in the report output.  This is why you don't see an unwanted "Total".

But, even with no data returned, the table is still present, and so the table footer is in the output.  Thus, you'll see the concatenated "Combined" value in the table footer.

I also want to add some info to my suggestion about conditionally hiding report objects. You can set visibility on the table objects, or on the objects that comprise the table, such as footer, a data row, a single text box in a data row, etc.  If you want the table to be present without data, but don't want a "Combined" footer in that case, you'll probably be interested in hiding just the footer.   And, when building the expression for the visibility property, you can leverage the fact that the RowNumber function (under Common Functions - Miscellaneous in the Edit Expression dialog) will evaluate to 0 when used in the table footer.
0
 
LVL 5

Expert Comment

by:adamsjs
ID: 34275926
Just wanted to see if this information has answered your question, and if you were able to get your report working as you wanted.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 34470692
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.
0

Featured Post

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.

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

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