none
Add (dummy) data rows in a tablix for use as lookupset criteria - Like a left join - without SQL (Sharepoint source) RRS feed

  • Question

  • </button>

    I have created a report in report builder against sharepoint list. The report pulls data from a single SP list that pertains to "ISsues" and is like the following: Team, category, createDate. this is dataset dsCURRENT

    The report requirement is to show counts for 4 weeks up to the runDate, broken down by Team, and Category. This is no problem, I created parameters to represent the 4 weeks and was able to do the grouping and subgrouping, giving all of the correct rows and column groups give the counts by week. This is tablix tbCURRENT.

    Also required was a Year over year comparison of these counts, so for each week, compare to the same week in the prior year. I was able to accomplish this using a second dataset (dsPRIORYEAR) that pulls the same data (weeks) but for the prior year and using lookupSets and Iif to subtract current count - priorYearCount.

    The ISSUE - which comes in the form of new requirement , and my question, is what if there are categories in dsPRIORYEAR that are not in dsCURRENT ? Since I am using lookup sets to get the value it is like a left join - I only get the set of categories that exist in dsCURRENT. New requirement says show all categories even if they are not in the current data

    The good thing is that I can know the distinct set of categories at design time (there are only 6)

    So, I was thinking maybe I can Add missing ones - Like dummy records in dsCURRENT after it populates so that I always get at least one row for each category to compare, even if the query returns none. I assume if it's even possible, I would then need to subtract those records out in my lookusets.

    Any help on creating the dummy records in the tablix, or other ways to go about this would be appreciated. And I can give further clarification if needed.

    Thanks,


    • Edited by Ibis Ripley Thursday, October 10, 2019 5:00 PM
    Thursday, October 10, 2019 5:00 PM

Answers

All replies

  • Hi Ibis,

    Just wonder how you pulled your data, could you use Sharepoint Join node to get the left join as you wanted?

    Left Outer Join in SharePoint

    Join 2 lists in SharePoint using CAML query

    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.

    Friday, October 11, 2019 7:55 AM
  • Hello Lucas,

    Thanks for answering.  Much appreciated!  Unfortunately, I don't think a left join CAML query will solve my issue.  My issue is less like a left join problem and I can explain better.   All of the original description holds true, but I may be going about it wrong.  I just need to report on all of the categories (known ahead of time, 6 of them) whether or not they exist.  

    Monday, October 14, 2019 2:49 AM
  • Hi Ibis,

    Yes, I noticed that the description in the post and post's title are somehow different and also confused me. So I give the link from the understanding of you title.

    I also find that you have open a new ticket in the forum with updated information, could you kindly help to close this thread so we could focus on the new one?

    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 1:48 AM
    • Marked as answer by Ibis Ripley Tuesday, October 15, 2019 9:14 PM
    Tuesday, October 15, 2019 9:13 PM