Can I Create grouping for Rows that don't exist in dataset? SSRS

0
votes

I have attempted to ask this question before, but I think that question was based on my taking the wrong approach so I am trying again. Original question is here https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9833e8f8-b900-40e2-ab9b-8ba9fb5a5b24/add-dummy-data-rows-in-a-tablix-for-use-as-lookupset-criteria-like-a-left-join-without-sql?forum=sqlreportingservices#9833e8f8-b900-40e2-ab9b-8ba9fb5a5b24

I need to report ( out of sharepoing, using report builder (no SQL, No VS )) on a single list that contains general information about emails. The data is simple - Team (who the email went to) Category (one of 6 categories to which the emails are added) create date.

I need to be able to group the data by Team and category which I accomplish with 2 nested groups. I have also Added Column groups to break COUNTS (the only data in the report other than the group values) so I have a nice table of team/category counts by month.

THE PROBLEM is that not all categories are always represented and so they don't show up. I do know the values ahead of time, so can anyone help me in the right direction on how to show rows for each category? In SQL, I would probably do something like right join to a pseudo table like select * from my Data Right join (SELECT "Category1", "Category2"...

But since I am hitting Sharepoint I cannot write SQL. I am just stuck since these reports were all built and working properly for all Teams Groups and Category groups, just not showing categories that aren't in the given data Set..

Any guidance is appreciated as I'm stuck.

Ibis

1
reporting-servicesssrs-tablix

1 Answers

0
votes

I'm a little fuzzy on your data but I think your best approach would be to hard code each line with the team and categories and use an expression to SUM the count when they match.

enter image description here

For the table, I added extra lines with the Group and ADDed a Group Expression of 1 so it's all aggregated.

The Expression for the Total column just uses the TEAM but you could add you Category field as well.

=SUM(IIF(Fields!TEAM.Value = 1, Fields!AMT.Value, 0))

It's a little tedious as you have to make a separate line with slightly different criteria for each expression.