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

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Getting the SaleLine.L...
Microsoft Dynamics AX (Archived)

Getting the SaleLine.LineAmount Field into the Sales Cube and pulling the correct data?

(0) ShareShare
ReportReport
Posted on by Community Member

Hi All,

 

I am trying to add the line amount for each sales order line as a measure in the sales cube and am running into a problem.

 

The SalesLine.LineAmount field was added to the SalesLineExpanded query and view, which placed it in the sales cube perspective, where I updated the visual studio project and deployed the modified sales cube.

 

I can now see the sales line amount measure in the cube and I can use it in pivot tables, the only problem is that the data does not seem to match up with what is in the SQL server database. What I mean by this is that the net amount showing up in excel (or when I query the sales cube is SSMS) is different than when I run the following query in sql.

 

Select

       Sum(lineamount)

From

      dbo.salesline

 

The amount I see in the cube is quite a bit lower.

 

 

Does anyone have any idea why that might be?

 

Thanks,

 

Phil

 

*This post is locked for comments

I have the same question (0)
  • Community Member Profile Picture
    Community Member on at
    RE: Getting the SaleLine.LineAmount Field into the Sales Cube and pulling the correct data?

    Thank you

  • dolee Profile Picture
    dolee 11,279 on at
    RE: Getting the SaleLine.LineAmount Field into the Sales Cube and pulling the correct data?

    Hi Phil,

    This is what you are referring to above, correct? - Yes.

    You can modify the SalesLineExpanded query and it'll update the view as well. AX query have other options: outer join, exist join and not exist join.

    However, I don't fully understand why the salesLineExpanded view is built this way and maybe there's a point to it. So why not make a copy and edit the new view instead. Then you can add new measure to the cube based on the new customized view.

  • Community Member Profile Picture
    Community Member on at
    RE: Getting the SaleLine.LineAmount Field into the Sales Cube and pulling the correct data?

    I am seeing that under the CustPackingSlipOnTimeStatus table in the SaleLineExtend Query there is a relationship set up between SalesLine.InventTransID == CustPackingSlipOnTimeStatus.

    This is what you are referring to above, correct?

    Also, is there any ways to change this to a left or right join? Or are joins expressed this way only going to be inner joins?

    Thanks,

    Phil

  • Verified answer
    dolee Profile Picture
    dolee 11,279 on at
    RE: Getting the SaleLine.LineAmount Field into the Sales Cube and pulling the correct data?

    Hi Phil,

    You can see the reason by looking at the SalesLineExpanded view object in AOT. There are quite a few joins in the view that reduced the number of selected records.

    For example, there is an inner join from SalesLine to CustPackingSlipOnTimeStatus view.

    When I compare the record count with and without having this join. I get:

    - SELECT COUNT(*) FROM SALESLINE -- 185557

    - SELECT COUNT(*) FROM

     SALESLINE a

     JOIN CUSTPACKINGSLIPONTIMESTATUS b

     ON a.INVENTTRANSID a= b.INVENTTRANSID   -- 17122

    If your intention is to see all salesLine records, you probably should build a new view instead of extending on SalesLineExpanded view.

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

News and Announcements

Dynamics 365 Community Calls Return
Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft
Responsible AI policies for the Community

Quick Links

Responsible AI policies

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

Mansi Soni – Community Spotlight

We are honored to recognize Mansi Soni as our August 2025 Community…

Congratulations to the July Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Syed Haris Shah Profile Picture

Syed Haris Shah 9

#2
Mea_ Profile Picture

Mea_ 4

#3
KP-31070522-0 Profile Picture

KP-31070522-0 3

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans