6 Replies Latest reply on Sep 20, 2012 8:32 AM by micinfo

    Report help URGENT


      Dear forum.


      Basically, I have 3 table involved in this problem.


      Table 1 has all the wind turbine parts listed with the main field being the _kp_OEM Part Number


      Table 2 has a wind farms part usage information for 2011 given as yearly data (i.e. at the wind farm it tells me that a month / year i used how many of each OEM Part Number)


      Table 3 resembles table 2 with the same wind farms part usage information but this time for 2012 with data given monthly (i.e January 2012 only populates this table so far...-so i kknow how many of each OEM Part Number were used in Jan 2012.)



      I want to create report (layout) that shows the following:


      OEM Part No. Manufacturer part no. Description Current Year Current Month Quantity usage Year Quantity usage in previous year (2011) Average monthly previous usage for 2011 Variance in usage
      1. 1234 abs1002 generator 2012 Jan 4 2011 30 (30/12)= 2.5 (4-2.5)= + 1.5
      2 4567 abs39495 switch 2012 Jan 3 2011 40 (40/12)= 3.33 (3-3.33)= - 3.33




      (the first 3 fields above are from Table 1 (parts list)

      the next 3 fields are from Table 3 (current usage)

      the next 3 fields are from Table 2 (previous/past usage)

      and the final field is the variance calculated from the current months usage (Jan 2012) minus the previous months usage (average of 2011 data for a month- given by yearly usage divided by 12)


      Does anyone know how i would show this all in one report.

      I can obviously show the first 6 fields okay

      but i do not know how to then show the previous usge fields linking up to the same OEM Part Nunmbers!?



      Can anyone please help me on this urgent matter!!???




        • 1. Re: Report help URGENT

          Sorry that table distorted! It is supposed to run from left to right like in filemaker table view!

          • 2. Re: Report help URGENT

            Danielle --


            I think you should change two things to get the solution you are seeking.


            1) Change your data structure.  You do not need to have two separate tables for 2011 usage and 2012 usage.  All usage should be in the same table with a field for both the month and the year of the usage, or a date field that can be broken down into the month & year.


            2) I'd switch to Excel for the final report itself.  You could export data for both 2011 & 2012 usage to a 'Raw_Data" worksheet, create a second worksheet for a Pivot Table that can re-organize and group your data appropriately, then set up a third worksheet with a series of VLOOKUP() functions to that can neatly display the data you are looking for.


            My guess is that the people who are going to receive the report are expecting Excel anyway.  For some data analysis, Excel is the better/simpler tool.


            In FM12, there would be some argument for using the ExecuteSQL() function to grab the data you are looking for, but I know Excel better.  I'm also guessing that you don't need to see, on a dynamic record-by-record basis, what the comparison of a particular farm's usage for this month to the same month last year is.  That would require moment-to-moment access to the data for immediate updates, and that doesn't sound like your current need.


            -- Drew Tenenholz

            • 3. Re: Report help URGENT

              1. I agree with Drew, change your structure. The same type of data with only a year value differing them should be in the same table, with a "year" field. Much easier to push and pull for reporting.


              2. I just wrote a blog about cross-tab reports with related summary fields. See it it helps:


                   "Aggregates in Filtered Portals"

                   - there are also links to related blogs about cross-tab reports at the end of the article, for more ideas, especially for exporting this to Excel.



              • 4. Re: Report help URGENT

                OK, I'll trust that you have good reason to keep Table 2 separate from Table 3, if for no other reason than Table 3 is month-and-year-wise and Table 2 is only year-wise data. In any case, assuming you need to stick with 3 tables, then if we can assume that there is a unique record for each "OEM Part No." in Table 1, then you could create the appropriate relationship between Table 1 (Part No.) and the two Usage tables. This assume that there is a unique record in Table 2 for each OEM Part No. & Year combination, and also a unique record in Table 3 for each OEM Part No. & Year & Month combination. The idea is to create a 1-to-1 relationship so that you do not end up with ambiguous related data, as follows:


                To link Table 1 to Table 3, use "OEM Part No." and "Month" as the match fields in a complex relationship, e.g., called "Current Yr Usage" (assuming Table 3 ONLY contains data for the Current Year and also assuming your report is only for a single month, otherwise if it contains data from more than one year, then simply add the Year as a third field in your complex relationship; it's possible to make the report cover multiple months, it's just another dimension that needs to be added to the report). You would either manually select the month (and year, if necessary) from a value list in Table 1, or you could simply calculate it via a script that generates the report.


                To link Table 1 to Table 2, just use "OEM Part No." as the match field in a simple relationship, e.g., called "Prev Yr Usage" (assuming Table 2 only has annual, not monthly, data from the previous year for each part number).


                Once you uniquely identify each related record, you then can represent your Usage data within the approrpiate single-row that matches the relationship for each part number and year (for Table 2) and also month (for Table 3). And just as you can specify which related data displays in each Usage field from both Tables, you also can perform math between those portals in order to calculate the resulting Variance as a function of the OEM Part No. (you might need to widen the window to get all the following to spread out properly):



                OEM Pt • Manuf pt • Descript • Current Yr • Current Mo • Qty UsgYear •  Qty Usg Prev YrAvg Mo'y usg Prev YrVariance

                1234    abs1002    generator   Tab1::yr    Tab1::mo    Tab1::usg  Tab2::yr   Tab2::prevYr     Tab2::usg     (Tab1::usg)-(Tab2::prevYr)



                As the other posters here have suggested, you can combine your usage data into a single table, but that still would require you to create two separate relationships, one with the month and current year and one with just the previous year excluding the month. These parameters can be selected in Table 1 via value lists which set fields that are incorporated into the relationship.



                Would that work for you?



                Phil (@ micinfo.com)

                • 5. Re: Report help URGENT

                  Long term, it would be wise to bring all that data into a single table.  But in the short term, Phil's suggestion should get you the report you need.


                  In addition to the descriptions above, you could also use the virtual list technique to gather all the values into a global variable. Then you can display it as you see fit. It might be a little bit of an expansion of your current skill set, but in the long run can help you make more flexible reports. 

                  • The idea, basically go through the process ( Looping script or such ), and gather the values for each OEM Part. 
                  • Calculating the needed values as you go, each separated by a tab or some other delimiter.
                  • Then in a VirtualList table, each field is a calculation that equals the respective segment of the delimited row.  Each record will pull the next value ( FM Value = row in a list ) from the global variable.


                  That may be more long term of an approach than getting this report to the necessary parties, but adding it to your toolbelt will serve you well as you continue work in FM.

                  • 6. Re: Report help URGENT

                    There's a misleading label in my post. What I refer to as the relationship "Tab1::" should be labeled "Tab3::" to more clearly identify that it's referring to a relationship bewteen Table 1 and Table 3. Therefore, the red fields in my example should be "Tab3::yr" and "Tab3::mo". LIkewise, the final field to the right should read "(Tab3::usg)-(Tab2::usg)".


                    To make it even more clear (and more consistent with my suggestions for constructing the relationships), what I just said should be the red label of "Tab3::" in the colored example refers to the relationship I describe in the text description as "Current Yr Usg::" while my blue label "Tab2::" in the color example refers to the relaionship "Prev Yr Usg::" in the description.


                    Sorry for any confusion. I was in a hurry to reply to your urgent request!