9 Replies Latest reply on Sep 16, 2015 10:11 AM by BruceRobertson

    An awesome challenge: building in charts and maps into summary report

    sunil72

      The context: Govt.  of X is  running a multiple state skill  development programme. The programme's monitoring & evaluation team  intends to carry out a review of the performance of the programme. The  stakeholders of the programme include states and project implementation  agencies (PIA), firms that train the candidates. As part of the review,  the M&E team aims to report state wise performance and PIA wise performance. The performance indicators are captured in different spreadsheets but the indicators are linked to unique IDs. Further, each record is a project that among other things  capture details on state, PIA, districts within state( this will come from other table) and training targets. So, the team  is aware that in FileMaker, the indicators (fields) will come from  different tables. The M&E team intends to include a lot of charts  and also preferably a map that will capture district wise targets that  have been assigned to the states. A  typical chart in a state wise summary will capture total number of  candidates that were trained in July, Aug and Sept. Within the state summary the chart will capture total number of candidates that were trained in July, Aug and Sept. How should the team proceed in developing the report?


      Regards

      Sunil

        • 1. Re: An awesome challenge: building in charts and maps into summary report
          sunil72

          Hello: please help me with this challenging problem 

          • 2. Re: An awesome challenge: building in charts and maps into summary report
            sunil72

            Hi Community: Please take up this challenge!

            • 3. Re: An awesome challenge: building in charts and maps into summary report
              sunil72

              Hello! Please have a look at this!

              • 4. Re: An awesome challenge: building in charts and maps into summary report
                Mike_Mitchell

                You haven't provided enough information to give a specific answer. (And asking the question 4 times in 8 hours won't likely improve the quality of the answer.)

                 

                On a simple summary report, the chart can just use the summarized data from the current found set of records, as shown below:

                 

                dialog.png

                 

                The summary fields can be individual data series. If, on the other hand, you need to see data from different series (perhaps different tables summarized differently, you can use individual record data and summarize the data in global variables.

                 

                As far as the map is concerned, that's largely dependent on exactly what you want. It's possible to tie into the Google Maps API, for example, using various JSON or other web coding to display a map in a Web Viewer object. However, it sounds like what you want is something like a political map of states with targets overlaid on it. In that case, you might be better served to build a layout that shows the map and simply put fields on top of it that contain the data you want to display.

                 

                Again, we really can't answer your question unless we have sufficient detail of your desired end state goal.

                • 5. Re: An awesome challenge: building in charts and maps into summary report
                  sunil72

                  Hi Mike: I will quickly tender an apology if my questions were not sharp. I have re-phrased the question below and I have also built in context:

                   

                  In each of the tables given below, each project ID is one project. There are several tables. The tables are given below

                   

                  Table 1

                  ProjectID

                   

                   

                  Name of State

                   

                   

                  Scheme

                   

                   

                  Name of PIA

                   

                   

                   

                   

                  Total in trained in Aug

                   

                   

                   

                  Total trained in Sep

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                  Table 2

                  ProjectID

                  Sector

                  Trade

                  NCVT/SSC Trade Code

                  Course Duration (in No. of Hours)

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                  Table 3

                  ProjectID

                  District

                  Training target

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                  Table 4

                  ProjectID

                  Total amount released to PIAs in Rs.

                  Amount released in 1st instlmt in Rs.

                  Date of release of 1st instalment (dd-mm-yyyy)

                  Amount released in 2nd instalment  in Rs.

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                  Using the fields captured in the above I want to create a report that will capture state wise figures for Total in trained in Aug and Total trained in Sep, in the form of a chart. Within the same report, using portal tool, I also want to capture the total count of trades offered; total training training target; total amount released to PIAs and other fields. Within the same report I want to create a map that will capture the district wise training targets in the state. Please suggest what should I do. I hope now my question is not ambiguous.

                  • 6. Re: An awesome challenge: building in charts and maps into summary report
                    SteveMartino

                    An Awesome Challenge...for who?  How far along are you with this database?  Is it even built yet?

                     

                    I think all the people in the forums who volunteer to help (for free), don't mind offering help whether the question/topic is easy, hard or complicated-and usually follow through with additional questions.  I've asked my share of questions and received plenty of excellent help from many people.

                     

                    But, unless someone can point you to something similar, already created, you should be looking for someone to hire for this situation.  About 25 more questions about your design, relationships, goals, etc. would have to be asked/answered, including sample files going back and forth to make this happen.

                     

                    Also, try to break in down into pieces.  What do you have to solve first?  Is the proper structure in place? Etc.

                    • 7. Re: An awesome challenge: building in charts and maps into summary report
                      sunil72

                      Hello Steve: I have titled my query to pique the interest of the members. I also thought that the members are intrigued by difficult problems and will be willing to help.

                       

                      I'm ready with the database. It is all set. But I'm struggling to put charts in the stat summary report. My first hurdle will be to get in the chart that will give state wise summary in the summary report.

                      • 8. Re: An awesome challenge: building in charts and maps into summary report
                        Mike_Mitchell

                        Your data structure is very strange. You have four tables, all four of which appear to represent the same entity (a project). That would not be a normal structure. Further, it's not usual to have a data column (field) for something like "Total in August" or "Total in September", because such a structure would have to change in order to add a new month (unless this were a structure intended strictly for reporting, such as for a "pivot table" sort of presentation).

                         

                        That said, it's a trivial matter to create a chart that shows the total by state in August or September. The Total in August / September are simply data series, summarized by month. Total count of trades offered (which I presume is coming from Table 2) would simply be summarized data. And, as I said before, the map would probably be best done via a graphic with a field in each state. I'd probably just run a script that summarizes the total number by state (you'd have to connect State to District) and then just insert that value in the appropriate field.

                         

                        I suggest you pick up a copy of the FileMaker Training Series and go through it. You appear to be missing some of the fundamentals regarding data modeling and charting, and the training program will provide those for you. I think you need more assistance than we can reasonably give you here.

                         

                        Mike

                        • 9. Re: An awesome challenge: building in charts and maps into summary report
                          BruceRobertson

                          "I'm ready with the database. It is all set."

                          No, you're not.

                          +1 for comments from Mike and Steve.