November 20, 2015 at 9:51 pm
Hi, all
I'm having and challenge to format some data like in sample, it's just simple data but there is some levels/grouping and customer wants to display totals amount of each group level (groups are Hierarchical).
I'm thinking to organize all data in kinda of tree structure and append number of spaces(or underscores) in left of each line go get this tree structure.
I also learned about Recursive Parent property for SSRS (group/sort/advanced/Recursive Parent) which allow sort data in right order, maybe I can do it too? I really appreciate any input/directions.
This is my test data:
-- select * from test1 -- drop table test1
/*
select * into dbo.Test1 from (
select '1A' L1, '2BI' L2, '3MMM' L3, 'info1' Name, 101 Amt, 2012 year union
select '1A' L1, '2BI' L2, '3MMM' L3, 'info2' Name, 102 Amt, 2012 year union
select '1A' L1, '2WR' L2, '3TOM' L3, 'info3' Name, 103 Amt, 2012 year union
select '1A' L1, '2WR' L2, '3TOM' L3, 'info4' Name, 104 Amt, 2012 year union
select '1A' L1, '2BI' L2, '3MMM' L3, 'info5' Name, 105 Amt, 2012 year union
select '1A' L1, '2BI' L2, '3MMM' L3, 'info6' Name, 106 Amt, 2012 year union
select '1A' L1, '2BI' L2, '3MLL' L3, 'info7' Name, 107 Amt, 2012 year union
select '1Z' L1, '2MT' L2, '3RIM' L3, 'info8' Name, 211 Amt, 2012 year union
select '1Z' L1, '2MT' L2, '3QQQ' L3, 'info8' Name, 222 Amt, 2012 year
) F
and this is my wanted output (formatted), I have ssrs 2012 as a tool.
/*
1A___________728
__2BI________521
____3MMM_____521
____3RIM_____108
1Z___________433
__2MT________433
____3RIM_____211
____3TOM_____297 */
I used this sql to put data into kinda of tree structure not sure if it's right direction:
SELECT 0 Level, L1 Lname, sum(amt) Amt from test1 group by L1 union
SELECT 1 Level, L2 Lname, sum(amt) Amt from test1 group by L2 union
SELECT 2 Level, L3 Lname, sum(amt) Amt from test1 group by L3
November 20, 2015 at 11:06 pm
Mario,
I hope your math is wrong, because mine doesn't match what you're getting. For the moment, I'll pretend it does.
I think the whole hierarchy bit is a red herring. It doesn't seem that you need a recursive query - you could do the whole thing with a matrix with 3 row groups (L1, L2, L3). How does the math work for L2, L3? My L1s are fine, but L2 and L3 seem to be off.
At any rate, this is as far as I got... (see attached)
November 21, 2015 at 9:28 am
Thanks much PietLinden!!!
Wonderful and simple solution!!!
Thanks for your tolerance of my output error, I made it manually so make typo.
My overnight learning produces that Report2.rdl which uses Recursive Parent option, but it has much more sql overhead .
Thanks again!
Mario
Just realized that I can't attach file without own hosting so pasting my rdl in sep post below.
November 21, 2015 at 9:29 am
<?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>1in</Width>
</TablixColumn>
<TablixColumn>
<Width>1in</Width>
</TablixColumn>
<TablixColumn>
<Width>1in</Width>
</TablixColumn>
<TablixColumn>
<Width>1in</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>Level</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontSize>11pt</FontSize>
<FontWeight>Bold</FontWeight>
<Color>White</Color>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Center</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<rd:DefaultName>textbox2</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>DimGray</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>Name</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontSize>11pt</FontSize>
<FontWeight>Bold</FontWeight>
<Color>White</Color>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Center</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<rd:DefaultName>textbox3</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>DimGray</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>Parent</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontSize>11pt</FontSize>
<FontWeight>Bold</FontWeight>
<Color>White</Color>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Center</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<rd:DefaultName>textbox4</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>DimGray</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>amt</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontSize>11pt</FontSize>
<FontWeight>Bold</FontWeight>
<Color>White</Color>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Center</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox7</rd:DefaultName>
<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.21in</Height>
<TablixCells>
<TablixCell>
<CellContents>
<Textbox Name="Level">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!Level.Value</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Level</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="Lname">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!Lname.Value</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Lname</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="Parent">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!Parent.Value</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Parent</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="amt">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!amt.Value</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Right</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<rd:DefaultName>amt</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 />
</TablixMembers>
</TablixColumnHierarchy>
<TablixRowHierarchy>
<TablixMembers>
<TablixMember>
<TablixHeader>
<Size>1in</Size>
<CellContents>
<Textbox Name="Textbox19">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Lname</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontSize>11pt</FontSize>
<FontWeight>Bold</FontWeight>
<Color>White</Color>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Center</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox19</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>DimGray</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="Lname">
<GroupExpressions>
<GroupExpression>=Fields!Lname.Value</GroupExpression>
</GroupExpressions>
<Parent>=Fields!Parent.Value</Parent>
</Group>
<SortExpressions>
<SortExpression>
<Value>=Fields!Lname.Value</Value>
</SortExpression>
</SortExpressions>
<TablixHeader>
<Size>1in</Size>
<CellContents>
<Textbox Name="Lname1">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!fill.Value & Fields!Lname.Value</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Lname1</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>
</TablixHeader>
<TablixMembers>
<TablixMember>
<Group Name="table1_Details_Group">
<DataElementName>Detail</DataElementName>
</Group>
<TablixMembers>
<TablixMember />
</TablixMembers>
<DataElementName>Detail_Collection</DataElementName>
<DataElementOutput>Output</DataElementOutput>
<KeepTogether>true</KeepTogether>
</TablixMember>
</TablixMembers>
</TablixMember>
</TablixMembers>
</TablixRowHierarchy>
<DataSetName>DataSet1</DataSetName>
<Top>0.36in</Top>
<Height>0.43in</Height>
<Width>5in</Width>
<Style />
</Tablix>
<Textbox Name="textbox1">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Data </Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontSize>14pt</FontSize>
<FontWeight>Bold</FontWeight>
<Color>SteelBlue</Color>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>textbox1</rd:DefaultName>
<Height>0.36in</Height>
<Width>5in</Width>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</ReportItems>
<Height>3.61292in</Height>
<Style />
</Body>
<Width>10in</Width>
<Page>
<LeftMargin>1in</LeftMargin>
<RightMargin>1in</RightMargin>
<TopMargin>1in</TopMargin>
<BottomMargin>1in</BottomMargin>
<Style />
</Page>
<AutoRefresh>0</AutoRefresh>
<DataSources>
<DataSource Name="DataSource1">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>Data Source=T510L;Initial Catalog=AdventureWorks</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
<rd:SecurityType>Integrated</rd:SecurityType>
<rd:DataSourceID>57b6deae-ca31-4784-959d-80aa8ae43e11</rd:DataSourceID>
</DataSource>
</DataSources>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<DataSourceName>DataSource1</DataSourceName>
<CommandText>drop table dbo.Test1
select * into dbo.Test1 from (
select '1A' L1, '2BI' L2, '3MMM' L3, 'info1' Name, 101 Amt, 2012 year union
select '1A' L1, '2BI' L2, '3MMM' L3, 'info2' Name, 102 Amt, 2012 year union
select '1A' L1, '2WR' L2, '3TOM' L3, 'info3' Name, 103 Amt, 2012 year union
select '1A' L1, '2WR' L2, '3TOM' L3, 'info4' Name, 104 Amt, 2012 year union
select '1A' L1, '2BI' L2, '3MMM' L3, 'info5' Name, 105 Amt, 2012 year union
select '1A' L1, '2BI' L2, '3MMM' L3, 'info6' Name, 106 Amt, 2012 year union
select '1A' L1, '2BI' L2, '3MLL' L3, 'info7' Name, 107 Amt, 2012 year union
select '1Z' L1, '2MT' L2, '3RIM' L3, 'info8' Name, 211 Amt, 2012 year union
select '1Z' L1, '2MT' L2, '3QQQ' L3, 'info8' Name, 222 Amt, 2012 year
) F
select * from (
SELECT 1 Level, Replicate(char(160),0) fill, L1 Lname, 'boss' Parent, sum(amt) Amt from test1 group by L1 union
SELECT 2 Level, Replicate(char(160),2)+'.' fill,L2 Lname, max(L1) Parent, sum(amt) Amt from test1 group by L2 union
SELECT 3 Level, Replicate(char(160),4)+'.' fill, L3 Lname, max(L2) Parent, sum(amt) Amt from test1 group by L3
)j</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="Level">
<DataField>Level</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="Lname">
<DataField>Lname</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="fill">
<DataField>fill</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Parent">
<DataField>Parent</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="amt">
<DataField>Amt</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
</DataSet>
</DataSets>
<ReportParameters>
<ReportParameter Name="Param">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>1</Value>
</Values>
</DefaultValue>
<Prompt>param</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>2</Value>
<Label>2</Label>
</ParameterValue>
<ParameterValue>
<Value>1</Value>
<Label>1</Label>
</ParameterValue>
<ParameterValue>
<Value>3</Value>
<Label>3</Label>
</ParameterValue>
<ParameterValue>
<Value>4</Value>
<Label>4</Label>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
<ReportParameter Name="Wait">
<DataType>String</DataType>
<AllowBlank>true</AllowBlank>
<Prompt>Wait</Prompt>
</ReportParameter>
</ReportParameters>
<Language>en-US</Language>
<ConsumeContainerWhitespace>true</ConsumeContainerWhitespace>
<rd:ReportUnitType>Inch</rd:ReportUnitType>
<rd:ReportID>219a0625-a7b0-49aa-a87f-f95866a1a0ee</rd:ReportID>
</Report>
November 21, 2015 at 11:05 am
Could you explain why you need a recursive query for this? Performance of recursive queries will probably be really slow. If you have a dummy database outside of work, you could use dummy data... as long as the math works in your sample, that's all that matters. I take it you don't have access to wherever the report is saved. Correct?
November 21, 2015 at 8:45 pm
No any need, it's just b'z I had this as a only solution -) + I had similar tree task before where I learned about that cool Advanced option for SSRS grouping. Yours 100% is much cleaner and faster.
In my case actually user wants them into excel, having all levels in same column with spacer, but it's just cosmetic details.
Tx again for your solution.
V
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply