Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics SL forum
Answered

SL View Budget vs Actual

Posted on by Microsoft Employee

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,

  • Community Member Profile Picture
    Community Member Microsoft Employee 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 Microsoft Employee 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,757 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 Microsoft Employee 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 Microsoft Employee 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,757 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.

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,989 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,588 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans