I am trying to customize an SSRS report which is bundled with TFS. The current report displays data only for "Active" bugs. I need to change it to display "New" and "Approved" bugs.
Explanation: In our environment we consider New/Approved bugs as Active bugs, and we do not use Active status.
These values need to be hard-coded, so there is no need to create parameters for these.
The current MDX query in report is as below:
SELECT
{
[Measures].[Work Item Count]
} ON COLUMNS,
{
// Filter out people who don't currently have any work items assigned to them.
NonEmpty
(
CrossJoin
(
//
// This block of code gets the top 10 users, based on how many active bugs they have.
//
TopCount(
NonEmpty(
[Work Item].[System_AssignedTo].Children,
[Measures].[Work Item Count]
),
@TopUserCount,
[Measures].[Work Item Count]
),
[Work Item].[Microsoft_VSTS_Common_Priority].[All].Children,
[Work Item].[Microsoft_VSTS_Common_Severity].[All].Children
),
[Measures].[Work Item Count]
)
} ON ROWS
FROM
(
SELECT
CrossJoin(
StrToMember("[Team Project].[Project Node GUID].&[{" + @ProjectGuid + "}]"),
StrToMember("[Work Item].[System_WorkItemType].[" + @BugName + "]"),
StrToMember("[Work Item].[System_State].&[" + @ActiveName + "]"),
StrToSet(@AreaParam),
StrToSet(@IterationParam),
StrToSet(@PriorityParam),
StrToSet(@SeverityParam)
) ON COLUMNS
FROM [Team System]
I was suggested to change below line:
StrToMember("[Work Item].[System_State].[" + @ActiveName + "]"),
to this:
StrToSet("{[Work Item].[System_State].&[New],[Work Item].[System_State].&[Approved]}")
I tried this and it show and error while executing report:
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'dsCurrentBugCountAllUsers'. (rsErrorExecutingCommand)
Query (23, 4) Parser: The syntax for 'StrToSet' is incorrect.
I also tried changing it to this:
StrToSet("{[Work Item].[System_State].[New],[Work Item].[System_State].[Approved]}")
and this:
StrToSet("{[Work Item].[System_State].[New]}")
StrToSet("{[Work Item].[System_State].&[New]}")
All of these gives same error as above.
Can someone advise me what I am doing wrong?
Note: I am a complete n00b in MDX.
I believe you want single quotes rather than double. For example, this works against the AdventureWorks cube.
SELECT StrToSet
('{[Geography].[Geography].[Country].[Germany],[Geography].[Geography].[Country].[Canada]}')
ON 0
FROM [Adventure Works]