Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Business Central forum / How do I get monthly s...
Business Central forum
Answered

How do I get monthly sales for a fiscal year broken out by customer?

(0) ShareShare
ReportReport
Posted on by Tricon916 18

This seems like a bog standard report and I can't believe its not available...or am I missing something? I'm looking for essentially the Customer Sales Statistics report, but I need 12 months broken out, not 4.

I've tried doing a sales analysis report but can't seem to get the formula for the dates to work and I can't choose a date range when I run the matrix. I've tried customizing the reports but I'm still learning AL and am struggling a little there. Am I missing an easy way to get this standard report? I can setup an Excel layout, but I don't see how to get the right data for it, I think I would need the sales ledger, but I can't export that to build a template.

I'm obviously just in the dark on the right way forward. Appreciate any help.

Categories:
Other
Reports and Business Intelligence
Financial Management
  • Suggested answer
    JAngle Profile Picture
    JAngle 33,155 on at
    RE: How do I get monthly sales for a fiscal year broken out by customer?

    I would add a calendar table like this: www.sqlbi.com/.../

    The item table too because filters should derive from there and not the ILE’s. You may have dimension filter requirements too so add those if necessary.

    I believe the report you’re trying to replicate uses the customer ledger entries. If you want an item sales focused spin on things you’re going in the right direction. Value entries hold the value invoices though. ILE’s do have a flowfield to the info and of course display better detail.

  • Tricon916 Profile Picture
    Tricon916 18 on at
    RE: How do I get monthly sales for a fiscal year broken out by customer?

    This is very informative, appreciate it. Im working through the AL just to learn, but I keep running into the suggestion to get PowerBI up and running so Im running through the trial now.

    Do you happen to know offhand what tables I'd need to grab to recreate the Customer Sales Statistics report? I started with the Customer table and Item ledger but I think Im missing some needed data.

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 32,744 Moderator on at
    RE: How do I get monthly sales for a fiscal year broken out by customer?

    Totally agree with Josh, go Power BI if you have the chance to do so.

  • Suggested answer
    JAngle Profile Picture
    JAngle 33,155 on at
    RE: How do I get monthly sales for a fiscal year broken out by customer?

    I’d produce a new report. Power BI would be the best place to do it. If you don’t have access then maybe AL is ok. A general approach to try with AL:

    Main dataitem to be the customer

    Var

    DateHdr: array[12] of Text;

    Amt: array[12] of decimal;

    Dataset wise have a column for all 12 months:

    column(DateHdr1; DateHdr[1]) { }

                   column(DateHdr2; DateHdr[2]) { }

                   column(DateHdr3; DateHdr[3]) { }

                   column(DateHdr4; DateHdr[4]) { }

                   column(DateHdr5; DateHdr[5]) { }

                   column(DateHdr6; DateHdr[6]) { }

                   column(DateHdr7; DateHdr[7]) { }

                   column(DateHdr8; DateHdr[8]) { }

                   column(DateHdr9; DateHdr[9]) { }

                   column(DateHdr10; DateHdr[10]) { }

                   column(DateHdr11; DateHdr[11]) { }

                   column(DateHdr12; DateHdr[12]) { }

    column(Amt1; Amt[1]) { }

                   column(Amt2; Amt[2]) { }

                   column(Amt3; Amt[3]) { }

                   column(Amt4; Amt[4]) { }

                   column(Amt5; Amt[5]) { }

                   column(Amt6; Amt[6]) { }

                   column(Amt7; Amt[7]) { }

                   column(Amt8; Amt[8]) { }

                   column(Amt9; Amt[9]) { }

                   column(Amt10; Amt[10]) { }

                   column(Amt11; Amt[11]) { }

                   column(Amt12; Amt[12]) { }

    Trigger wise:

    trigger OnPreDataItem()

               var

                   CurrDate: Date;

                   i: Integer;

                   Month: Integer;

                   Year: Integer;

               begin

                   CurrDate := GetRangeMin("Start Date");

    for i := 1 to ArrayLen(DateHdr) do begin

                       MnthArr[i] := Date2DMY(CurrDate, 2);

                       YearArr[i] := Date2DMY(CurrDate, 3);

                       DateHdr[i] := StrSubstNo('%1/%2', Date2DMY(CurrDate, 2), Date2DMY(CurrDate, 3));

                       CurrDate := CalcDate('<1M>', CurrDate);

                   end;

               end;

    OnAfterGetRecord have a procedure called to calculate the values.

    for i := 1 to ArrayLen(DateHdr) do begin

               CalcPeriodDate(i, StartDate, EndDate);

    Amt[i] := whatever you need it to be

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,558 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,647 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans