5 Replies Latest reply on Feb 14, 2012 9:32 AM by philmodjunk

    Need Help with cross tab report -text fields

    SudhaMovva

      Title

      Need Help with cross tab report -text fields

      Post

       Hello everyone,

      Would it be possible to create an FM report in the following format? I could find various ways to create a crosstab with calculation and number fields, but not with text fields.

      I need to create a report that lists all the segments and products in that segments on the left side, vertically. On the top, the report should populate all the test plans available in FM. And the report should populate the market test description and FM record number in the cells corresponding to a particular segment and test plan.

      For example, For the text  in blue , it should give the description of market test that was used in segment 1 in plan 5. It should also populate the corresponding FM records number in paranthesis.

      Any help in matter will be greatly appreciated.

      Thanks,

      Sudha

          Year 2010 Year 2011  
      Client Name and Year Product Plan 4 Plan 5 Plan 6 Plan #1 …..continues till all the plans in FM are listed
      Segment 1 - Text Field Product Name1 - Text Field Market test description -text field
      (Filemaker Record number)
      Market test description -text field
      (Filemaker Record number)
          …continues
      Segment 2 Product Name2 Market test description -text field
      (Filemaker Record number)
            …continues
      …..continues till all the Segments in FM are listed …continues …continues …continues …continues …continues …continues

        • 1. Re: Need Help with cross tab report -text fields
          philmodjunk

          Text, number, date and other field data types can all be included in cross tab style reports like this. My answer assumes that you are using FileMaker 11 so that we can take advantage of portal filters. If you do not have FileMaker 11, let me know and I'll change my approach to work with versions prior to 11.

          First you need a data structure that will work with your report. Each item in the left hand column represents a different record in the table on which this layout is based. The remaining columns are either fields from a related table or a one row, filtered portal to a related table with a text field inside it.

          And "continues until all plans are listed" just isn't possible in FileMaker. we can, however, set up portal filters so that you can click a button or select a value from a value list to pull up different sub sets of the total set of plans if you don't have enough columns added to your layout to display them all.

          I'll work with three tables here, related in this fashion:

          Products----<Segments----<TestPlans

          Products::__pk_ProductID = Segments::_fk_ProductID

          Segments::__pk_SegmentId = TestPlans::_fk_SegmentID

          Now create a list view layout based on Segments.

          Put the needed text field from segment in column 1.

          Put the ProductName field from Products in column 2.

          For column three add a one row portal to TestPlans. Specify this portal filter expression: TestPlans::TestNumber = 1. Resize the portal row so that you can place record number field underneath the text field in the same row.

          for the subsequent columns, make copies of this portal, but edit the portal filter to be Testnumber = 2, 3, etc.

          See if you can make this work first. This has "hardwired" columns for the test plans. If you want to then modify the design so that you can select a test number such as "3" from a value list and then the report displays columns of data starting with test plan number 3, let me know and I'll tell you what changes you need to make to support that approach.

          • 2. Re: Need Help with cross tab report -text fields
            SudhaMovva

             Hi Phil,

            Thank very much for you response.

            Creating portal seems like a great idea. But I should have described my database in my earlier post. All fields are in the same table in the database. 

            I created segment name and product in list view and tried to create portal for the rest of the fields. However, the portal setup is not allowing me to select the table I need. Since I already used the table for segment and product, it is listed as current table and disabled.

            Is there any way for portals to work in the same table?

            Thanks,

            Sudha

            • 3. Re: Need Help with cross tab report -text fields
              philmodjunk

              I strongly recommend you restructure your data to have separate related tables instead of multiple fields in the same record.

              • 4. Re: Need Help with cross tab report -text fields
                SudhaMovva

                 

                Hi Phil,

                I made some changes to the database, used portals, and got the following output. Each row below represents a record in the FM.

                Segment Name

                Product Name

                Plan 1- Test Description

                Plan 2 - Test Description

                Plan 3 - Test Description

                Segment 1

                Product 5

                xyz

                 

                 

                Segment 1 Product 9 abc    
                Segment 1 Product 9   xyz  
                Segment 1 Product 9     lmn

                Segment 1

                Product 4

                 

                abc

                 

                But, I want just one row for a given combination of Segment and Product. So, I tried summary report but ended up with the following format

                Segment Name

                Product Name

                Plan 1- Test Description

                Plan 2 - Test Description

                Plan 3 - Test Description

                Segment 1

                Product 5

                 

                 

                 

                 

                 

                xyz

                 

                 

                Segment 1

                Product 9

                 

                 

                 

                 

                 

                abc

                 

                 

                 

                 

                 

                xyz

                 

                 

                 

                 

                 

                lmn

                Segment 1

                Product 4

                 

                 

                 

                 

                 

                 

                abc

                 

                I would like to have all the data combined into one row for a given segment and product name. For example, in the table, the test descriptions for Segment 1 and Product 9 should be in a single row as shown below. Would it possible to combine data from different records on a layout?

                Segment Name

                Product Name

                Plan 1- Test Description

                Plan 2 - Test Description

                Plan 3 - Test Description

                Segment 1

                Product 5

                xyz

                 

                 

                Segment 1

                Product 9

                abc

                xyz

                lmn

                Segment 1

                Product 4

                 

                abc

                 

                 

                Thank you very much for your  help.

                -Sudha

                • 5. Re: Need Help with cross tab report -text fields
                  philmodjunk

                  Why would you have multiple records for the same segment and product?

                  Looks like you have one table where you need two. A segments table where you have one record for each segment and product combination and a plans table where you link plans records to a given segment record.