Solved

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

Posted on 2010-11-27
6
551 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
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 100

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

747 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now