CODEWITHSUNDEEP
sql-serverreporting-servicesssrs-tablix
Sebastian
Sebastian

Reputation: 4811

SHOW tables with zero records in SSRS Tablix with RowGroups

I have a Tablix and a Dataset is binded to the tablix. One of the column in the dataset is used as "RowGroups" and a sub table is designed to show the results for each Rowgroups in a seperate table .

While running the report it is appearing correctly , but one issue noticed is that if we choose a SELECT statement with q where condition as 

  WHERE RowGroupID IN(xx,yy,zz)  group by RowGroupID

And the result set contain values for xx and yy only and no records for zz . So while rendering the report 2 sub tables will be created in report for xx and yy but no tables or no information for RowGroupId zz

What settings or properties i should set to shoow the RowGroupID's with 0 records in a Tablix with RowGroups

Upvotes: 0

Views: 60

Answers (1)

sepupic
sepupic

Reputation: 8697

There is no settings/properties to makes it shows the tablix rows that there aren't. Your result set contains 2 rows and not 3, so 2 rows are shown.

You should change your query instead, to make it return result set with all 3 rows.

You can do it by left joining your current result set to the 3 fixed rows containing xx, yy and zz.

Here is my example.

I have a table of natural numbers dbo.Nums that has no negative numbers, but suppose I want to have a row with -1 included in my result set.

If I just filter my table like this: where nums.n in (-1,2,3) I'll miss the row with -1.

For fixing that I left join my result from dbo.Nums with fixed rows containing -1, 2, 3, so my where condition becomes on condition like this:

with cte as
(
select *
from (values (-1), (2), (3))v(n)
)
select *
from cte c
     left join dbo.nums nums
        on c.n = nums.n
           and nums.n in (-1,2,3);

enter image description here

Upvotes: 1

Related Questions