Working with Cross project Reports

Hi,

We have multiple team projects environment.
How can we produce a cross project reports

For example, we would like to see all the active bugs
in all team projects.

Thanks,

Michael

Answer this question

Working with Cross project Reports

Cameron Gocke
Hi,

I would like to change the Work Items report (Work Items.rdl which located in
the TFS Server), so I will be able to choose parameters from all Team Projects
fields (Build, Area, Iteration..).

Can you post this file (marked with all the changes) as a sample

Thanks in advance,

Michael
Pedro Ruivo
Hi,

Which parameter I should remove from the RDL files

Can you please send an example

Thanks,

Michael
DarrellMerryweather
you want to change the queries on the report

Typically every report has a bunch of parameters (these are the drop downs that you specify at the top of the report) which allow you to filter the body of the report. For each parameter will have one or two queries. One of the queries returns the allowed list of values, the other parameter specifies which values should be checked from this list. Then the main body of the report has a main query. The main query returns you the body of the report.

When Reporting Services renders a report first every parameter query is executed. Then once parameters are specified the main query is executed.

There are couple of report parameters that are filtered by Project (for example Found In Build, Fixed in Build etc...), there are couple of others that are not like State (since most of the time the same projects will share the same set of states (Active/Resolved/Closed))

When you want to make a report work accross projects you will need to modify the report queries (parameter or main query) that filter by project. you will need to take out statements like:
where
{
...
,@Project <-- you will need to remove this from the where clause
}

If you have a particular report in mind you want to make run accross projects you can send it to me (danta(donotspam)@microsoft.com) and I can modify it for you and post it here as a sample.
Andrew Conrad - MSFT

If you ware using December CTP this approach should work;

replace the Project parameter in the report:

ORIGINAL:

<ReportParameter Name="Project">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>=IIF(LEN(Parameters!ExplicitProject.Value) > 0,Parameters!ExplicitProject.Value,SPLIT(Globals!ReportFolder, "/").GetValue(IIF(split(Globals!ReportFolder, "/").Length > 1, 1, 0)))</Value>
</Values>
</DefaultValue>
<Prompt>Project</Prompt>
<Hidden>true</Hidden>
</ReportParameter>

REPLACEMENT:

<ReportParameter Name="Project">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>All</Value>
</Values>
</DefaultValue>
<Prompt>Project</Prompt>
<Hidden>true</Hidden>
</ReportParameter>

For beta 3 here is the work items report that will display you all the work items regardless of project:

< xml version="1.0" encoding="utf-8" >
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
    <DataSource Name="TfsOlapReportDS">
      <DataSourceReference>TfsOlapReportDS</DataSourceReference>
      <rd:DataSourceID>b692e00b-b1c4-4dbc-b73a-aca72a7efb5d</rd:DataSourceID>
    </DataSource>
    <DataSource Name="TfsReportDS">
      <DataSourceReference>TfsReportDS</DataSourceReference>
      <rd:DataSourceID>96d7e5d2-7af4-46b6-8a5f-b695a67b1d04</rd:DataSourceID>
    </DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<PageWidth>11in</PageWidth>
