Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Data with Multiple Fin...
Finance | Project Operations, Human Resources, ...
Unanswered

Data with Multiple Financial Dimension

(1) ShareShare
ReportReport
Posted on by nbhatti2001 220
Hi, 
 
I need to develop a report where I need data with multiple dimension. I know I can put check on LedgerDimension and can check if it haw my required dimension then insert into temp table, otherwise leave that record. In this way report generating time will be comporise and in case of large data it will take much time. I have to work with 4 dimensions. I am using the following query to get the data. But when I try to get the the data with multiple dimension it create the duplicate record against each transaction because of multiple dimension. 
 
Now if I try to get all data during the given period and then check the dimensions data to verify requied dimension if will compromize performance. 
 
Please help how I can get data with multiple dimension quickly and processed quickly.
 
The required dimensions are 
  1.  Project
  2.  Project Competency
  3.  Business Unit
  4.  Export Unit
I need data like this
((finDim== 'Project Competency' && dimValue=='C11') && (finDim== 'Business Unit' && dimValue=='03') && (finDim== ' Export Unit' && dimValue=='01'))
 
If I put these dimension in query it duplicate the record, so how to avoid duplication?
or how to get data quickly with these dimension, as volume of data may be high.
 
        GeneralJournalAccountEntry                  generalJournalAccountEntry;        GeneralJournalEntry                         generalJournalEntry;        DimensionAttributeValueCombination dimensionAttributeValueCombination;        DimensionAttributeValueGroupCombination dimensionAttributeValueGroupCombination;        DimensionAttributeValueGroup dimensionAttributeValueGroup;        DimensionAttributeLevelValue dimensionAttributeLevelValue;        DimensionAttributeValue dimensionAttributeValue;        DimensionAttribute dimensionAttribute;        MainAccount                                 mainAccount;        xT_ProjectWisePLAccounts                   mainAccountsGroupTbl;str dimType =/Project/;         while select   sum(ReportingCurrencyAmount) FROM generalJournalAccountEntry            group by mainAccountsGroupTbl.xT_AccountType, mainAccountsGroupTbl.MainAccountNum, dimensionAttributeLevelValue.DisplayValue               order by dimensionAttributeLevelValue.DisplayValue ,mainAccountsGroupTbl.MainAccountNum ,mainAccountsGroupTbl.xT_AccountType Asc            join AccountingDate from  generalJournalEntry where generalJournalEntry.RecId == generalJournalAccountEntry.GeneralJournalEntry            join  dimensionAttributeValueCombination where dimensionAttributeValueCombination.RecId ==  generalJournalAccountEntry.LedgerDimension            join dimensionAttributeValueGroupCombination where dimensionAttributeValueGroupCombination.DimensionAttributeValueCombination == dimensionAttributeValueCombination.RecId            join dimensionAttributeValueGroup where dimensionAttributeValueGroup.recid == dimensionAttributeValueGroupCombination.DimensionAttributeValueGroup            join dimensionAttributeLevelValue where dimensionAttributeLevelValue.DimensionAttributeValueGroup == dimensionAttributeValueGroup.RecId            join dimensionAttributeValue where dimensionAttributeLevelValue.DimensionAttributeValue == dimensionAttributeValue.RecId            join dimensionAttribute where dimensionAttribute.recid == dimensionAttributeValue.DimensionAttribute            join mainAccountsGroupTbl            where mainAccountsGroupTbl.MainAccountNum == dimensionAttributeValueCombination.MainAccountValue                && (mainAccountsGroupTbl.xT_AccountType == xT_AccountType::Revenue  ||mainAccountsGroupTbl.xT_AccountType ==xT_AccountType::BonusAllowances||mainAccountsGroupTbl.xT_AccountType ==xT_AccountType::OtherDirectCost ||mainAccountsGroupTbl.xT_AccountType ==xT_AccountType::Payroll ||mainAccountsGroupTbl.xT_AccountType == xT_AccountType::BadDebts )                && generalJournalAccountEntry.POSTINGTYPE != 19 && generalJournalAccountEntry.POSTINGTYPE != 0 && generalJournalEntry.Ledger == Ledger::current()                && generalJournalEntry.AccountingDate <= contract.ParmToDate() && generalJournalEntry.AccountingDate >= contract.ParmFromDate()                && dimensionAttribute.Name ==  dimType
 
  • nbhatti2001 Profile Picture
    nbhatti2001 220 on at
    Data with Multiple Financial Dimension
    Thanks Martin, for your response. Actually query is returning the data vertically and I need it horizontally, now the major time is going to consume in data processing. I want to save that time so, I can get the data quickly. Here is my required output. How I can get data according to my requirement from query so, it take less time to generate the report. 
     
     
  • Martin Dráb Profile Picture
    Martin Dráb 232,011 Most Valuable Professional on at
    Data with Multiple Financial Dimension
    Yes, this result corresponds to your query. It's still true that "all I can say is what your code does, not what you want it to do". I understand that you're not happy with the query you created, but I don't know what result you want to get.
     
    Anyway, it seems that you now want to use the new thread Need to filter Report data on basis of multiple ledger dimensions" and this one becomes obsolete.
  • nbhatti2001 Profile Picture
    nbhatti2001 220 on at
    Data with Multiple Financial Dimension
    Hi Martin,
     
    Sorry for misunderstanding, let me explain my requirement with more detail, I have translate the query for SQL studio
    Query 
    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)
    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
     
    Query
      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 (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.000000
     
    Hope you will get it now.
     
  • Martin Dráb Profile Picture
    Martin Dráb 232,011 Most Valuable Professional on at
    Data with Multiple Financial Dimension
    I'm sorry, but I don't understand the description of your problem ("it create the duplicate record against each transaction because of multiple dimension"). This code doesn't create any records - it merely fetches data from database. Maybe your problem lies somewhere else, or the description is misleading.
     
    Also, it doesn't return transactions - it returns summarized data with a single record for each (applicable) combination of xT_AccountType, MainAccountNum and DisplayValue. Therefore if you return data for three display values, such as 'C11', '03' and '01', you'll indeed get tree records, because that's what you asked for.
     
    Unfortunately all I can say is what your code does, not what you want it to do.
  • nbhatti2001 Profile Picture
    nbhatti2001 220 on at
    Data with Multiple Financial Dimension
    Thanks Martin,
     
    I will be waiting for solution, as I am very much carefull about the performance of report.
  • Martin Dráb Profile Picture
    Martin Dráb 232,011 Most Valuable Professional on at
    Data with Multiple Financial Dimension
    First of all, let me format your code correctly, so we can actually read it. I changed the query a bit it to make it easier to follow.
    I also fixed code at line 13.
    GeneralJournalAccountEntry     generalJournalAccountEntry;
GeneralJournalEntry            generalJournalEntry;

DimensionAttributeValueCombination         dimensionAttributeValueCombination;
DimensionAttributeValueGroupCombination dimensionAttributeValueGroupCombination;
DimensionAttributeValueGroup             dimensionAttributeValueGroup;
DimensionAttributeLevelValue             dimensionAttributeLevelValue;
DimensionAttributeValue                 dimensionAttributeValue;
DimensionAttribute                        dimensionAttribute;

xT_ProjectWisePLAccounts mainAccountsGroupTbl;
container accountTypes = [xT_AccountType::Revenue, xT_AccountType::BonusAllowances, xT_AccountType::OtherDirectCost, xT_AccountType::Payroll, xT_AccountType::BadDebts];
str dimType = 'Project';

while select sum(ReportingCurrencyAmount) from generalJournalAccountEntry
    group by mainAccountsGroupTbl.xT_AccountType, mainAccountsGroupTbl.MainAccountNum, dimensionAttributeLevelValue.DisplayValue
    order by dimensionAttributeLevelValue.DisplayValue, mainAccountsGroupTbl.MainAccountNum, mainAccountsGroupTbl.xT_AccountType
    where generalJournalAccountEntry.PostingType != 19
       && generalJournalAccountEntry.PostingType != 0
       && generalJournalEntry.Ledger == Ledger::current()
    join AccountingDate from generalJournalEntry
        where generalJournalEntry.RecId == generalJournalAccountEntry.GeneralJournalEntry
           && generalJournalEntry.AccountingDate <= contract.ParmToDate()
           && generalJournalEntry.AccountingDate >= contract.ParmFromDate()
    join dimensionAttributeValueCombination
        where dimensionAttributeValueCombination.RecId == generalJournalAccountEntry.LedgerDimension
    join dimensionAttributeValueGroupCombination
        where dimensionAttributeValueGroupCombination.DimensionAttributeValueCombination == dimensionAttributeValueCombination.RecId
    join dimensionAttributeValueGroup
        where dimensionAttributeValueGroup.RecId == dimensionAttributeValueGroupCombination.DimensionAttributeValueGroup
    join dimensionAttributeLevelValue
        where dimensionAttributeLevelValue.DimensionAttributeValueGroup == dimensionAttributeValueGroup.RecId
    join dimensionAttributeValue
        where dimensionAttributeLevelValue.DimensionAttributeValue == dimensionAttributeValue.RecId
    join dimensionAttribute
        where dimensionAttribute.RecId == dimensionAttributeValue.DimensionAttribute
           && dimensionAttribute.Name == dimType 
    join mainAccountsGroupTbl
        where mainAccountsGroupTbl.MainAccountNum == dimensionAttributeValueCombination.MainAccountValue
           && mainAccountsGroupTbl.xT_AccountType in accountTypes
{}

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

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

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

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 293,278 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 232,011 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans