Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX forum / Creating a date range ...
Microsoft Dynamics AX forum
Suggested answer

Creating a date range parameter on SSRS report in AX 2012

(0) ShareShare
ReportReport
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

Categories:
Reporting and BI
  • Martin Dráb Profile Picture
    Martin Dráb 228,041 Super User 2024 Season 2 on at
    RE: Creating a date range parameter on SSRS report in AX 2012

    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.

  • XB Profile Picture
    XB 1,867 on at
    RE: Creating a date range parameter on SSRS report in AX 2012

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

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

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Creating a date range parameter on SSRS report in AX 2012

    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.

  • Martin Dráb Profile Picture
    Martin Dráb 228,041 Super User 2024 Season 2 on at
    RE: Creating a date range parameter on SSRS report in AX 2012

    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.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Creating a date range parameter on SSRS report in AX 2012

    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.

  • Suggested answer
    keoma Profile Picture
    keoma 32,671 on at
    RE: Creating a date range parameter on SSRS report in AX 2012

    hi,

    use 2 parameters startDate and endDate.

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

  • Girija Shankar Beuria Profile Picture
    Girija Shankar Beuria 690 on at
    RE: Creating a date range parameter on SSRS report in AX 2012

    Hi,

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

    - Girija

  • Martin Dráb Profile Picture
    Martin Dráb 228,041 Super User 2024 Season 2 on at
    RE: Creating a date range parameter on SSRS report in AX 2012

    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.

Helpful resources

Quick Links

Dynamics 365 Community Update – Sep 9th

Welcome to the next edition of the Community Platform Update. This is a weekly…

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Community Spotlight for September 2024

Kudos to Mohana Yadav!

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,246 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,041 Super User 2024 Season 2

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans