Subreport example embedded in drill down (has code) (Network Steve Forum)

Subreport example embedded in drill down (has code)

I have a report and I want to embed a subreport... I seem to have trouble passing parameters and triggering the subreport for a given branch if it exists. Data is in the first post, the main report in the second post and the target report that belongs in a subreport from a drilldown (where data exists) needs to be embedded in the first (third post). VS2010 - copy and paste into SQL ssms and rdl's below - into SSRS. DATA: (Note the create word seems to be banned on this site)createviewvHierarchyTestASSLECT 1 child,NULL parent,'CPO'cname,NULL pname,230 dayz,'bob'NAME,10 obl,0 exc,179 avail,41 fcst,2012 FY,'MGMT'typ,1 lvl UNION SLECT 2,1,'VP Branch Efficient','CPO',230,'dave',189,0,0,41,2012,'MGMT',2 UNION SLECT 3,1,'VP Branch Steady','CPO',230 dayz,'barb',189,0,0,41,2012,'MGMT',2 UNION SLECT 4,1,'VP Branch Improvement','CPO',230 dayz,'jill',189,0,0,41,2012,'MGMT',2 UNION SLECT 5,2,'Branch 1','VP Branch Efficient',240 dayz,'sandy',199,0,0,51,2012 FiscalYea,'MGMT'typr,3 UNION SLECT 17,2,'Branch 4','VP Branch Efficient',240 dayz,'betty',199,0,0,51,2012 FiscalYea,'MGMT'typr,3 UNION SLECT 6,3,'Branch 2','VP Branch Steady',240 dayz,'celia'name,199,0,0,51,2012,'MGMT',3 UNION SLECT 7,4,'Branch 3','VP Branch Improvement',240 dayz,'jim',199,0,0,51,2012,'MGMT',3 UNION SLECT 8,5,'StoreMgt 1','Branch 1',250,'todd',209,0,0,41,2012,'MGMT',4 UNION SLECT 9,6,'StoreMgt 2','Branch 2',250,'dan',209,0,0,41,2012,'MGMT',4 UNION SLECT 10,7,'StoreMgt 3','Branch 3',250 dayz,'zack',209,0,0,41,2012,'MGMT',4 UNION SLECT 11,8,'Emp 1','StoreMgt 1',250,'bill',250,0,0,0,2012,'EMPL',5 UNION SLECT 12,8,'Emp 2','StoreMgt 2',250,'art',250,0,0,0,2012,'EMPL',5 UNION SLECT 13,8,'Emp 3','StoreMgt 3',250 dayz,'phil',250,0,0,0,2012,'EMPL',5 UNION SLECT 14,9,'Emp 4','StoreMgt 1',250,'dick',250,0,0,0,2012,'EMPL',5 UNION SLECT 15,9,'Emp 5','StoreMgt 2',250,'zoe',250,0,0,0,2012,'EMPL',5 UNION SLECT 16,9,'Emp 6','StoreMgt 3',250 dayz,'ann',250,0,0,0,2012,'EMPL',5 UNION SLECT 14,10,'Emp 7','StoreMgt 1',250,'kate',250,0,0,0,2012,'EMPL',5 UNION SLECT 15,10,'Emp 8','StoreMgt 2',250,'john',250,0,0,0,2012,'EMPL',5 UNION SLECT 16,10,'Emp 9','StoreMgt 3',250 dayz,'joe',250,0,0,0,2012,'EMPL',5 UNION SLECT 17,5,'StoreMgt 1','Branch 1',250,'rich',209,0,0,41,2012,'MGMT',4 ------ ALTER PROC [dbo].[HierarchyDrillDownTest] (@FY INT,@cname varchar(50))AS DECLARE @lvl INT,@count INT SET @lvl=(SELECT DISTINCT lvl FROM dbo.vHierarchyTest WHERE cname=@cname) SET @count=(SELECT SUM(v1.dayz) FROM vHierarchyTest v1 LEFT JOIN vHierarchyTest v2 ON v1.child = v2.parent LEFT JOIN vHierarchyTest v3 ON v2.child = v3.parent LEFT JOIN vHierarchyTest v4 ON v3.child = v4.parent LEFT JOIN vHierarchyTest v5 ON v4.child = v5.parent WHERE v1.pname = @cname AND v1.FY =@FY AND v1.lvl>=@lvl) DECLARE @hold TABLE(NAME VARCHAR(50),cname VARCHAR(50),pname VARCHAR(50),typ CHAR(4),dayz INT, obl INT,exc INT,fcst INT,avail INT,lvl INT) INSERT INTO @hold(NAME,cname,pname,typ,dayz,obl,exc,fcst,avail,lvl)SELECT v1.name,v1.cname,v1.pname unit,v1.typ,SUM(v1.dayz) dayz,SUM(v1.obl) obl,SUM(v1.exc) exc,SUM(v1.fcst) fcst,SUM(v1.avail) avail,v1.lvl FROM vHierarchyTest v1 LEFT JOIN vHierarchyTest v2 ON v1.child = v2.parent LEFT JOIN vHierarchyTest v3 ON v2.child = v3.parent LEFT JOIN vHierarchyTest v4 ON v3.child = v4.parent LEFT JOIN vHierarchyTest v5 ON v4.child = v5.parent WHERE v1.pname = @cname AND v1.FY =@FY AND v1.lvl>=@lvl GROUP BY v1.typ,v1.lvl,v1.pname,v1.cname,v1.name SET @count=(SELECT count(*) FROM @hold)IF @count=0 INSERT INTO @hold(NAME,cname,pname,typ,dayz,obl,exc,fcst,avail,lvl)SELECT v1.name,v1.cname,v1.pname unit,v1.typ,v1.dayz,v1.obl,v1.exc,v1.fcst,v1.avail,v1.lvl FROM vHierarchyTest v1 WHERE v1.cname = @cname AND v1.FY =@FY AND v1.lvl>=@lvl SELECT * FROM @hold ALTER PROC HierarchyTest (@FY INT)AS SELECT * FROM vHierarchyTest WHERE FY=@FY AND lvl<5 R, J

September 21st, 2012 1:45pm

<?xml version="1.0" encoding="utf-8"?> <Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"> <Body> <ReportItems> <Tablix Name="List1"> <TablixBody> <TablixColumns> <TablixColumn> <Width>7.98959in</Width> </TablixColumn> </TablixColumns> <TablixRows> <TablixRow> <Height>1.13in</Height> <TablixCells> <TablixCell> <CellContents> <Rectangle Name="List1_Contents"> <ReportItems> <Tablix Name="matrix1"> <TablixCorner> <TablixCornerRows> <TablixCornerRow> <TablixCornerCell> <CellContents> <Textbox Name="textbox6"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value /> <Style> <FontFamily>Tahoma</FontFamily> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>textbox6</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> <RowSpan>2</RowSpan> </CellContents> </TablixCornerCell> </TablixCornerRow> <TablixCornerRow> <TablixCornerCell /> </TablixCornerRow> </TablixCornerRows> </TablixCorner> <TablixBody> <TablixColumns> <TablixColumn> <Width>0.40625in</Width> </TablixColumn> <TablixColumn> <Width>0.42708in</Width> </TablixColumn> <TablixColumn> <Width>0.44791in</Width> </TablixColumn> <TablixColumn> <Width>0.44792in</Width> </TablixColumn> <TablixColumn> <Width>0.38542in</Width> </TablixColumn> <TablixColumn> <Width>0.45834in</Width> </TablixColumn> <TablixColumn> <Width>0.45833in</Width> </TablixColumn> <TablixColumn> <Width>0.47917in</Width> </TablixColumn> </TablixColumns> <TablixRows> <TablixRow> <Height>0.21in</Height> <TablixCells> <TablixCell> <CellContents> <Textbox Name="fcst"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=iif(Fields!typ.Value="MGMT",Sum(Fields!fcst.Value, "matrix1_unit",Recursive),0)</Value> <Style> <FontFamily>Tahoma</FontFamily> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>fcst</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>LightSteelBlue</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> <DataElementOutput>Output</DataElementOutput> </TablixCell> <TablixCell> <CellContents> <Textbox Name="obl"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=iif(Fields!typ.Value="MGMT",Sum(Fields!obl.Value, "matrix1_unit",Recursive),0)</Value> <Style> <FontFamily>Tahoma</FontFamily> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>obl</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>LightSteelBlue</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> <DataElementOutput>Output</DataElementOutput> </TablixCell> <TablixCell> <CellContents> <Textbox Name="exc"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=iif(Fields!typ.Value="MGMT",Sum(Fields!exc.Value, "matrix1_unit",Recursive),0)</Value> <Style> <FontFamily>Tahoma</FontFamily> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>exc</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>LightSteelBlue</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> <DataElementOutput>Output</DataElementOutput> </TablixCell> <TablixCell> <CellContents> <Textbox Name="avail"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=iif(Fields!typ.Value="MGMT",Sum(Fields!avail.Value, "matrix1_unit",Recursive),0)</Value> <Style> <FontFamily>Tahoma</FontFamily> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>avail</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>LightSteelBlue</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> <DataElementOutput>Output</DataElementOutput> </TablixCell> <TablixCell> <CellContents> <Textbox Name="empfcst"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=iif(not Fields!typ.Value="MGMT",Sum(Fields!fcst.Value, "matrix1_unit",Recursive),0)</Value> <Style> <FontFamily>Tahoma</FontFamily> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>empfcst</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> <DataElementOutput>Output</DataElementOutput> </TablixCell> <TablixCell> <CellContents> <Textbox Name="empobl"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=iif(not Fields!typ.Value="MGMT",Sum(Fields!obl.Value, "matrix1_unit",Recursive),0)</Value> <Style> <FontFamily>Tahoma</FontFamily> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>empobl</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> <DataElementOutput>Output</DataElementOutput> </TablixCell> <TablixCell> <CellContents> <Textbox Name="empexc"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=iif(not Fields!typ.Value="MGMT",Sum(Fields!exc.Value, "matrix1_unit",Recursive),0)</Value> <Style> <FontFamily>Tahoma</FontFamily> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>empexc</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> <DataElementOutput>Output</DataElementOutput> </TablixCell> <TablixCell> <CellContents> <Textbox Name="empavail"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=iif(not Fields!typ.Value="MGMT",Sum(Fields!avail.Value, "matrix1_unit",Recursive),0)</Value> <Style> <FontFamily>Tahoma</FontFamily> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>empavail</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> <DataElementOutput>Output</DataElementOutput> </TablixCell> </TablixCells> </TablixRow> </TablixRows> </TablixBody> <TablixColumnHierarchy> <TablixMembers> <TablixMember> <TablixHeader> <Size>0.21in</Size> <CellContents> <Textbox Name="Textbox12"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>Managment</Value> <Style> <FontFamily>Tahoma</FontFamily> <FontWeight>Bold</FontWeight> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Center</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox12</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>#6e9eca</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixHeader> <TablixMembers> <TablixMember> <TablixHeader> <Size>0.21in</Size> <CellContents> <Textbox Name="textbox7"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>FC</Value> <Style> <FontFamily>Tahoma</FontFamily> <FontWeight>Bold</FontWeight> <Color>White</Color> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Right</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>textbox7</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>SlateGray</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixHeader> <KeepTogether>true</KeepTogether> </TablixMember> <TablixMember> <TablixHeader> <Size>0.21in</Size> <CellContents> <Textbox Name="textbox8"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>OB</Value> <Style> <FontFamily>Tahoma</FontFamily> <FontWeight>Bold</FontWeight> <Color>White</Color> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Right</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>textbox8</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>SlateGray</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixHeader> <KeepTogether>true</KeepTogether> </TablixMember> <TablixMember> <TablixHeader> <Size>0.21in</Size> <CellContents> <Textbox Name="textbox9"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>EX</Value> <Style> <FontFamily>Tahoma</FontFamily> <FontWeight>Bold</FontWeight> <Color>White</Color> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Right</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>textbox9</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>SlateGray</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixHeader> <KeepTogether>true</KeepTogether> </TablixMember> <TablixMember> <TablixHeader> <Size>0.21in</Size> <CellContents> <Textbox Name="textbox10"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>AV</Value> <Style> <FontFamily>Tahoma</FontFamily> <FontWeight>Bold</FontWeight> <Color>White</Color> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Right</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>textbox10</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>SlateGray</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixHeader> <KeepTogether>true</KeepTogether> </TablixMember> </TablixMembers> <DataElementOutput>Output</DataElementOutput> </TablixMember> <TablixMember> <TablixHeader> <Size>0.21in</Size> <CellContents> <Textbox Name="Textbox13"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>Employee</Value> <Style> <FontFamily>Tahoma</FontFamily> <FontWeight>Bold</FontWeight> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Center</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox13</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>#6e9eca</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixHeader> <TablixMembers> <TablixMember> <TablixHeader> <Size>0.21in</Size> <CellContents> <Textbox Name="Textbox14"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>FC</Value> <Style> <FontFamily>Tahoma</FontFamily> <FontWeight>Bold</FontWeight> <Color>White</Color> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Right</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox14</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>SlateGray</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixHeader> </TablixMember> <TablixMember> <TablixHeader> <Size>0.21in</Size> <CellContents> <Textbox Name="Textbox17"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>OB</Value> <Style> <FontFamily>Tahoma</FontFamily> <FontWeight>Bold</FontWeight> <Color>White</Color> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Right</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox17</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>SlateGray</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixHeader> </TablixMember> <TablixMember> <TablixHeader> <Size>0.21in</Size> <CellContents> <Textbox Name="Textbox20"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>EX</Value> <Style> <FontFamily>Tahoma</FontFamily> <FontWeight>Bold</FontWeight> <Color>White</Color> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Right</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox20</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>SlateGray</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixHeader> </TablixMember> <TablixMember> <TablixHeader> <Size>0.21in</Size> <CellContents> <Textbox Name="Textbox23"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>AV</Value> <Style> <FontFamily>Tahoma</FontFamily> <FontWeight>Bold</FontWeight> <Color>White</Color> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Right</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox23</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>SlateGray</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixHeader> </TablixMember> </TablixMembers> </TablixMember> </TablixMembers> </TablixColumnHierarchy> <TablixRowHierarchy> <TablixMembers> <TablixMember> <Group Name="matrix1_unit"> <GroupExpressions> <GroupExpression>=Fields!child.Value</GroupExpression> </GroupExpressions> <Parent>=Fields!parent.Value</Parent> </Group> <TablixHeader> <Size>4.47917in</Size> <CellContents> <Textbox Name="levelname"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Fields!cname.Value</Value> <Style> <FontFamily>Tahoma</FontFamily> <FontWeight>Bold</FontWeight> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>levelname</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>=switch(Fields!typ.Value="MGMT", "#6e9eca",Fields!typ.Value="EMPL", "Gray")</BackgroundColor> <PaddingLeft>=20*Level() &"pt"</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixHeader> <TablixMembers> <TablixMember> <DataElementOutput>Output</DataElementOutput> <KeepTogether>true</KeepTogether> </TablixMember> </TablixMembers> <Visibility> <Hidden>true</Hidden> <ToggleItem>levelname</ToggleItem> </Visibility> <DataElementOutput>Output</DataElementOutput> </TablixMember> </TablixMembers> </TablixRowHierarchy> <RepeatColumnHeaders>true</RepeatColumnHeaders> <RepeatRowHeaders>true</RepeatRowHeaders> <DataSetName>DataSetMain</DataSetName> <Top>0.5in</Top> <Height>0.63in</Height> <Width>7.98959in</Width> <Style /> </Tablix> <Textbox Name="YYYYMM"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>="Click on the plus symbol to expand unit display options" & vbCrLf & "Click on the unit for a graphical presentation"</Value> <Style> <FontSize>12pt</FontSize> <Color>SlateGray</Color> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>YYYYMM</rd:DefaultName> <Height>0.27in</Height> <Width>7.98959in</Width> <ZIndex>1</ZIndex> <Style> <Border /> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </ReportItems> <KeepTogether>true</KeepTogether> <Style /> </Rectangle> </CellContents> </TablixCell> </TablixCells> </TablixRow> </TablixRows> </TablixBody> <TablixColumnHierarchy> <TablixMembers> <TablixMember /> </TablixMembers> </TablixColumnHierarchy> <TablixRowHierarchy> <TablixMembers> <TablixMember> <DataElementOutput>Output</DataElementOutput> <KeepTogether>true</KeepTogether> </TablixMember> </TablixMembers> </TablixRowHierarchy> <DataSetName>DataSetMain</DataSetName> <Top>0.37389in</Top> <Height>1.13in</Height> <Width>7.98959in</Width> <Style> <FontFamily>Tahoma</FontFamily> <FontSize>14pt</FontSize> <Color>SlateGray</Color> </Style> </Tablix> <Textbox Name="textbox1"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>Employee Utilization [Day Counts]</Value> <Style> <FontFamily>Tahoma</FontFamily> <FontSize>20pt</FontSize> <FontWeight>Bold</FontWeight> <Color>SteelBlue</Color> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>textbox1</rd:DefaultName> <Height>0.36in</Height> <Width>7.98959in</Width> <ZIndex>1</ZIndex> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </ReportItems> <Height>1.50389in</Height> <Style /> </Body> <Width>8.05208in</Width> <Page> <LeftMargin>1in</LeftMargin> <RightMargin>1in</RightMargin> <TopMargin>1in</TopMargin> <BottomMargin>1in</BottomMargin> <Style /> </Page> <AutoRefresh>0</AutoRefresh> <DataSources> <DataSource Name="Arcnet"> <ConnectionProperties> <DataProvider>SQL</DataProvider> <ConnectString>Data Source=eclipse;Initial Catalog=work</ConnectString> <IntegratedSecurity>true</IntegratedSecurity> </ConnectionProperties> <rd:SecurityType>Integrated</rd:SecurityType> <rd:DataSourceID>a3e3b78c-732c-4146-8ea6-d9b3b7349daf</rd:DataSourceID> </DataSource> </DataSources> <DataSets> <DataSet Name="DataSetMain"> <Query> <DataSourceName>Arcnet</DataSourceName> <QueryParameters> <QueryParameter Name="@FY"> <Value>=Parameters!FY.Value</Value> </QueryParameter> </QueryParameters> <CommandType>StoredProcedure</CommandType> <CommandText>dbo.HierarchyTest</CommandText> <rd:UseGenericDesigner>true</rd:UseGenericDesigner> </Query> <Fields> <Field Name="child"> <DataField>child</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="parent"> <DataField>parent</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="cname"> <DataField>cname</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="pname"> <DataField>pname</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="dayz"> <DataField>dayz</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="NAME"> <DataField>NAME</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="obl"> <DataField>obl</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="exc"> <DataField>exc</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="avail"> <DataField>avail</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="fcst"> <DataField>fcst</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="FY"> <DataField>FY</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="typ"> <DataField>typ</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="lvl"> <DataField>lvl</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> </Fields> </DataSet> <DataSet Name="DataSetFY"> <Query> <DataSourceName>Arcnet</DataSourceName> <CommandText>Select '2010' FY Union Select '2011' Union Select '2012' Union Select '2013'</CommandText> </Query> <Fields> <Field Name="FY"> <DataField>FY</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> </Fields> </DataSet> </DataSets> <ReportParameters> <ReportParameter Name="FY"> <DataType>String</DataType> <DefaultValue> <Values> <Value>2012</Value> </Values> </DefaultValue> <Prompt>FY</Prompt> <ValidValues> <DataSetReference> <DataSetName>DataSetFY</DataSetName> <ValueField>FY</ValueField> <LabelField>FY</LabelField> </DataSetReference> </ValidValues> </ReportParameter> <ReportParameter Name="cname"> <DataType>String</DataType> <DefaultValue> <DataSetReference> <DataSetName>DataSetMain</DataSetName> <ValueField>cname</ValueField> </DataSetReference> </DefaultValue> <Hidden>true</Hidden> <ValidValues> <DataSetReference> <DataSetName>DataSetMain</DataSetName> <ValueField>cname</ValueField> <LabelField>cname</LabelField> </DataSetReference> </ValidValues> </ReportParameter> </ReportParameters> <Language>en-US</Language> <ConsumeContainerWhitespace>true</ConsumeContainerWhitespace> <rd:ReportUnitType>Inch</rd:ReportUnitType> <rd:ReportID>312f9b34-f1e0-455a-a71e-7166424ec4e2</rd:ReportID> </Report>R, J

Free Windows Admin Tool Kit Click here and download it now

September 21st, 2012 2:05pm

<?xml version="1.0" encoding="utf-8"?> <Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"> <Body> <ReportItems> <Tablix Name="table1"> <TablixBody> <TablixColumns> <TablixColumn> <Width>4.58332in</Width> </TablixColumn> <TablixColumn> <Width>0.41666in</Width> </TablixColumn> <TablixColumn> <Width>0.39584in</Width> </TablixColumn> <TablixColumn> <Width>0.34375in</Width> </TablixColumn> <TablixColumn> <Width>0.33334in</Width> </TablixColumn> <TablixColumn> <Width>0.33334in</Width> </TablixColumn> <TablixColumn> <Width>0.33333in</Width> </TablixColumn> <TablixColumn> <Width>0.34375in</Width> </TablixColumn> <TablixColumn> <Width>0.375in</Width> </TablixColumn> </TablixColumns> <TablixRows> <TablixRow> <Height>0.22in</Height> <TablixCells> <TablixCell> <CellContents> <Textbox Name="textbox2"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Fields!pname.Value</Value> <Style> <FontFamily>Tahoma</FontFamily> <FontSize>11pt</FontSize> <FontWeight>Bold</FontWeight> <Color>White</Color> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>textbox2</rd:DefaultName> <Visibility> <Hidden>true</Hidden> </Visibility> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>SteelBlue</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="textbox3"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>FC</Value> <Style> <FontFamily>Tahoma</FontFamily> <FontSize>11pt</FontSize> <FontWeight>Bold</FontWeight> <Color>White</Color> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Right</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>textbox3</rd:DefaultName> <Visibility> <Hidden>true</Hidden> </Visibility> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>SteelBlue</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="textbox4"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>OB</Value> <Style> <FontFamily>Tahoma</FontFamily> <FontSize>11pt</FontSize> <FontWeight>Bold</FontWeight> <Color>White</Color> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Right</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>textbox4</rd:DefaultName> <Visibility> <Hidden>true</Hidden> </Visibility> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>SteelBlue</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="Textbox13"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>EX</Value> <Style> <FontFamily>Tahoma</FontFamily> <FontSize>11pt</FontSize> <FontWeight>Bold</FontWeight> <Color>White</Color> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Right</TextAlign> </Style> </Paragraph> </Paragraphs> <Visibility> <Hidden>true</Hidden> </Visibility> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>SteelBlue</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="textbox6"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>AV</Value> <Style> <FontFamily>Tahoma</FontFamily> <FontSize>11pt</FontSize> <FontWeight>Bold</FontWeight> <Color>White</Color> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Right</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>textbox6</rd:DefaultName> <Visibility> <Hidden>true</Hidden> </Visibility> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>SteelBlue</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="textbox7"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>FC</Value> <Style> <FontFamily>Tahoma</FontFamily> <FontSize>11pt</FontSize> <FontWeight>Bold</FontWeight> <Color>White</Color> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Right</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>textbox7</rd:DefaultName> <Visibility> <Hidden>true</Hidden> </Visibility> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>SteelBlue</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="textbox8"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>OB</Value> <Style> <FontFamily>Tahoma</FontFamily> <FontSize>11pt</FontSize> <FontWeight>Bold</FontWeight> <Color>White</Color> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Right</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>textbox8</rd:DefaultName> <Visibility> <Hidden>true</Hidden> </Visibility> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>SteelBlue</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="textbox9"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>EX</Value> <Style> <FontFamily>Tahoma</FontFamily> <FontSize>11pt</FontSize> <FontWeight>Bold</FontWeight> <Color>White</Color> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Right</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>textbox9</rd:DefaultName> <Visibility> <Hidden>true</Hidden> </Visibility> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>SteelBlue</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="textbox10"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>AV</Value> <Style> <FontFamily>Tahoma</FontFamily> <FontSize>11pt</FontSize> <FontWeight>Bold</FontWeight> <Color>White</Color> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Right</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>textbox10</rd:DefaultName> <Visibility> <Hidden>true</Hidden> </Visibility> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>SteelBlue</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> </TablixCells> </TablixRow> <TablixRow> <Height>0.25in</Height> <TablixCells> <TablixCell> <CellContents> <Textbox Name="Textbox25"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Fields!pname.Value</Value> <Style> <FontFamily>Tahoma</FontFamily> <FontWeight>Bold</FontWeight> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox25</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>LightGrey</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="Textbox26"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=iif(Fields!typ.Value="MGMT",Sum(Fields!fcst.Value,"Unit",Recursive),0) </Value> <Style> <FontFamily>Tahoma</FontFamily> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox26</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>LightBlue</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="Textbox27"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value> =iif(Fields!typ.Value="MGMT",Sum(Fields!obl.Value,"Unit",Recursive),0) </Value> <Style> <FontFamily>Tahoma</FontFamily> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox27</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>LightBlue</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="Textbox28"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value> =iif(Fields!typ.Value="MGMT",Sum(Fields!exc.Value,"Unit",Recursive),0) </Value> <Style> <FontFamily>Tahoma</FontFamily> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox28</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>LightBlue</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="Textbox29"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value> =iif(Fields!typ.Value="MGMT",Sum(Fields!avail.Value,"Unit",Recursive),0) </Value> <Style> <FontFamily>Tahoma</FontFamily> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox29</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>LightBlue</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="Textbox30"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value> =iif(not Fields!typ.Value="MGMT",Sum(Fields!fcst.Value,"Unit", Recursive),0) </Value> <Style> <FontFamily>Tahoma</FontFamily> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox30</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="Textbox31"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value> =iif(not Fields!typ.Value="MGMT",Sum(Fields!obl.Value,"Unit", Recursive),0) </Value> <Style> <FontFamily>Tahoma</FontFamily> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox31</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="Textbox32"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value> =iif(not Fields!typ.Value="MGMT",Sum(Fields!exc.Value,"Unit", Recursive),0) </Value> <Style> <FontFamily>Tahoma</FontFamily> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox32</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="Textbox33"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value> =iif(not Fields!typ.Value="MGMT",Sum(Fields!avail.Value,"Unit", Recursive),0) </Value> <Style> <FontFamily>Tahoma</FontFamily> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox33</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> </TablixCells> </TablixRow> <TablixRow> <Height>0.21in</Height> <TablixCells> <TablixCell> <CellContents> <Textbox Name="levelname"> <CanGrow>true</CanGrow> <HideDuplicates>DataSetMain</HideDuplicates> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Fields!cname.Value+" "+Fields!Name.Value</Value> <Style> <FontFamily>Tahoma</FontFamily> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>levelname</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>Silver</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="fc_officer"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value> =iif(Fields!typ.Value="MGMT",Fields!fcst.Value,0) </Value> <Style> <FontFamily>Tahoma</FontFamily> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>fc_officer</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>LightBlue</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="ob_officer"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value> =iif(Fields!typ.Value="MGMT",Fields!obl.Value,0) </Value> <Style> <FontFamily>Tahoma</FontFamily> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>ob_officer</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>LightBlue</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="ex_officer"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value> =iif(Fields!typ.Value="MGMT",Fields!exc.Value,0) </Value> <Style> <FontFamily>Tahoma</FontFamily> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>ex_officer</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>LightBlue</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="av_officer"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value> =iif(Fields!typ.Value="MGMT",Fields!avail.Value,0) </Value> <Style> <FontFamily>Tahoma</FontFamily> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>av_officer</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>LightBlue</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="fc_enlisted"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value> =iif(not Fields!typ.Value="MGMT",Fields!fcst.Value,0) </Value> <Style> <FontFamily>Tahoma</FontFamily> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>fc_enlisted</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="ob_enlisted"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value> =iif(not Fields!typ.Value="MGMT",Fields!obl.Value,0) </Value> <Style> <FontFamily>Tahoma</FontFamily> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>ob_enlisted</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="ex_enlisted"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value> =iif(not Fields!typ.Value="MGMT",Fields!exc.Value,0) </Value> <Style> <FontFamily>Tahoma</FontFamily> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>ex_enlisted</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="av_enlisted"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value> =iif(not Fields!typ.Value="MGMT",Fields!avail.Value,0) </Value> <Style> <FontFamily>Tahoma</FontFamily> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>av_enlisted</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> </TablixCells> </TablixRow> </TablixRows> </TablixBody> <TablixColumnHierarchy> <TablixMembers> <TablixMember /> <TablixMember /> <TablixMember /> <TablixMember /> <TablixMember /> <TablixMember /> <TablixMember /> <TablixMember /> <TablixMember /> </TablixMembers> </TablixColumnHierarchy> <TablixRowHierarchy> <TablixMembers> <TablixMember> <TablixHeader> <Size>0.04167in</Size> <CellContents> <Textbox Name="Textbox22"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>Unit</Value> <Style> <FontFamily>Tahoma</FontFamily> <FontSize>11pt</FontSize> <FontWeight>Bold</FontWeight> <Color>White</Color> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox22</rd:DefaultName> <Visibility> <Hidden>true</Hidden> </Visibility> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>SteelBlue</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixHeader> <TablixMembers> <TablixMember> <KeepTogether>true</KeepTogether> </TablixMember> </TablixMembers> </TablixMember> <TablixMember> <Group Name="Unit"> <GroupExpressions> <GroupExpression>=Fields!Child.Value</GroupExpression> </GroupExpressions> <Parent>=Fields!Parent.Value</Parent> </Group> <SortExpressions> <SortExpression> <Value>=Fields!cname.Value</Value> </SortExpression> </SortExpressions> <TablixHeader> <Size>0.04167in</Size> <CellContents> <Textbox Name="Unit"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Fields!pname.Value</Value> <Style> <FontFamily>Tahoma</FontFamily> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Unit</rd:DefaultName> <Visibility> <Hidden>true</Hidden> </Visibility> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>Silver</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixHeader> <TablixMembers> <TablixMember> <KeepWithGroup>After</KeepWithGroup> </TablixMember> <TablixMember> <Group Name="table1_levelname"> <GroupExpressions> <GroupExpression>=Fields!Name.Value</GroupExpression> </GroupExpressions> <Parent>=Fields!pname.Value</Parent> </Group> <SortExpressions> <SortExpression> <Value>=Fields!pname.Value</Value> </SortExpression> </SortExpressions> <TablixMembers> <TablixMember /> </TablixMembers> </TablixMember> </TablixMembers> </TablixMember> </TablixMembers> </TablixRowHierarchy> <DataSetName>DataSetMain</DataSetName> <Left>0.19794in</Left> <Height>0.68in</Height> <Width>7.5in</Width> <Style /> </Tablix> </ReportItems> <Height>0.70083in</Height> <Style> <Border> <Style>None</Style> </Border> </Style> </Body> <Width>7.69794in</Width> <Page> <LeftMargin>1in</LeftMargin> <RightMargin>1in</RightMargin> <TopMargin>1in</TopMargin> <BottomMargin>1in</BottomMargin> <Style /> </Page> <AutoRefresh>0</AutoRefresh> <DataSources> <DataSource Name="ResNet"> <DataSourceReference>ResNet</DataSourceReference> <rd:SecurityType>None</rd:SecurityType> <rd:DataSourceID>2d1c078e-9f0f-44eb-9e05-509928018fa8</rd:DataSourceID> </DataSource> </DataSources> <DataSets> <DataSet Name="DataSetMain"> <Query> <DataSourceName>ResNet</DataSourceName> <QueryParameters> <QueryParameter Name="@FY"> <Value>=Parameters!FY.Value</Value> </QueryParameter> <QueryParameter Name="@cname"> <Value>=Parameters!cname.Value</Value> </QueryParameter> </QueryParameters> <CommandText>dbo.HierarchyDrillDownTest @FY,@cname</CommandText> <rd:UseGenericDesigner>true</rd:UseGenericDesigner> </Query> <Fields> <Field Name="Name"> <DataField>name</DataField> <rd:UserDefined>true</rd:UserDefined> </Field> <Field Name="cname"> <DataField>cname</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="pname"> <DataField>pname</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="typ"> <DataField>typ</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="dayz"> <DataField>dayz</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="Child"> <DataField>Child</DataField> <rd:UserDefined>true</rd:UserDefined> </Field> <Field Name="obl"> <DataField>obl</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="exc"> <DataField>exc</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="Parent"> <DataField>Parent</DataField> <rd:UserDefined>true</rd:UserDefined> </Field> <Field Name="fcst"> <DataField>fcst</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="avail"> <DataField>avail</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="lvl"> <DataField>lvl</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> </Fields> </DataSet> <DataSet Name="DataSetHierarchyNames"> <Query> <DataSourceName>ResNet</DataSourceName> <CommandText> SELECT DISTINCT cname, lvl FROM vHierarchyTest ORDER BY lvl, cname </CommandText> </Query> <Fields> <Field Name="cname"> <DataField>cname</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="lvl"> <DataField>lvl</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> </Fields> </DataSet> </DataSets> <ReportParameters> <ReportParameter Name="FY"> <DataType>String</DataType> <DefaultValue> <Values> <Value>2012</Value> </Values> </DefaultValue> <Prompt>FY</Prompt> <Hidden>true</Hidden> </ReportParameter> <ReportParameter Name="cname"> <DataType>String</DataType> <DefaultValue> <DataSetReference> <DataSetName>DataSetHierarchyNames</DataSetName> <ValueField>cname</ValueField> </DataSetReference> </DefaultValue> <Prompt>cname</Prompt> <ValidValues> <DataSetReference> <DataSetName>DataSetHierarchyNames</DataSetName> <ValueField>cname</ValueField> <LabelField>cname</LabelField> </DataSetReference> </ValidValues> </ReportParameter> </ReportParameters> <Language>en-US</Language> <ConsumeContainerWhitespace>true</ConsumeContainerWhitespace> <rd:ReportUnitType>Inch</rd:ReportUnitType> <rd:ReportID>009d3565-c23a-4a86-b60d-638f7c914bd8</rd:ReportID> </Report>R, J

September 21st, 2012 2:09pm

This topic is archived. No further replies will be accepted.