9 Replies Latest reply on Apr 25, 2017 10:49 AM by philmodjunk

    Challenging cross tab report

    sccardais

      I would like to create a report similar to the one shown on the left side of the screenshot below. The two boxes on the right represent the tables and source data fields.

       

      After setting 2 global date fields, and starting from a layout based on PRODUCTS, I want to compare the total number of customers for each product and calculate the difference.

       

      The total number of customers for each product is a calculation field using the DATA: date_ordered and DATA_date_canceled. For example, the value for Product A under global date 1 would be calculated by counting all customer ids for orders received before global date 1 and subtracting a count of all customer ids for orders canceled before global date 1.

       

      Can anyone suggest how I might accomplish this?

       

        • 1. Re: Challenging cross tab report
          philmodjunk

          Are your relationships set up like this?

           

          customers---<invoices---<line items>----products

          ---<.   Means "one to many"

          • 2. Re: Challenging cross tab report
            sccardais

            Yes . PRODUCTS has 10 records and DATA has many records with matching products.

             

            However, there are no join tables. The relationship direct from Products > Data based on a match of the PRODUCT.

             

            Thanks.

            • 3. Re: Challenging cross tab report
              philmodjunk

              Then your answer is no. What you describe does not match what I posted.

               

              Please post a screen shot of the relevant portion of your relationship graph. Where the data for your report is located and how the tables are related are critical details.

              • 4. Re: Challenging cross tab report
                sccardais

                Don’t understand.

                 

                The screenshot in the OP showed the table relationship to the right of the sample report I am wanting to create.

                 

                Products > DATA

                 

                Perhaps that part of the screenshot was cut off or truncated?

                 

                My reply to your post was confirming that one record in PRODUCTS relates to many records in DATA. I thought that was the basic question since my OP didn’t say anything about invoices or line items.

                 

                What am I missing?

                • 5. Re: Challenging cross tab report
                  philmodjunk

                  Yes, but my smart phone clipped the right part of the image leaving no clue that there was additional info to see if I opened the image for a close up. On my computer, I can see the relationships.

                  • 6. Re: Challenging cross tab report
                    sccardais

                    Ah. That explains it.

                    • 7. Re: Challenging cross tab report
                      philmodjunk

                      You can add more occurrences to your relationship graph that use your global fields as match fields with inequality operators or you can use ExecuteSQL to get the counts that you want.

                       

                      Which approach would you like to use? (if you are not familiar with SQL, use the extra occurrences approach).

                      • 8. Re: Challenging cross tab report
                        sccardais

                        I’d prefer to use the extra occurrences approach.

                         

                        Assuming a value of 1/1/2017 for global_date_1 and PRODUCTS::product_id = DATA::product_id

                         

                        for each of the 10 records in PRODUCTS, I want to show the customer count for two dates. (global date 1 and 2)

                         

                        Customer Count for Date 1 is:

                         

                        - count of DATA::org_id if the date ordered is less than global date 1 LESS count of DATA::Org_id if date-canceled is less than global date 1

                         

                        Customer Count for Date 2 is basically the same - substituting global date 2, of course.

                         

                        I’m assuming I’ll get the Difference by naming the results and creating a variable that uses the named objects to calculate the difference.

                         

                        Thanks for your help.

                        • 9. Re: Challenging cross tab report
                          philmodjunk

                          You can set up these relationships for one global field. Just replicate using the second global field to get column 2:

                           

                          Products::GlobalDate1 < DATAbyOrderedGD1:: date_ordered

                           

                          And another relationship:

                           

                          Products::GlobalDate1 < DatabyCanceledGD1::date_Canceled

                           

                          Then:

                           

                          Count ( DATAbyOrderedGD1:: date_ordered ) - Count ( DatabyCanceledGD1::date_Canceled )

                           

                          Gives you the difference.

                           

                          I've used < because you specified "before the date". If you actually want "on or before the date", use  ≤