Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics GP (Archived)

Adding aging buckets to the RM Historical Aged Trial Balance SSRS report

Posted on by Microsoft Employee
I've been trying to add additional aging buckets to the RM historical aged trial balance report. I've taken the existing expression =SUM(iif (Fields!AGNGBUKT.Value=4, Fields!AGING_AMOUNT.Value,0)) and changed it to =SUM(iif (Fields!AGNGBUKT.Value=5, Fields!AGING_AMOUNT.Value,0)) for the new bucket but I keep getting error when I preview the report in BIDS stating: An error occurred during local report processing. An err has occurred during report processing. Query execution failed for dataset 'dsPrmCustomerName' Incorrect syntax near '

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Adding aging buckets to the RM Historical Aged Trial Balance SSRS report

    You're getting that error because the dsPrmCustomerName data source is based off of a report model that you likely don't have in Visual Studio. If you use the Report Designer (as Susan did), you should be able to add the columns and test it. To do that, in the SSRS site, go to the report and hover over the report until the action menu appears. Click the down arrow and then choose Edit in Report Builder.

  • sueconrod Profile Picture
    sueconrod 335 on at
    RE: Adding aging buckets to the RM Historical Aged Trial Balance SSRS report

    Went into the Historical Aged Trail Balance Report and did the same thing, inserted in a column, then went and copied each field individually, from Aging bucket 4 then had to open the fx (expression) and substitute in 5 instead of 4 for each field that I had copied over, then had to enter in a date that I knew I had data in there for and ran the report and it brought back the data I needed.  So, ran the report for 03/01/2014 to 03/31/2014 and all the data came back as expected.  

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Adding aging buckets to the RM Historical Aged Trial Balance SSRS report

    You're not looking at the historical aged trial balance report, which is the one giving me the problem.

  • Suggested answer
    sueconrod Profile Picture
    sueconrod 335 on at
    RE: Adding aging buckets to the RM Historical Aged Trial Balance SSRS report

    Okay, just went into my SSRS report builder Version 3.0)

    1. did an insert of a column to my Aged Trial Balance report between the AGINGBK4 and the Balance by going to the Grey area (column header) on the balance and doing an insert column to the left  

    2.  Then did a total copy of the Column with the Agngbukt4 and placed into this new column.

    3. then went in and use the notepad that comes up (when you hover over the field)   which gives you a list of all the fields and went way down to the bottom and replaced each place where it had AGINGBK4 with AGINGBK5 saved the report and ran it and everything was fine.  

    When I ran the report I went in and chose all of my customers A-Z and ran it and everything is coming up correctly.  Are you setup in security to have access to the report?

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Adding aging buckets to the RM Historical Aged Trial Balance SSRS report

    Yes.  The report never had all the buckets so I wanted to align the report with the setup in RM.

  • Suggested answer
    sueconrod Profile Picture
    sueconrod 335 on at
    RE: Adding aging buckets to the RM Historical Aged Trial Balance SSRS report

    Did you go into Great Plains into Tools>> Setup>>Receivables and actually change the Ageing buckets?  And then run the reconcile feature so that it would re-align what was in the buckets?  

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans