4 Replies Latest reply on Jan 21, 2014 5:56 PM by jmvatc

    sub summary part when sorted by cartesian related table field

    jmvatc

      Title

      sub summary part when sorted by cartesian related table field

      Post

           Hello,

           I am trying to create a "risk exposure by body part" report for my clients.

           For each client we track risk through four tables ["cs", "tos", "pas" & "eas"] each related to Clients.  I have also created a table BodyParts and created a cartesian relationship between Body Part::Body Part Name and Clients::Client Name.  From my understanding, this allows each record in Clients to share all of the records in Body Parts.

           Since each client has one unique risk exposure, I created a report layout using records from Clients called Risk Exposure by Body Part.  I have a sub-summary part for Client [sorted by Clients::Clients Name] then a second sub-summary part for Body Part [sorted by Body Part::Body Part Name].

           In both sub summaries, I have a portal for each of the related tables ["cs", "tos", "pas" & "eas"] with the summary field s_exposure for each [s_exposure is a total of exposure].  These portals are filtered by cs::client name and cs::body part name so that the s_exposure field only displays the s_exposure for the specific client and body part.

           This is working with correct data except that the sub-summary for Body Part only shows the first record in Body Part.  The layout is sorted correctly by Clients::Client Name then Body Part::Body Part Name.

           Is there something else I need to add?

           Thank you in advance.

           Jeremy

           FM11

        • 1. Re: sub summary part when sorted by cartesian related table field
          philmodjunk

               Seems like your layout is functioning exactly as you have designed it to function, just not as you want it to function.

               I would guess, (there's a lot you don't tell us about your database design that might affect how this works), that you need to remove the field from Body Parts from the Sub Summary layout part and replace it with Cs::body part name. You could also place a one row filtered portal to Body Parts with the same type of filter expression used with the other portals in the sub summary layout part.

          • 2. Re: sub summary part when sorted by cartesian related table field
            jmvatc
                 

                      Seems like your layout is functioning exactly as you have designed it to function, just not as you want it to function.

                 Precisely!
                  
                 I expected the first sub-summary to display each client name and this works as expected.  I expected the second sub-summary to then display each body part name. and this does not work as expected.
                  
                 I've attached the design.  Basically the question would be how do I get each body part name to list in the sub summary?
            • 3. Re: sub summary part when sorted by cartesian related table field
              philmodjunk

                   It looks like your layout is based on the wrong table, but given that you are using a cartesian join here, it's not clear if your current data model will even support such a report design as you are trying to set up.

                   If you had this data model:

                   Clients-----<Client_BodyPart>-----BodyParts

                   Clients::__pkClientID = Client_BodyPart::_fkClientID
                   BodyParts::__pkBodyPartID = Client_BodyPart::_fkBodyPartID

                   Then you could set up such a summary report by using a layout based on the Client_BodyPart layout and still be able to include fields from Clients and BodyParts.

                   For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

              • 4. Re: sub summary part when sorted by cartesian related table field
                jmvatc

                     Thanks for the clarification! I was trying to avoid creating the "Invoices" scenario that you reference.

                     I ended up taking your advice though, and changed the basis of the layout Body Part Risk from Client to Body Part.  I have a separate table that holds some global fields.  I was able to use the gClientID field from that table as the filter for the portals.

                     This creates the layout I was expecting.  Thanks again!

                      

                     Jeremy