Custom Crystal Report for Sales 1

[Farru21]

Hello Everyone,

I am working on writing a custom report for GP 8.0. This report needs to show Sales totals by month with YTD totals.

Here is the generic layout:

CUSTNMBR, ITEMNMBR, ITEMDESC, MTD_SALES, PREVIOUS_MTD_SALES, YTD_SALES, PREVIOUS_YTD_SALES

The report will be run filtered by SLPRSNID and requested Month to report for.

I am able to link up tables to get the SLPRNSID, CUSTNMBR, ITEMNBR, ITEMDESC related information but need to determine where can I pull the sales totals by item for MTD, YTD based totals...

Just need a kick start here so any help would be appreciated.

Regards,
Frank

 

[Dukester0122]

1) Create a formula for Amount and Quantity that would show negative numbers for Returns(if included)
AMOUNT:
if {SOP30200.SOPTYPE} = 4 then - {SOP30300.XTNDPRCE} else {SOP30300.XTNDPRCE}

QUANTITY:
if {SOP30200.SOPTYPE} = 4 then - {SOP30300.QUANTITY} else {SOP30300.QUANTITY}

2) Create a formula for your Monthly and YTD invoice and qty
For Monthly Invoice Amount:
if Month({SOP30200.DOCDATE}) = Month({?PickDate}) and {SOP30200.SOPTYPE} = 3 then {@Amount} else 0

For Monthly Invoice Quantity:
if MOnth({SOP30200.DOCDATE}) = Month({?PickDate}) and {SOP30200.DOCDATE} = 3 then {@Qty} else 0

3) Create a parameter for date - {?PickDate}

Hope this helps you get started.

 

[Farru21]

Thanks Dukester for the tip, it has definetly gotten me started on the right path,

One question though, the report needs to show the totals based on periods, which table could I link up to get that, I am hoping that there is a GL table out there with links possible to the SOP tables being used in the report.

Thanks Again,
Frank