tsql - 如何在单行上对齐 SSRS 中的每日时间线?
问题描述
我正在尝试创建一个 SSRS 报告,其中每天的同一小时对齐单行?以下是迄今为止我在 tablix 中的内容,但正如您将看到的那样,它将单独的日期(列)放在单独的行而不是同一行上。(此外,我希望能够再添加一列,这将是该BucketSort
列,以便我可以看到现在是什么时间)。目前这个tablix 结构非常简单。
CREATE TABLE #wam(
[date] [date] NULL,
[BucketSort] [varchar](5) NULL,
[DAY1] [varchar](max) NULL
)
insert #wam values ('2019-02-12' ,'08:00', 'CALL 1')
insert #wam values ('2019-02-12' ,'08:00', 'PTO')
insert #wam values ('2019-02-12' ,'08:00', 'TIME OFF REQUEST APPROVED')
insert #wam values ('2019-02-12' ,'08:00', 'AVAILABLE')
insert #wam values ('2019-02-12' ,'09:00', 'COUNTY LINE MILEAGE')
insert #wam values ('2019-02-12' ,'16:00', 'MILEAGE FOR ATTEMPTED VISIT AND NON VISITS')
insert #wam values ('2019-02-12' ,'16:00', 'COUNTY LINE MILEAGE')
insert #wam values ('2019-02-13' ,'08:00', 'CALL 2')
insert #wam values ('2019-02-13' ,'08:00', 'AVAILABLE')
insert #wam values ('2019-02-13' ,'09:00', 'NVA Custom1')
insert #wam values ('2019-02-13' ,'16:00', 'NVA Custom2')
insert #wam values ('2019-02-13' ,'16:00', 'NVA Custom3')
insert #wam values ('2019-02-14' ,'08:00', 'PTO')
insert #wam values ('2019-02-14' ,'08:00', 'AVAILABLE')
insert #wam values ('2019-02-14' ,'09:00', 'NVA Custom4')
insert #wam values ('2019-02-14' ,'16:00', 'NVA Custom6')
insert #wam values ('2019-02-14' ,'16:00', 'NVA Custom5')
insert #wam values ('2019-02-15' ,'08:00', 'TIME OFF REQUEST APPROVED')
insert #wam values ('2019-02-15' ,'08:00', 'AVAILABLE')
insert #wam values ('2019-02-18' ,'08:00', 'AVAILABLE')
从上面的查询可以看出,同一天和同一时间段可以有多个事件。
解决方案
您可以通过在矩阵中嵌套 tablix来做到这一点
示例报告预览
示例报告设计
这是来自 .rdl 文件的报告 XML。
<?xml version="1.0" encoding="utf-8"?>
<Report MustUnderstand="df" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns:df="http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition/defaultfontfamily">
<Description>A calendar view of the events</Description>
<df:DefaultFontFamily>Segoe UI</df:DefaultFontFamily>
<AutoRefresh>0</AutoRefresh>
<DataSources>
<DataSource Name="yourdatasource">
<DataSourceReference>yourdatasource</DataSourceReference>
<rd:SecurityType>None</rd:SecurityType>
<rd:DataSourceID>68eb8e17-19b1-4ab3-8315-91c1cb31c63a</rd:DataSourceID>
</DataSource>
</DataSources>
<DataSets>
<DataSet Name="Sessions">
<Query>
<DataSourceName>yourdatasource</DataSourceName>
<CommandText>SELECT tbl.* FROM (VALUES
( 'John Doe', '12-Feb-2019', '08:00', 'CALL 1')
, ( 'John Doe', '12-Feb-2019', '08:00', 'PTO')
, ( 'John Doe', '12-Feb-2019', '08:00', 'TIME OFF REQUEST APPROVED')
, ( 'John Doe', '12-Feb-2019', '08:00', 'AVAILABLE')
, ( 'John Doe', '12-Feb-2019', '09:00', 'COUNTY LINE MILEAGE')
, ( 'John Doe', '12-Feb-2019', '16:00', 'MILEAGE FOR ATTEMPTED VISIT AND NON VISITS')
, ( 'John Doe', '12-Feb-2019', '16:00', 'COUNTY LINE MILEAGE')
, ( 'John Doe', '13-Feb-2019', '08:00', 'CALL 2')
, ( 'John Doe', '13-Feb-2019', '08:00', 'AVAILABLE')
, ( 'John Doe', '13-Feb-2019', '09:00', 'NVA Custom1')
, ( 'John Doe', '13-Feb-2019', '16:00', 'NVA Custom2')
, ( 'John Doe', '13-Feb-2019', '16:00', 'NVA Custom3')
, ( 'John Doe', '14-Feb-2019', '08:00', 'PTO')
, ( 'John Doe', '14-Feb-2019', '08:00', 'AVAILABLE')
, ( 'John Doe', '14-Feb-2019', '09:00', 'NVA Custom4')
, ( 'John Doe', '14-Feb-2019', '16:00', 'NVA Custom6')
, ( 'John Doe', '14-Feb-2019', '16:00', 'NVA Custom5')
, ( 'John Doe', '15-Feb-2019', '08:00', 'TIME OFF REQUEST APPROVED')
, ( 'John Doe', '15-Feb-2019', '08:00', 'AVAILABLE')
, ( 'John Doe', '18-Feb-2019', '08:00', 'AVAILABLE')
) tbl ([UserName], [date], [BucketSort], [DAY1]) </CommandText>
</Query>
<Fields>
<Field Name="UserName">
<DataField>UserName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="date">
<DataField>date</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="BucketSort">
<DataField>BucketSort</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="DAY1">
<DataField>DAY1</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
</DataSet>
</DataSets>
<ReportSections>
<ReportSection>
<Body>
<ReportItems>
<Tablix Name="tbxDynamicGroupHeader">
<TablixBody>
<TablixColumns>
<TablixColumn>
<Width>9.65604cm</Width>
</TablixColumn>
</TablixColumns>
<TablixRows>
<TablixRow>
<Height>0.6cm</Height>
<TablixCells>
<TablixCell>
<CellContents>
<Textbox Name="lblDynamicGroupHeader">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value />
<Style>
<FontFamily>Tahoma</FontFamily>
<FontWeight>Bold</FontWeight>
<Color>DimGray</Color>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Left</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<Style>
<Border>
<Style>Solid</Style>
<Width>0.75pt</Width>
</Border>
<BackgroundColor>=Variables!ColorTableHeader.Value</BackgroundColor>
<VerticalAlign>Middle</VerticalAlign>
</Style>
</Textbox>
</CellContents>
</TablixCell>
</TablixCells>
</TablixRow>
<TablixRow>
<Height>1.6675cm</Height>
<TablixCells>
<TablixCell>
<CellContents>
<Tablix Name="tbxBookingDetails">
<TablixBody>
<TablixColumns>
<TablixColumn>
<Width>7.12802cm</Width>
</TablixColumn>
</TablixColumns>
<TablixRows>
<TablixRow>
<Height>0.9175cm</Height>
<TablixCells>
<TablixCell>
<CellContents>
<Textbox Name="lblday_name">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!date.Value</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontSize>8pt</FontSize>
<FontWeight>Bold</FontWeight>
<Color>DimGray</Color>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Center</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<Style>
<Border>
<Style>Solid</Style>
<Width>0.75pt</Width>
</Border>
<BackgroundColor>=Variables!ColorTableHeader.Value</BackgroundColor>
<VerticalAlign>Middle</VerticalAlign>
</Style>
</Textbox>
</CellContents>
</TablixCell>
</TablixCells>
</TablixRow>
<TablixRow>
<Height>0.75cm</Height>
<TablixCells>
<TablixCell>
<CellContents>
<Tablix Name="tbxDay">
<TablixBody>
<TablixColumns>
<TablixColumn>
<Width>7.12802cm</Width>
</TablixColumn>
</TablixColumns>
<TablixRows>
<TablixRow>
<Height>0.75cm</Height>
<TablixCells>
<TablixCell>
<CellContents>
<Textbox Name="txtroom_name">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!DAY1.Value</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontSize>8pt</FontSize>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Left</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>None</Style>
<Width>0.75pt</Width>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
</TablixCells>
</TablixRow>
</TablixRows>
</TablixBody>
<TablixColumnHierarchy>
<TablixMembers>
<TablixMember />
</TablixMembers>
</TablixColumnHierarchy>
<TablixRowHierarchy>
<TablixMembers>
<TablixMember>
<Group Name="Details" />
<SortExpressions>
<SortExpression>
<Value>=Fields!DAY1.Value</Value>
</SortExpression>
</SortExpressions>
<TablixMembers>
<TablixMember />
</TablixMembers>
</TablixMember>
</TablixMembers>
</TablixRowHierarchy>
<DataSetName>Sessions</DataSetName>
<Style>
<Border>
<Style>Solid</Style>
<Width>0.75pt</Width>
</Border>
<BackgroundColor>=Code.CandyStripe(False, Variables!ColorCandyStripeOddRow.Value, Variables!ColorCandyStripeEvenRow.Value)</BackgroundColor>
<FontFamily>Tahoma</FontFamily>
<FontSize>8pt</FontSize>
</Style>
</Tablix>
</CellContents>
</TablixCell>
</TablixCells>
</TablixRow>
</TablixRows>
</TablixBody>
<TablixColumnHierarchy>
<TablixMembers>
<TablixMember>
<Group Name="ColumnGroup">
<GroupExpressions>
<GroupExpression>=Fields!date.Value</GroupExpression>
</GroupExpressions>
</Group>
<SortExpressions>
<SortExpression>
<Value>=Fields!date.Value</Value>
</SortExpression>
</SortExpressions>
<KeepTogether>true</KeepTogether>
</TablixMember>
</TablixMembers>
</TablixColumnHierarchy>
<TablixRowHierarchy>
<TablixMembers>
<TablixMember>
<TablixHeader>
<Size>2.52802cm</Size>
<CellContents>
<Textbox Name="lblweek_date">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Time</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontSize>8pt</FontSize>
<FontWeight>Bold</FontWeight>
<Color>DimGray</Color>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Center</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<Style>
<Border>
<Style>Solid</Style>
<Width>0.75pt</Width>
</Border>
<BackgroundColor>=Variables!ColorTableHeader.Value</BackgroundColor>
<VerticalAlign>Middle</VerticalAlign>
</Style>
</Textbox>
</CellContents>
</TablixHeader>
<TablixMembers>
<TablixMember>
<KeepTogether>true</KeepTogether>
</TablixMember>
</TablixMembers>
<KeepWithGroup>After</KeepWithGroup>
<RepeatOnNewPage>true</RepeatOnNewPage>
<KeepTogether>true</KeepTogether>
</TablixMember>
<TablixMember>
<Group Name="week_date">
<GroupExpressions>
<GroupExpression>=Fields!BucketSort.Value</GroupExpression>
</GroupExpressions>
</Group>
<SortExpressions>
<SortExpression>
<Value>=Fields!BucketSort.Value</Value>
</SortExpression>
</SortExpressions>
<TablixHeader>
<Size>2.52802cm</Size>
<CellContents>
<Textbox Name="txtweek_date">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!BucketSort.Value</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontSize>8pt</FontSize>
<FontWeight>Bold</FontWeight>
<Format>=Variables!FormatDate.Value</Format>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Center</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<Style>
<Border>
<Style>Solid</Style>
<Width>0.75pt</Width>
</Border>
<BackgroundColor>=Code.CandyStripe(True, Variables!ColorCandyStripeEvenRow.Value, Variables!ColorCandyStripeOddRow.Value)</BackgroundColor>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixHeader>
<TablixMembers>
<TablixMember>
<RepeatOnNewPage>true</RepeatOnNewPage>
<KeepTogether>true</KeepTogether>
</TablixMember>
</TablixMembers>
<KeepTogether>true</KeepTogether>
</TablixMember>
</TablixMembers>
</TablixRowHierarchy>
<RepeatRowHeaders>true</RepeatRowHeaders>
<DataSetName>Sessions</DataSetName>
<Style>
<Border>
<Style>None</Style>
<Width>0.75pt</Width>
</Border>
<FontFamily>Tahoma</FontFamily>
</Style>
</Tablix>
</CellContents>
</TablixCell>
</TablixCells>
</TablixRow>
</TablixRows>
</TablixBody>
<TablixColumnHierarchy>
<TablixMembers>
<TablixMember />
</TablixMembers>
</TablixColumnHierarchy>
<TablixRowHierarchy>
<TablixMembers>
<TablixMember>
<Group Name="grpDocumentMap">
<GroupExpressions>
<GroupExpression>=Fields!UserName.Value</GroupExpression>
</GroupExpressions>
<PageBreak>
<BreakLocation>Between</BreakLocation>
</PageBreak>
</Group>
<SortExpressions>
<SortExpression>
<Value>=Fields!UserName.Value</Value>
</SortExpression>
</SortExpressions>
<TablixMembers>
<TablixMember />
<TablixMember />
</TablixMembers>
</TablixMember>
</TablixMembers>
</TablixRowHierarchy>
<RepeatColumnHeaders>true</RepeatColumnHeaders>
<KeepTogether>true</KeepTogether>
<DataSetName>Sessions</DataSetName>
<Height>2.2675cm</Height>
<Width>9.65604cm</Width>
<Style>
<Border>
<Style>None</Style>
</Border>
</Style>
</Tablix>
</ReportItems>
<Height>0.89272in</Height>
<Style />
</Body>
<Width>3.80159in</Width>
<Page>
<PageFooter>
<Height>0.02646cm</Height>
<PrintOnFirstPage>true</PrintOnFirstPage>
<PrintOnLastPage>true</PrintOnLastPage>
<Style>
<Border>
<Style>None</Style>
</Border>
</Style>
</PageFooter>
<PageHeight>29.7cm</PageHeight>
<PageWidth>42cm</PageWidth>
<LeftMargin>0.5cm</LeftMargin>
<RightMargin>0.5cm</RightMargin>
<TopMargin>1cm</TopMargin>
<BottomMargin>1cm</BottomMargin>
<ColumnSpacing>0.13cm</ColumnSpacing>
<Style>
<Border>
<Style>None</Style>
</Border>
<BottomBorder>
<Style>Solid</Style>
</BottomBorder>
</Style>
</Page>
</ReportSection>
</ReportSections>
<ReportParametersLayout>
<GridLayoutDefinition>
<NumberOfColumns>3</NumberOfColumns>
<NumberOfRows>2</NumberOfRows>
</GridLayoutDefinition>
</ReportParametersLayout>
<Code>
Private Alt As Boolean
Public Function CandyStripe(ByVal NewRow As Boolean, ByVal OddColor as String, ByVal EvenColor as String) As String
'------------------------------------------------------------------------------------------------
' Purpose: To candy stripe the detail rows of a report
' Example: BackgroundColor = Code.CandyStripe()
' Note: The first column needs a parameter of "True" passed in example: Code.CandyStripe(True)
'------------------------------------------------------------------------------------------------
If NewRow Then
Alt = Not Alt
End If
If Alt Then
Return OddColor
Else
Return EvenColor
End If
End Function
</Code>
<Variables>
<Variable Name="ColorCandyStripeEvenRow">
<Value>#FFFFFF</Value>
</Variable>
<Variable Name="ColorCandyStripeOddRow">
<Value>WhiteSmoke</Value>
</Variable>
<Variable Name="ColorTableHeader">
<Value>WhiteSmoke</Value>
</Variable>
<Variable Name="FormatDate">
<Value>dd-MMM-yyyy</Value>
</Variable>
<Variable Name="FormatDateTime">
<Value>dd-MMM-yyyy hh:mm tt</Value>
</Variable>
<Variable Name="UserName">
<Value>=Right(User!UserID, len(User!UserID)-instr(User!UserID, "\"))</Value>
</Variable>
</Variables>
<rd:ReportUnitType>Cm</rd:ReportUnitType>
<rd:ReportID>6203a576-d7cc-4b57-9a9b-12cf325d6203</rd:ReportID>
</Report>
推荐阅读
- python - Python,熊猫数据框的输入行,不同数据框的输出行。并行运行
- ios - 如果用户已经快速登录,如何跳过登录
- kubernetes - 更多副本中的 Kubernetes pod - 反亲和性规则
- ios - 当用户开始在 UIImageView 上滑动时,UITableView 滑动操作不起作用
- php - ErrorException (E_NOTICE) 未定义变量:用户
- php - unset 给出 array_search() 期望参数 2 是数组,给定字符串
- css - HTML/CSS 表:在列上对齐“子行”
- javascript - 仅删除数字开头不必要的 0
- python - 是否在 Python 函数中创建要返回的新变量
- python - 无法使用 networkx 和 d3 创建图形链接/边