3 Replies Latest reply on Dec 10, 2012 9:02 AM by Mark Holtz

    How to filter off of a secondary data source?

    Cory Johnson

      I'm trying to set up a report that shows a list of sales reps and the new companies they've signed.  However, if a company hasn't done any business with us, they don't show up in the primary data source (SQL) even though they have a rating tariff.

       

      In an attempt to work my way around that, I tried to make my Excel spreadsheet that had the names of all the sales reps and the companies they've signed, but when I attempt to set up a filter by date off of the now secondary data source, it won't let me.  Is it possible to filter data from the secondary data source?

        • 1. Re: How to filter off of a secondary data source?
          Agustin Diaz

          Maybe you can use a dummy data source with all companies and use it as the main data source...

          • 2. Re: How to filter off of a secondary data source?
            Catherine Rivier

            Hi,

            You asked: "Is it possible to filter data from the secondary data source?"  Unfortunately right now, the answer is not really, as you found out.

             

            What if, instead of using the list of all sales reps and the companies they've signed (your Excel sheet) as the Primary data source, you combine it into your original (SQL) data source?

             

            For example, if your orignal (SQL) source was:

            SALESREP / COMPANY / DATE / SALES

             

            You'd put that Excel sheet info into a table in your database (called "CompanyList"), and create a union to that table, like:

            Union All

            Select SALESREP, COMPANY, '' as DATE, '' as SALES

            From CompanyList

             

            As long as you are careful counting records (don't count null values), this should work, if it's possible to add a table to your database.  Would this work?

            • 3. Re: How to filter off of a secondary data source?
              Mark Holtz

              As Catherine said, you cannot actually use the data fields from one source to filter another source.

               

              However, you can get around this by using a parameter field. You can create a string parameter and use the your list of companies from Excel to populate it.  The down-side is that as your Excel list is updated, you cannot force the parameter selection options to dynamically update to reflect new additions (or removals).

               

              You can then create a calculated field to use in the filter shelf:
              IF [Company] = [ParameterCompany] THEN 'Show' ELSE 'Hide' END

               

              The better way would be to merge the desired information into your original data source in the SQL as Catherine suggested, but I know it is not always possible to create your own tables in your database environment... I **** heads with our DBAs as well. =)