none
Create SSRS Row Group for a grouping value and have it show when no rows returned RRS feed

  • Question

  • Hi,  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 myData 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 dataSet..

    Any guidance is appreciated as I'm stuck.

    Ibis

    Monday, October 14, 2019 3:08 AM

Answers

  • HI Ibis,

    As I viewed your both posts, as far as I understand, your issue is more likely like a full outer join of two datasets instead of a left/right join?

    If you are sure that the prior year contains all the categories of current year, which is kind "right join" in this case. You could use last year as lookup argument and look into the target dataset as this year.

    But in fact SSRS is more a reporting tool, it's ability to handle multiple datasets is quite constrained. You could pre-process  the data before SSRS than design the report with those "cooked" data. That would be much easier. Your choice could be : to use SSAS first to process the SP list or in Sharepoint side to manipulate the data with CAML.

     

    For you case, another workaround is so :

    1. You could create a list structure. Group your list detail on the team field. 

    2. Inside the list create six tables one after another. Put the category and other count fields in it. 

    3. Set filter for each of the six tables one by one manually type the arguments as group name, so that the table only shows related data. For those category don't have data will be blank.

    The layout should looks like so.



    I hope above could help.

    Regards,

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.




    Tuesday, October 15, 2019 3:01 AM

All replies

  • HI Ibis,

    As I viewed your both posts, as far as I understand, your issue is more likely like a full outer join of two datasets instead of a left/right join?

    If you are sure that the prior year contains all the categories of current year, which is kind "right join" in this case. You could use last year as lookup argument and look into the target dataset as this year.

    But in fact SSRS is more a reporting tool, it's ability to handle multiple datasets is quite constrained. You could pre-process  the data before SSRS than design the report with those "cooked" data. That would be much easier. Your choice could be : to use SSAS first to process the SP list or in Sharepoint side to manipulate the data with CAML.

     

    For you case, another workaround is so :

    1. You could create a list structure. Group your list detail on the team field. 

    2. Inside the list create six tables one after another. Put the category and other count fields in it. 

    3. Set filter for each of the six tables one by one manually type the arguments as group name, so that the table only shows related data. For those category don't have data will be blank.

    The layout should looks like so.



    I hope above could help.

    Regards,

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.




    Tuesday, October 15, 2019 3:01 AM
  • Thanks for this information Lukas!  I have been so busy that I could not reply fully.  It turns out that I will be doing the "much easier", "cooked data" route as I have managed to convince the client to pull the data to a database on a regular basis.  Much more pleasant when SQL is allowed!  Even though I am taking this approach, I think I will be referring back to your post if I ever need to mix the List and tablix as you mention above.  It's amazing what you can do in SSRS when you CAN'T use SQL...

    Tuesday, October 15, 2019 9:05 PM
  • I‘m so glad that the issue is resolved.

    Yes, to save the data in the DB would be a superb choice considering scaling, storage and  processing.

    Cheers!



    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, October 16, 2019 1:34 AM