AX 2009 SSRS Date Range Parameters

(0) Share

Report

Posted on by John Martin

235

I have a lot of expereince with raw SSRS and SQL but am somewhat new to the AX variant of this technology and I am looking for any advice on how to effectivly write reports that make use of date ranges. I have tried looking at the LedgerSRS report library, more specifically the Divided Trial Balance report. This seems to make calls to FIMConstants classes that sit in .NET

Has anyone got any expereince of making use of these and are there any sources of documentation, alternativly any pointers on how you might have overcome this would be appreciated.

Many thanks

John

*This post is locked for comments

All responses (12)

Answers (0)

Community Member Microsoft Employee on at

Like (0)

Report

RE: AX 2009 SSRS Date Range Parameters

Don,

Do you have an example of this code for an AX2012R2 report? I have a report based from an AOT query and I need the same range functionality but I cannot use an RDP controller for this as it is an EP report. I tried this code but I cannot get it to compile.

Thanks,

Steve
Don Shields 650 on at

Like (0)

Report

Re: Re: Re: Re: Re: Re: Re: Re: Re: Re: AX 2009 SSRS Date Range Parameters

Glad you got it going!
KindlePower KindleP... 65 on at

Like (0)

Report

Re: Re: Re: Re: Re: Re: Re: Re: Re: AX 2009 SSRS Date Range Parameters

Don you ROCK! I got it to work! I really appreciate your help.

Now I'm going to use your code as a basis and try to get a pulldown list of YTD,QTD, MTD, WTD, LYTD etc.

Thanks sooo much for your help sir:-)

KindlePower
Don Shields 650 on at

Like (0)

Report

Re: Re: Re: Re: Re: Re: Re: Re: AX 2009 SSRS Date Range Parameters

Make sure you edit the properties of the created date time parameter in SSRS and change the data type from System.DateTime to System.String.

Don
KindlePower KindleP... 65 on at

Like (0)

Report

Re: Re: Re: Re: Re: Re: Re: AX 2009 SSRS Date Range Parameters

Don I got the example to compile, but now it wont let me apply the string result from the method to the CreatedDAteTime parameter (came from range in AX AOT Query). Previously I had the simple parameter set to string and made the value of the CreatedDateTime parameter (system.DateTime) = the sting parameter. I can type 1/1/2010..1/1/2011 in there and it works. Is there a way to do this with the AOT Query or do I have to create a datamethod to get my data as well?

I'm also having trouble with calculated fields. In normal (non AX project) ssrs I can just right click and add a calculated field say field1 + field2. Now with the AX AOT Query it doesn't allow that so I have to have huge complex statements in the places that allow expressions (i.e. matrix fields).

I appreciate all your help!

KindlePower
Don Shields 650 on at

Like (0)

Report

Re: Re: Re: Re: Re: Re: AX 2009 SSRS Date Range Parameters

Here is a data method in c# that should do the trick for you. I see you were backing up the startdate by a day and adding one to the end date. there are similar functions in c# to do that if you need to. I definately recommend using c# instead of vb for these projects since 99% of the examples you will find are in c#. I can send you a complete test project I setup in VS if you prefer.

[DataMethod(), AxSessionPermission(SecurityAction.Assert)]
    public static DataTable dsDateParam(DateTime StartDate, DateTime endDate)
    {
        string StrDate = StrDate = StartDate.ToShortDateString() + ".." + endDate.ToShortDateString();

        DataTable NewTable = new DataTable();
        NewTable.Columns.Add("Col1", typeof(string));
        NewTable.Columns.Add("Col2", typeof(Int32));
        NewTable.Columns["Col2"].AutoIncrement = true;

        DataRow firstrow = NewTable.NewRow();
        firstrow["Col1"] = StrDate;
        NewTable.Rows.Add(firstrow);

        return NewTable;
    }

Don Shields
KindlePower KindleP... 65 on at

Like (0)

Report

Re: Re: Re: Re: Re: AX 2009 SSRS Date Range Parameters

Thanks for your reply on this. I've been trying to find some sample cs or vb, but have not seen anything quite like this. I'm not sure if I should use vb or cs with AX. I got the idea to use a parameter with value label combinations for MTD for the label and =cstr(DateAdd("D", -1.0 * DatePart("D", Today) + 1, Today)) + ".." + cstr(Today) for the value. Then I set the CreatedDateTime parameter from the SalesLine data set to the value of this new parameter list result. It just shows the expression instead of the result of the expression. Sorry I'm not a vb or cs programmer yet so it seems like I'm over my head. When I run the report it give me errors that seem unrelated for another parameter value is missing. Clearly to me the error is erroneous, because the parameter it errors on comes up and lets you select the value from another list. All very frustrating so I really appreciate your comments and guidance:-)

Would you mind posting a sample of your vb data method or cs data method?

KindlePower
Don Shields 650 on at

Like (0)

Report

Re: Re: Re: Re: AX 2009 SSRS Date Range Parameters

What error are you receiving in SRS when you run the report from report manager? In order to use the datepicker control you will need to create a datamethod in VS which has input paramaters of startdate and enddate, both of datatype of date time. Your datamethod needs to return a table. This table will return only one row with one field. In the datamethod you will take your input parameters and concatenate them so that the resultant string is like 1/1/2010..3/31/2010. Then insert that string into a datatable and return it.

The reason you need to return a datatable is so that you can create a datasource based on the datamethod. Then use that datasource to populate the parameter for the date range on you aot query.

Does that make sense? I'll see if I can find the code I used for the datamethod. As far as YTD, LYTD, QTD....etc you would probably have to make that a datamethod and use a switch statement to populate the return value based on a parameter supplied by the end user...

Don
KindlePower KindleP... 65 on at

Like (0)

Report

Re: Re: Re: AX 2009 SSRS Date Range Parameters

I'm fairly new to using the AX 2009 SSRS method, but have been making some headway. I am trying to get a report working using this an AOT Query with the SalesLine table joined to a couple custom tables. I'm trying to use the CreatedDateTime as a range because the query is an aggregate with a group by. I don't want to return the date field and then filter it in the SSRS report, but rather pass the date range like 1/1/2010..3/31/2010 through the parameter to the AOT Query range. I tried manually changing the system.DateTime to system.string per the above post. It works fine in SSRS locally, but one I save it to the AOD and try to run it from within AX it bombs. I would like to be able to use the date picker as (I think) the function in another post above shows, but I don't know how I would actually use that function in SSRS. I don't see a place in the properties of the parameter to use it.

Also I've been asked to have the ability to pick some predefined ranges like “YTD” “LYTD”, “QTD”, “WEEK24”, etc.

Any ideas on how to accomplish this?

Any help would be appreciated.

Thanks,

KindlePower
Don Shields 650 on at

Like (0)

Report

Re: Re: AX 2009 SSRS Date Range Parameters

I was just inquiring as to if John had gotten an answer to the original question. Your code will work fine. The issue is however that if you add a query datasource and select a query from AX with a Date field as the range then VS will add a single parameter field as a type system.datetime. This field defaults to a date select control and will not allow you to enter a value as above or fill in a function returning a string as a default. What I found is that you can manually change the type of the parameter field to system.string and then enter a date like 1/1/2010..3/31/2010 or use a function like you suggested.

Don