View with query using aggregate functions

(1) Share

Report

Posted on by Pete Alberts

3,542

I need a cross company view of bank account balances.

I am incompetent with views, but quite nifty with queries in my opinion (I'm probably wrong :D ). The query would look something like this:

BankAccountTable (root) (Cross company)

nested BankAccountTrans (inner join 1:N)

sum(AmountMST)

I tried adding this query, QueryBalances to my view, ViewBalances;;; without success. The view doesn't allow me to drag in queryField sum(AmountMST) and if I manually specify the view field, I get errors. (I can go and check exactly what error it is, if you'd like to know).

I solved the problem by building another query, that looks more or less like this:

BankAccountTrans (root) (cross company)

AmountMST

nested BankAccountTable (inner join 1:1)

*all the necessary detail fields about the account*

Pulled the query into my view and specified a Sum Aggregation on the AmountMST field.

Which is in working order.

I was just wondering about why my first solution (the preferred one), didn't work. Any comments? Should it work (in other words, I did something wrong), or is it not possible to do that (adding a query sum field to a view)?

Thanks for reading

*This post is locked for comments

All responses (8)

Answers (1)

Suggested answer

Martin Dráb 230,235 Most Valuable Professional on at

Like (0)

Report

RE: View with query using aggregate functions

It's actually very easy. Create a view, add your data sources and set fields to group by. Then add fields that you want the view to expose and set an aggregation function for those you don't group by. Like this:
Pete Alberts 3,542 on at

Like (0)

Report

RE: View with query using aggregate functions

In any case, I make it work with the view aggregate functions. Thanks
Pete Alberts 3,542 on at

Like (0)

Report

RE: View with query using aggregate functions

Now I remember what the "error" was. If BankAccountTrans is the root data source in the query, I need to group by AccountId (and possibly currecyCode - the standard code groups by CurrecyCode and I don't understand why). But then AccountID is not retrieved by the query and can't be retrieved (aggregate fields and normal fields aren't allowed). I still get the same error if I drag in AccountID from BankAccountTrans or BankAccountTable.
Pete Alberts 3,542 on at

Like (0)

Report

RE: View with query using aggregate functions

That's what I mean with a view doesn't allow you to use query aggregate fields. So I reverted to use a normal query (without summing) and then use the aggregate functions in the view.

I just wanted to know why this doesn't work. It seems logical to me....
Pete Alberts 3,542 on at

Like (0)

Report

RE: View with query using aggregate functions

So one of the first issues I encountered was adding a normal field after I added some aggregate fields to the query.

That's why I made BankAccountTable the root datasource - then I can still retrieve some details.

Next would be to create a view from the above query. For some reason I don't get any errors now...

But AX doesn't allow me to drag in the sum fields. So I specified them manually as such:

Then I get all the records. Which doesn't make sense or I don't know what I'm doing. The table browser:
Pete Alberts 3,542 on at

Like (0)

Report

RE: View with query using aggregate functions

I'm building some elements to show what I mean, will get back shortly.

And in the first query I just forgot to mention the group by AccountID (apologies). I edited the post.
Verified answer

Martin Dráb 230,235 Most Valuable Professional on at

Like (0)

Report

RE: View with query using aggregate functions

You're talking about two completely different queries. The first one doesn't have any grouping, therefore it would sum value across all transactions and give you a single number. The other is grouped by Account ID, therefore you would get a list of account IDs and their amounts.

You said "I need a cross company view of bank account balances", therefore I assume you want grouping. Therefore the first query doesn't meet your requirements.

I can't comment on the error without knowing the error message.

By the way, starting with BankAccountTrans makes a better sense for me anyway.
Pete Alberts 3,542 on at

Like (0)

Report

RE: View with query using aggregate functions

I just realised that my second solution shouldn't actually work as I group by Account Id and then sum on AmountMST. But it works for some reason............. #believeIt I'll go through it again. (The main question stays the same.)