Removing elements from the flow of the document in a SSRS report

We’re designing a report that is printed and sent out to customers that has details about lottery syndicate entries. The details include the following data:

• Details about winnings earned from the previous month, if any
• Details about termination notice, if one was supplied
• Details about participation in the next month, if any

The key words used here are “if any”. There’s a message that’s displayed on the report that says “Your termination notice was received on 4/28/2017 and your last month of play will be June 2017”. If they never called in to terminate their membership, this paragraph wouldn’t even show up. However, this is where the trouble begins. On the paragraph that contains the expression that generates the text in quotes above, I can add a visibility expression such as =IIF(IsNothing(First(Fields!ReceivedTerminationNoticeOn.Value, "DataSet"))), True, False), then the paragraph will be hidden. However, to introduce a CSS analogy, it’s more like visible:hidden rather than display:none. In other words, the element is hidden, but it’s not taken out of the document flow, so it makes it invisible, but takes up space and leaves a big ugly gap. These documents are printed and snail-mailed to people, so they have to look good. So, after about 15 minutes perusing StackOverflow for answers, and about 30 minutes of fooling around with SSRS, I’ve figured out how to trick SSRS into removing the element completely, shifting other page elements up.

1. Drag a new table onto the design surface. I’m using VS 2015 and SSDT-BI 17.1, but I believe it’s worked this way since the beginning of time: A new table is created, that has 3 empty columns, and 1 row group, with 2 rows: The header, and the row ‘template’ that is repeated for each row in the dataset.
2. Delete 2 of the columns. Now you have one column and 2 rows. We’re going to be adding something to the ‘header’ cell, since it’s only shown once, not once for each item in the dataset.
3. Now, any element you drag into a cell is going to take up all available space in that column, meaning you can only have one element as the child element of that cell, and will resize based on the size of the table added. If you want to be able to add more elements, or position them how you like, then drag a rectangle into this header cell, then add your elements.
4. Click in the header cell, but not in any elements in the cell, and right-click the bar on the left and select ‘Row Visibility…’, and then enter an expression that dictates whether the cell should be shown or not.
5. Click in the ‘template’ row, then click the bar on the left and select ‘Row Visibility’. Select ‘Hide’ instead of the default ‘Show’.
6. Size your table as you need it, and while you cannot delete the ‘template’ row, you can size it so it’s 1px high and it’s not going to show anyway since you set the row visibility to hide.

That will now collapse the table if the visibility condition isn’t met, rather than just making it ‘invisible’ and taking up space.

Caveat: The ‘elephant in the room’ is datasets. You can totally add a child table into this one, as that’s how we were able to show the results of the items. The outer table will grow to accommodate the sizing of its children. However, the dataset used by the elements inside must be the same as the outer dataset. This can obviously cause some problems if you want to have 2 tables with different datasets, but there is a way… which, by the way, is also not pretty.

Let’s say you have 2 datasets that you want to hide but you can only have one dataset as per the rules above.

Dataset1: Let’s say it’s database table ‘TableA’ and has columns A, B, and C; row with values 1, 2, 3 and row with values 4, 5, 6

Dataset2: Database table ‘TableB’ has columns D, E, and F; row with values 11, 12, 13 and row with values 14, 15, 16.

Combine them into a single dataset something like this SQL pseudocode:

SELECT 'TableA' AS Dataset, A, B, C, NULL AS D, NULL AS E, NULL AS F
UNION ALL SELECT 'TableB', NULL, NULL, NULL, D, E, F

On the inner table, you’ll have the columns you want to display, and you can then set a table filter to only show rows that match specific criteria, such as [Dataset] = 'TableA'. The other table would have the same thing, but it would = ‘TableB’. This way, you have multiple datasets under the guise of a single dataset. You would have to UNION all of the rows together and have NULLs in the columns that aren’t used for a particular dataset.

My dataset had the first column called ‘Dataset’ and had one row for ‘Customer’, one row for ‘Winnings_MajorLottery1’, ‘Winnings_MajorLottery2’, ‘NextDrawings_Overview’, ‘NextDrawings_LinesPlayed’. I didn’t know of an easy way in SSRS that you can find the number of rows in a table when using filters (to set the table to not display if there were not any rows), so if there are is no rows in the ‘Winnings_MajorLottery1’ “dataset”, I had a column in the “Customer” dataset called HasMajorLottery1Winnings” that was a BIT field; it was far easier to make a BIT field do my bidding rather than try to fanagle some crazy way to make it work right in SSRS.

Yes, this means you’re going to have a lot of columns – column 1 is the ‘Dataset’ column, then all of the columns from the first dataset, then all of the columns from the next dataset, which means that your query is going to have a lot of NULL entries for the columns that aren’t used for a particular dataset. You’re going to have to structure the SQL well so it’s readable in the sea of NULLs the query is going to have.

However, you may not have to go overboard for simpler reports, but the one I was working on (still am working on, who am I kidding!) was anything but simple and required some outside-of-the-box thinking.

Happy programming!

Advertisement