web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / DataAreaId and ProjDat...
Microsoft Dynamics AX (Archived)

DataAreaId and ProjDataAreaId in Query

(0) ShareShare
ReportReport
Posted on by Eugene V 5

Hello everyone,

I have been working on cubes customization and I have the next scenario:

TSTimesheetLine table has two fields that can determine dataarea for different purposes: DataAreaId - company dataarea value, ProjectDataAreaId - dataarea of the project.

I need to create a relation in query to ProjTable, but using not DataAreaId, but ProjectDataAreaId. Making this, I always get three fields in relation part of query (looking at from SQL Manager), where I have ProjId, DataAreaId and ProjectDataAreaId. But I need only ProjId and ProjectDataAreaId.

Is there any solution for this?

Thanks a lot for any ideas.

Update: I am working with AX 2012 trying to perform this.

*This post is locked for comments

I have the same question (0)
  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 300,721 Super User 2025 Season 2 on at
    RE: DataAreaId and ProjDataAreaId in Query

    Hi Eugene,

    Can you indicate what tool you are using to create the query? If you create queries using the visual query editor, you can add and delete relations as you like. The query editor tries to map fields automatically without knowing what you really would like to do.

  • Eugene V Profile Picture
    Eugene V 5 on at
    RE: DataAreaId and ProjDataAreaId in Query

    Hi Andre,

    Thanks for the quick respond.

    I am using an editor that Morphx provides. Just from AOT I am opening a query and trying to modify it.

    Could you please give me more info about visual query editor?

    Do you mean named query from cube side?

    Also, one more thing: if I edit a query anywheer else, AX will update the view on this query after the next syncronization, won't it? 

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 300,721 Super User 2025 Season 2 on at
    RE: DataAreaId and ProjDataAreaId in Query

    Hi Eugene,

    You are confusing me. First you talked about SQL manager, now a Morphx editor. Can you share a screenshot of what you exactly are doing? This helps understanding where exactly you are looking at.

    Probably you need to use the crosscompany statement to bypass the DataAreaId range.

  • Eugene V Profile Picture
    Eugene V 5 on at
    RE: DataAreaId and ProjDataAreaId in Query

    Let me get this straight with description of what I am doing step-by-step.

    1. I open AX 2012 and create a query in AOT/Queries

    2. Add TSTimesheetLine as a first root datasource

    3. In TSTimesheetLine’s datasources node I create a new one that references to ProjTable.

    Both of the tables have SaveDataPerCompany property as Yes.

    4. After that I add two new relation to ProjTable datasource:

    TSTimesheetLine.ProjId == ProjTable.ProjId

    and

    TSTimesheetLine.ProjectDataAreaId == ProjTable.DataAreaId

    As I want to find a project in the company it exists (even when TSTimesheetLine.ProjectDataAreaId is not equal to TSTimesheetLine.DataAreaId)

    5. After that I go to AOT/Data Dictionary/Views and create a new view

    6. Add the created query to view as its datasource and add new fields in Fields node to be selected.

    7. But I do not have expected result. The project record is NULL where TSTimesheetLine.ProjectDataAreaId is not equal to TSTimesheetLine.DataAreaId.

    8. After that I go to SQL Server Management Studio, look for this view and look at the join that it has.

    The joins look like (simplified):

    TSTimesheetLine.ProjId == ProjTable.ProjId AND TSTimesheetLine.ProjectDataAreaId == ProjTable.DataAreaId AND TSTimesheetLine.DataAreaId == ProjTable.DataAreaId AND TSTimesheetLine.Partition == ProjTable. Partition

    The key point is here:

    TSTimesheetLine.ProjectDataAreaId == ProjTable.DataAreaId AND TSTimesheetLine.DataAreaId == ProjTable.DataAreaId

    These two relations filter each other so I do not have such records.

    This TSTimesheetLine.ProjectDataAreaId == ProjTable.DataAreaId is a relation added by me.

    This TSTimesheetLine.DataAreaId == ProjTable.DataAreaId is a relation added by AX Kernel to resolve two SaveDataPerCompany=Yes tables.

    So, the main question was: Can I somehow leave only this one:

    TSTimesheetLine.ProjectDataAreaId == ProjTable.DataAreaId

    And avoid adding this one:

    TSTimesheetLine.DataAreaId == ProjTable.DataAreaId

    Hope this is more clear.

    Thanks for your help here.

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 300,721 Super User 2025 Season 2 on at
    RE: DataAreaId and ProjDataAreaId in Query

    Hi Eugene,

    Check the property AllowCrossCompany on the query. This needs to be enabled in this scenario.

  • Eugene V Profile Picture
    Eugene V 5 on at
    RE: DataAreaId and ProjDataAreaId in Query

    Hi again,

    I have already tried to change this property to Yes, but had the same result.

    DataAreaId was still included into the relation list of the query.

  • Eugene V Profile Picture
    Eugene V 5 on at
    RE: DataAreaId and ProjDataAreaId in Query

    Hi again!

    Does anyone have any other ideas?

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 300,721 Super User 2025 Season 2 on at
    RE: DataAreaId and ProjDataAreaId in Query

    Hi Eugene,

    I tested your scenario and I experience the same. However I have seen similar things in the past. When you create a view, you can use display methods or T-SQL to get the fields from the projects from other legal entities. In this way you are not using the Query, but directly a view with some methods.

  • Joel Leichty Profile Picture
    Joel Leichty 466 on at
    RE: DataAreaId and ProjDataAreaId in Query

    I believe the issue is that CrossCompany only works if the first table in the query is not company specific. Try doing a join where the Company info table is first, then ProjTable and TSTimesheetTrans.

  • Eugene V Profile Picture
    Eugene V 5 on at
    RE: DataAreaId and ProjDataAreaId in Query

    Hello, Joel,

    Thanks for your message!

    I tried the way you suggested, but had the same result.

    I also tried to make the next chain: TSTimesheetLine -. CompanyInfo -> ProjTable, but also had the same result.

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Priya_K Profile Picture

Priya_K 4

#2
Alexey Lekanov Profile Picture

Alexey Lekanov 3

#3
Scott_itD Profile Picture

Scott_itD 2 Community Manager

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans