SSRS为每个组的远程客户查找类别计数，挑战
SSRS为每个组的远程客户查找类别计数，挑战
提问于 2019-06-04 17:53:10
我试着计算每个年龄类别的不同的Cust为每个供应商，并有大的问题在这里。我可以得到正确的CountDistinct客户数量，但不能得到正确的细分成人/高级类别。您是否可以考虑在rdl中实现，因为我真的不能触摸现有的数据集，这是简单的细节，就像在我的图片的右边。
SELECT 'Agent100' ProvID, '1111' CustID, 'Adult' Cat, '2019-1-1' Serv\_Date UNION SELECT 'Agent100' ProvID, '1111' CustID, 'Adult' Cat, '2019-2-2' Serv\_Date UNION SELECT 'Agent100' ProvID, '1111' CustID, 'Adult' Cat, '2019-3-3' Serv\_Date UNION SELECT 'Agent100' ProvID, '222' CustID, 'Senior' Cat, '2019-10-10' Serv\_Date UNION SELECT 'Agent100' ProvID, '333' CustID, 'Adult' Cat, '2019-11-11' Serv\_Date UNION SELECT 'Agent2' ProvID, '555' CustID, 'Adult' Cat, '2019-1-1' Serv\_Date 粘贴了整个测试rdl，只需更改DataSource即可。感谢所有的SSRS‘s’s‘所以治愈了他们的好工作！
<?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">
<df:DefaultFontFamily>Segoe UI</df:DefaultFontFamily>
<AutoRefresh>0</AutoRefresh>
<DataSources>
<DataSource Name="DataSource1">
<DataSourceReference>YourDataSource</DataSourceReference>
<rd:SecurityType>None</rd:SecurityType>
<rd:DataSourceID>9c15088f-a478-45ea-ad37-a0a289162354</rd:DataSourceID>
</DataSource>
</DataSources>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<DataSourceName>DataSource1</DataSourceName>
<CommandText>SELECT 'Agent100' AS ProvID, '1111' AS CustID, 'Adult' AS Cat, '2019-1-1' AS Serv_Date
UNION
SELECT 'Agent100' AS ProvID, '1111' AS CustID, 'Adult' AS Cat, '2019-2-2' AS Serv_Date
UNION
SELECT 'Agent100' AS ProvID, '1111' AS CustID, 'Adult' AS Cat, '2019-3-3' AS Serv_Date
UNION
SELECT 'Agent100' AS ProvID, '222' AS CustID, 'Senior' AS Cat, '2019-10-10' AS Serv_Date
UNION
SELECT 'Agent100' AS ProvID, '333' AS CustID, 'Adult' AS Cat, '2019-11-11' AS Serv_Date
UNION
SELECT 'Agent2' AS ProvID, '555' AS CustID, 'Adult' AS Cat, '2019-1-1' AS Serv_Date</CommandText>
</Query>
<Fields>
<Field Name="ProvID">
<DataField>ProvID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CustID">
<DataField>CustID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Cat">
<DataField>Cat</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Serv_Date">
<DataField>Serv_Date</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
</DataSet>
</DataSets>
<ReportSections>
<ReportSection>
<Body>
<ReportItems>
<Tablix Name="Tablix2">
<TablixBody>
<TablixColumns>
<TablixColumn>
<Width>0.6875in</Width>
</TablixColumn>
</TablixColumns>
<TablixRows>
<TablixRow>
<Height>0.25in</Height>
<TablixCells>
<TablixCell>
<CellContents>
<Textbox Name="Textbox6">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Cat</Value>
<Style>
<Color>White</Color>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox5</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>Gray</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="Cat2">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!Cat.Value</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Cat</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>
<Visibility>
<Hidden>true</Hidden>
</Visibility>
</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>Prov ID</Value>
<Style>
<Color>White</Color>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox19</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>DarkBlue</BackgroundColor>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixHeader>
<TablixMembers>
<TablixMember>
<TablixHeader>
<Size>1in</Size>
<CellContents>
<Textbox Name="Textbox35">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Adult</Value>
<Style>
<Color>White</Color>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Center</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox35</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>DarkBlue</BackgroundColor>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixHeader>
<TablixMembers>
<TablixMember>
<TablixHeader>
<Size>1in</Size>
<CellContents>
<Textbox Name="Textbox33">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Senior</Value>
<Style>
<Color>White</Color>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Center</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox33</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>DarkBlue</BackgroundColor>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixHeader>
<TablixMembers>
<TablixMember>
<TablixHeader>
<Size>1in</Size>
<CellContents>
<Textbox Name="Textbox37">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Distinct Cust</Value>
<Style>
<Color>White</Color>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Center</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox37</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>DarkBlue</BackgroundColor>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixHeader>
<TablixMembers>
<TablixMember />
</TablixMembers>
</TablixMember>
</TablixMembers>
</TablixMember>
</TablixMembers>
</TablixMember>
</TablixMembers>
<KeepWithGroup>After</KeepWithGroup>
</TablixMember>
<TablixMember>
<Group Name="ProvID">
<GroupExpressions>
<GroupExpression>=Fields!ProvID.Value</GroupExpression>
</GroupExpressions>
</Group>
<SortExpressions>
<SortExpression>
<Value>=Fields!ProvID.Value</Value>
</SortExpression>
</SortExpressions>
<TablixHeader>
<Size>1in</Size>
<CellContents>
<Textbox Name="ProvID1">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!ProvID.Value</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>ProvID1</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>
<TablixHeader>
<Size>1in</Size>
<CellContents>
<Textbox Name="Textbox36">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Sum(Iif(Fields!Cat.Value="Adult",1,0))</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox36</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>
<TablixHeader>
<Size>1in</Size>
<CellContents>
<Textbox Name="Textbox34">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Sum(Iif(Fields!Cat.Value="Senior",1,0))</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox34</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>
<TablixHeader>
<Size>1in</Size>
<CellContents>
<Textbox Name="Textbox38">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=CountDistinct(Fields!CustID.Value)</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox38</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 />
</TablixMembers>
</TablixMember>
</TablixMembers>
</TablixMember>
</TablixMembers>
</TablixMember>
</TablixMembers>
</TablixMember>
</TablixMembers>
</TablixRowHierarchy>
<DataSetName>DataSet1</DataSetName>
<Height>0.5in</Height>
<Width>4.6875in</Width>
<Style>
<Border>
<Style>None</Style>
</Border>
</Style>
</Tablix>
</ReportItems>
<Height>0.5in</Height>
<Style />
</Body>
<Width>4.6875in</Width>
<Page>
<LeftMargin>1in</LeftMargin>
<RightMargin>1in</RightMargin>
<TopMargin>1in</TopMargin>
<BottomMargin>1in</BottomMargin>
<Style />
</Page>
</ReportSection>
</ReportSections>
<ReportParametersLayout>
<GridLayoutDefinition>
<NumberOfColumns>4</NumberOfColumns>
<NumberOfRows>2</NumberOfRows>
</GridLayoutDefinition>
</ReportParametersLayout>
<rd:ReportUnitType>Inch</rd:ReportUnitType>
<rd:ReportID>3e0536ce-aa9c-4d8f-afa8-3f0ed263e768</rd:ReportID>
</Report>回答 2
Stack Overflow用户
回答已采纳
发布于 2019-06-04 19:21:53
你需要使用矩阵而不是表格。因为您已经有了一个很好的数据集，这很容易。
添加矩阵，将ProvID拖到“行”占位符，将Cat拖动到“列”占位符。
在“数据”占位符中，将表达式设置为
=CountDistinct(Fields!CustID.Value)如果您需要第三列中的总数，则右击下面面板中的“猫”列组，然后选择“添加总=>”。
报告设计将如下所示(留给您原始的比较)
当它呈现成这样..。
Stack Overflow用户
发布于 2019-06-04 21:14:19
对于这种简单的情况，它应该可以工作，这基本上可以被描述为SUMDISTINCT，如果您有其他列并且希望保留表解决方案，您可以在DataSet中添加位逻辑，比如这里的https://www.codeproject.com/Questions/1045429/How-to-get-Distinct-Sum-at-SSRS-Expression
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56449049
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号