4 Replies Latest reply on Feb 5, 2013 2:04 PM by philmodjunk

    Report creation - monthly sales forecast by sales stage



      Report creation - monthly sales forecast by sales stage



           I would like to replicate the attached report in FileMaker Pro 12.

           The corresponding database field names are as such:

           Probability - cProbability

           Opportunity Name - Opportunity Name

           Delivery Date - cMonth

           Value - Price


           Your assistance and guidance is much appreciated.


        • 1. Re: Report creation - monthly sales forecast by sales stage

               You'll need to desribe the structure or your data in more detail.

               Does each row in this report represent a single record in one of your tables (Do you perhaps have a table with one record for each opportunity?)

               Where does the data in the monthly columns come from? a related table perhaps?

               Your sample shows columns for 7 months of data. Will there always be 7 months of data (not necessarily the same months)?

               This looks like a cross tab or "pivot table" report--not something FileMaker Excels at, but such can be done if you invest sufficient effort in the design of the layout, your tables and relationships.

               Other summary type reports that organize data in a more vertical format (not columns with subtotals in each column) can be simpler to create in FileMaker.

          • 2. Re: Report creation - monthly sales forecast by sales stage

                 I've attached sample data to show you how it's structured.  This is all one table.  Each row in the report is a single record.  The data in the montly columns would/could come from cMonth or Delivery Date.  The reason the sample only shows 7 months is because there is no data for the other five months.  I do expect however to fill up these months with new data as new opportunities arise.  So assume there will be twelve months of data in each given calendar year.  If there is an easy way of doing this or a similar variation, great, otherwise, if you know how, I'm prepared to try to create it...assuming you're able/willing to help.

            • 3. Re: Report creation - monthly sales forecast by sales stage

                   A key limitation to "cross tab" reports in FileMaker is that you will have a fixed number of columns to use for your monthly totals. You can dynamically control which month appears in each column, but the total number of columns must be fixed unless you get really creative with a data URL to your data pulled up in a web viewer and then you are pretty much restricted to a single page for your report.

                   I do suggest you add a new table for opportunities with one record for each opportunity. We don't strictly need it for this report, but such a table will make it easier to manage your opportunities and can serve as a value list source for your opportunities to streamline data entry and make entering an opportunity name more error resistant. In addition, fields like cProability would be defined in this table so that you have one such value in one such record instead of needing to copy this value across all records for the same opportunity.

                   The following info works just from your current table, but with just a few changes that I'll document at the end, it could be set up from a layout based on such an Opportunities table. I know from another thread that your table is not named "Sales" but I'm going to use that name here to save typing:

                   We'll need to make a duplicate Tutorial: What are Table Occurrences? of Sales to use in a relationship so that we can set up some filtered portals that compute the needed sub totals and grand totals required for your report.

                   In Manage | Database | relationships, make a new table occurrence of Sales by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box to be SalesSameOpp.

                   We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

                   Add it to your relationships like this:

                   Sales::Opportunity Name = SalesSameOpp::Opportunity Name.

                   Define a summary field, sTotalPrice in Sales that computes the total of Price.

                   Define a date field, gMonth1 and use field options to specify global storage and to give it this auto-enter calculation:

                   Self - Day ( self ) + 1

                   Clear the "do not replace existing value..." check box.

                   Now create a new layout that refers to Sales in "show records from". You can use the "blank" option to make this a blank layout as we will modify the layout without using the layout wizard.

                   While in layout mode, double click the "body" part label to open the Part Setup... dialog. Change the body layout part into a sub summary part "when sorted by Opportunity Name". This will condense all the records for a given Opportunity down into a single horizontal row when you sort your records by the Opportunity Name field. Select "print above" when asked. Add a second sub summary part "when sorted by cProbability". Select "print below" for it to put it beneath the other sub summary layout part. This will provide the row in your report where you will see sub totals for each group of records. To get the correct report format, it will be necessary to always sort your records first by cProbability, then by Opportunity Name.

                   Now you can add the Opportunity Name and cProbability fields to the upper sub summary part. Then add a one row portal to SalesSameOpp in this layout part. Use portal setup... to specify this portal filter expression:

                   SalesSameOpp::cMonth > Sales::gMonth1 And
                   SalesSameOpp::cMonth < Date ( Month ( Sales::gMonth1 ) + 12 ; 1 ; Year ( Sales::gMonth1 ) )
                   //the date function works even when the month value is greather than 12

                   Put the SalesSameOpp::sTotalPrice field as the only field inside this portal. You can then set the portal borders to 0 width to hide them from view so that this looks just like a norma field on your layout. This portal produces the "Grand Total" column of your report, assuming that you always want to see a 12 month based total for each opportunity.

                   Now select the portal and it's field and control drag (option drag on macs) to make a copy of it. double click the portal or select Portal Setup... from the format menu to re-open portal setup and change the portal filter to:

                   SalesSameOpp::cMonth = Sales::gMonth1

                   This portal then produces the left most column of monthly totals.

                   If you duplicate this portal again and use:

                   SalesSameOpp::cMonth = Date ( Month ( Sales::gMonth1 ) + 1 ; 1 ; Year ( Sales::gMonth1 ) )

                   You have the second column of sub totals.

                   Repeat these steps until you have all twelve, just changing the number added inside the Date function in each portal's portal filter.

                   This does not complete the project, but stop here and check to make sure all is working. You may want to just set up one or two portals to start. To get everything created to date to show up:

                   Select Show All Records or perform a find to pull up the desired set of records for a given date range (Such as all records for 2013).

                   Sort your records by Sales::cProbability and Sales::Opportunity Name to properly group your records.

                   Enter a date, such as Jan 1, 2013 into the gMonth1 field.

                   As a temporary measure, perform a script with this one script step: Refresh Window [Flusch Cached Join Results] to force the filtered portals to update after entering a date in the global gMonth1 field.

              • 4. Re: Report creation - monthly sales forecast by sales stage

                     To set up a table of Opportunities, you can import records from your existing table into this new table and if you set up a Unique values, validate always validation setting on your Opportunity Name field in the new table, you will get one record for each opportunity. I'd also add a serial number field to such a table and use it to link each record in the new table to the corresponding records in your existing table. This avoids problems that occur if you try to change the name--say to correct a mistake made when entering the name the first time, of an existing opportunity record.

                     Such a table can be used in Place of Sales in the relationship that I described using this relationship:

                     Opportunities::__pkOpportunityID = SalesSameOpp::_fkOpportunityID

                     Then your layout can be based on Opportunities and the upper sub summary layout part can simply be the body layout part instead of a sub summary part.