It’s the strangest thing. I’ve written hundreds of customized reports, but it seems like the most interesting ones in terms of challenges always revolve around the project module and Inventory Value reports. What I’ve noticed is several suttle changes to the data model in Dynamics 365, which affect general ledger reports, inventory value reports, and project module reports. If you learn some of these new techniques, I’m convinced that reporting will actually be easier in Dynamics 365 than it was in 2012. Let’s start by covering the project module.

So, my client presented me with a special challenge. They need to see the project module transactions posted by dimension. They need the data granularized where it shows individual project transactions, but here is the twist. They need to see the dimensions that were posted at the GL report.

First, notice that with DimensionAttributeValueCombination

First, Open up SQL Server and run a query against DimensionAttributeValueCombination (select * from dimensionattributevaluecombination). Notice how it gives us a really nice advantage from reporting because we can pull values from it. In fact, we can pull any dimension values this way. It gives us a powerful way when importing Dynamics 365 data into Excel for reporting.

One thing, you will note that there are other dimension values also that are generated based upon account structure. These are dropped and recreated based upon changes to the account structure and are currently inaccessible via normal x++ methods (there are several ways around this). They will start with “SystemGeneratedAttribute”. You see them in some of the default Power BI dashboards for optimized speed by Microsoft. This could change in the future so please remember the date of this post.

Go to DimensionAttributeValueCombination and run a query against projtransposting

Select ljt.journalnum, ljt.voucher, dvc.PROJECTSVALUE, dvc.*

from ledgerjournaltrans ljt

inner
join DIMENSIONATTRIBUTEVALUECOMBINATION DVC

on ljt.LEDGERDIMENSION = dvc.recid

where ljt.voucher in
(select voucher from projtransposting)

and DVC.projectvalue =
”

Third, understanding the query trick:

There is a little known trick towards getting dimensions out of the subledgers if an organization doesn’t have advanced rules configured which allocate percentages. Advanced rules will require a variant of the approach. Basically, if you run a query for subledgerdata with project module granularity matched to dimensions, you won’t get anything in most cases. However, we have another way. On the same voucher, there are often offset transactions which will actually have the dimensions. Thus, we will exploit this trick in our next post to pull the dimensions at the project granularity level. Tied together with a few tricks that you will see, we can successfully use the offset transactions to match GL data accurately – thus getting the best of both worlds with detailed data that ties to the GL.

Okay, this is one of those series that is advanced & fun enough to require multiple postings. But what we’ve done is take a very advanced but common request to see project module transactions in our Excel sheet with full visibility into dimensions at a level that can be linked backed to the General ledger. This gives our project module accountants, financial analysts, and practitioners a powerful way to speed up data validation. In part 2, we’ll explore the implications of the trick that I’ve given by translating these assumptions in x++ and going to town.