7 Replies Latest reply on Nov 5, 2009 10:44 AM by philmodjunk

    Create a report

    lamp

      Title

      Create a report

      Post

      Dear All,

      I have a database with current table ( customer, query, sub-query). 

      A customer raise an query we put it in the database any work is done related to that query registers as sub-query. Relation is as follow

       

      customer.customerid=query.customerid

      query.queryid=subquery.queryid

       

      When I want to create a report from "sub-query" I want to list all queries with related sub-query.

      for example:

      query                 sub-query            

      shoping              list the wanted items

      shoping              get price from shops

      shoping              purchased

       

      upsent               holiday

      upsent               sickness

      upsent               bank holiday

      upsent               suspend

       

      buy car              search for car

      buy car              see car seller

      buy car              put a bit

      buy car              bought car

       

       

      as we can see above there is one query called ( shoping ) it has three sub-query and so on. how can I make my report to look like follow:

       

      query                 sub-query            

      shoping              list the wanted items

                               get price from shops

                               purchased

       

      upsent               holiday

                               sickness

                               bank holiday

                               suspend

       

      buy car              search for car

                               see car seller

                               put a bit

                               bought car

       

      Please let me know if you need more information or is not clear.

       

      Thanks in advance

       

      Lamp

       

        • 1. Re: Create a report
          philmodjunk
            

          I suggest looking up "summary reports" in the filemaker help system

           

          If you were to print out the report in this format:

           

          Shopping               

                                   list the wanted items

                                   get price from shops

                                   purchased

           

          absent

                                   holiday

                                   sickness

                                   bank holiday

                                   suspend

           

          buy car

                                   search for car

                                   see car seller

                                   put a bit

                                   bought car

           

          It's easier to set up in filemaker.

           

          Create a summary report based on your subquery table.

          Create a sub-summary part (Sorted by subquery::queryid, Print Above)

          Place the fields you want from Query in your sub summary part.

           

          Find the records you want and sort the result by subquery::queryid.

           

          Report will be immediately visible in FMP 10. In earlier versions, enter preview or print to see this report.

           

          Once you get that working, if you really want the format you specified, let me know and I can show you a way to get it.

          • 2. Re: Create a report
            lamp
              

            Thanks a lot PhilModJunk for reply.

             

            It worked with sub summary.

            Is there any way I can expract to excel or csv file in that format?

             

            Thanks again

            Lamp

            • 3. Re: Create a report
              philmodjunk
                

              Quoting from the filemaker help system:

               

              "If your FileMaker Pro contains summary fields, you can group by a sorted field in order to export subsummary values, such as subtotals generated by a report with grouped data. This process exports one record for each group. For example, if you have a report that totals sales by region, you can export one summary value for each region."

               

              Select Export Records, and use the group by pane in the dialog to specify the same field(s) by which you group your summary report.

              • 4. Re: Create a report
                lamp
                  

                Is there any way I can list the quries does not have any sub-query too in the same preview page?

                 

                 

                • 5. Re: Create a report
                  philmodjunk
                    

                  not easily. There has to be at least one record present. You could create a single record that is blank except for the fields needed to identify it as a member of a specific sub-summary group--but this may cause other problems for you.

                   

                  • 6. Re: Create a report
                    lamp
                      

                    Thanks a lot PhilModJunk,

                     

                    The report they asked me to create for them I have to create that blank sub-query. 

                     

                    Thanks

                    lamp

                    • 7. Re: Create a report
                      philmodjunk
                        

                      Perhaps this general outline of a script will work:

                       

                      Create one blank record for each type of query that does not have any in the current found set. (You may have to perform a find for each type and use Get(foundcount) or use a relationship to figure out which query types don't have any records for the specified date range or other criteria you use to set up the complete report.)

                      Mark a field with a unique value such as "Dummy" or "Place Holder" so that you can easily find these blank records.

                      After the report is viewed/printed/PDF'd, Find all records marked in this fashion and delete them.

                       

                      The alternative is to base your report on your Query table and use a portal to display your subqueries. With slide up selected on the portal, you can set the portal to display a very large number of rows, but slide up to eliminate unused space. The drawback here is that if you ever get more subqueries than you have rows in your portal, you won't see all of them in the report.