<ReportParameters>
    <ReportParameter Name="Project">
      <DataType>String</DataType>
      <Prompt>Project</Prompt>
    </ReportParameter>
    <ReportParameter Name="Iteration">
      <DataType>String</DataType>
      <DefaultValue>
        <DataSetReference>
          <DataSetName>dsIterationDefault</DataSetName>
          <ValueField>ParameterValue</ValueField>
        </DataSetReference>
      </DefaultValue>
      <Prompt>Iteration</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>dsIterationParam</DataSetName>
          <ValueField>ParameterValue</ValueField>
          <LabelField>ParameterCaption</LabelField>
        </DataSetReference>
      </ValidValues>
      <MultiValue>true</MultiValue>
    </ReportParameter>
    <ReportParameter Name="Area">
      <DataType>String</DataType>
      <DefaultValue>
        <DataSetReference>
          <DataSetName>dsAreaDefault</DataSetName>
          <ValueField>ParameterValue</ValueField>
        </DataSetReference>
      </DefaultValue>
      <Prompt>Area</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>dsAreaParam</DataSetName>
          <ValueField>ParameterValue</ValueField>
          <LabelField>ParameterCaption</LabelField>
        </DataSetReference>
      </ValidValues>
      <MultiValue>true</MultiValue>
    </ReportParameter>
    <ReportParameter Name="WorkItemType">
      <DataType>String</DataType>
      <DefaultValue>
        <DataSetReference>
          <DataSetName>dsWorkItemTypeParam</DataSetName>
          <ValueField>ParameterValue</ValueField>
        </DataSetReference>
      </DefaultValue>
      <Prompt>Work Item Type</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>dsWorkItemTypeParam</DataSetName>
          <ValueField>ParameterValue</ValueField>
          <LabelField>ParameterCaption</LabelField>
        </DataSetReference>
      </ValidValues>
      <MultiValue>true</MultiValue>
    </ReportParameter>
    <ReportParameter Name="AssignedTo">
      <DataType>String</DataType>
      <DefaultValue>
        <DataSetReference>
          <DataSetName>dsAssignedToParam</DataSetName>
          <ValueField>ParameterValue</ValueField>
        </DataSetReference>
      </DefaultValue>
      <Prompt>Assigned To</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>dsAssignedToParam</DataSetName>
          <ValueField>ParameterValue</ValueField>
          <LabelField>ParameterCaption</LabelField>
        </DataSetReference>
      </ValidValues>
      <MultiValue>true</MultiValue>
    </ReportParameter>
    <ReportParameter Name="State">
      <DataType>String</DataType>
      <DefaultValue>
        <DataSetReference>
          <DataSetName>dsStateDefault</DataSetName>
          <ValueField>ParameterValue</ValueField>
        </DataSetReference>
      </DefaultValue>
      <Prompt>State</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>dsStateParam</DataSetName>
          <ValueField>ParameterValue</ValueField>
          <LabelField>ParameterCaption</LabelField>
        </DataSetReference>
      </ValidValues>
      <MultiValue>true</MultiValue>
    </ReportParameter>
    <ReportParameter Name="Priority">
      <DataType>String</DataType>
      <DefaultValue>
        <DataSetReference>
          <DataSetName>dsPriorityParam</DataSetName>
          <ValueField>ParameterValue</ValueField>
        </DataSetReference>
      </DefaultValue>
      <Prompt>Priority</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>dsPriorityParam</DataSetName>
          <ValueField>ParameterValue</ValueField>
          <LabelField>ParameterCaption</LabelField>
        </DataSetReference>
      </ValidValues>
      <MultiValue>true</MultiValue>
    </ReportParameter>
    <ReportParameter Name="Issue">
      <DataType>String</DataType>
      <DefaultValue>
        <DataSetReference>
          <DataSetName>dsIssueParam</DataSetName>
          <ValueField>ParameterValue</ValueField>
        </DataSetReference>
      </DefaultValue>
      <Prompt>Issue</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>dsIssueParam</DataSetName>
          <ValueField>ParameterValue</ValueField>
          <LabelField>ParameterCaption</LabelField>
        </DataSetReference>
      </ValidValues>
      <MultiValue>true</MultiValue>
    </ReportParameter>
    <ReportParameter Name="ExitCriteria">
      <DataType>String</DataType>
      <DefaultValue>
        <DataSetReference>
          <DataSetName>dsExitCriteriaParam</DataSetName>
          <ValueField>ParameterValue</ValueField>
        </DataSetReference>
      </DefaultValue>
      <Prompt>Exit Criteria</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>dsExitCriteriaParam</DataSetName>
          <ValueField>ParameterValue</ValueField>
          <LabelField>ParameterCaption</LabelField>
        </DataSetReference>
      </ValidValues>
      <MultiValue>true</MultiValue>
    </ReportParameter>
    <ReportParameter Name="FoundInBuild">
      <DataType>String</DataType>
      <DefaultValue>
        <DataSetReference>
          <DataSetName>dsFoundInBuildParam</DataSetName>
          <ValueField>ParameterValue</ValueField>
        </DataSetReference>
      </DefaultValue>
      <Prompt>Found In Build</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>dsFoundInBuildParam</DataSetName>
          <ValueField>ParameterValue</ValueField>
          <LabelField>ParameterCaption</LabelField>
        </DataSetReference>
      </ValidValues>
      <MultiValue>true</MultiValue>
    </ReportParameter>
    <ReportParameter Name="FixedInBuild">
      <DataType>String</DataType>
      <DefaultValue>
        <DataSetReference>
          <DataSetName>dsFixedInBuildParam</DataSetName>
          <ValueField>ParameterValue</ValueField>
        </DataSetReference>
      </DefaultValue>
      <Prompt>Fixed In Build</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>dsFixedInBuildParam</DataSetName>
          <ValueField>ParameterValue</ValueField>
          <LabelField>ParameterCaption</LabelField>
        </DataSetReference>
      </ValidValues>
      <MultiValue>true</MultiValue>
    </ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
    <ReportItems>
      <Textbox Name="textbox13">
        <Left>0.125in</Left>
        <Top>0.125in</Top>
        <ZIndex>3</ZIndex>
        <Width>6in</Width>
        <Style>
          <PaddingLeft>2pt</PaddingLeft>
          <FontFamily>Tahoma</FontFamily>
          <FontSize>8pt</FontSize>
          <Color>Gray</Color>
          <PaddingRight>2pt</PaddingRight>
        </Style>
        <CanGrow>true</CanGrow>
        <Height>0.125in</Height>
        <Value>="Report Generated: " + Globals!ExecutionTime + " by " + User!UserID + "; Last Warehouse Update: " + First(Fields!LastProcessedTime.Value, "dsLastProcessedTime")</Value>
      </Textbox>
      <Textbox Name="textbox20">
        <Left>0.125in</Left>
        <ZIndex>2</ZIndex>
        <Width>6in</Width>
        <Style>
          <PaddingLeft>2pt</PaddingLeft>
          <PaddingBottom>2pt</PaddingBottom>
          <FontFamily>tahoma</FontFamily>
          <FontWeight>700</FontWeight>
          <Color>Gray</Color>
          <PaddingRight>2pt</PaddingRight>
          <PaddingTop>2pt</PaddingTop>
        </Style>
        <CanGrow>true</CanGrow>
        <Height>0.125in</Height>
        <Value>=Globals!ReportName</Value>
      </Textbox>
      <Textbox Name="textbox21">
        <Left>0.125in</Left>
        <Top>0.375in</Top>
        <ZIndex>1</ZIndex>
        <Width>6in</Width>
        <Style>
          <PaddingLeft>2pt</PaddingLeft>
          <FontFamily>tahoma</FontFamily>
          <FontSize>8pt</FontSize>
          <PaddingRight>2pt</PaddingRight>
        </Style>
        <CanGrow>true</CanGrow>
        <Height>0.125in</Height>
        <Value>What are our current work items </Value>
      </Textbox>
      <Table Name="table2">
        <Left>0.125in</Left>
        <DataSetName>dsCurrentWorkItem</DataSetName>
        <Top>0.5in</Top>
        <Details>
          <TableRows>
            <TableRow>
              <TableCells>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="ID">
                      <ZIndex>7</ZIndex>
                      <Action>
                        <Hyperlink>=Fields!Work_Item_Artifact_Url.Value</Hyperlink>
                      </Action>
                      <Style>
                        <BorderStyle>
                          <Bottom>Solid</Bottom>
                          <Top>Solid</Top>
                        </BorderStyle>
                        <TextAlign>Left</TextAlign>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingBottom>2pt</PaddingBottom>
                        <FontFamily>Tahoma</FontFamily>
                        <FontSize>8pt</FontSize>
                        <BorderColor>
                          <Bottom>#f1efe2</Bottom>
                          <Top>#f1efe2</Top>
                          <Default>White</Default>
                        </BorderColor>
                        <BackgroundColor>White</BackgroundColor>
                        <Color>Blue</Color>
                        <TextDecoration>Underline</TextDecoration>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                      </Style>
                      <CanGrow>true</CanGrow>
                      <Value>=Fields!Work_Item_ID.Value</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="AssignedTo">
                      <ZIndex>6</ZIndex>
                      <Style>
                        <BorderStyle>
                          <Bottom>Solid</Bottom>
                          <Top>Solid</Top>
                        </BorderStyle>
                        <TextAlign>Left</TextAlign>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingBottom>2pt</PaddingBottom>
                        <FontFamily>Tahoma</FontFamily>
                        <FontSize>8pt</FontSize>
                        <BorderColor>
                          <Bottom>#f1efe2</Bottom>
                          <Top>#f1efe2</Top>
                          <Default>White</Default>
                        </BorderColor>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                      </Style>
                      <CanGrow>true</CanGrow>
                      <Value>=Fields!Person.Value</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="State">
                      <rd:DefaultName>State</rd:DefaultName>
                      <ZIndex>5</ZIndex>
                      <Style>
                        <BorderStyle>
                          <Bottom>Solid</Bottom>
                          <Top>Solid</Top>
                        </BorderStyle>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingBottom>2pt</PaddingBottom>
                        <FontFamily>Tahoma</FontFamily>
                        <FontSize>8pt</FontSize>
                        <BorderColor>
                          <Bottom>#f1efe2</Bottom>
                          <Top>#f1efe2</Top>
                          <Default>White</Default>
                        </BorderColor>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                      </Style>
                      <CanGrow>true</CanGrow>
                      <Value>=Fields!State.Value</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="RemainingHours">
                      <ZIndex>4</ZIndex>
                      <Style>
                        <BorderStyle>
                          <Bottom>Solid</Bottom>
                          <Top>Solid</Top>
                        </BorderStyle>
                        <TextAlign>Left</TextAlign>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingBottom>2pt</PaddingBottom>
                        <FontFamily>Tahoma</FontFamily>
                        <FontSize>8pt</FontSize>
                        <BorderColor>
                          <Bottom>#f1efe2</Bottom>
                          <Top>#f1efe2</Top>
                          <Default>White</Default>
                        </BorderColor>
                        <BackgroundColor>White</BackgroundColor>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                      </Style>
                      <CanGrow>true</CanGrow>
                      <Value>=Fields!Current_Work_Item_Remaining_Work.Value</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="CompletedHours">
                      <ZIndex>3</ZIndex>
                      <Style>
                        <BorderStyle>
                          <Bottom>Solid</Bottom>
                          <Top>Solid</Top>
                        </BorderStyle>
                        <TextAlign>Left</TextAlign>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingBottom>2pt</PaddingBottom>
                        <FontFamily>Tahoma</FontFamily>
                        <FontSize>8pt</FontSize>
                        <BorderColor>
                          <Bottom>#f1efe2</Bottom>
                          <Top>#f1efe2</Top>
                          <Default>White</Default>
                        </BorderColor>
                        <BackgroundColor>White</BackgroundColor>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                      </Style>
                      <CanGrow>true</CanGrow>
                      <Value>=Fields!Current_Work_Item_Completed_Work.Value</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="WorkItemType">
                      <ZIndex>2</ZIndex>
                      <Style>
                        <BorderStyle>
                          <Bottom>Solid</Bottom>
                          <Top>Solid</Top>
                        </BorderStyle>
                        <TextAlign>Left</TextAlign>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingBottom>2pt</PaddingBottom>
                        <FontFamily>Tahoma</FontFamily>
                        <FontSize>8pt</FontSize>
                        <BorderColor>
                          <Bottom>#f1efe2</Bottom>
                          <Top>#f1efe2</Top>
                          <Default>White</Default>
                        </BorderColor>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                      </Style>
                      <CanGrow>true</CanGrow>
                      <Value>=Fields!Work_Item_Type.Value</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="Priority">
                      <ZIndex>1</ZIndex>
                      <Style>
                        <BorderStyle>
                          <Bottom>Solid</Bottom>
                          <Top>Solid</Top>
                        </BorderStyle>
                        <TextAlign>Left</TextAlign>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingBottom>2pt</PaddingBottom>
                        <FontFamily>Tahoma</FontFamily>
                        <FontSize>8pt</FontSize>
                        <BorderColor>
                          <Bottom>#f1efe2</Bottom>
                          <Top>#f1efe2</Top>
                          <Default>White</Default>
                        </BorderColor>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                      </Style>
                      <CanGrow>true</CanGrow>
                      <Value>=Fields!Priority.Value</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="Title">
                      <rd:DefaultName>Title</rd:DefaultName>
                      <Style>
                        <BorderStyle>
                          <Bottom>Solid</Bottom>
                          <Top>Solid</Top>
                        </BorderStyle>
                        <TextAlign>Left</TextAlign>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingBottom>2pt</PaddingBottom>
                        <FontFamily>Tahoma</FontFamily>
                        <FontSize>8pt</FontSize>
                        <BorderColor>
                          <Bottom>#f1efe2</Bottom>
                          <Top>#f1efe2</Top>
                          <Default>White</Default>
                        </BorderColor>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                      </Style>
                      <CanGrow>true</CanGrow>
                      <Value>=Fields!Title.Value</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
              </TableCells>
              <Height>12.8pt</Height>
            </TableRow>
          </TableRows>
          <Sorting>
            <SortBy>
              <SortExpression>=Val(Fields!Work_Item_ID.Value)</SortExpression>
              <Direction>Ascending</Direction>
            </SortBy>
          </Sorting>
        </Details>
        <Style>
          <BorderWidth>
            <Top>2pt</Top>
            <Default>2pt</Default>
          </BorderWidth>
          <FontFamily>Verdana</FontFamily>
        </Style>
        <Header>
          <TableRows>
            <TableRow>
              <TableCells>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="IDHeader">
                      <ZIndex>15</ZIndex>
                      <Style>
                        <BorderStyle>
                          <Bottom>Solid</Bottom>
                          <Top>Solid</Top>
                        </BorderStyle>
                        <TextAlign>Left</TextAlign>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingBottom>2pt</PaddingBottom>
                        <FontFamily>Tahoma</FontFamily>
                        <FontSize>8pt</FontSize>
                        <BorderColor>
                          <Bottom>#f1efe2</Bottom>
                          <Top>#f1efe2</Top>
                          <Default>White</Default>
                        </BorderColor>
                        <BackgroundColor>#f1efe2</BackgroundColor>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                      </Style>
                      <CanGrow>true</CanGrow>
                      <UserSort>
                        <SortExpression>=Val(Fields!Work_Item_ID.Value)</SortExpression>
                      </UserSort>
                      <Value>ID</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="AssignedToHeader">
                      <ZIndex>14</ZIndex>
                      <Style>
                        <BorderStyle>
                          <Bottom>Solid</Bottom>
                          <Top>Solid</Top>
                        </BorderStyle>
                        <TextAlign>Left</TextAlign>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingBottom>2pt</PaddingBottom>
                        <FontFamily>Tahoma</FontFamily>
                        <FontSize>8pt</FontSize>
                        <BorderColor>
                          <Bottom>#f1efe2</Bottom>
                          <Top>#f1efe2</Top>
                          <Default>White</Default>
                        </BorderColor>
                        <BackgroundColor>#f1efe2</BackgroundColor>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                      </Style>
                      <CanGrow>true</CanGrow>
                      <UserSort>
                        <SortExpression>=Fields!Person.Value</SortExpression>
                      </UserSort>
                      <Value>Assigned To</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="StateHeader">
                      <ZIndex>13</ZIndex>
                      <Style>
                        <BorderStyle>
                          <Bottom>Solid</Bottom>
                          <Top>Solid</Top>
                        </BorderStyle>
                        <TextAlign>Left</TextAlign>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingBottom>2pt</PaddingBottom>
                        <FontFamily>Tahoma</FontFamily>
                        <FontSize>8pt</FontSize>
                        <BorderColor>
                          <Bottom>#f1efe2</Bottom>
                          <Top>#f1efe2</Top>
                          <Default>White</Default>
                        </BorderColor>
                        <BackgroundColor>#f1efe2</BackgroundColor>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                      </Style>
                      <CanGrow>true</CanGrow>
                      <UserSort>
                        <SortExpression>=Fields!State.Value</SortExpression>
                      </UserSort>
                      <Value>State</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="RemainingHoursHeader">
                      <ZIndex>12</ZIndex>
                      <Style>
                        <BorderStyle>
                          <Bottom>Solid</Bottom>
                          <Top>Solid</Top>
                        </BorderStyle>
                        <TextAlign>Left</TextAlign>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingBottom>2pt</PaddingBottom>
                        <FontFamily>Tahoma</FontFamily>
                        <FontSize>8pt</FontSize>
                        <BorderColor>
                          <Bottom>#f1efe2</Bottom>
                          <Top>#f1efe2</Top>
                          <Default>White</Default>
                        </BorderColor>
                        <BackgroundColor>#f1efe2</BackgroundColor>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                      </Style>
                      <CanGrow>true</CanGrow>
                      <UserSort>
                        <SortExpression>=Val(Fields!Current_Work_Item_Remaining_Work.Value)</SortExpression>
                      </UserSort>
                      <Value>Rem. Hrs.</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="CompletedHoursHeader">
                      <ZIndex>11</ZIndex>
                      <Style>
                        <BorderStyle>
                          <Bottom>Solid</Bottom>
                          <Top>Solid</Top>
                        </BorderStyle>
                        <TextAlign>Left</TextAlign>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingBottom>2pt</PaddingBottom>
                        <FontFamily>Tahoma</FontFamily>
                        <FontSize>8pt</FontSize>
                        <BorderColor>
                          <Bottom>#f1efe2</Bottom>
                          <Top>#f1efe2</Top>
                          <Default>White</Default>
                        </BorderColor>
                        <BackgroundColor>#f1efe2</BackgroundColor>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                      </Style>
                      <CanGrow>true</CanGrow>
                      <UserSort>
                        <SortExpression>=Val(Fields!Current_Work_Item_Completed_Work.Value)</SortExpression>
                      </UserSort>
                      <Value>Comp. Hrs.</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="WorkItemTypeHeader">
                      <ZIndex>10</ZIndex>
                      <Style>
                        <BorderStyle>
                          <Bottom>Solid</Bottom>
                          <Top>Solid</Top>
                        </BorderStyle>
                        <TextAlign>Left</TextAlign>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingBottom>2pt</PaddingBottom>
                        <FontFamily>Tahoma</FontFamily>
                        <FontSize>8pt</FontSize>
                        <BorderColor>
                          <Bottom>#f1efe2</Bottom>
                          <Top>#f1efe2</Top>
                          <Default>White</Default>
                        </BorderColor>
                        <BackgroundColor>#f1efe2</BackgroundColor>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                      </Style>
                      <CanGrow>true</CanGrow>
                      <UserSort>
                        <SortExpression>=Fields!Work_Item_Type.Value</SortExpression>
                      </UserSort>
                      <Value>WI Type</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="PriorityHeader">
                      <ZIndex>9</ZIndex>
                      <Style>
                        <BorderStyle>
                          <Bottom>Solid</Bottom>
                          <Top>Solid</Top>
                        </BorderStyle>
                        <TextAlign>Left</TextAlign>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingBottom>2pt</PaddingBottom>
                        <FontFamily>Tahoma</FontFamily>
                        <FontSize>8pt</FontSize>
                        <BorderColor>
                          <Bottom>#f1efe2</Bottom>
                          <Top>#f1efe2</Top>
                          <Default>White</Default>
                        </BorderColor>
                        <BackgroundColor>#f1efe2</BackgroundColor>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                      </Style>
                      <CanGrow>true</CanGrow>
                      <UserSort>
                        <SortExpression>=Fields!Priority.Value</SortExpression>
                      </UserSort>
                      <Value>Priority</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="TitleHeader">
                      <ZIndex>8</ZIndex>
                      <Style>
                        <BorderStyle>
                          <Bottom>Solid</Bottom>
                          <Top>Solid</Top>
                        </BorderStyle>
                        <TextAlign>Left</TextAlign>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingBottom>2pt</PaddingBottom>
                        <FontFamily>Tahoma</FontFamily>
                        <FontSize>8pt</FontSize>
                        <BorderColor>
                          <Bottom>#f1efe2</Bottom>
                          <Top>#f1efe2</Top>
                          <Default>White</Default>
                        </BorderColor>
                        <BackgroundColor>#f1efe2</BackgroundColor>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                      </Style>
                      <CanGrow>true</CanGrow>
                      <Value>Title</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
              </TableCells>
              <Height>12.8pt</Height>
            </TableRow>
          </TableRows>
          <RepeatOnNewPage>true</RepeatOnNewPage>
        </Header>
        <TableColumns>
          <TableColumn>
            <Width>0.75in</Width>
          </TableColumn>
          <TableColumn>
            <Width>1in</Width>
          </TableColumn>
          <TableColumn>
            <Width>0.75in</Width>
          </TableColumn>
          <TableColumn>
            <Width>1in</Width>
          </TableColumn>
          <TableColumn>
            <Width>1in</Width>
          </TableColumn>
          <TableColumn>
            <Width>0.875in</Width>
          </TableColumn>
          <TableColumn>
            <Width>0.875in</Width>
          </TableColumn>
          <TableColumn>
            <Width>2.625in</Width>
          </TableColumn>
        </TableColumns>
      </Table>
    </ReportItems>
    <Height>0.85556in</Height>
</Body>
<rd:ReportID>68731260-8ad1-4b5e-b8b9-0ac476fddd4b</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
    <DataSet Name="dsCurrentWorkItem">
      <Query>
        <rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>
        <CommandText>
SELECT
    {
        [Measures].[Work Item Artifact Url],
        [Measures].[Microsoft_VSTS_Scheduling_CompletedWork],
        [Measures].[Microsoft_VSTS_Scheduling_RemainingWork]
    } ON COLUMNS,
    NONEMPTYCROSSJOIN
    (
  [Work Item ID].[Work Item ID].[Work Item ID],
  [Work Item ID].[Work Item Artifact Moniker].[Work Item Artifact Moniker],
  [Work Item ID].[Title].[Title],
  DESCENDANTS(STRTOSET(@dsAssignedToParam),[Assigned To].[Person].[Person]),
  --DESCENDANTS(STRTOSET("[Assigned To].[Person].[Person]"),[Assigned To].[Person].[Person]),
  DESCENDANTS(STRTOSET(@dsStateParam),[State].[State].[State]),
  --DESCENDANTS(STRTOSET("[State].[State].[All]"),[State].[State].[State]),
  DESCENDANTS(STRTOSET(@dsPriorityParam),[Microsoft_VSTS_Common_Priority].[Microsoft_VSTS_Common_Priority].[Microsoft_VSTS_Common_Priority]),
  --DESCENDANTS(STRTOSET("[Microsoft_VSTS_Common_Priority].[Microsoft_VSTS_Common_Priority].[All]"),[Microsoft_VSTS_Common_Priority].[Microsoft_VSTS_Common_Priority].[Microsoft_VSTS_Common_Priority]),
  DESCENDANTS(STRTOSET(@dsWorkItemTypeParam),[System_WorkItemType].[System_WorkItemType].[System_WorkItemType]),
  --DESCENDANTS(STRTOSET("[System_WorkItemType].[System_WorkItemType].[All]"),[System_WorkItemType].[System_WorkItemType].[System_WorkItemType]),
        [Measures].[Current Work Item Count],
        7
    ) ON ROWS
FROM [Team System]
WHERE
(
    --STRTOSET("[Team Project].[Team Project].[Team Project]"),
    --STRTOSET(@Project),
    --STRTOSET("[Iteration].[Iteration Path].[Iteration Path]"),
    STRTOSET(@dsIterationParam),
    --STRTOSET("[Area].[Area Path].[Area Path]"),
    STRTOSET(@dsAreaParam),
    --STRTOSET("[Microsoft_VSTS_Common_Issue].[Microsoft_VSTS_Common_Issue].[Microsoft_VSTS_Common_Issue]"),
    STRTOSET(@dsIssueParam),
    --STRTOSET("[Microsoft_VSTS_Common_ExitCriteria].[Microsoft_VSTS_Common_ExitCriteria].[Microsoft_VSTS_Common_ExitCriteria]"),
    STRTOSET(@dsExitCriteriaParam),
    --STRTOSET("[Microsoft_VSTS_Build_FoundIn].[Build].[All]"),
    STRTOSET(@dsFoundInBuildParam),
    --STRTOSET("[Microsoft_VSTS_Build_IntegrationBuild].[Build].[All]")
    STRTOSET(@dsFixedInBuildParam)
)
</CommandText>
        <QueryParameters>
          <QueryParameter Name="Project">
            <Value>="[Team Project].[Team Project].[" + Parameters!Project.Value + "]"</Value>
          </QueryParameter>
          <QueryParameter Name="dsIterationParam">
            <Value>=Parameters!Iteration.Value</Value>
          </QueryParameter>
          <QueryParameter Name="dsAreaParam">
            <Value>=Parameters!Area.Value</Value>
          </QueryParameter>
          <QueryParameter Name="dsWorkItemTypeParam">
            <Value>=Parameters!WorkItemType.Value</Value>
          </QueryParameter>
          <QueryParameter Name="dsAssignedToParam">
            <Value>=Parameters!AssignedTo.Value</Value>
          </QueryParameter>
          <QueryParameter Name="dsStateParam">
            <Value>=Parameters!State.Value</Value>
          </QueryParameter>
          <QueryParameter Name="dsPriorityParam">
            <Value>=Parameters!Priority.Value</Value>
          </QueryParameter>
          <QueryParameter Name="dsIssueParam">
            <Value>=Parameters!Issue.Value</Value>
          </QueryParameter>
          <QueryParameter Name="dsExitCriteriaParam">
            <Value>=Parameters!ExitCriteria.Value</Value>
          </QueryParameter>
          <QueryParameter Name="dsFoundInBuildParam">
            <Value>=Parameters!FoundInBuild.Value</Value>
          </QueryParameter>
          <QueryParameter Name="dsFixedInBuildParam">
            <Value>=Parameters!FixedInBuild.Value</Value>
          </QueryParameter>
        </QueryParameters>
        <DataSourceName>TfsOlapReportDS</DataSourceName>
        <rd:MdxQuery><QueryDefinition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="MDXQueryhttp://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification xsi:type="MDXQuerySpecification"><Select><Items><Item><ID xsi:type="Level"><DimensionName>Work Item ID</DimensionName><HierarchyName>Work Item ID</HierarchyName><HierarchyUniqueName>[Work Item ID].[Work Item ID]</HierarchyUniqueName><LevelName>Work Item ID</LevelName><UniqueName>[Work Item ID].[Work Item ID].[Work Item ID]</UniqueName></ID><ItemCaption>Work Item ID</ItemCaption></Item><Item><ID xsi:type="Level"><DimensionName>Work Item ID</DimensionName><HierarchyName>Work Item Artifact Moniker</HierarchyName><HierarchyUniqueName>[Work Item ID].[Work Item Artifact Moniker]</HierarchyUniqueName><LevelName>Work Item Artifact Moniker</LevelName><UniqueName>[Work Item ID].[Work Item Artifact Moniker].[Work Item Artifact Moniker]</UniqueName></ID><ItemCaption>Work Item Artifact Moniker</ItemCaption></Item><Item><ID xsi:type="Level"><DimensionName>Work Item ID</DimensionName><HierarchyName>Title</HierarchyName><HierarchyUniqueName>[Work Item ID].[Title]</HierarchyUniqueName><LevelName>Title</LevelName><UniqueName>[Work Item ID].[Title].[Title]</UniqueName></ID><ItemCaption>Title</ItemCaption></Item><Item><ID xsi:type="Level"><DimensionName>Assigned To</DimensionName><HierarchyName>Person</HierarchyName><HierarchyUniqueName>[Assigned To].[Person]</HierarchyUniqueName><LevelName>Person</LevelName><UniqueName>[Assigned To].[Person].[Person]</UniqueName></ID><ItemCaption>Person</ItemCaption></Item><Item><ID xsi:type="Level"><DimensionName>State</DimensionName><HierarchyName>State</HierarchyName><HierarchyUniqueName>[State].[State]</HierarchyUniqueName><LevelName>State</LevelName><UniqueName>[State].[State].[State]</UniqueName></ID><ItemCaption>State</ItemCaption></Item><Item><ID xsi:type="Level"><DimensionName>Microsoft_VSTS_Common_Priority</DimensionName><HierarchyName>Microsoft_VSTS_Common_Priority</HierarchyName><HierarchyUniqueName>[Microsoft_VSTS_Common_Priority].[Microsoft_VSTS_Common_Priority]</HierarchyUniqueName><LevelName>Microsoft_VSTS_Common_Priority</LevelName><UniqueName>[Microsoft_VSTS_Common_Priority].[Microsoft_VSTS_Common_Priority].[Microsoft_VSTS_Common_Priority]</UniqueName></ID><ItemCaption>Priority</ItemCaption></Item><Item><ID xsi:type="Level"><DimensionName>System_WorkItemType</DimensionName><HierarchyName>System_WorkItemType</HierarchyName><HierarchyUniqueName>[System_WorkItemType].[System_WorkItemType]</HierarchyUniqueName><LevelName>System_WorkItemType</LevelName><UniqueName>[System_WorkItemType].[System_WorkItemType].[System_WorkItemType]</UniqueName></ID><ItemCaption>Work Item Type</ItemCaption></Item><Item><ID xsi:type="Measure"><MeasureName>Work Item Artifact Url</MeasureName><UniqueName>[Measures].[Work Item Artifact Url]</UniqueName></ID><ItemCaption>Work Item Artifact Url</ItemCaption><FormattedValue>true</FormattedValue></Item><Item><ID xsi:type="Measure"><MeasureName>Microsoft_VSTS_Scheduling_CompletedWork</MeasureName><UniqueName>[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]</UniqueName></ID><ItemCaption>Completed Work</ItemCaption><FormattedValue>true</FormattedValue></Item><Item><ID xsi:type="Measure"><MeasureName>Microsoft_VSTS_Scheduling_RemainingWork</MeasureName><UniqueName>[Measures].[Microsoft_VSTS_Scheduling_RemainingWork]</UniqueName></ID><ItemCaption>Remaining Work</ItemCaption><FormattedValue>true</FormattedValue></Item></Items></Select><From>Team System</From><Filter><FilterItems /></Filter><Calculations /><Aggregates /><QueryProperties /></QuerySpecification><Query><Statement>
SELECT
    {
        [Measures].[Work Item Artifact Url],
        [Measures].[Microsoft_VSTS_Scheduling_CompletedWork],
        [Measures].[Microsoft_VSTS_Scheduling_RemainingWork]
    } ON COLUMNS,
    NONEMPTYCROSSJOIN
    (
  [Work Item ID].[Work Item ID].[Work Item ID],
  [Work Item ID].[Work Item Artifact Moniker].[Work Item Artifact Moniker],
  [Work Item ID].[Title].[Title],
  DESCENDANTS(STRTOSET(@dsAssignedToParam),[Assigned To].[Person].[Person]),
  --DESCENDANTS(STRTOSET("[Assigned To].[Person].[Person]"),[Assigned To].[Person].[Person]),
  DESCENDANTS(STRTOSET(@dsStateParam),[State].[State].[State]),
  --DESCENDANTS(STRTOSET("[State].[State].[All]"),[State].[State].[State]),
  DESCENDANTS(STRTOSET(@dsPriorityParam),[Microsoft_VSTS_Common_Priority].[Microsoft_VSTS_Common_Priority].[Microsoft_VSTS_Common_Priority]),
  --DESCENDANTS(STRTOSET("[Microsoft_VSTS_Common_Priority].[Microsoft_VSTS_Common_Priority].[All]"),[Microsoft_VSTS_Common_Priority].[Microsoft_VSTS_Common_Priority].[Microsoft_VSTS_Common_Priority]),
  DESCENDANTS(STRTOSET(@dsWorkItemTypeParam),[System_WorkItemType].[System_WorkItemType].[System_WorkItemType]),
  --DESCENDANTS(STRTOSET("[System_WorkItemType].[System_WorkItemType].[All]"),[System_WorkItemType].[System_WorkItemType].[System_WorkItemType]),
        [Measures].[Current Work Item Count],
        7
    ) ON ROWS
FROM [Team System]
WHERE
(
    --STRTOSET("[Team Project].[Team Project].[Team Project]"),
    --STRTOSET(@Project),
    --STRTOSET("[Iteration].[Iteration Path].[Iteration Path]"),
    STRTOSET(@dsIterationParam),
    --STRTOSET("[Area].[Area Path].[Area Path]"),
    STRTOSET(@dsAreaParam),
    --STRTOSET("[Microsoft_VSTS_Common_Issue].[Microsoft_VSTS_Common_Issue].[Microsoft_VSTS_Common_Issue]"),
    STRTOSET(@dsIssueParam),
    --STRTOSET("[Microsoft_VSTS_Common_ExitCriteria].[Microsoft_VSTS_Common_ExitCriteria].[Microsoft_VSTS_Common_ExitCriteria]"),
    STRTOSET(@dsExitCriteriaParam),
    --STRTOSET("[Microsoft_VSTS_Build_FoundIn].[Build].[All]"),
    STRTOSET(@dsFoundInBuildParam),
    --STRTOSET("[Microsoft_VSTS_Build_IntegrationBuild].[Build].[All]")
    STRTOSET(@dsFixedInBuildParam)
)
</Statement><ParameterDefinitions><ParameterDefinition><Name>Project</Name><DefaultValues><DefaultValue>[Team Project].[Team Project].[All]</DefaultValue></DefaultValues><Caption>Project</Caption><HierarchyUniqueName>[Team Project].[Team Project]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[Team Project].[Team Project].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Team Project].[Team Project].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Team Project].[Team Project].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Team Project].[Team Project].ALLMEMBERS ON ROWS FROM [Team System]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery></ParameterDefinition><ParameterDefinition><Name>dsIterationParam</Name><DefaultValues><DefaultValue>[Iteration].[Parent_ID].[All]</DefaultValue></DefaultValues><Caption>dsIterationParam</Caption><HierarchyUniqueName>[Iteration].[Parent_ID]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[Iteration].[Parent_ID].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Iteration].[Parent_ID].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Iteration].[Parent_ID].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[P

Working with Cross project Reports

Hot Topic

VS Team System

Working with Cross project Reports

Working with Cross project Reports

Cameron Gocke

Pedro Ruivo

DarrellMerryweather

Andrew Conrad - MSFT