Sage 100

 View Only

  • 1.  Crystal Report - Customer sales by month

    Posted 07-15-2021 20:23
    I'm working on a custom report that displays the customer sales by month. When the customer runs this report she wants to it display the current sales based on the date she is running the report and 12 months back for each customer. The formula that I created but isn't working and displaying 0.00 dollar amounts for each month even if there is a monthly sales amount. Here is my formula for total sales by month:

    if {AR_InvoiceHistoryHeader.OrderDate} in DateSerial (year(CurrentDate),month(CurrentDate) -5,1 ) to
    DateSerial (year(CurrentDate),month(CurrentDate)-4 ,1 )-1 then {@InvTotal} else 0

    I'm sure it's something simple I'm missing. TIA.


    ------------------------------
    Shannon Stanley
    Sage 100 Consultant
    Ardent Consulting LLC
    704-839-6697
    ------------------------------


  • 2.  RE: Crystal Report - Customer sales by month

    Posted 07-16-2021 02:37
    Edited by Dan Burleson 07-16-2021 02:53
      |   view attached
    That formula works for me to get the 5th prior month. I would check the @InvTotal formula and that the customers have sales for the current year.

    I would also recommend using a Cross-Tab so that you don't have to have so many formulas.

    Plus with "Calculated Members" you can add a number of summary columns for things like "prior 3 month average", "12 month average", "This Year - Last  Year", and "This Year vs. Last Year %" as shown here.
    ​​


    ------------------------------
    Dan Burleson
    Software Consultant
    Connex Software
    Corvallis OR
    541-224-6642
    ------------------------------


  • 3.  RE: Crystal Report - Customer sales by month

    Posted 07-16-2021 08:54
    OrderDate or InvoiceDate?

    ------------------------------
    Robert White
    Munjal White Consulting Co.
    Toronto ON
    ------------------------------



  • 4.  RE: Crystal Report - Customer sales by month

    Posted 07-18-2021 22:36
    @Robert White Customer wants to pull by order date. @Dan Burleson If the customer wants to see data for each customer all on one line of the report. Where is the best place to add this cross tab at the line detail or group footer?

    ---------------------------------
    Shannon Stanley
    Sage 100 Consultant
    Ardent Consulting LLC
    704-839-6697
    ---------------------------------



    Original Message


  • 5.  RE: Crystal Report - Customer sales by month

    Posted 07-19-2021 01:45
    Edited by Dan Burleson 07-19-2021 01:49
      |   view attached
    You place the cross tab based on how often you want its basis repeated. For example, if you only wanted it once in the entire report then you would put it in a Report Header (where the Cross-Tab Wizard puts it) or Report Footer. If you wanted to repeat it for each division, then you could put it in a Group Header or Group Footer. Nothing has to go into the detailed section. Sometimes I will put a detailed report in the Detail Section after the cross tab summary (in a Report Header) or I will have two cross-tabs with different perspectives, one after the other in a RHa and RHb. If you don't use the Cross-Tab wizard that is part of the File, New command then you can add a cross-tab to an existing report with the Insert Cross-Tab. If you use the wizard then I prefer to create formulas for the rows and values. These have to be created after running the wizard and set using the Cross-Tab Expert command (available by right-clicking the upper or lower left of the cross-tab in the Design view.)

    See the attached prototype.

    ------------------------------
    Dan Burleson
    Software Consultant
    Connex Software
    Corvallis OR
    541-224-6642
    ------------------------------

    Attachment(s)

    rpt
    ShannonCT.rpt   50 KB 1 version
    Original Message


  • 6.  RE: Crystal Report - Customer sales by month

    Posted 07-19-2021 10:35
    Watch for one-step invoices (with no SO).  Those will be excluded from your results if you just query based on OrderDate.

    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    Toronto ON
    ------------------------------

    Original Message


Related Content