3 Replies Latest reply on Sep 26, 2018 5:24 AM by Alexander Dawson

    Filtering across data source by surrogate field

    Charlie Baker

      Note, the below is an approximation of my problem.  Without getting into the details, I cannot use joins to solve the problem below.



      Here's my goal:


      I have a data source with PersonId and Gender.  There is one record per Person.  I have a pie chart of breakdown by Gender


      I have another data source with PersonId and Pets.  There can be more than one sibling per person.  I have a bar chart of Pets by count (i.e. n total dogs, n total cats owned in the population).


      What I want to do is link these data sources together by PersonId (which I know how to do) and be able to, and then, for example, click 'Male' in the pie chart and have by bar chart filter to contain pets owned by people who are Male.  That is, I want to filter the BarChart by Gender by using resulting set of PersonIds as a surrogate for 'Males'.  So whatever PerosnIds correspond to 'Male' are the only ones taken into account when creating the bar chart.

        • 1. Re: Filtering across data source by surrogate field
          Alexander Dawson

          I'm going to assume that more than one sibling per person means more than one pet per person.


          How are you joining the two data sources?


          A person has to have a single gender status.  I know we've evolved beyond the binary M/F.  So, if needed, you'll need to use different statuses for agender, uncertain, fluid etc....but you need to have one and only record per person.


          The People/Gender table need to left outer join People/pets.  I'm not sure if your pets are already aggregated, or you'll do that on the report.  This will be joined on the Person ID.


          If you set it up like this, you should be able to let choosing the gender filter drive which pets are counted. 


          Note this has to be done on the data source, not by linking a primary and secondary data source on the report worksheet.

          • 2. Re: Filtering across data source by surrogate field
            Charlie Baker

            Yes, sorry my mistake, siblings is pets.


            Unfortunately, I can't use joins to accomplish this.  I have to keep the tables separated because I'm working on a data set that spans 10 different tables and joining them all together results in billions of records.

            • 3. Re: Filtering across data source by surrogate field
              Alexander Dawson

              Hi Charlie,


              I'm not sure the volume of data should be a constraint.  Looking at it both ways though. 


              For your data, make sure it has Connection = Extract.  Within the extract check off aggregation.

              Make sure both data sets are added to the same connection.


              If you're still worried about a potential performance issue, then filter.  I never just let the whole thing run until I'm ready to use it.  For your People table, add something like Filter_ID < 100.




              APPROACH 2:
              If you really do want to keep things how they are, ideally you'll want to create the data source, and then requery to it.   But you can't quite do that.  It's a highly requested idea which has not been implemented:



              However what you can do is build an interim table then export it.  You may be able to do this with what you already have.  Just export it into a new data file, then use that as your data source.