Hello, im making reports for my car rental.
I have a problem with report which shows all earned money by single cars.
E.G:
Nissan GTR - 300.000
BMW M3 - 150.000
AUDI A6 - 100.000
I made a dataset which using a query(using tables: CarTable, TransactionTable). Halp!
Hi,
What version of AX you are using? If you are using AX 2012, then is the SSRS Report a Query based or RDP based report?
Can you please provide the above information.
Sincerely,
Muneeb
Im using ax2012 and its SSRS Report a Query based.
You can have a look at the below link to understand how the keyword “Sum” works and accordingly use it in your scenario.
msdn.microsoft.com/…/aa656402.aspx
Sincerely,
Muneeb
static void MMA_CreateQuery(Args _args)
{
TreeNode treeNodeObj;
Query q; // Extends TreeNode class.
QueryBuildDataSource qbds;
QueryBuildDataSource qbds2;
QueryBuildRange qbr;
QueryRun qr;
TransactionsTable transTable;
CarsTable carTable;
str queryName = "QueryForCarEarnings";
// Macro.
#AOT
// Delete the query from the AOT, if the query exists.
treeNodeObj = TreeNode::findNode(#QueriesPath);
treeNodeObj = treeNodeObj.AOTfindChild(queryName);
if (treeNodeObj) { treeNodeObj.AOTdelete(); }
// Add the query to the AOT.
treeNodeObj = TreeNode::findNode(#QueriesPath);
treeNodeObj.AOTadd(queryName);
q = treeNodeObj.AOTfindChild(queryName);
// Further define the query.
qbds = q.addDataSource(tablenum(CarsTable));
qbds2 = qbds.addDataSource(tableNum(TransactionsTable));
qbds2.relations(true);
qbds2.joinMode(JoinMode::InnerJoin);
qbds2.fetchMode(QueryFetchMode::One2One);
qbds2.addLink(fieldNum(CarsTable, RecId), fieldNum(TransactionsTable, RefTo_CarsTable));
// Select only the Qty field, and then sum the Qty field.
qbds2.addSelectionField(fieldnum(TransactionsTable, TransactionCost),selectionfield::Sum);
// Set the range to the ItemId field.
qbr = qbds.addRange(fieldnum(CarsTable, CarId));
// The range for the where statement specifies an 'or' statement.
// qbr.value(strfmt('(%1 == "%2")',fieldstr(MMA_Cars_v3,CarId), fieldStr(MMA_Transactions_v3, MMA_Cars_v31)));
// Compile the query.
q.AOTcompile(1);
q.AOTsave();
// Run the query.
qr = new QueryRun("QueryForCarEarnings");
if(qr.prompt()) // Comment 'if' statement if you don't want
{ // to see query dialogue at runtime
while (qr.next())
{
transTable = qr.get(tableNum(TransactionsTable));
carTable = qr.get(tableNum(CarsTable));
Global::info(strFmt("%1, %2, %3, %4, %5",
carTable.CarId,
carTable.Brand,
carTable.Model,
transTable.TransactionCost));
}
}
// Delete the query from the AOT.
// treeNodeObj = TreeNode::findNode(#QueriesPath);
// treeNodeObj = treeNodeObj.AOTfindChild(queryName);
// treeNodeObj.AOTdelete();
}
Its my code. I know im doing something wrong and I have do more than that but i actually i have no idea what to do.
My goal is to this to sum earnings by one car and display it with carid, car brand and model. E.G:
If i have 5 transactions like this:
transId - carBrand - carModel - transactionCost
Nissan GTR - 12.000$
BMW M3 - 3.000$
Nissan GTR - 14.000$
Nissan GTR - 4.000$
BMW M3 - 7.000$
i want to see sum on $
carId - carBrand - carModel - sum(transactionCost)
Nissan GTR - 30.000$
BMW M3 - 10.000$
static void MMA_CreateQuery(Args _args)
{
TreeNode treeNodeObj;
Query q; // Extends TreeNode class.
QueryBuildDataSource qbds;
QueryBuildDataSource qbds2;
QueryBuildRange qbr;
QueryRun qr;
TransactionsTable transTable;
CarsTable carTable;
str queryName = "QueryForCarEarnings";
// Macro.
#AOT
// Delete the query from the AOT, if the query exists.
treeNodeObj = TreeNode::findNode(#QueriesPath);
treeNodeObj = treeNodeObj.AOTfindChild(queryName);
if (treeNodeObj) { treeNodeObj.AOTdelete(); }
// Add the query to the AOT.
treeNodeObj = TreeNode::findNode(#QueriesPath);
treeNodeObj.AOTadd(queryName);
q = treeNodeObj.AOTfindChild(queryName);
// Further define the query.
qbds = q.addDataSource(tablenum(CarsTable));
qbds2 = qbds.addDataSource(tableNum(TransactionsTable));
qbds2.relations(true);
qbds2.joinMode(JoinMode::InnerJoin);
qbds2.fetchMode(QueryFetchMode::One2One);
qbds2.addLink(fieldNum(CarsTable, RecId), fieldNum(TransactionsTable, RefTo_CarsTable));
// Select only the Qty field, and then sum the Qty field.
qbds2.addSelectionField(fieldnum(TransactionsTable, TransactionCost),selectionfield::Sum);
// Set the range to the ItemId field.
qbr = qbds.addRange(fieldnum(CarsTable, CarId));
// The range for the where statement specifies an 'or' statement.
// qbr.value(strfmt('(%1 == "%2")',fieldstr(MMA_Cars_v3,CarId), fieldStr(MMA_Transactions_v3, MMA_Cars_v31)));
// Compile the query.
q.AOTcompile(1);
q.AOTsave();
// Run the query.
qr = new QueryRun("QueryForCarEarnings");
if(qr.prompt()) // Comment 'if' statement if you don't want
{ // to see query dialogue at runtime
while (qr.next())
{
transTable = qr.get(tableNum(TransactionsTable));
carTable = qr.get(tableNum(CarsTable));
Global::info(strFmt("%1, %2, %3, %4, %5",
carTable.CarId,
carTable.Brand,
carTable.Model,
transTable.TransactionCost));
}
}
// Delete the query from the AOT.
// treeNodeObj = TreeNode::findNode(#QueriesPath);
// treeNodeObj = treeNodeObj.AOTfindChild(queryName);
// treeNodeObj.AOTdelete();
}
Its my code. I know im doing something wrong and I have do more than that but i actually i have no idea what to do.
My goal is to this to sum earnings by one car and display it with carid, car brand and model. E.G:
If i have 5 transactions like this:
transId - carBrand - carModel - transactionCost
Nissan GTR - 12.000$
BMW M3 - 3.000$
Nissan GTR - 14.000$
Nissan GTR - 4.000$
BMW M3 - 7.000$
i want to see sum on $
carId - carBrand - carModel - sum(transactionCost)
Nissan GTR - 30.000$
BMW M3 - 10.000$
Its my code. I know im doing something wrong and I have do more than that but i actually i have no idea what to do.
My goal is to this to sum earnings by one car and display it with carid, car brand and model. E.G:
If i have 5 transactions like this:
transId - carBrand - carModel - transactionCost
Nissan GTR - 12.000$
BMW M3 - 3.000$
Nissan GTR - 14.000$
Nissan GTR - 4.000$
BMW M3 - 7.000$
and i want see this as a result:
carId - carBrand - carModel - sum(transactionCost)
Nissan GTR - 30.000$
BMW M3 - 10.000$
— edit —
im trying to paste the code, but something didn’t work w8
First of all, stop dropping the AOT query and recreating it. It’s not needed at all and it will only get you into troubles.
You can design the query in AOT and just use it. Or you can design it in AOT and modify it by X++. Or you can build it purely in X++. Neither option requires manipulation AOT nodes at runtime.
If you decide to use an AOT query, you can get a Query object for it by q = new Query(queryStr(QueryForCarEarnings)).
One obvious bug is how you create relations between those tables, because you’re doing it thrice:
Use the first option only; the second option is worse and you should never use the third one.
Calling relations(true) requires a relation defined in AOT, so if you don’t have any, go and add it.
Personally, I would make the query in AOT, rather than of wasting my time writing X++ code. I believe it will be easier for you as well. Regarding relations, you’ll just change Relations property of the child datasource to Yes and you don’t need any code.
Another bug is that you added an aggregation, but you forgot to add any grouping. Therefore you’ll get one total number, not totals grouped by cars.
Okay, i make the query in AOT, set child relation property to yes but still i dont know how to use this query to make a raport which sum TransactionCosts for every car. 
Create a new report. Add a data set and use your query as its data source. Then create a report design and you’re done.
You can find more details in AX documentation, such as How to: Use a Query in a Report [AX 2012].
kk, im trying to figure it out. working on my report