Archive for May, 2010

MDX sets the date parameter to Text but it is nicer to have it display as a date time picker on Reporting Services report. Here is the function to use to convert it. Thanks to JJ, my colleague who  have written this generic function to do it. Other way to do it is to use the following formula directly on Step 4:
="[Date].[Date].[" & Format(Parameters!FromDate.Value, "yyyy-MM-dd") & "T00:00:00]"

Step 1.

In Design Mode, right Click outside of the report body and select Report Properties.  Then Select code and put the following Function in there:


Function ParseDateToMDX( ByVal dimentionStr AS String, ByVal sDate AS DateTime)

                Dim returnValue AS String

                Dim yr AS String

                Dim mnth AS String

                Dim Dy AS String


                yr = YEAR(sDate)


                mnth = IIF(MONTH(sDate) >9, MONTH(sDate).ToString(), "0" & MONTH(sDate).ToString())


                Dy = IIF(DAY(sDate) > 9, DAY(sDate).ToString(), "0" & DAY(sDate).ToString())


                returnValue = dimentionStr + ".&[" & yr & "-" & mnth & "-" & dy & "T00:00:00]"


                Return returnValue


End Function



Step 2.


Create your dimension filters on the query as per usual.  Example.  From Date.  The system will then create a parameter called FromDate automatically.


Step 3

Edit the PARAMETER FromDate.

General -> Change the Data Type to Date/Time

Available Values -> Change to NONE

Default Values -> You can create a default date expression typical to: =CDate(DateAdd("d",-8, Today))


Step 4

Right Click on your Query in Report Data, select DataSet Properties, then Paramerters.  Goto the FromDate Parameter and click the Equation Button.

Insert the following Code:


=Code.ParseDateToMDX("[Date].[Date]", CDate(Parameters!FromDate.Value))


The [Date].[Date] is the dimension in question and FromDate is your parameter name.



Step 5

Delete the queries that was generated by the system for the Parameters.


Read Full Post »

Take a look at this blog ..



Read Full Post »

%d bloggers like this: