Open Forum

  • 1.  Sales reports by client

    Posted Oct 21, 2021 10:16 PM
    @Belinda Allen

    Do you have any recommendations for a report that pulls in all sales by client then allows for drilldown to single client and/or specific date ranges?   ​Our data is not in the SOP tables.

    #BIandReporting

    any help would be very much appreciated! ​

    Shelagh Esteb

    ------------------------------
    Shelagh Esteb
    NA
    Stafford TX
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Sales reports by client

    TOP CONTRIBUTOR
    Posted Oct 22, 2021 07:47 AM
    Hi @Shelagh Esteb ,

    If your data isn't in the SOP tables, I'm assuming it is in the Receivables table then?  If so, I'd recommend considering the following depending how you need to see the data.

    Option 1:
    If you want something standard out of the box, there is a Receivables Transactions report inside of GP (Sales>>Reports>>Analysis>>Receivables Transactions)​.  You can restrict by date range, customer ID, document type (i.e. exclude payments, returns, credits, etc.).  It provides a summary by customer at the bottom of the customer and a detailed list of documents that meet your criteria.  It's not perfect and may not give you exactly what you want.  I also don't love the layout myself.  You can modify the layout, but if you're going to those lengths, you may want to consider option 2 below anyway.

    Option 2
    You can use the Receivables Transactions SQL view and build a report that looks exactly how you want it to look.  This would let you write a query where you include/exclude payments, where you show returns and credits as negatives if you need to reduce the net sales by those amounts, present summary lines initially (so the report is more condensed) and then drill into the details to get a list of documents by customer.

    If you go Option 2, you can choose to the medium you consume that report in once you have the query written the way you want.  Refreshable excel, SSRS, and Power BI are great options for that depending who all needs to see the report.  SmartList can also be used, but SmartList is generally used to show summary or detail but not necessarily both.


    Does that make sense?  Let me know what questions you have or if you run into any issues as you start exploring options.


    Best,


    Samantha

    ------------------------------
    Samantha Higdon ,CPA,CGMA
    Consultant
    Lagom, LLC
    Carmel IN
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Sales reports by client

    Posted Oct 23, 2021 01:21 AM
    Thanks so much @Samantha Higdon.  I already created a smartlist for the user based on that view but they didn't like it.  I exported to excel and showed her how to  exclude the payments and convert the credits and pivot the data.   Its not great but the data is there.   I am not familiar with the excel reports but I can sure learn.  I wonder if SSRS would be better.. not sure which way to go.  Any feedback is welcome! ​

    ------------------------------
    Shelagh Esteb
    NA
    Stafford TX
    ------------------------------

    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role