SSRS Report SysLastValue query

Finance and Operations/AX Developer Forum
1

Hi,

one of my SSRS reports query is run with a range from sysLastValue,

SSRS report has 3 dialogs- fromDate, toDate, itemNumber,

i want that it would store fromDate and toDate in sysLastValue but woudnt create range for itemNumber from sysLastValue,

i tryed

query = this.getFirstQuery();

query.dataSourceTable(tableNum(InventTable)).clearRanges();

query.dataSourceTable(tableNum(InventTrans)).clearRanges();

query as i saw from debugger is :

Query RBA_PktRdlllItemTransList object 2d819458:

SELECT ItemId, ItemType, NameAlias FROM InventTable (InventTable_1) OUTER JOIN

InvoiceId, ItemId, PackingSlipId, Voucher, DateFinancial, DatePhysical, DateInvent, Qty FROM InventTrans(InventTrans_1) ON InventTable.ItemId = InventTrans.ItemId AND ((DatePhysical>-{ts ‘2005-10-01 00:00:00.000’} AND

DatePhysical <={ts ‘2015-10-09 00:00:00.000’})) WHERE ((InventTable(InventTable_1).ItemId = N’1000’))

clearRanges() - does not delete the WHERE ((InventTable(InventTable_1).ItemId = N’1000’)) part

if i do delete_from * sysLastValue the problem is gone, but i dont want to ask users to do it

2

Trying to delete a single range from a saved query is weird. What problem are you trying by that?

3

You mean three dialog fields, Do you have contract for this?

4

  1. SSRS is called with output menu item from with ItemId from args, after few calls SSRS report gets query loaded from SysLastValue and when i open SSRS report dialog form i have ItemId field values preloaded from sysLastValue, i dont need them, user has to click remove in dialog to delete them, this is the problem, saved date fields is ok, but saved itemId is not ok.

  2. sometimes i open SSRS report from menu item without ItemId in args, but ItemId is saved from older queries in Report dialog so i have to use delete_all from syslastvalue to solve the problem,

  3. and sometimes something weird is going on with SSRS Query from what i see in debugger, i get something like without any value in range and the query returns null, while it should return records based on date fields

WHERE (InventTable(InventTable_1).ItemId = ,

Solving all the problems with working clearRanges() would be solution to all these 3 problems instead of using delete_from sysLastValue, but clearRanges() doesnt seem to work for some reason,

EXAMPLE SITUATION:

1)Run Report with args record InventSum DS ItemId =‘1000’

Choose dates from Date: 01/12/2010 , to Date 01/12/2015,

report executes OK.

2)Run report with args without InventSum DS ItemId,

Report Dialog has predefined values From Date 01/12/2010, To Date 01/12/2015, ItemId 1000,

Problem : I dont need itemId 1000, users have to click remove to remove it from report dialog mannualy, i only want dates to be predefined and itemid should be null;

You mean three dialog fields, Do you have contract for this?

Yes, 3 dialog fields, i have a contract

5

I doubt that clearRanges() doesn’t work. If you believe so, let Microsoft know that they have a bug in AX kernel. Nevertheless it’s much more likely that the problem is in your own code. For example, are you sure you call it after the query is read from SysLastValue? If you do it before, you’re changing the query that will be overwritten.

Consider decorating your data contract with SysOperationAlwaysInitializeAttribute and implementing your logic in initialize().

6

I had the same issue, but found out the solution. I assume you are using AX 2012 or later, because with this release QueryFilter class was introduced. I couldn’t clear AccountNum range from CustTrans table with standard qbds.clearRanges(), but here is what worked for me:

Query query = this.getFirstQuery();
QueryBuildDataSource qbds_CustTrans = query.dataSourceTable(tableNum(CustTrans));
query.clearQueryFilters(qbds_CustTrans, fieldStr(CustTrans, AccountNum));

Hope that works for you too :wink: