Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX (Archived)

DataAreaId and ProjDataAreaId in Query

(0) ShareShare
ReportReport
Posted on by 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

  • Suggested answer
    Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at
    RE: DataAreaId and ProjDataAreaId in Query

    I have faced this problem in the past. There is no way to eliminate the automatic relationship between DataAreaId between two tables with SaveDataPerCompany set to Yes. If one of them had SaveDataPerCompany set to No, it would probably work just fine.

    By the way, the AllowCrossCompany property on queries and view is a little misleading. Often it is still required to set AllowCrossCompany on the form data source to get it to work.

    https://msdn.microsoft.com/en-us/library/cc621079.aspx

    "In the Application Object Tree (AOT), you can build a view by dragging a query node onto the Data Sources node of your view. The query could have the AllowCrossCompany property set to Yes. However, this will not return cross-company results from the view.

    To return cross-company results from a view, you need to use the crossCompany keyword on the select statement that reads from the view. The AllowCrossCompany setting on the view's data source query is ignored in X++ select statements that read from a view."

    There's a subtle behavior to cross-company queries that matters here.

    https://msdn.microsoft.com/en-us/library/cc634544.aspx

    "The results returned for an X++ select crossCompany query match what could be returned by a union of several select statements that each omits the crossCompany keyword.

    In the Microsoft Dynamics AX client, you could set your current session company to CM1, then run a select query and save the results. You could switch to company CM2, rerun the query, and then add the results to the earlier results. You could repeat this cycle for all the companies you are authorized to see. Your accumulated results would be the same as running one select crossCompany."

    The key here is that a cross-company query eliminates the implicit DataAreaId == curext() filter on the root data source only.  It does not break the automatic DataAreaId linkage between a parent data source and a child data source where one is required (when both have SaveDataPerCompany set to Yes).  You can break this linkage by injecting a table where SaveDataPerCompany is set to No, but often finding such a table properly related to your original parent/child tables is difficult (DirPartyTable can sometimes save the day in cases like that).

  • 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.

  • 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.

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,391 Super User 2024 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.

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

    Hi again!

    Does anyone have any other ideas?

  • 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.

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,391 Super User 2024 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

    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... 291,391 Super User 2024 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

    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? 

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 Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,445 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans