Microsoft Dynamics AX (Archived)

FromDate and toDate filter in SSRS report using Static query object

(0) Share

Report

Posted on by Hawra

786

Hi all,

I am using ax 2012 and having visual studio 2010

while creating ssrs report i need to add filter for date i m not supposed to use dataprovider and contract class as i have ax 2012 and visual studio 2010.

I need to used static query object

when i put a range for single date i get the filter right

but when i put 2 ranges for from date and to date i am not able to get the filter or report is not run.

Please help me to form a query object which can contain 2 ranges and i can filter from date and to date on a single date field in the table.

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (1)

Sort by

Suggested answer

Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi hawra,

Well i think that your problem is that query ranges in a static query object are combined by the OR logical operator that is why i think your filter is not applied properly to your report.

If you want to keep your static query object and your query ranges the only solution i know is to implement a controller class which will run your report and overwrite it's prerunmodifycontract() method, in which you will get your query object method via your controller method "getfirstquery()"

then get ranges values set by user using query.dataSourceTable(tableNum(TableId)).range(int rangeNo).value() ,

finally you add your filter with using SRSSrsReportHelper SRSSrsReportHelper::addFromAndToDateRangeToQuery(query,fromDate,toDate,tableNum(TableId),fieldNum(TableId, DateField));

I hope this will help you

Regards.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report
Copy link

Link copied!

Try to use "Transdate" data type for your FromDate and toDate.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi Hawra,

On your table, goto the field properties of the date field that you were trying to filter, and get the property value of Extended Data Type, then use it as your data type for FromDate and toDate.

Was this reply helpful? Yes No
Suggested answer

syed baber 11,633 on at

Like (0)

Report
Copy link

Link copied!

Hi Hawra,

You cannot apply date range filter on query based report. You need to make a new controller class and override prepromptModifyContract or preRunModifyContract method to apply the ranges accordingly in X++ code. For example of this, take a look at \Classes\CustInterestNoteFormLetterController\prePromptModifyContract method. There are many other examples in AX.

Please verify the solution and let me know if you have further queries.

Thanks,

Baber.

Was this reply helpful? Yes No
Hawra 786 on at

Like (0)

Report
Copy link

Link copied!

Hi All
thank you so mush all but can you tell me how can i show date in the filter on report

Please see that ..

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

yeah..

just simple go to VS and in properties. check show parameters on report.

if u can not do it then let me know. but i hope u will get it.

Was this reply helpful? Yes No
Verified answer

syed baber 11,633 on at

Like (0)

Report
Copy link

Link copied!

Hi Hawra,

Check the below blog post:

dev.goshoom.net/.../showing-dynamic-parameters

It will certainly help you as it addresses the same issue as yours.

Plese verify and let me know if you have further queries.

Thanks,

Baber.

Was this reply helpful? Yes No