DataAreaId and ProjDataAreaId in Query

(0) Share

Report

Posted on by Eugene V

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

All responses (11)

Answers (0)

Suggested answer

Brandon Wiese 17,788 on at

Like (0)

Report

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 5 on at

Like (0)

Report

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 466 on at

Like (0)

Report

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 Cal... 291,391 Super User 2024 Season 2 on at

Like (0)

Report

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 5 on at

Like (0)

Report

RE: DataAreaId and ProjDataAreaId in Query

Hi again!

Does anyone have any other ideas?
Eugene V 5 on at

Like (0)

Report

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 Cal... 291,391 Super User 2024 Season 2 on at

Like (0)

Report

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 5 on at

Like (0)

Report

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 Cal... 291,391 Super User 2024 Season 2 on at

Like (0)

Report

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 5 on at

Like (0)

Report

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?