Hello everyone,
I’m trying to put a date range on a report I’m running. I have a query and put the date field in the range.
I put (greaterThanUtcDate(0)) in the value of that Range. I then created a SSRS report with that query as a datasource. I created a menu item that calls that report. When I call it, I get this screen…
But when I run that, I only get the dates for 2017-03-30 not all the dates greater than or equal to…can someone help me do this correctly?
If you look at \Classes\SysQueryRangeUtil\greaterThanUtcDate it add relative days to the current date. It should be showing the data greater than the system date in your case (as you are passing zero).
Where does this date come from? Is that your system date?
That date (2017-03-30) is what I chose. It was blank the first time I ran the report. I know there are records on that date and after so it was a good test. So you are saying the (greaterThanUtcDate(0)) should work? Will it change when I change that date in my parameters or will it only always show greater than current date? Is there another way I should add a date range to my ssrs report that is connected to my query? I need to parameters eventually. Start Date and End Date so user’s can run the report for that date range.
You have added a range with value on the query. But then you are passing date from parameter to the query. If you want to pass date from the front end, then it will be a good option to allow users to modify the query itself. Or provide 2 different dates - from date & to date. Pass both to the query.
If you directly allow to modify the query, users can pass conditions as shown below.
See if this helps: https://community.dynamics.com/ax/b/alirazatechblog/archive/2015/07/04/query-based-ssrs-report-from-scratch-dynamics-ax-2012-r3
That’s a great link for adding a parameter that comes from a table like I did with the Statistics Group, but I want to do a date range. I want to have two date pickers on the report prompt. One for Start Date and one for End Date. I then want to filter what shows up in the report…
This should be so simple. I can take off the range in the query, add a parameter in the report, and filter data that way, but then the date picker includes the time which I don’t like the look of.
Someone must be able to walk me through what to do.
Thanks for the suggestion about user’s modifying the query, but that is not what I want. I want them to click the report menu button, get prompted where they can choose the Stat Group, Start Date, End Date and then get the info they are looking for. Can someone please help?
This might be an issue due to the extended data type of the field you are using as filter. You need to add a field which is just Date & not UTCDateTime. Are you using DataProvider & Contract classes with this report?
I’m not using classes. I just created the query, and then created a report with that query as a datasource. Then I created a menu button that calls that report. It’s the SalesLine table and I’m trying to put a date range on ReturnDeadline. I don’t want to create fields in this table if I don’t need to.
Andrew,
I checked the salesline table & the field ReturnDeadLine is of type date only. So time picker shouldn’t come, unless the field is modified to display date/time in AX. Else, it looks like visual studio is adding that by converting a date field to date time field. It happens sometimes with that. You should check the report in Visual Studio and check the parameter’s data type. You can change it there, instead of making changes in AX table.
If you create dynamic query So you can put date range like below statements.
qbr.value(SysQuery::range(dateNull(), maxDate())));
Ajay,
I don’t really want to make a dynamic query but thank you for your suggestion
