14 Replies Latest reply on Mar 4, 2014 6:15 AM by MallieWilliams

    Generating a 30 60 90 day monthly report

    MallieWilliams

      Hello everyone,

       

      I’m trying to generate a report that shows a 30 day break down of cost and number of sales, a 90 day break down of cost and sales and a 90 day. Should I create a summary report? I want the user to be able to click a button and have the reports created on click. This seems to require a script. What do you think?

        • 1. Re: Generating a 30 60 90 day monthly report
          davidsan

          Hi!

           

          This is a Crosstab report.

           

          I think there are primarily two techniques to do this report. One is using sub summary fields, as you describe, and the other is using Virtual Lists. Sub sumary is slower but it is easier to do, virtual list is faster but requires scripting.

           

          You only have to do a calculation field that dynamically calculates if it is 30, 60 or 90 days. Then create sub sumary fields that take in consideration that calculation field. Remember to sort by the calculation field before entering preview mode.

           

          FileMaker Training series 12 Lesson 7.4 includes a step by step sample.

          • 2. Re: Generating a 30 60 90 day monthly report
            MallieWilliams

            Okay..Thanks David..

            • 3. Re: Generating a 30 60 90 day monthly report
              erolst

              Are you talking about three different reports, one for each range, or one report that shows all three ranges?

              • 4. Re: Generating a 30 60 90 day monthly report
                MallieWilliams

                A final report that shows all three.. I've attached a screenshot of what it should look like.

                 

                I made a list of Entities and Attributes.. My Entities are 30day, 60day, 90day , and Backlog

                my attributes are ship date, count of sales order, sum of cost price, order status and shipping months

                • 5. Re: Generating a 30 60 90 day monthly report
                  MallieWilliams

                  1 report that shows all 3 ranges.

                  • 6. Re: Generating a 30 60 90 day monthly report
                    DavidJondreau

                    I think a regular sub-summary report will be fine, a cross-tab probably isn't necessary.

                     

                    You need to create a calculation field that takes the date field and converts it to 30, 60, or 90. You summary part should be based on that new calculation field.

                    • 7. Re: Generating a 30 60 90 day monthly report
                      MallieWilliams

                      So I can create a summary chart right? That's like a sub summary report..I will try to create a field that converts the days. Thanks David!!

                      • 8. Re: Generating a 30 60 90 day monthly report
                        MallieWilliams

                        Okay..this is the way I created the calculation and it's not working..

                         

                        If (salesorder::Expected ship date  ≥  30; "salesorder::Expected ship date 30day";If (salesorder::Expected ship date  ≥  60; "salesorder::Expected ship date 60day";If (salesorder::Expected ship date  ≥  90; "salesorder::Expected ship date 90day")))

                        • 9. Re: Generating a 30 60 90 day monthly report
                          Mike_Mitchell

                          Mallie -

                           

                          There are at least two problems with your calculation:

                           

                          1) You've enclosed the results in quotes. I'm assuming you have some fields named "salesorder::Expected ship date 30day" and so forth. Presumably, these fields contain some text string you want to use for sorting? If you enclose the field name in quotes, FileMaker will not parse out the value in the field. Instead, it will parse out the literal text string, "salesorder::Expected ship date 30day", which is probably not what you want. Get rid of the quotes.

                           

                          2) The larger problem, and probably the one that's stopping your forward progress, is the order of your operations. The first leg of your calculation is:

                           

                               If (salesorder::Expected ship date  ≥  30 ...

                           

                          If the expected date is more than 30 days out, then this first leg will be true. That will be the case even if the date is more than 60, or 90 days out (because those are greater than 30 days, too). So you've short-circuited the logic of the calculation.

                           

                          Some further refinements:

                           

                          3) If your fields are really just constants, unless you need them someplace else (and even if you do, probably), you don't need to define them as fields. Just define them to say what you want inside your calculation.

                           

                          4) It's far more efficient for the calculation engine to use the Case ( ) function than a series of nested If ( ) functions.

                           

                          Taking all that into consideration, your final calculation might look something like this:

                           

                               Case ( salesorder::Expected ship date ≥ 90 ; "90 days" ; salesorder::Expected ship date ≥ 60 ; "60 days" ; salesorder::Expected ship date ≥ 30 ; "30 days" )

                           

                          That should be closer to what you're looking for.

                           

                          Mike

                          • 10. Re: Generating a 30 60 90 day monthly report
                            erolst

                            Mike_Mitchell wrote:

                            4) It's far more efficient for the calculation engine to use the Case ( ) function than a series of nested If ( ) functions.

                            I don't think the calculation engine breaks a sweat here – but using Case() is far more efficient to write, read and debug from the programmer's perspective. (First make the fr…n' thing work, then worry about optimizations … )

                             

                            One way to let the calc engine work more efficiently (and make the calculation easier on the eyes) would be to use Let() and a bit of formatting:

                             

                            Let (

                              ~s = salesorder::Expected ship date ;

                              Case (

                                ~s ≥ 90 ; "90 days" ;

                                ~s ≥ 60 ; "60 days" ;
                                ~s ≥ 30 ; "30 days"

                              )

                            )

                             

                            OTOH, I assume "Expected ship date" is a date, so this calculation would compare a date to a number; not too helpful in this case.

                            Don't we need the difference between the ship date and today's date? Then the calculation would look along these lines:


                            Let ( [

                              ~sd = salesorder::Expected ship date ;

                              ~td = Get ( CurrentDate ) ;

                              ~diff = ~sd - ~td

                              ] ;

                              Case (

                                ~diff ≥ 90 ; "90 days" ;

                                ~diff ≥ 60 ; "60 days" ;

                                ~diff ≥ 30 ; "30 days"

                              )

                            )

                            • 11. Re: Generating a 30 60 90 day monthly report
                              Mike_Mitchell

                              "OTOH, I assume "Expected ship date" is a date, so this calculation would compare a date to a number..."

                               

                              Missed that part. Good catch.

                              • 12. Re: Generating a 30 60 90 day monthly report
                                MallieWilliams

                                Thanks Mike,

                                 

                                I see what you mean. I read an article about the IF vs. Case statement. I will try to use the case statment and go from there. Also I will fix the issue with the date espicially since some months go past the 30..lol

                                • 13. Re: Generating a 30 60 90 day monthly report
                                  MallieWilliams

                                  Expected Shipped Date is a date.

                                  • 14. Re: Generating a 30 60 90 day monthly report
                                    MallieWilliams

                                    Thanks Erolst,

                                     

                                    What I'm working on is based off of this report that was given to me on a spread sheet. So I'm trying to convert this data to filemaker pro just to get some practice.This is a screenshot of the report. That I posted before... What I'm trying to do is sort shipdate as 30 day 60 day and 90 day like the top of the screenshot I uploaded. Then create a report showing 30-60-90 monthy showing the filed (Stages) stage 4 only like on the bottom of the screenshot.