1 2 Previous Next 21 Replies Latest reply on Jul 24, 2012 9:39 AM by philmodjunk

    Difficult Report Challenge

    mgxdigital

      Title

      Difficult Report Challenge

      Post

      Hi All,

       

      Our owner has prsented me with a report that she would like that seems impossible to me. Could anyone tell me if this is even possible or if i should stop spending time on it?

       

      She's wanting a list of sales reps displayed vertically with contact types listed hoirzontally with the count under each sales rep name. Example

       

                      Greg           Emily          Patty           Scott

      Phone        2                 4               6                  1

      Email          3                6               4                  7

      Fax           1                  0              3                   2

       

      Then, if this is even possible at all, she would want some kind of conditional formatting to highlight the number of hte sales rep with the most. Example:

                      Greg           Emily          Patty           Scott

      Phone        2                 4               6                  1

      Email          3                6               4                  7

      Fax           1                  0              3                   2


      Any help would be amazing. Thanks.

        • 1. Re: Difficult Report Challenge
          philmodjunk

          This is called a cross tab report.

          Each column can be produced with filtered portals.

          The rows are either individual records or sub summary layout parts. Either way, it's usually a list view type report, though I have seen and created examples of this in form view.

          Much depends on the structure of your basic data.

          The conditional format should be possible as well.

          • 2. Re: Difficult Report Challenge
            mgxdigital

            Hi Phil

            Could you help me more with this? It might be complicated.

             

            Tables effected: 

            Contacts

            Activites

            Categories

             

            Contacts are related to the activites and each contact can have multiple activites. Each contact has a sales rep associated with it.

            If you create a activity for a contact, you have a list of contact types that you can choose from. These are from the acticity category table occurance.

            What more can i provide to get a little more insturtion on who to set this up?

            • 3. Re: Difficult Report Challenge
              philmodjunk

              Contacts are related to the activites and each contact can have multiple activites

              Implies this relationship:

              Contacts::ContactID = Activities::ContactID

              and this one:

              Categories::Cat_ID = Activities::Cat_ID

              Do you have this relationship?

              SalesReps::SR_ID = Contacts::SR_ID

              That would produce this picture:

              Sales_Reps----<Contacts-----<Activities>-----Categories   (---< means one to many)

              Are the following, all fields in your Activities table? (You may have others, but these would seem to be the ones we need for your report)

              ContactID
              CategoryID
              ActivityDate  (Or ActivityTimeStamp)

              When you pull up such a report, will it be for a specified set of activity records such as all activity records for the 1st quarter 2012?

              What version of FileMaker are you using?

              • 4. Re: Difficult Report Challenge
                mgxdigital

                Yes, I have all of the relationships you listed above just like your wrote expect for Sales_Reps----<Contacts-----<Activities>-----Categories   (---< means one to many), but i can create that one.

                I am pulling all records in the activited table like you said

                I do have the 3 fields: 

                ContactID
                CategoryID
                ActivityDate

                Yes, i will want to limit the report to a found date range.

                 

                I'm using Filemaker 11

                • 5. Re: Difficult Report Challenge
                  philmodjunk

                  Yes, I have all of the relationships you listed above just like your wrote expect for....

                  How's that again? The last bit simply sums up the details of what I had describeed in more detail. If you have all the relationships listed before, you have that as well.

                  FileMaker 11 and 12 allow you to use filtered portals-- a very useful tool for cross tab reports. FileMaker 12 also can use the Execute SQL function to pull up such data.

                  Add some global date fields to the Category table: gDateStart and gDateEnd. We can use these in a portal filter to limit the counts displayed to a given date range.

                  Add a summary field, sCount to the Activities table as the count of ContactID (any one of these will work as all should never be empty).

                  Make a new occurrence of Sales_Reps and link it directly to Categories like this:

                  Categories::anyfield X AllSales_Reps::anyField  (you can select any field for the match fields as long as you also specify X instead of = in this relationship.)

                  Set up a list view layout based on Categories and put the Category name in the body on the left hand side to list the possible Categories. In the header, put a one row portal to AllSales_Reps and put the name field from AllSales_Reps in the portal row. Make several copies of this portal, put them side by side, but use portal setup to specify that the second portal use "initial Row" 2, the third portals would specify "initial row 3" and so forth.

                  Test this out and make sure that you can get a list of categories down the left side and a list of sales reps across the top.

                  Let me know when you have this working and we can go to the next step, adding the row of one row portals that will count activities for a specified category, sales_rep and date_Range.

                  • 6. Re: Difficult Report Challenge
                    mgxdigital

                    I got them - categories listed down the left side, sales reps along the top. Ready for the next steps. Thanks again.

                    • 7. Re: Difficult Report Challenge
                      philmodjunk

                      Sales_Reps----<Contacts-----<Activities>-----Categories----X---AllSales_Reps

                      Now to add a new occurrence of activities to support the relationship we need. This occurrence is not strictly necessary, but enables us to include the global fields in the relationship so that the portal filters will all smoothly update whenever you change the date in one of them.

                      Relate it like this:

                      Categories::gDateStart < XtabActivities::Date AND
                      Categories::gDateEnd > XtabActivites::Date AND
                      Categories::CategoryID = XtabActivities::CategoryID

                      Add a new occurrence of Contacts, linked like this:

                      XtabActivities::ContactID = XtabContacts::ContactID

                      Put a one row portal to XtabActivities under the first column heading sales rep name portal and give it this portal filter:

                      XtabContacts::SR_ID = GetNthRecord ( AllSales_Reps::SR_ID ; 1 )

                      Put the sCount summary field from xtabActivities in the single row of this portal.

                      YOu can now duplicate this portal for each of the other columns, but change the "1" to 2, 3, 4, etc in the portal filter expression to match to different sales rep ID's.

                      This should provide everything that you asked for except for the conditional formatting to highlight the sales Rep with the high count for that category. I'm afraid that I've painted us into a bit of a corner on that one, so while I think that one over a bit further, see if you can get the columns of counts to display correctly. (You can put the gDateStart and gDateEnd fields just about anywhere that works for a place to specify the desired date range, you can also use scripts to set these dates as well.)

                      • 8. Re: Difficult Report Challenge
                        mgxdigital

                        Hi Phil,

                        I'm unable to get any counts to appear on my layout.

                        Do i need to enter anything into the global date field for them to appear?

                        I made the relationships just as you explained.

                        • 9. Re: Difficult Report Challenge
                          philmodjunk

                          Yes, you need to enter dates into both global date fields.

                          • 10. Re: Difficult Report Challenge
                            mgxdigital

                            I've gotten a list of all reps to appear. The filter on the xtabactivies does not seem to be working. 

                            XtabContacts::SR_ID = GetNthRecord ( AllSales_Reps::SR_ID ; 1 )

                            • 11. Re: Difficult Report Challenge
                              philmodjunk

                              Do Contacts and the Sales Reps have matching values in the SR_ID field?

                              Using the data viewer (if you have advanced) or a calculation field (if you don't) check and see what value is returned by GetNthRecord ( AllSales_Reps::SR_ID ; 1 ).

                              It should return the ID of the first Sales Rep record in your Sales Rep Table.

                              • 12. Re: Difficult Report Challenge
                                mgxdigital

                                Yes, that is working just fine.

                                I've made a portal in the body of hte layout just to check the id and the sales id is working. The filter is setting the sales id to the correct number, but the list is not populating the company_count correctly. 

                                • 13. Re: Difficult Report Challenge
                                  philmodjunk

                                  What are you getting in the portal? A blank field or an incorrect total. What have you set up for the two date fields? Do they have global storage specified?

                                  • 14. Re: Difficult Report Challenge
                                    mgxdigital

                                    Yes, the date fields in categories are both global. The date field in activites is not, it is a creation date.

                                     

                                    The portal is showing all of the sales reps per category, so the total is all of hte sales reps activites combined. 

                                     

                                    But when i put the filter ContactsXtab::ID_Sales_fk = GetNthRecord (AllSales::ID_Sales_pk ; 1 ) fields in the portal, the ContactsXtab: ID_SAles_fk does change to the AllSAles reocrd number that i assign. 

                                     

                                    So i'm not sure what i have wrong.

                                    1 2 Previous Next