Hi,
I am quite new in AX and SSRS report…
and I’m trying to make a SSRS report that with dataset which is an AOT Query as “ProdRouteJournal innerJoin inventDim”. And there is an range ProcessDate as an input of the report.
And I’d like the report to look like this
where Material is from InventDim.Material, and MaterialCat is from a method of InventDim to decide its category from its Material.
My question is that I’d like to calculate the red values in the above report which are
-
Sum(Qty) of each MaterialCat, e.g. A: 30
-
Total Qty of the selected date,
-
Total Qty of the month of the selected date.
I was wondering if there is any good way to do the above 1/2/3 ? (in this situation that I can not add a MaterialCat field into inventDim).
Many thanks.
VincentC
What’s your problem? That you can’t filter or group by your display method? That’s correct - you need to use something that can be referred to from SQL. I don’t know the content of your method, so I can’t offer any specific solution, but you may be able to simply use a joined table instead of the display method. In more complex cases, you can create a view with a computed column.
Hi Martin,
thanks, let me try to clear my problem.
the dataset of the report (of my current solution) is bringing from the AOT-build Query with specific date (processDate), so now I only have data (rows) of a specific date, but I need to a value of Sum(Qty of day1 of the month to processDate) <–Problem1
and the MaterialCat display method is very simple, it just check the Material field (as string, material could be combined with “A”, “B”, “C” and “D” with “|” as separator, e.g. " A | B" or “B | D” or “A | B | D”.)
if the Material contains “A”, then it is Cat “A”,
else if it contains “B”, then it is Cat “B”
else if it contains “C”, then it is Cat “C”
else it is Cat “D”
and Problem 2 is that I need to calculate the Sum(Qty of each MaterialCat). ← Problem 2
this is my first time to use AX with SSRS, and about SSRS, before this I just read ch1-ch4 of “AX 2012 R3 reporting cookbook”, I think i just know a few ways to do this. And I’ll try to take a look of “crate a view with a computed column” tomorrow.
many thanks.
Vincent
The reason why you can’t meaningfully work with your data model is that it’s completely wrong. It violates already the first normal form of database design.
Instead of defining material ID as “A | B”, create a table holding material definition and refer it its primary key from the Material field. Then create a table mapping material IDs to category IDs (possibly with a separate table for categories, if category has more than a single field).
Then you’ll be able to query categories easily, such as design queries like this:
// Psedocode only
select sum(Qty) from {tables}
group by MaterialCategories.CategoryId
Also, make sure you’re not duplicating a logic that already exists in standard AX. I would think that material is a quite important concept in production, so there may already be a data model for dealing with it.
Hi Martin,
thanks, actually our company’s AX was developer by other company, and it has already been done like this (about the Material, they use InventDim.xxx_DIM8 as “XX | YY | ZZ” to represent material combinations (which is their customized feature for textile industry). But the manager level would like to know the productivity of it by MaterialCategory.
But yes, you are right. I will double check the requirement and the database design.
many thanks.
Vincent
