2 Replies Latest reply on Jun 25, 2009 12:43 PM by mark_d2x

    re summary reports



      re summary reports


      Hi, struggling again!


      Have read 2 books and still struggling with creating a report; what I thought was that I could do in effect a "pivot table" type report.


      What I want is to produce several different types of report - all very similar with increasing complexity.


      Firstly, I want to create a report based on the surgeon (SURGEON_GMC_CODE) and the operations they have done (PRIMARY_PROCEDURE_NAME).  


      I want to have a drop down  box to choose the surgeon then in a table it has all the kinds of operations they have done for a given period of time.




      Surgeon A - from (DATE_OF_OPERATION) [start date] to [end date] 


      Procedure 1   [result = count PRIMARY-PROCEDURE_NAME 1]  

      Procedure 2   [result = count PRIMARY-PROCEDURE_NAME 2]

      Procedure 3   [result = count PRIMARY-PROCEDURE_NAME 3]



      Then I would like to add another variable [MAJOR_POST_OP_COMPLICATION



       Surgeon A - from (DATE_OF_OPERATION) [start date] to [end date] 


      Procedure 1   [result = number] 

      [result = count complication 1]

      [result = count complication 2] 

        [result = count complication 3] 


      Procedure 2   [result = number] 

      [result = count complication 1]

      [result = count complication 2] 

        [result = count complication 3]  


      My question is this; is this possible in FMP ? or is this best done in Excel?


      I also need to run a similar report for procedure and complications.


      Best wishes as always



        • 1. Re: re summary reports

          It's all about the relational structure (or lack thereof) of your database. I hope the books you read gave some good instruction on relational design, because without it a database is more like a spreadsheet (but slower). 


          The basic rule of reports is to do the report in the table which actually has all the data (if possible), or "child to parent" access to it. But in this case that may not be possible. It seems that the base table here would be Procedures, which would have access to its "parent" Operations, which would have access to its parent(s) Surgeon (multiple surgeons per op?).


          Complications would be children of a Procedure (always?). But any given Procedure may or may not have Complications (hopefully not).


          So, the question arises, how many fields do you need from Complications. If only a few, you could possibly gather them, via the List() function, or via a Custom Function (requires FileMaker Pro Advanced to implement).


          This is all doable, providing you have a decent relational structure. Many people do not think that is important at first. You can get away with inadequate design when just putting data fields on layouts. But then, when you get to reports (or any kind of real automation), you run into roadblocks. Heck, you may run into a few anyway, but only the "necessary" ones :-]

          • 2. Re: re summary reports



            The  main database structure is:   


            Patient (_kp patient)

            Tumour (_kp tumour, _kf patient)

            Treatment (_kp treatment, _kf tumour)

            Follow up (_kp follow up, _kf patient)


            This was set-up so that a particular patient could have more than 1 tumour with more than 1 treatment for that tumour and multiple follow ups.


            For each of the look ups variables, they are in a look up table; the lookup table has 2 fields - description and code, when I am running my report, I can pull the description if I want to use it locally or use the code field instead if it is for uploading to government.   These have a relationship set and are in the look up values list.


            SURGEON_GMC_CODE field is linked to the _lookup surgeon gmc code table for the lookup etc


            There are about 20 surgeons and 20 procedures that we record for the purposes of this.