Creating a date range parameter on SSRS report in AX 2012

(0) Share

Report

Posted on by tonyb

Hi,

I'm brand new to the forum and to Dynamics AX 2012 report development, and have a question around ranges and parameters.

I've built a new query in the AOT, dragged tables into, and defined the relationships. I've made a test report and precision design in Visual Studio 2010 and the query pulls the data I expected and displays it appropriately. Now, I am looking to add the ability for the user to enter a date range to run the report for.

I've tried adding a column to my query as a range. The column is called startDateTime and is data type DateTime. I refresh my data set in VS, and it appears as a parameter under both the data set, as well as the report. By default, it is giving me the option to enter a single DateTime value which appears to be added to the query as a constraint.

However, I want the user to enter a date range, preferably without entering times. For example, 01-SEP-2014 to 30-SEP-2014. I haven't found any way to change the data type to strip the time off, nor have I found a way to choose a date range instead of a single date.

I did try using the "dot-dot" syntax: 9/1/2014..9/30/2014 and I received an error. Trying it with time attached doesn't work either.

Can anyone provide advice on the proper way to accomplish this?

Thanks much.

Tony

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (0)

Martin Dráb 237,990 Most Valuable Professional on at

Like (0)

Report

Using the syntax like 9/1/2014..9/30/2014 is the right approach. I can't say what's your problem unless you tell us more than just "I received an error".

Just one attempt - is 9/1/2014 the correct date format in your system? You mentioned 01-SEP-2014 before.

Was this reply helpful? Yes No
Girija Shankar Beuria 690 on at

Like (0)

Report

Hi,

"dot..dot" syntax should work... Can you tell us what the exact error is ?

- Girija

Was this reply helpful? Yes No
Suggested answer

keoma 32,729 on at

Like (0)

Report

hi,

use 2 parameters startDate and endDate.

follow technet.microsoft.com/.../gg731939.aspx

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

If Tony's seeing the same behaviour I am whilst trying to retrieve records within a date range, Visual Studio will be warning that "Dataset parameter <name> cannot be bound to report parameter <name> because they do not have the same data type"

The error makes sense in itself, but when the Query range is on a field of type DateTime, the SSRS dataset parameter is also of DateTime, so it's not immediately apparent to me how you specify a date range. Constructing a string expression in SSRS using two user-visible parameters is easy enough, but Visual Studio doesn't seem to be happy passing that in as a parameter.

To respond to Jonathan's suggestion of the two parameters: I'm afraid that walkthrough seems to display the StartDate & EndDate columns: there's perhaps an implication that the AutoDesign could use filters, but it's not explicit.

Was this reply helpful? Yes No
Martin Dráb 237,990 Most Valuable Professional on at

Like (0)

Report

Query ranges and parameters are different things. 9/1/2014..9/30/2014 is a valid value for a query range (in a dynamic parameter), but not a valid value of a datetime parameter. It seems that you're trying to do the latter (as mentioned a named datetime parameter), which is wrong (and which is also something else than we discussed in this thread). Please correct me if I didn't get correctly what you're doing.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

That's the first mention I've seen of dynamic parameters on this thread, and hadn't appreciated that a range of values could only be applied with a dynamic parameter. I wasn't really expecting the 9/1/2014..9/30/2014 value for a DateTime parameter to work, but was trying to suggest how Tony might have been getting the issue: discovering that I might need to use a dynamic parameter for a date range would be a helpful side-benefit.

Is it possible to apply a combination of dynamic and static parameters to a query? I was just about resigned to using an RDP class rather than an AOT query as the datasource for my dataset, but I'll experiment with dynamic parameters first.

Was this reply helpful? Yes No
XB 1,875 on at

Like (0)

Report

If Finally you have to use a Data Contract Class, here's you have an example.

axlearn.blogspot.com.es/.../2_1008.html

Was this reply helpful? Yes No
Martin Dráb 237,990 Most Valuable Professional on at

Like (0)

Report

It wasn't necessary to mention dynamic parameters by name, because we're talking about a query (which AX implements as dynamic parameters). I mentioned it just to make clearer for you that we weren't talking about "normal" parameters when discussing query range values such as 9/1/2014..9/30/2014. Query ranges indeed make sense with queries only.

If you use a parameter, you're clearly not using a query range, although you may add some code to add a query range based on a parameter. For example, you could create two parameters for from date and to date and build a query range from them. Nevertheless if you don't have any special reason to do it, just let users to set query ranges as they need - it's more flexible and you don't have to write any code.

Was this reply helpful? Yes No