web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro
Introducing our New Video Series: Community Spotlight
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Financail...GP Profit ...
Microsoft Dynamics GP (Archived)

Financail...GP Profit and Loss Reporting

(0) ShareShare
ReportReport
Posted on by Camerica 90

I am looking to create SQL Server sql view to deliver Profit and Loss Reporting to match my requirents to consolidate several Companies into one Financial statement

instead of using Management Reporter or inbuilt reports

does anyone have and sql code that addresses this request?

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at

    I have used this SQL Script for GL Trial Balance previously to achieve the same requirements. It does need modifications in order to have a dynamic criteria for retrieving a specific year, quarter, month ...etc

  • Camerica Profile Picture
    Camerica 90 on at

    Many thanks for your fast response

    Yes this is very helpful to me and I will base my approach around this script

    Great!

  • Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at

    Most welcome

    Please feel free to close the case if it fulfills your requirements.

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    Hi Camerica,

    Additional to saadi's detailed script, please find the below simple script that may helps you.

    select

    AccountSummary.[Account Number],

    AccountSummary.[Account Description],

    AccountSummary.[Account Category Number],

    AccountSummary.[Account Type],

    AccountSummary.[Active],

    AccountSummary.[Year],

    AccountSummary.[Period ID],

    AccountSummary.[Credit Amount],

    AccountSummary.[Debit Amount],

    AccountSummary.[Period Balance],

    case when AccountSummary.[Period Balance] < = 0 then 'Loss' else 'Profit' end 'Status'

    from AccountSummary

  • Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at

    When it comes to building a dynamic report, I personally do not prefer considering any of the summary tables, which have fixed calculations that are updated after every transaction or through the common utility "Module Reconciliation"

    Therefore, it would be a better approach to build your report to read all the transactions and do the calculations.

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

News and Announcements

🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro
Introducing our New Video Series: Community Spotlight

Quick Links

Season of Sharing Community Challenge Launch!

Jump in, show your community spirit, and win prizes!

Women in Power Builds Momentum

Expanding mentorship, skilling, and AI innovation

Congratulations to the May Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
Dan Liebl Profile Picture

Dan Liebl 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans