Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Suggested answer

Need to filter Report data on basis of multiple ledger dimensions

(0) ShareShare
ReportReport
Posted on by

I need to develop a report where I need data with multiple dimensions.
 
 
Now if I try to get all data during the given period and then check the dimensions data to verify required dimension it will compromise performance. 
 
Please help how I can get data with multiple dimensions quickly and processed quickly.
 
The required dimensions are 
 Project
 Project Competency
 Business Unit
 Export Unit
Let me explain my problem in detail,with this query.This query is working  fine with single dimension(Like given in the result below).
 
select generalJournalEntry.AccountingDate, DimensionAttribute.Name,  DimensionAttributeLevelValue.DisplayValue , GENERALJOURNALENTRY.LEDGER,generalJournalAccountEntry.RECID, generalJournalAccountEntry.MAINACCOUNT, GeneralJournalEntry.SUBLEDGERVOUCHER, generalJournalAccountEntry.POSTINGTYPE, generalJournalEntry.ACCOUNTINGDATE,generalJournalAccountEntry.ReportingCurrencyAmount 
FROM generalJournalAccountEntry            
inner join  generalJournalEntry on generalJournalEntry.RecId = generalJournalAccountEntry.GeneralJournalEntry
inner join  dimensionAttributeValueCombination on DimensionAttributeValueCombination.RecId =  generalJournalAccountEntry.LedgerDimension            
inner join dimensionAttributeValueGroupCombination on DimensionAttributeValueGroupCombination.DimensionAttributeValueCombination = DimensionAttributeValueCombination.RecId
            inner join dimensionAttributeValueGroup on DimensionAttributeValueGroup.recid = DimensionAttributeValueGroupCombination.DimensionAttributeValueGroup
            inner join dimensionAttributeLevelValue  on DimensionAttributeLevelValue.DimensionAttributeValueGroup = DimensionAttributeValueGroup.RecId
             inner join dimensionAttributeValue on DimensionAttributeLevelValue.DimensionAttributeValue = DimensionAttributeValue.RecId
            inner join dimensionAttribute on DimensionAttribute.recid = DimensionAttributeValue.DimensionAttribute
             where    GENERALJOURNALENTRY.LEDGER=5637145326 and   DimensionAttribute.Name in ('Project')
and GENERALJOURNALENTRY.ACCOUNTINGDATE between '2022/01/01' and '2022/01/01'
 order by GeneralJournalEntry.SUBLEDGERVOUCHER
 
Result (this is the result with one dimention which is project, this data is correct).But when we are working with multiple dimensions like in the second query it returns separte rec for each dimension and  we need to process data horizantally,which requires a lot of time to process.
 
AccountingDate    Name    DisplayValue    LEDGER    RECID    MAINACCOUNT    SUBLEDGERVOUCHER    POSTINGTYPE    ACCOUNTINGDATE    ReportingCurrencyAmount
2022-01-01 00:00:00.000    Project    501-001    5637145326    5642727628    5637144823    FTV-000558    126    2022-01-01 00:00:00.000    558.500000
2022-01-01 00:00:00.000    Project    115-017    5637145326    5642741908    5637144714    FTV-000559    126    2022-01-01 00:00:00.000    9437.000000
2022-01-01 00:00:00.000    Project    267-002    5637145326    5642889170    5637144823    FTV-000565    126    2022-01-01 00:00:00.000    385.000000
 
query2
  select generalJournalEntry.AccountingDate, DimensionAttribute.Name,  DimensionAttributeLevelValue.DisplayValue , GENERALJOURNALENTRY.LEDGER,generalJournalAccountEntry.RECID, generalJournalAccountEntry.MAINACCOUNT, GeneralJournalEntry.SUBLEDGERVOUCHER, generalJournalAccountEntry.POSTINGTYPE, generalJournalEntry.ACCOUNTINGDATE,generalJournalAccountEntry.ReportingCurrencyAmount FROM generalJournalAccountEntry            inner join  generalJournalEntry on generalJournalEntry.RecId = generalJournalAccountEntry.GeneralJournalEntry
  inner join  dimensionAttributeValueCombination on DimensionAttributeValueCombination.RecId =  generalJournalAccountEntry.LedgerDimension            
  inner join dimensionAttributeValueGroupCombination on DimensionAttributeValueGroupCombination.DimensionAttributeValueCombination = DimensionAttributeValueCombination.RecId            
  inner join dimensionAttributeValueGroup on DimensionAttributeValueGroup.recid = DimensionAttributeValueGroupCombination.DimensionAttributeValueGroup            
  inner join dimensionAttributeLevelValue  on DimensionAttributeLevelValue.DimensionAttributeValueGroup = DimensionAttributeValueGroup.RecId             
  inner join dimensionAttributeValue on DimensionAttributeLevelValue.DimensionAttributeValue = DimensionAttributeValue.RecId            
  inner join dimensionAttribute on DimensionAttribute.recid = DimensionAttributeValue.DimensionAttribute             
  where    GENERALJOURNALENTRY.LEDGER=5637145326 and   DimensionAttribute.Name in ('Project','ProjectCompetency')
  and GENERALJOURNALENTRY.ACCOUNTINGDATE between '2022/01/01' and '2022/01/01' 
  order by GeneralJournalEntry.SUBLEDGERVOUCHER
Result:
Result (this is the result with two dimention which is project and ProjectCompetency, this data is not correct, and data going to duplicate here)
AccountingDate    Name    DisplayValue                LEDGER    RECID    MAINACCOUNT    SUBLEDGERVOUCHER    POSTINGTYPE    ACCOUNTINGDATE    ReportingCurrencyAmount
2022-01-01 00:00:00.000    Project                    501-001   5637145326    5642727628    5637144823    FTV-000558    126    2022-01-01 00:00:00.000    558.500000
2022-01-01 00:00:00.000    ProjectCompetency    C12      5637145326    5642727628    5637144823    FTV-000558    126    2022-01-01 00:00:00.000    558.500000
2022-01-01 00:00:00.000    Project                    115-017   5637145326    5642741908    5637144714    FTV-000559    126    2022-01-01 00:00:00.000    9437.000000
2022-01-01 00:00:00.000    ProjectCompetency    C11      5637145326    5642741908    5637144714    FTV-000559    126    2022-01-01 00:00:00.000    9437.000000
2022-01-01 00:00:00.000    ProjectCompetency    C16      5637145326    5642889170    5637144823    FTV-000565    126    2022-01-01 00:00:00.000    385.000000
2022-01-01 00:00:00.000    Project                    267-002   5637145326    5642889170    5637144823    FTV-000565    126    2022-01-01 00:00:00.000    385.000000a
 
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 230,846 Most Valuable Professional on at
    Need to filter Report data on basis of multiple ledger dimensions
    Moved from D365 general forum to the Finance forum.
     
    I'm not sure what your problem is. If you dislike the fact that you get a separate line for each dimension value, I see two options:
    1. You use a matrix control in your report (SSRS or Power BI) to represent dimensions as columns).
    2. You'll use a different query (e.g. with a computed column for each dimension).

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

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,971 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,846 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans