Skip to main content

Notifications

Announcements

Check out the New Agent Creator Community
Announcing new navigation menus and the Engage with the Community forum!
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics SL (Archived) / SL View Budget vs Actual
Microsoft Dynamics SL (Archived)

SL View Budget vs Actual

(0) ShareShare
ReportReport
Posted on by Community Member

Thanks in advance for any help.  We are on SL 2015.  I have been tasked with creating a SSRS that will display Budget Vs Actual for a job.  I have found the tables, that hold the separate info, but gets convoluted when i join them.  I was told by our accounting dept that there maybe a report out there that already does what I'm looking for.  if that is true then there should be a view.  If anyone could point me to this view I would greatly appreciate the help.

Thanks,

*This post is locked for comments

  • Community Member Profile Picture
    Community Member on at
    RE: SL View Budget vs Actual

    I had to step away from this for a bit, but I'm hopeful someone can assist with this again.  The previous query didn't quite get me what I was looking for.

    This query works and gets me everything, except for costs in the gltran table

    SELECT DISTINCT RTRIM(PJPENT.pjt_entity) AS exp, PJPENT.pjt_entity_desc, RTRIM(PJPENT.project) AS project, SUM(PJPTDSUM.eac_amount) AS Budget

    FROM            PJPTDSUM INNER JOIN

                            PJPENT ON PJPTDSUM.pjt_entity = PJPENT.pjt_entity AND PJPTDSUM.project = PJPENT.project

    WHERE        (PJPTDSUM.project BETWEEN 415270 AND 418780) AND (PJPTDSUM.acct <> 'REV') AND (PJPTDSUM.acct <> 'Contract REC') AND (PJPTDSUM.acct <> 'ADMIN JOB COST')

    GROUP BY PJPENT.project, PJPENT.pjt_entity, PJPENT.pjt_entity_desc

    ORDER BY project, exp

    As soon as I add something like this, my record count goes way up.  I'm trying to add the sum of  amount to my previous query to get the total spent.  Any help would be appreciated.

    SELECT DISTINCT RTRIM(PJPENT.pjt_entity) AS exp, PJPENT.pjt_entity_desc, RTRIM(PJPENT.project) AS project, SUM(PJPTDSUM.eac_amount) AS Budget, SUM(PJTran.amount) AS Cost

    FROM            PJPTDSUM INNER JOIN

                            PJPENT ON PJPTDSUM.pjt_entity = PJPENT.pjt_entity AND PJPTDSUM.project = PJPENT.project INNER JOIN

                            PJTran ON PJPTDSUM.pjt_entity = PJTran.pjt_entity AND PJPTDSUM.project = PJTran.project

    WHERE        (PJPTDSUM.project BETWEEN 415270 AND 418780) AND (PJPTDSUM.acct <> 'REV') AND (PJPTDSUM.acct <> 'Contract REC') AND (PJPTDSUM.acct <> 'ADMIN JOB COST')

    GROUP BY PJPENT.project, PJPENT.pjt_entity, PJPENT.pjt_entity_desc, PJTran.amount

    ORDER BY project, exp

    Thanks

  • Community Member Profile Picture
    Community Member on at
    RE: SL View Budget vs Actual

    Thank you so much for the assistance I really appreciate it.

  • Verified answer
    Butch Adams Profile Picture
    Butch Adams 4,763 on at
    RE: SL View Budget vs Actual

    Ahh ... you want project budgeting.

    It's a little more difficult to give you something straight forward because everybody sets up projects, accts, subaccts, acct categories, tasks differently. Whether your using the Contract Management or Project Budgeting modules makes a difference too.

    Those QQ_ views are used by Quick Query. (If you didn't know) I'm not a fan of joining those up with anything, but that's just me - I don't have a reason from any wisdom for feeling that way.

    Check out PJPTDSUM for your budget info.

    I didn't analyse your queries for the problem, but something like this will get you your budget to the task level:

         SELECT

          PJPROJ.CpnyId,

          PJPROJ.Project,

      PJPROJ.project_desc,

          PJPTDSUM.acct,

          PJPTDSUM.pjt_entity,

      PJPTDSUM.total_budget_amount

         FROM

           PJPTDSUM

         INNER JOIN PJProj

         ON

           PJPTDSUM.Project = PJProj.project

    You should be able to join this up with PJTran and get a good BVA

  • Community Member Profile Picture
    Community Member on at
    RE: SL View Budget vs Actual

    Here is the query i'm using that is producing bad results

    SELECT        QQ_pjtran.project, QQ_pjtran.[project description], QQ_pjtran.task, QQ_pjtran.subtask, QQ_pjtran.[account

                       category], QQ_pjtran.[transaction date], QQ_pjtran.amount, QQ_pjtran.units, QQ_pjtran.[transaction comment],

                       QQ_pjtran.[G/L account], QQ_pjtran.subaccount, QQ_pjtran.[fiscal period], QQ_pjtran.[vendor number], CASE WHEN [G/L account] = '6110' THEN units ELSE NULL END AS MH, CASE WHEN [G/L account] NOT IN (6110, 6120,

                            6200, 6210, 6220, 6230) THEN units ELSE NULL END AS Sum_Units, PJPENT.pjt_entity_desc

    FROM            QQ_pjtran INNER JOIN

                            PJPENT ON QQ_pjtran.project = PJPENT.project AND QQ_pjtran.task = PJPENT.pjt_entity INNER JOIN

                            PJBHSSUM ON QQ_pjtran.project = PJBHSSUM.project AND PJBHSSUM.pjt_entity = QQ_pjtran.task

    WHERE        (QQ_pjtran.project = '415100') AND (QQ_pjtran.[G/L account] BETWEEN 6000 AND 9999) AND (QQ_pjtran.[fiscal period] <= '201801')

  • Community Member Profile Picture
    Community Member on at
    RE: SL View Budget vs Actual

    Thanks for the response.  I'm looking for a little more detail.  Detail like whats in these tables/views

    QQ_pjtran, PJPENT. PJBHSSUM  I just can find a way to join these three together correctly

  • Suggested answer
    Butch Adams Profile Picture
    Butch Adams 4,763 on at
    RE: SL View Budget vs Actual

    Check out the AcctHist table. It has the "Balance Type" column that tells you if it is a (B)udget line or an (A)ctual line. Match up the lines by CpnyId, Acct, Sub and FiscYr. There are PTD and YTD columns for each period.

    Generally, BVA reporting is done using Management Reporter or some other financial package that supports SL.

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

Check out the New Agent Creator Community
Announcing new navigation menus and the Engage with the Community forum!

Quick Links

Jainam Kothari – Community Spotlight

We are honored to recognize Jainam Kothari as our June 2025 Community…

Congratulations to the May Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard >

Last month Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans