Query with fields and aggregate function

(0) Share

Report

Posted on by Community Member

Hi All

I am writing a query for use on an ssrs report. I have several data sources nested in the query. At one level I want to pull in a date field and a [count of SalesId].

The query doesnt seem to like having an aggregate and a field together at the same level. The solution would seem to be to link the sales table again and use one for aggregates and one for fields.

I was wondering is someone could explain why it behaves in this way.

Also, I will be grouping my data by year / month based on CreatedDateTime. Would it be best practice to do this on the SSRS report it's self?

Thanks in advance.

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

Suggested answer

Chaitanya Golla 17,225 on at

Like (0)

Report

Hi,

Is it a custom/standard report you are referring to? When you specify grouping/aggregation it applies to the whole query.Also, you can add the grouping to your report query in the processReport method of DP class. If possible can you share your query(with a screen shot) such that we can better help you.

Refer following link:

(Walkthrough: Creating an AOT Query that has Group By and Having Nodes [AX 2012])

msdn.microsoft.com/.../hh745337.aspx

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Its a basic custom report, it's actually my first attempt at writing one from scratch. I tried a work around of adding SalesLine_2 as a second data source and adding createdDateTime there and it seems to work, but as soon as I add the field to a query to test, it applies the count function. As you say it applies to the whole query.

I was hoping to steer clear of writing an rdp report for this one!

Was this reply helpful? Yes No
Chaitanya Golla 17,225 on at

Like (0)

Report

Hi,

DP class is required only if we have some business logic to get processed before rendering data to the screen or else ssrs report can be developed only based on query. Could you provide some more details about your business requirement like what are the fields would you like to get displayed on report.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I need to return sales values by 'Employee responsible' split out by year and month (based on SalesTable, DateCreated). I will probably add year as a parameter (but not critical just now).

CustTable - CustId - MainContactWorker (aka Employee responsible)

Sales Table (Count of SalesId)

SalesLine (Sum Of LineAmount).

Should be all the data I need. Strangely, now I have removed ALL aggregates from my query and my view is still adding 'CountOfxxxxx' when i try to re-add the fields.

I think I may try creating the view from scratch and re-adding the query.

Was this reply helpful? Yes No
Suggested answer

Chaitanya Golla 17,225 on at

Like (0)

Report

Hi,

As per your business requirement, your query should like below

Steps followed:

1. Add CustTable as datasource and set dynamic property on fields to No. On the groupby Node, create groupby on AccountNum

2. Add SalesTable at the next level datasource, set properties FetMode to 1:n, JoineMode to InnerJoirn, Relations to Yes on datasource and on on fields set dynamic property to No. Select Count of SalesId on Fields Node.

3. Add SalesLine as the next level datasource, set properties FetMode to 1:n, JoineMode to InnerJoirn, Relations to Yes on datasource and on on fields set dynamic property to No. Select Sum LineAmount on Fields Node.

Yes you need to refresh your view to remove already selected aggregate functions.

Following link provides you steps to develop ssrs report based on query:

(Developing SSRS report using Query in Microsoft Dynamics AX 2012)

https://community.dynamics.com/ax/b/dynamics101trainingcenterax/archive/2013/07/10/developing-ssrs-report-using-query-in-microsoft-dynamics-ax-2012

Was this reply helpful? Yes No