I have created an MDX report that uses below query:
SELECT
NON EMPTY
{
[Measures].[Anchor Production Duration]
,[Measures].[Duration]
,[Measures].[Current Work Standard]
,[Measures].[Loss Point Value]
,[Measures].[Efficiency Current Work Standard]
,[Measures].[Anchor EV Value]
,[Measures].[Plant Operating]
,[Measures].[Anchor CWS]
} ON COLUMNS
,NONEMPTY
({
[NewProduct].[Product Name].[Product Name].ALLMEMBERS*
[AssetOrganizational].[PLANT NAME].[PLANT NAME].ALLMEMBERS*
[ProcessParameter2].[PARAM NAME].[PARAM NAME].ALLMEMBERS*
[DayOfShift].[DATE ID].[DATE ID].ALLMEMBERS*
[AssetOrganizational].[ASSET NAME].[ASSET NAME].ALLMEMBERS
},[Measures].[Anchor Production Duration])
DIMENSION PROPERTIES
MEMBER_CAPTION
,MEMBER_UNIQUE_NAME
ON ROWS
FROM
(
SELECT
StrToMember
(@StartDate
,CONSTRAINED
)
:
StrToMember
(@EndDate
,CONSTRAINED
)
ON COLUMNS
FROM
(
SELECT
StrToMember
(@PlantId
,CONSTRAINED
)
ON COLUMNS
FROM [FusionNewCube]
)
)
The @StartDate and @EndDate parameters have following parameter value expressions:
="[DayOfShift].[DATE ID].&[" + Format(CDate(Parameters!StartDate.Value), "yyyy-MM-ddT00:00:00") + "]"
="[DayOfShift].[DATE ID].&[" + Format(CDate(Parameters!EndDate.Value), "yyyy-MM-ddT00:00:00") + "]"
This works fine for me. Now the issue is that these above parameter expressions help me find measure values for the StartDate to EndDate range which is not inclusive of time.
I want to query the cube with datetime values also.
The cube also has another dimension which I understand, can satisfy this requirement. It is as below.
[DayOfMinute].[Granularity].[MINUTE ID].&[2010D287HR12MIN45]
Here, in above example, D287 refers to 287th day of the year(as per my understanding of this cube) . I have tried to use this dimension for my date parameter expression but in vain. I am not sure how to format the date expression for the 287th day. See
below tried implementation which fails (have also tried a few other implementaions).
="[DayOfMinute].[Granularity].[MINUTE ID].&[" + Format(CDate(Parameters!StartDate.Value), "yyyyDdddHRhhMINmm") + "]"
="[DayOfMinute].[Granularity].[MINUTE ID].&[" + Format(CDate(Parameters!EndDate.Value), "yyyyDdddHRhhMINmm") + "]"
I get the error as follows:The Restrictions imposed by the constrained flag in the STRTOMEMBER function were violated.
I need to pass either date only, or datetime to @StartDate and @EndDate parameters depending on a flag setting in my UI. I am not sure if I can achieve this by using the same parameters for both of these different type of inputs. (I am using Calender Control
in my SSRS 2008 report for @StartDate and @EndDate parameters. Depending on the flag setting on the UI, if flag is set for datetime values, I need to retrieve the datetime values, from relational database tables by calling a custom function, for dates specified
by user in Calender control. These I will then pass to my mdx date parameters.)
Do I need to go for a linked server implementation where I can call the mdx query inside a SQL Stored procedure if it is not possible this way.
Please suggest a solution.
Thanks