Date parameter problem

I found a possible solution to my problem in a previous post.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1177748&SiteID=1

I need to do the same thing. However, I have problem in getting the exposed parameter to the hidden parameter. My date dimension is not continuous. If the user select a date that doesn't have the correct date in the dimension, how can I find the nearest one?

Best bet is to use the OLAP data set builder in SSRS and set parameters there. It should build a date parameter for you that populates from your cube. The only dates displayed would then be valid dates in the dimension.

Good luck,

Bryan

|||The problem with this approach is a result of long date list after running the cube for years.

I have used the method of passing parameter to report viewer through another web page. The only problem that I got is about the parameter value. I understand that the parameter must use the format like [Dim Name].[Att name].[Value]. This solves most of my problem now.

Thanks for your input.|||

Alex,

I struggled with MDX date parameters in SSRS for a while. In the end, the approach I adopted was the following:

1. create a visible non-queried parameter strongly typed as date. (say it's called FromDate)

2. create a hidden parameter typed as a string (say, FromDateHiddenString)

3. set the default value for the hidden parameter to be somethign like: ="[TIME].[Date].&[" & Format(Parameters!FromDate.Value, "yyyy-MM-dd") & "T00:00:00]"

Benefit of this is that you get a calendar picker for the date (FromDate). Then the value chosen by the user is passed into the hidden parameter (FromDateHiddenString), which is the parameter linked to your mdx query.

Unfortunately you cannot restrict the dates available via the calendar. but I've found that it drives the users more crazy to have to scroll through a massive list of dates, than to get no data when they choose an invalid date (after all, they generally know which dates they are interested in!)

i am also in HKSAR struggling to learn SSRS/SSAS/SSIS/MDX .. let me know if you want ot chat offline.

tx,

JG

|||Dear JG,

I've found this trick in somewhere else before. This seems nice but it can't suit my need. The main problem is I can't guarantee the date dimension is continuous. If the user pick that missing date from the date picker unluckily, they will not understand what's happening... after that, complain may come saying the report doesn't working. So, my current approach is creating another aspx and use the ReportViewer control. Then, I can have full control over the date selection. More than that, users will be happier to have a similar web interface to access their reports.

Regards,
Alex