SSRS Data Returned but Not Showing in Tablix

Question

SQL Server 2019

Report is being built in Visual Studio 2019, and is not yet deployed to SSRS.

Scenario - Report calls a stored procedure passing in 3 parameters (Rep ID, Year, Month) then displays the "first row" value from several columns of the data returned in a page header section, then displays the rest of the columns' data in a tablix.

Issue - it works fine when I request for all (wildcard % feeding into a LIKE statement) Reps for any given month/year combo. It breaks when I request for a specific Rep ID.

Here's what I've found:

it passes the correct Rep ID value in the query as viewed via Profiler,
the statement grabbed from Profiler works fine in SSMS directly
the number of rows returned via SSMS direct is 347, the number of rows returned as seen in Profiler is 355 (odd (to me), but not the driving concern at the moment as there's also a rowcount diff in the "all" query that is displaying fine; assuming some sort of overhead)
the "first row" values from the returned data are showing fine on the report preview screen
it's the rest of the columns' data that is not showing up in the tablix on the report preview screen when I select a specific Rep ID vs. using All
I also tried hard-coding the values within the stored procedure, and the same number of rows are returned, with the same result of "first row" values being used but the tablix not displaying the rest of the data
I do have a "no rows" message, but we know it's seeing the rows or it couldn't get the "first row" values so that's rather moot

What sorts of things do I need to look at to get this working?

Thanks in advance!

2020-10-21 EDIT: for what it's worth, I've now also tried

recreating the report without the top-level grouping (REP) since a single-Rep version does not need that level, no change
adding to the report (so, it's seeing the exact same data) a matrix vs. a tablix, matrix displays both the all-rep and single-rep data fine, I just can't make that give me the same format, arrangement, and sub-total lines, etc. as the tablix (not surprising since they're different beasts) though I'm trying my best there in case I can't get the tablix to behave

profiler-ssms-reportpreview-image

Have you tried either pressing the green round refresh button or remove all *.data files to make sure VS is not caching query results locally while developing? — Ross Bush
Yes, I have a tool that removes all the .rdl.data cached data and run it frequently and definitely for troubleshooting this. — Becca
Have you tried to remove the tablix and add it back in.. I have had this issue before.. with random reports doing this! Frustrating. On a side note.. refrain from prefixing your stored procedures with SP_xxx. SP does NOT stand for stored procedure.. look it up — Harry
I did after I posted. Also tried recreating from scratch. No change in behavior. Re: the naming convention, hey, you looked at the image, thanks, and good catch (I meant that to be sppci_). — Becca

Becca Becca · Accepted Answer · 2020-10-29T11:59:52

I found the issue. It was hiding the grid on 1 Rep row instead of hiding it at 0 rows as expected.

            <Visibility>
              <Hidden>=RunningValue(Fields!dataRepName.Value, CountDistinct, Nothing) = 1</Hidden>
            </Visibility>

SSRS Data Returned but Not Showing in Tablix

1 Answers