?
Solved

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

Posted on 2010-11-27
6
Medium Priority
?
569 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
  • 3
5 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 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

755 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