I have an MDX query that has two calculated members that use dates.
Currently these dates are "hardcoded" in the calculated member definition.
I want to change this to use input parameters, thereby allowing the user to set the dates. I will then deploy the .rdl file to the Report Server. I have found no examples of using parameters in calculated members, so I am wondering if this is a bad idea.
I have explored the "filter wizard" in Visual Studio, and I am only offered the opportunity to filter on the fields that appear in the report.
I have made several attempts to alter the statement within Visual Studio 2005 while having the MDX statement in "Design Mode", but as soon as I attempt to execute the statement (or change to another view, such as "Preview", Visual Studio tells me that the "query cannot be retrieved from the query builder" and then continues with the (older) most valid query, which does not have reference to the parameter. (This is a Reporting Services project).
Here is a statement with hard coded dates that works properly.
WITH
MEMBER [Measures].[Completed Work On Period Start] AS
(
[Date].[Year Month Date].[Date].&[2006-04-30T00:00:00],
[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]
)
MEMBER [Measures].[Completed Work On Period End] AS
(
[Date].[Year Month Date].[Date].&[2006-05-06T00:00:00],
[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]
)
MEMBER [Measures].[Completed_Work] AS
(
[Measures].[Completed Work On Period End] - [Measures].[Completed Work On Period Start]
)
SELECT
NON EMPTY
(
FILTER
(
NONEMPTYCROSSJOIN
(
[Assigned To].[Person].[Person],
[Work Item].[System_Id].[System_Id],
[Work Item].[System_Title].[System_Title],
[Work Item].[System_WorkItemType].[System_WorkItemType],
[Measures].[Current Work Item Count],
4
),
[Measures].[Completed_Work] > 0
)
)
ON ROWS,
NON EMPTY
{
[Measures].[Work Item Url],
[Measures].[Completed_Work]
}
ON COLUMNS
FROM
[Team System]
You need to be in the MDX view to hand author the query to do this.
- In the Query Designer, switch to MDX view
- Change the query to be a parameterised query, e.g. replacing the date with StrToSet(@.Date, CONSTRAINED)
- Select the "Query Parameters" option from the toolbar, to define the parameter. You must provide a default value, that will be used when previewing/preparing the query
|||Following Paul's guidance, I was able to edit the query without VS flagging the query as invalid.
BTW: I used StrToMember instead of StrToSet, since my parameter was in the Members section. The results of the query are valid.
WITH
MEMBER [Measures].[Completed Work On Period Start] AS
(
(STRTOMember(@.prmStartDate)),
[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]
)
MEMBER [Measures].[Completed Work On Period End] AS
(
(STRTOMember(@.prmEndDate)),
[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]
)
MEMBER [Measures].[Completed_Work] AS
(
[Measures].[Completed Work On Period End] - [Measures].[Completed Work On Period Start]
)
SELECT
NON EMPTY
(
FILTER
(
NONEMPTYCROSSJOIN
(
[Assigned To].[Person].[Person],
[Work Item].[System_Id].[System_Id],
[Work Item].[System_Title].[System_Title],
[Work Item].[System_WorkItemType].[System_WorkItemType],
[Measures].[Current Work Item Count],
4
),
[Measures].[Completed_Work] > 0
)
)
ON ROWS,
NON EMPTY
{
[Measures].[Work Item Url],
[Measures].[Completed_Work]
}
ON COLUMNS
FROM
[Team System]
No comments:
Post a Comment