|
|
You're in the
sql server reporting services
group:How do we do a Grand Total
sql server reporting services:
don't think it can be done! First there isn't a report footer (only a page footer) and you can't count the number of (unique) occurences of a group header. See my example below. This example is overly simplified - I am actually tracking 16 metrics in the report, but if I can this to work, then I can get all of them to work. So far, I have tried Sum and RunningValue (blah, blah, blah) - but everything is "out of scope". Please - someone prove me wrong! I am beginning to really doubt that MSRS can do this! OR Room 1 <------------Group 1 Header 8/1/2004 <------------Group 2 Header xxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxx Cases 3 <------------Group 2 Footer 8/3/2004 xxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxx Cases 2 Total Cases: 5 <------------Group 1 Footer OR Room 2 8/1/2004 xxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxx Cases 3 8/2/2004 xxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxx Cases 2 8/4/2004 xxxxxxxxxxxxxxxxxxxxxxx Cases 1 (Report Footer) Total Days of Utilization: 5 Total Cases: 11 Total Rooms Utilized: 2
One of the things you might be having trouble with is that Reporting
Services allows you to have multiple queries and multiple data regions in the same report. In products where you only have a single query, the collection that you want to aggregate is implicit. For Reporting Services, you need to specify the scope of your aggregate. Formulas inside of a data region implicitly get the scope of the containing data region. Formulas outside of a data region must have a scope specified. Also, because Reporting Services reports are not constrained to horizontal bands, there is no need to define a single 'report footer'. The report footer is the part of the body that is after any repeating sections. If you are using a table, the table footer can serves as the report footer. If you are using lists, put a textbox after the outer list. Then specify the aggregate with a scope, i.e. =Sum(Fields!Quantity.Value, "DataSet1"). This will aggregate over all rows of DataSet1. You could even have a grand total of values from two data sets, =Sum(Fields!Quantity.Value, "DataSet1") + =Sum(Fields!Quantity.Value, "DataSet2"). -- Brian Welcker Group Program Manager SQL Server Reporting Services This posting is provided "AS IS" with no warranties, and confers no rights. [quoted text, click to view] "Kevin B" <No-SPAM@misnet.info> wrote in message news:uYgaSXagEHA.3016@tk2msftngp13.phx.gbl... >I am trying to do a grand total for a report - but, would you know it, I > don't think it can be done! First there isn't a report footer (only a > page > footer) and you can't count the number of (unique) occurences of a group > header. See my example below. This example is overly simplified - I am > actually tracking 16 metrics in the report, but if I can this to work, > then > I can get all of them to work. > > So far, I have tried Sum and RunningValue (blah, blah, blah) - but > everything is "out of scope". > > Please - someone prove me wrong! I am beginning to really doubt that MSRS > can do this! > > > OR Room 1 <------------Group 1 Header > 8/1/2004 <------------Group 2 Header > xxxxxxxxxxxxxxxxxxxxxxx > xxxxxxxxxxxxxxxxxxxxxxx > xxxxxxxxxxxxxxxxxxxxxxx > Cases 3 > <------------Group 2 Footer > 8/3/2004 > xxxxxxxxxxxxxxxxxxxxxxx > xxxxxxxxxxxxxxxxxxxxxxx > Cases 2 > Total Cases: 5 > <------------Group 1 Footer > OR Room 2 > 8/1/2004 > xxxxxxxxxxxxxxxxxxxxxxx > xxxxxxxxxxxxxxxxxxxxxxx > xxxxxxxxxxxxxxxxxxxxxxx > Cases 3 > 8/2/2004 > xxxxxxxxxxxxxxxxxxxxxxx > xxxxxxxxxxxxxxxxxxxxxxx > Cases 2 > 8/4/2004 > xxxxxxxxxxxxxxxxxxxxxxx > Cases 1 > > > (Report Footer) > Total Days of Utilization: 5 > Total Cases: 11 > Total Rooms Utilized: 2 > > >
I follow your explantion, but you didn't follow my problem. I don't want to
to total all the records returned by the SQL statement! Specifically, I want to count the number of unique values within a group. Hopefully, to simply, I have attached a RDL against Northwinds (just change the server name in the dataset). Draw your attention to "textbox8". I have used this formula: =count(Fields!ShippedDate.Value,"table1_Group2") and it says it is out of scope...well the scope I want to count is table1_Group2! If I use this formula: =count(Fields!ShippedDate.Value,"table1_Group2") it returns 143 as the value. The value I am expecting is 6 - the number of months in the report. Thanks for you help - I have tried everything I can think of. Maybe this can help you to help me. -KB Here is the rdl referenced above <?xml version="1.0" encoding="utf-8"?> <Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini tion" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"> <RightMargin>1in</RightMargin> <Body> <ReportItems> <Textbox Name="textbox1"> <Style> <PaddingLeft>2pt</PaddingLeft> <FontFamily>Times New Roman</FontFamily> <BackgroundColor>Brown</BackgroundColor> <BorderWidth> <Bottom>3pt</Bottom> </BorderWidth> <BorderColor> <Bottom>Black</Bottom> </BorderColor> <BorderStyle> <Bottom>Solid</Bottom> </BorderStyle> <FontSize>18pt</FontSize> <TextAlign>Center</TextAlign> <Color>White</Color> <PaddingBottom>2pt</PaddingBottom> <PaddingTop>2pt</PaddingTop> <PaddingRight>2pt</PaddingRight> <FontWeight>700</FontWeight> </Style> <ZIndex>1</ZIndex> <rd:DefaultName>textbox1</rd:DefaultName> <Height>0.33in</Height> <CanGrow>true</CanGrow> <Value>Report1</Value> </Textbox> <Table Name="table1"> <Style> <BackgroundColor>White</BackgroundColor> <BorderWidth> <Top>3pt</Top> </BorderWidth> <BorderStyle> <Top>Solid</Top> </BorderStyle> </Style> <Header> <TableRows> <TableRow> <Height>0.21in</Height> <TableCells> <TableCell> <ReportItems> <Textbox Name="textbox23"> <Style> <PaddingLeft>2pt</PaddingLeft> <BorderWidth> <Bottom>2pt</Bottom> </BorderWidth> <BorderColor> <Bottom>DarkRed</Bottom> </BorderColor> <BorderStyle> <Bottom>Solid</Bottom> </BorderStyle> <TextAlign>Right</TextAlign> <PaddingBottom>2pt</PaddingBottom> <PaddingTop>2pt</PaddingTop> <PaddingRight>2pt</PaddingRight> <FontWeight>900</FontWeight> </Style> <ZIndex>29</ZIndex> <rd:DefaultName>textbox23</rd:DefaultName> <CanGrow>true</CanGrow> <Value>Year Shipped</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox17"> <Style> <PaddingLeft>2pt</PaddingLeft> <BorderWidth> <Bottom>2pt</Bottom> </BorderWidth> <BorderColor> <Bottom>DarkRed</Bottom> </BorderColor> <BorderStyle> <Bottom>Solid</Bottom> </BorderStyle> <TextAlign>Right</TextAlign> <PaddingBottom>2pt</PaddingBottom> <PaddingTop>2pt</PaddingTop> <PaddingRight>2pt</PaddingRight> <FontWeight>900</FontWeight> </Style> <ZIndex>28</ZIndex> <rd:DefaultName>textbox17</rd:DefaultName> <CanGrow>true</CnGrow> <Value>Month Shipped</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox27"> <Style> <PaddingLeft>2pt</PaddingLeft> <BorderWidth> <Bottom>2pt</Bottom> </BorderWidth> <BorderColor> <Bottom>DarkRed</Bottom> </BorderColor> <BorderStyle> <Bottom>Solid</Bottom> </BorderStyle> <PaddingBottom>2pt</PaddingBottom> <PaddingTop>2pt</PaddingTop> <PaddingRight>2pt</PaddingRight> </Style> <ZIndex>27</ZIndex> <rd:DefaultName>textbox27</rd:DefaultName> <CanGrow>true</CanGrow> <Value /> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox2"> <Style> <PaddingLeft>2pt</PaddingLeft> <BorderWidth> <Bottom>2pt</Bottom> </BorderWidth> <BorderColor> <Bottom>DarkRed</Bottom> </BorderColor> <BorderStyle> <Bottom>Solid</Bottom> </BorderStyle> <PaddingBottom>2pt</PaddingBottom> <PaddingTop>2pt</PaddingTop> <PaddingRight>2pt</PaddingRight> </Style> <ZIndex>26</ZIndex> <rd:DefaultName>textbox2</rd:DefaultName> <CanGrow>true</CanGrow> <Value /> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox3"> <Style> <PaddingLeft>2pt</PaddingLeft> <BorderWidth> <Bottom>2pt</Bottom> </BorderWidth> <BorderColor> <Bottom>DarkRed</Bottom> </BorderColor> <BorderStyle> <Bottom>Solid</Bottom> </BorderStyle> <PaddingBottom>2pt</PaddingBottom> <PaddingTop>2pt</PaddingTop> <PaddingRight>2pt</PaddingRight> </Style> <ZIndex>25</ZIndex> <rd:DefaultName>textbox3</rd:DefaultName> <CanGrow>true</CanGrow> <Value /> </Textbox> </ReportItems> </TableCell> </TableCells> </TableRow> </TableRows> <RepeatOnNewPage>true</RepeatOnNewPage> </Header> <Details> <TableRows> <TableRow> <Height>0.21in</Height> <TableCells> <TableCell> <ReportItems> <Textbox Name="textbox26"> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingBottom>2pt</PaddingBottom> <PaddingTop>2pt</PaddingTop> <PaddingRight>2pt</PaddingRight> </Style> <ZIndex>4</ZIndex> <rd:DefaultName>textbox26</rd:DefaultName> <CanGrow>true</CanGrow> <Value /> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="textbox20"> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingBottom>2pt</PaddingBottom> <PaddingTop>2pt</PaddingTop> <PaddingRight>2pt</PaddingRight> </Style> <ZIndex>3</ZIndex> <rd:DefaultName>textbox20</rd:DefaultName> <CanGrow>true</CanGrow> <Value /> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="ShippedDate"> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingBottom>2pt</PaddingBottom> <PaddingTop>2pt</PaddingTop> <PaddingRight>2pt</PaddingRight> </Style> <ZIndex>2</ZIndex> <rd:DefaultName>ShippedDate</rd:DefaultName> <CanGrow>true</CanGrow> <Value>=Fields!ShippedDate.Value</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="OrderID"> <Style> <PaddingLeft>2pt</PaddingLeft> <PaddingBottom>2pt</PaddingBottom> <PaddingTop>2pt</PaddingTop> <PaddingRight>2pt</PaddingRight> </Style> <ZIndex>1</ZIndex> <rd:DefaultName>OrderID</rd:DefaultName> <CanGrow>true</CanGrow> <Value>=Fields!OrderID.Value</Value> </Textbox> </ReportItems> </TableCell> <TableCell> <ReportItems> <Textbox Name="Subtotal"> <Style>
You need to use the CountDistinct aggregate. The key is what do you want to
count the distinct values of? If you want months within the year, you need to specify: =countdistinct(Fields!MonthShipped.Value) as the value of your textbox. -- Brian Welcker Group Program Manager Microsoft SQL Server Reporting Services This posting is provided "AS IS" with no warranties, and confers no rights. [quoted text, click to view] "Kevin B" <No-SPAM@misnet.info> wrote in message
news:%23TG0bb5gEHA.3864@TK2MSFTNGP10.phx.gbl... >I follow your explantion, but you didn't follow my problem. I don't want >to > to total all the records returned by the SQL statement! Specifically, I > want to count the number of unique values within a group. Hopefully, to > simply, I have attached a RDL against Northwinds (just change the server > name in the dataset). > > Draw your attention to "textbox8". I have used this formula: > =count(Fields!ShippedDate.Value,"table1_Group2") and it says it is out of > scope...well the scope I want to count is table1_Group2! If I use this > formula: =count(Fields!ShippedDate.Value,"table1_Group2") it returns 143 > as the value. The value I am expecting is 6 - the number of months in the > report. > > Thanks for you help - I have tried everything I can think of. Maybe this > can help you to help me. > -KB > > > Here is the rdl referenced above > > <?xml version="1.0" encoding="utf-8"?> > > <Report > xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini > tion" > xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"> > > <RightMargin>1in</RightMargin> > > <Body> > > <ReportItems> > > <Textbox Name="textbox1"> > > <Style> > > <PaddingLeft>2pt</PaddingLeft> > > <FontFamily>Times New Roman</FontFamily> > > <BackgroundColor>Brown</BackgroundColor> > > <BorderWidth> > > <Bottom>3pt</Bottom> > > </BorderWidth> > > <BorderColor> > > <Bottom>Black</Bottom> > > </BorderColor> > > <BorderStyle> > > <Bottom>Solid</Bottom> > > </BorderStyle> > > <FontSize>18pt</FontSize> > > <TextAlign>Center</TextAlign> > > <Color>White</Color> > > <PaddingBottom>2pt</PaddingBottom> > > <PaddingTop>2pt</PaddingTop> > > <PaddingRight>2pt</PaddingRight> > > <FontWeight>700</FontWeight> > > </Style> > > <ZIndex>1</ZIndex> > > <rd:DefaultName>textbox1</rd:DefaultName> > > <Height>0.33in</Height> > > <CanGrow>true</CanGrow> > > <Value>Report1</Value> > > </Textbox> > > <Table Name="table1"> > > <Style> > > <BackgroundColor>White</BackgroundColor> > > <BorderWidth> > > <Top>3pt</Top> > > </BorderWidth> > > <BorderStyle> > > <Top>Solid</Top> > > </BorderStyle> > > </Style> > > <Header> > > <TableRows> > > <TableRow> > > <Height>0.21in</Height> > > <TableCells> > > <TableCell> > > <ReportItems> > > <Textbox Name="textbox23"> > > <Style> > > <PaddingLeft>2pt</PaddingLeft> > > <BorderWidth> > > <Bottom>2pt</Bottom> > > </BorderWidth> > > <BorderColor> > > <Bottom>DarkRed</Bottom> > > </BorderColor> > > <BorderStyle> > > <Bottom>Solid</Bottom> > > </BorderStyle> > > <TextAlign>Right</TextAlign> > > <PaddingBottom>2pt</PaddingBottom> > > <PaddingTop>2pt</PaddingTop> > > <PaddingRight>2pt</PaddingRight> > > <FontWeight>900</FontWeight> > > </Style> > > <ZIndex>29</ZIndex> > > <rd:DefaultName>textbox23</rd:DefaultName> > > <CanGrow>true</CanGrow> > > <Value>Year Shipped</Value> > > </Textbox> > > </ReportItems> > > </TableCell> > > <TableCell> > > <ReportItems> > > <Textbox Name="textbox17"> > > <Style> > > <PaddingLeft>2pt</PaddingLeft> > > <BorderWidth> > > <Bottom>2pt</Bottom> > > </BorderWidth> > > <BorderColor> > > <Bottom>DarkRed</Bottom> > > </BorderColor> > > <BorderStyle> > > <Bottom>Solid</Bottom> > > </BorderStyle> > > <TextAlign>Right</TextAlign> > > <PaddingBottom>2pt</PaddingBottom> > > <PaddingTop>2pt</PaddingTop> > > <PaddingRight>2pt</PaddingRight> > > <FontWeight>900</FontWeight> > > </Style> > > <ZIndex>28</ZIndex> > > <rd:DefaultName>textbox17</rd:DefaultName> > > <CanGrow>true</CnGrow> > > <Value>Month Shipped</Value> > > </Textbox> > > </ReportItems> > > </TableCell> > > <TableCell> > > <ReportItems> > > <Textbox Name="textbox27"> > > <Style> > > <PaddingLeft>2pt</PaddingLeft> > > <BorderWidth> > > <Bottom>2pt</Bottom> > > </BorderWidth> > > <BorderColor> > > <Bottom>DarkRed</Bottom> > > </BorderColor> > > <BorderStyle> > > <Bottom>Solid</Bottom> > > </BorderStyle> > > <PaddingBottom>2pt</PaddingBottom> > > <PaddingTop>2pt</PaddingTop> > > <PaddingRight>2pt</PaddingRight> > > </Style> > > <ZIndex>27</ZIndex> > > <rd:DefaultName>textbox27</rd:DefaultName> > > <CanGrow>true</CanGrow> > > <Value /> > > </Textbox> > > </ReportItems> > > </TableCell> > > <TableCell> > > <ReportItems> > > <Textbox Name="textbox2"> > > <Style> > > <PaddingLeft>2pt</PaddingLeft> > > <BorderWidth> > > <Bottom>2pt</Bottom> > > </BorderWidth> > > <BorderColor> > > <Bottom>DarkRed</Bottom> > > </BorderColor> > > <BorderStyle> > > <Bottom>Solid</Bottom> > > </BorderStyle> > > <PaddingBottom>2pt</PaddingBottom> > > <PaddingTop>2pt</PaddingTop> > > <PaddingRight>2pt</PaddingRight> > > </Style> > > <ZIndex>26</ZIndex> > > <rd:DefaultName>textbox2</rd:DefaultName> > > <CanGrow>true</CanGrow> > > <Value /> > > </Textbox> > > </ReportItems> > > </TableCell> > > <TableCell> > > <ReportItems> > > <Textbox Name="textbox3"> > > <Style> > > <PaddingLeft>2pt</PaddingLeft> > > <BorderWidth> > > <Bottom>2pt</Bottom> > > </BorderWidth> > > <BorderColor> > > <Bottom>DarkRed</Bottom> > > </BorderColor> > > <BorderStyle> > > <Bottom>Solid</Bottom> > > </BorderStyle> > > <PaddingBottom>2pt</PaddingBottom> > > <PaddingTop>2pt</PaddingTop> > > <PaddingRight>2pt</PaddingRight> > > </Style> > > <ZIndex>25</ZIndex> > > <rd:DefaultName>textbox3</rd:DefaultName> > > <CanGrow>true</CanGrow> > > <Value /> > > </Textbox> > > </ReportItems> > > </TableCell> > > </TableCells> > > </TableRow> > > </TableRows> > > <RepeatOnNewPage>true</RepeatOnNewPage> > > </Header> > > <Details> > > <TableRows> > > <TableRow> > > <Height>0.21in</Height> > > <TableCells> > > <TableCell> > > <ReportItems> > > <Textbox Name="textbox26"> > > <Style> > > <PaddingLeft>2pt</PaddingLeft> > > <PaddingBottom>2pt</PaddingBottom> > > <PaddingTop>2pt</PaddingTop> > > <PaddingRight>2pt</PaddingRight> > > </Style> > > <ZIndex>4</ZIndex> > > <rd:DefaultName>textbox26</rd:DefaultName> > > <CanGrow>true</CanGrow> > > <Value /> > > </Textbox> > > </ReportItems> > > </TableCell> > > <TableCell> > > <ReportItems> > > <Textbox Name="textbox20"> > > <Style> > > <PaddingLeft>2pt</PaddingLeft> >
Assuming the MonthShipped value is actually a full DateTime object, you
should try this to get 14 distinct months: =CountDistinct(Year(Fields!MonthShipped.Value) * 100 + Month(Fields!MonthShipped.Value)) -- This posting is provided "AS IS" with no warranties, and confers no rights. [quoted text, click to view] "Kevin B" <No-SPAM@misnet.info> wrote in message
news:O22qvP8gEHA.596@TK2MSFTNGP11.phx.gbl... > That works, except when I "roll them up" in the table footer, it counts 12 > distinct months...novel, huh?? > > I have tried using the aggregate functions, but it seems they only recognize > a scope looking "out", not "in". For example, I can reference an aggregate > SUM value on table_Group1 in the table1_Group2 footer - but not vice versa. > More to the point, I can't reference an Aggregate sum of table1_Group1 from > the table footer. > > Regarding the CountDistinct function - I can count the distinct values in > Group 2, but how do I count each row in the Group 2 Header report? > > Example: > > Group 1 Header 2003 > Group 2 Header 07 > Group 2 Header 08 > Group 2 Header 09 > Group 2 Header 10 > Group 2 Header 11 > Group 2 Header 12 > Group 1 Footer Total: 6 months > Group 1 Header 2004 > Group 2 Header 01 > Group 2 Header 02 > Group 2 Header 03 > Group 2 Header 04 > Group 2 Header 05 > Group 2 Header 06 > Group 2 Header 07 > Group 2 Header 08 > Group 1 Footer Total: 8 months > Table Footer: Total: 14 months > > This is rather simplistic, but if it is "do-able", then it will solve other > problem in our attempt to calculate statistics at the end of a report. > > GOAL: Be able to calculate the "14" in the table footer...if I try Count, it > will count all of the rows in the dataset (way too many)...if I use > CountDistinct, it returns "12" - not enough! > > Thanks - I know you are trying! > > -KB > > > "Brian Welcker [MSFT]" <bwelcker@online.microsoft.com> wrote in message > news:u2KfYj7gEHA.1184@TK2MSFTNGP12.phx.gbl... > > You need to use the CountDistinct aggregate. The key is what do you want > to > > count the distinct values of? If you want months within the year, you need > > to specify: > > > > =countdistinct(Fields!MonthShipped.Value) > > > > as the value of your textbox. > > > > -- > > Brian Welcker > > Group Program Manager > > Microsoft SQL Server Reporting Services > > > > This posting is provided "AS IS" with no warranties, and confers no > rights. > > > > "Kevin B" <No-SPAM@misnet.info> wrote in message > > news:%23TG0bb5gEHA.3864@TK2MSFTNGP10.phx.gbl... > > >I follow your explantion, but you didn't follow my problem. I don't want > > >to > > > to total all the records returned by the SQL statement! Specifically, I > > > want to count the number of unique values within a group. Hopefully, to > > > simply, I have attached a RDL against Northwinds (just change the server > > > name in the dataset). > > > > > > Draw your attention to "textbox8". I have used this formula: > > > =count(Fields!ShippedDate.Value,"table1_Group2") and it says it is out > of > > > scope...well the scope I want to count is table1_Group2! If I use this > > > formula: =count(Fields!ShippedDate.Value,"table1_Group2") it returns > 143 > > > as the value. The value I am expecting is 6 - the number of months in > the > > > report. > > > > > > Thanks for you help - I have tried everything I can think of. Maybe > this > > > can help you to help me. > > > -KB > > > > > > > > > Here is the rdl referenced above > > > > > > <?xml version="1.0" encoding="utf-8"?> > > > > > > <Report > > > > xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini > > > tion" > > > > xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"> > > > > > > <RightMargin>1in</RightMargin> > > > > > > <Body> > > > > > > <ReportItems> > > > > > > <Textbox Name="textbox1"> > > > > > > <Style> > > > > > > <PaddingLeft>2pt</PaddingLeft> > > > > > > <FontFamily>Times New Roman</FontFamily> > > > > > > <BackgroundColor>Brown</BackgroundColor> > > > > > > <BorderWidth> > > > > > > <Bottom>3pt</Bottom> > > > > > > </BorderWidth> > > > > > > <BorderColor> > > > > > > <Bottom>Black</Bottom> > > > > > > </BorderColor> > > > > > > <BorderStyle> > > > > > > <Bottom>Solid</Bottom> > > > > > > </BorderStyle> > > > > > > <FontSize>18pt</FontSize> > > > > > > <TextAlign>Center</TextAlign> > > > > > > <Color>White</Color> > > > > > > <PaddingBottom>2pt</PaddingBottom> > > > > > > <PaddingTop>2pt</PaddingTop> > > > > > > <PaddingRight>2pt</PaddingRight> > > > > > > <FontWeight>700</FontWeight> > > > > > > </Style> > > > > > > <ZIndex>1</ZIndex> > > > > > > <rd:DefaultName>textbox1</rd:DefaultName> > > > > > > <Height>0.33in</Height> > > > > > > <CanGrow>true</CanGrow> > > > > > > <Value>Report1</Value> > > > > > > </Textbox> > > > > > > <Table Name="table1"> > > > > > > <Style> > > > > > > <BackgroundColor>White</BackgroundColor> > > > > > > <BorderWidth> > > > > > > <Top>3pt</Top> > > > > > > </BorderWidth> > > > > > > <BorderStyle> > > > > > > <Top>Solid</Top> > > > > > > </BorderStyle> > > > > > > </Style> > > > > > > <Header> > > > > > > <TableRows> > > > > > > <TableRow> > > > > > > <Height>0.21in</Height> > > > > > > <TableCells> > > > > > > <TableCell> > > > > > > <ReportItems> > > > > > > <Textbox Name="textbox23"> > > > > > > <Style> > > > > > > <PaddingLeft>2pt</PaddingLeft> > > > > > > <BorderWidth> > > > > > > <Bottom>2pt</Bottom> > > > > > > </BorderWidth> > > > > > > <BorderColor> > > > > > > <Bottom>DarkRed</Bottom> > > > > > > </BorderColor> > > > > > > <BorderStyle> > > > > > > <Bottom>Solid</Bottom> > > > > > > </BorderStyle> > > > > > > <TextAlign>Right</TextAlign> > > > > > > <PaddingBottom>2pt</PaddingBottom> > > > > > > <PaddingTop>2pt</PaddingTop> > > > > > > <PaddingRight>2pt</PaddingRight> > > > > > > <FontWeight>900</FontWeight> > > > > > > </Style> > > > > > > <ZIndex>29</ZIndex> > > > > > > <rd:DefaultName>textbox23</rd:DefaultName> > > > > > > <CanGrow>true</CanGrow> > > > > > > <Value>Year Shipped</Value> > > > > > > </Textbox> > > > > > > </ReportItems> > > > > > > </TableCell> > > > > > > <TableCell> > > > > > > <ReportItems> > > > > > > <Textbox Name="textbox17"> > > > > > > <Style> > > > > > > <PaddingLeft>2pt</PaddingLeft> > > > > > > <BorderWidth> > > > > > > <Bottom>2pt</Bottom> > > > > > > </BorderWidth> > > > > > > <BorderColor> > > > > > > <Bottom>DarkRed</Bottom> > > > > > > </BorderColor> > > > > > > <BorderStyle> > > > > > > <Bottom>Solid</Bottom> > > >
I don't understand your conclusion. Every row must have some identifier in
order for it to be grouped by that value. Every count has a scope, whether it is explicit or implicit. As you have not posted your "real" data, I am unsure why this will not work for you. IMHO, it seems like you have decided (for whatever reason) that you will be unable to use RS and you are now trying to justify this conclusion. -- Brian Welcker Group Program Manager Microsoft SQL Server Reporting Services This posting is provided "AS IS" with no warranties, and confers no rights. [quoted text, click to view] "Kevin B" <No-SPAM@misnet.info> wrote in message
news:uPhiiL9gEHA.644@tk2msftngp13.phx.gbl... >I see what you are trying to do - create a unique ID and then "count". >That > works for my "Northwinds" database, but it doesn't work for my "real" > data. > Which leads me back to square one. > > Regretfully, I am becoming more convinced that RS can't match up to > Crystal - which I would *love* to replace with RS. The report > distribution > options are by far excellent compared with Crystal (for our environment). > BUT...we haven't been sucessful at writing complex reports with many > statistics. Most all of our report have executive summarys at the end > them > to give comparative views or analysis; RS can't genereate even a "simple" > count of data. > > Robert, I appreciate your help - and I haven't completely given up. I > have > called our TAM with MS and he is researching this for me as well. I am > very > anxious to see what will develop with RS, but for now, it is a kid's > reporting tool. I have spent too much time trying to get blood out of > this > rock...it just cant do it. > > We have some reports that are simple - and RS does a great job. Everyone > wants to know when the other reports will be like those. I am starting to > doubt if it will be anytime soon. > > Our TAM has offered a conference call with a RS Product Manager to discuss > our problems. For us, this is a major show stopper, no matter how good > report distribution is - the report has to be "right". > > Thanks for your help - if you think of anything else that would help - > please let me know. > > -KB > > > > > "Robert Bruckner [MSFT]" <robruc@online.microsoft.com> wrote in message > news:%23KYF6o8gEHA.3988@tk2msftngp13.phx.gbl... >> Assuming the MonthShipped value is actually a full DateTime object, you >> should try this to get 14 distinct months: >> =CountDistinct(Year(Fields!MonthShipped.Value) * 100 + >> Month(Fields!MonthShipped.Value)) >> >> -- >> This posting is provided "AS IS" with no warranties, and confers no > rights. >> >> >> "Kevin B" <No-SPAM@misnet.info> wrote in message >> news:O22qvP8gEHA.596@TK2MSFTNGP11.phx.gbl... >> > That works, except when I "roll them up" in the table footer, it counts > 12 >> > distinct months...novel, huh?? >> > >> > I have tried using the aggregate functions, but it seems they only >> recognize >> > a scope looking "out", not "in". For example, I can reference an >> aggregate >> > SUM value on table_Group1 in the table1_Group2 footer - but not vice >> versa. >> > More to the point, I can't reference an Aggregate sum of table1_Group1 >> from >> > the table footer. >> > >> > Regarding the CountDistinct function - I can count the distinct values > in >> > Group 2, but how do I count each row in the Group 2 Header report? >> > >> > Example: >> > >> > Group 1 Header 2003 >> > Group 2 Header 07 >> > Group 2 Header 08 >> > Group 2 Header 09 >> > Group 2 Header 10 >> > Group 2 Header 11 >> > Group 2 Header 12 >> > Group 1 Footer Total: 6 months >> > Group 1 Header 2004 >> > Group 2 Header 01 >> > Group 2 Header 02 >> > Group 2 Header 03 >> > Group 2 Header 04 >> > Group 2 Header 05 >> > Group 2 Header 06 >> > Group 2 Header 07 >> > Group 2 Header 08 >> > Group 1 Footer Total: 8 months >> > Table Footer: Total: 14 months >> > >> > This is rather simplistic, but if it is "do-able", then it will solve >> other >> > problem in our attempt to calculate statistics at the end of a report. >> > >> > GOAL: Be able to calculate the "14" in the table footer...if I try > Count, >> it >> > will count all of the rows in the dataset (way too many)...if I use >> > CountDistinct, it returns "12" - not enough! >> > >> > Thanks - I know you are trying! >> > >> > -KB >> > >> > >> > "Brian Welcker [MSFT]" <bwelcker@online.microsoft.com> wrote in message >> > news:u2KfYj7gEHA.1184@TK2MSFTNGP12.phx.gbl... >> > > You need to use the CountDistinct aggregate. The key is what do you > want >> > to >> > > count the distinct values of? If you want months within the year, you >> need >> > > to specify: >> > > >> > > =countdistinct(Fields!MonthShipped.Value) >> > > >> > > as the value of your textbox. >> > > >> > > -- >> > > Brian Welcker >> > > Group Program Manager >> > > Microsoft SQL Server Reporting Services >> > > >> > > This posting is provided "AS IS" with no warranties, and confers no >> > rights. >> > > >> > > "Kevin B" <No-SPAM@misnet.info> wrote in message >> > > news:%23TG0bb5gEHA.3864@TK2MSFTNGP10.phx.gbl... >> > > >I follow your explantion, but you didn't follow my problem. I don't >> want >> > > >to >> > > > to total all the records returned by the SQL statement! > Specifically, >> I >> > > > want to count the number of unique values within a group. > Hopefully, >> to >> > > > simply, I have attached a RDL against Northwinds (just change the >> server >> > > > name in the dataset). >> > > > >> > > > Draw your attention to "textbox8". I have used this formula: >> > > > =count(Fields!ShippedDate.Value,"table1_Group2") and it says it is >> out >> > of >> > > > scope...well the scope I want to count is table1_Group2! If I use >> this >> > > > formula: =count(Fields!ShippedDate.Value,"table1_Group2") it > returns >> > 143 >> > > > as the value. The value I am expecting is 6 - the number of months > in >> > the >> > > > report. >> > > > >> > > > Thanks for you help - I have tried everything I can think of. >> > > > Maybe >> > this >> > > > can help you to help me. >> > > > -KB >> > > > >> > > > >> > > > Here is the rdl referenced above >> > > > >> > > > <?xml version="1.0" encoding="utf-8"?> >> > > > >> > > > <Report >> > > > >> > >> > xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini >> > > > tion" >> > > > >> > >> > xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"> >> > > > >> > > > <RightMargin>1in</RightMargin> >> > > > >> > > > <Body> >> > > > >> > > > <ReportItems> >> > > > >> > > > <Textbox Name="textbox1"> >> > > >
That works, except when I "roll them up" in the table footer, it counts 12
distinct months...novel, huh?? I have tried using the aggregate functions, but it seems they only recognize a scope looking "out", not "in". For example, I can reference an aggregate SUM value on table_Group1 in the table1_Group2 footer - but not vice versa. More to the point, I can't reference an Aggregate sum of table1_Group1 from the table footer. Regarding the CountDistinct function - I can count the distinct values in Group 2, but how do I count each row in the Group 2 Header report? Example: Group 1 Header 2003 Group 2 Header 07 Group 2 Header 08 Group 2 Header 09 Group 2 Header 10 Group 2 Header 11 Group 2 Header 12 Group 1 Footer Total: 6 months Group 1 Header 2004 Group 2 Header 01 Group 2 Header 02 Group 2 Header 03 Group 2 Header 04 Group 2 Header 05 Group 2 Header 06 Group 2 Header 07 Group 2 Header 08 Group 1 Footer Total: 8 months Table Footer: Total: 14 months This is rather simplistic, but if it is "do-able", then it will solve other problem in our attempt to calculate statistics at the end of a report. GOAL: Be able to calculate the "14" in the table footer...if I try Count, it will count all of the rows in the dataset (way too many)...if I use CountDistinct, it returns "12" - not enough! Thanks - I know you are trying! -KB [quoted text, click to view] "Brian Welcker [MSFT]" <bwelcker@online.microsoft.com> wrote in message
news:u2KfYj7gEHA.1184@TK2MSFTNGP12.phx.gbl... > You need to use the CountDistinct aggregate. The key is what do you want to > count the distinct values of? If you want months within the year, you need > to specify: > > =countdistinct(Fields!MonthShipped.Value) > > as the value of your textbox. > > -- > Brian Welcker > Group Program Manager > Microsoft SQL Server Reporting Services > > This posting is provided "AS IS" with no warranties, and confers no rights. > > "Kevin B" <No-SPAM@misnet.info> wrote in message > news:%23TG0bb5gEHA.3864@TK2MSFTNGP10.phx.gbl... > >I follow your explantion, but you didn't follow my problem. I don't want > >to > > to total all the records returned by the SQL statement! Specifically, I > > want to count the number of unique values within a group. Hopefully, to > > simply, I have attached a RDL against Northwinds (just change the server > > name in the dataset). > > > > Draw your attention to "textbox8". I have used this formula: > > =count(Fields!ShippedDate.Value,"table1_Group2") and it says it is out of > > scope...well the scope I want to count is table1_Group2! If I use this > > formula: =count(Fields!ShippedDate.Value,"table1_Group2") it returns 143 > > as the value. The value I am expecting is 6 - the number of months in the > > report. > > > > Thanks for you help - I have tried everything I can think of. Maybe this > > can help you to help me. > > -KB > > > > > > Here is the rdl referenced above > > > > <?xml version="1.0" encoding="utf-8"?> > > > > <Report > > xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini > > tion" > > xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"> > > > > <RightMargin>1in</RightMargin> > > > > <Body> > > > > <ReportItems> > > > > <Textbox Name="textbox1"> > > > > <Style> > > > > <PaddingLeft>2pt</PaddingLeft> > > > > <FontFamily>Times New Roman</FontFamily> > > > > <BackgroundColor>Brown</BackgroundColor> > > > > <BorderWidth> > > > > <Bottom>3pt</Bottom> > > > > </BorderWidth> > > > > <BorderColor> > > > > <Bottom>Black</Bottom> > > > > </BorderColor> > > > > <BorderStyle> > > > > <Bottom>Solid</Bottom> > > > > </BorderStyle> > > > > <FontSize>18pt</FontSize> > > > > <TextAlign>Center</TextAlign> > > > > <Color>White</Color> > > > > <PaddingBottom>2pt</PaddingBottom> > > > > <PaddingTop>2pt</PaddingTop> > > > > <PaddingRight>2pt</PaddingRight> > > > > <FontWeight>700</FontWeight> > > > > </Style> > > > > <ZIndex>1</ZIndex> > > > > <rd:DefaultName>textbox1</rd:DefaultName> > > > > <Height>0.33in</Height> > > > > <CanGrow>true</CanGrow> > > > > <Value>Report1</Value> > > > > </Textbox> > > > > <Table Name="table1"> > > > > <Style> > > > > <BackgroundColor>White</BackgroundColor> > > > > <BorderWidth> > > > > <Top>3pt</Top> > > > > </BorderWidth> > > > > <BorderStyle> > > > > <Top>Solid</Top> > > > > </BorderStyle> > > > > </Style> > > > > <Header> > > > > <TableRows> > > > > <TableRow> > > > > <Height>0.21in</Height> > > > > <TableCells> > > > > <TableCell> > > > > <ReportItems> > > > > <Textbox Name="textbox23"> > > > > <Style> > > > > <PaddingLeft>2pt</PaddingLeft> > > > > <BorderWidth> > > > > <Bottom>2pt</Bottom> > > > > </BorderWidth> > > > > <BorderColor> > > > > <Bottom>DarkRed</Bottom> > > > > </BorderColor> > > > > <BorderStyle> > > > > <Bottom>Solid</Bottom> > > > > </BorderStyle> > > > > <TextAlign>Right</TextAlign> > > > > <PaddingBottom>2pt</PaddingBottom> > > > > <PaddingTop>2pt</PaddingTop> > > > > <PaddingRight>2pt</PaddingRight> > > > > <FontWeight>900</FontWeight> > > > > </Style> > > > > <ZIndex>29</ZIndex> > > > > <rd:DefaultName>textbox23</rd:DefaultName> > > > > <CanGrow>true</CanGrow> > > > > <Value>Year Shipped</Value> > > > > </Textbox> > > > > </ReportItems> > > > > </TableCell> > > > > <TableCell> > > > > <ReportItems> > > > > <Textbox Name="textbox17"> > > > > <Style> > > > > <PaddingLeft>2pt</PaddingLeft> > > > > <BorderWidth> > > > > <Bottom>2pt</Bottom> > > > > </BorderWidth> > > > > <BorderColor> > > > > <Bottom>DarkRed</Bottom> > > > > </BorderColor> > > > > <BorderStyle> > > > > <Bottom>Solid</Bottom> > > > > </BorderStyle> > > > > <TextAlign>Right</TextAlign> > > > > <PaddingBottom>2pt</PaddingBottom> > > > > <PaddingTop>2pt</PaddingTop> > > > > <PaddingRight>2pt</PaddingRight> > > > > <FontWeight>900</FontWeight> > > > > </Style> > > > > <ZIndex>28</ZIndex> > > > > <rd:DefaultName>textbox17</rd:DefaultName> > > > > <CanGrow>true</CnGrow> > > > > <Value>Month Shipped</Value> > > > > </Textbox> > > > > </ReportItems> > > > > </TableCell> > > > > <TableCell> > > > > <ReportItems> > > > > <Textbox Name="textbox27"> > > > > <Style> > > > > <PaddingLeft>2pt</PaddingLeft> > > > > <BorderWidth> > > > > <Bottom>2pt</Bottom> > > > > </BorderWidth> > > > > <BorderColor> > > > > <Bottom>DarkRed</Bottom> > > > > </BorderColor> > > > > <BorderStyle> > > > > <Bottom>Solid</Bottom> > > > > </BorderStyle> > > > > <PaddingBottom>2pt</PaddingBottom> > > > > <PaddingTop>2pt</PaddingTop> > >
Even if your "real" data is not one DateTime field, but separate numeric
fields for Year and Month, it is even easier: =CountDistinct(Fields!YearShipped.Value * 100 + Fields!MonthShipped.Value) What is your "real" data? -- This posting is provided "AS IS" with no warranties, and confers no rights. [quoted text, click to view] "Kevin B" <No-SPAM@misnet.info> wrote in message
news:uPhiiL9gEHA.644@tk2msftngp13.phx.gbl... > I see what you are trying to do - create a unique ID and then "count". That > works for my "Northwinds" database, but it doesn't work for my "real" data. > Which leads me back to square one. > > Regretfully, I am becoming more convinced that RS can't match up to > Crystal - which I would *love* to replace with RS. The report distribution > options are by far excellent compared with Crystal (for our environment). > BUT...we haven't been sucessful at writing complex reports with many > statistics. Most all of our report have executive summarys at the end them > to give comparative views or analysis; RS can't genereate even a "simple" > count of data. > > Robert, I appreciate your help - and I haven't completely given up. I have > called our TAM with MS and he is researching this for me as well. I am very > anxious to see what will develop with RS, but for now, it is a kid's > reporting tool. I have spent too much time trying to get blood out of this > rock...it just cant do it. > > We have some reports that are simple - and RS does a great job. Everyone > wants to know when the other reports will be like those. I am starting to > doubt if it will be anytime soon. > > Our TAM has offered a conference call with a RS Product Manager to discuss > our problems. For us, this is a major show stopper, no matter how good > report distribution is - the report has to be "right". > > Thanks for your help - if you think of anything else that would help - > please let me know. > > -KB > > > > > "Robert Bruckner [MSFT]" <robruc@online.microsoft.com> wrote in message > news:%23KYF6o8gEHA.3988@tk2msftngp13.phx.gbl... > > Assuming the MonthShipped value is actually a full DateTime object, you > > should try this to get 14 distinct months: > > =CountDistinct(Year(Fields!MonthShipped.Value) * 100 + > > Month(Fields!MonthShipped.Value)) > > > > -- > > This posting is provided "AS IS" with no warranties, and confers no > rights. > > > > > > "Kevin B" <No-SPAM@misnet.info> wrote in message > > news:O22qvP8gEHA.596@TK2MSFTNGP11.phx.gbl... > > > That works, except when I "roll them up" in the table footer, it counts > 12 > > > distinct months...novel, huh?? > > > > > > I have tried using the aggregate functions, but it seems they only > > recognize > > > a scope looking "out", not "in". For example, I can reference an > > aggregate > > > SUM value on table_Group1 in the table1_Group2 footer - but not vice > > versa. > > > More to the point, I can't reference an Aggregate sum of table1_Group1 > > from > > > the table footer. > > > > > > Regarding the CountDistinct function - I can count the distinct values > in > > > Group 2, but how do I count each row in the Group 2 Header report? > > > > > > Example: > > > > > > Group 1 Header 2003 > > > Group 2 Header 07 > > > Group 2 Header 08 > > > Group 2 Header 09 > > > Group 2 Header 10 > > > Group 2 Header 11 > > > Group 2 Header 12 > > > Group 1 Footer Total: 6 months > > > Group 1 Header 2004 > > > Group 2 Header 01 > > > Group 2 Header 02 > > > Group 2 Header 03 > > > Group 2 Header 04 > > > Group 2 Header 05 > > > Group 2 Header 06 > > > Group 2 Header 07 > > > Group 2 Header 08 > > > Group 1 Footer Total: 8 months > > > Table Footer: Total: 14 months > > > > > > This is rather simplistic, but if it is "do-able", then it will solve > > other > > > problem in our attempt to calculate statistics at the end of a report. > > > > > > GOAL: Be able to calculate the "14" in the table footer...if I try > Count, > > it > > > will count all of the rows in the dataset (way too many)...if I use > > > CountDistinct, it returns "12" - not enough! > > > > > > Thanks - I know you are trying! > > > > > > -KB > > > > > > > > > "Brian Welcker [MSFT]" <bwelcker@online.microsoft.com> wrote in message > > > news:u2KfYj7gEHA.1184@TK2MSFTNGP12.phx.gbl... > > > > You need to use the CountDistinct aggregate. The key is what do you > want > > > to > > > > count the distinct values of? If you want months within the year, you > > need > > > > to specify: > > > > > > > > =countdistinct(Fields!MonthShipped.Value) > > > > > > > > as the value of your textbox. > > > > > > > > -- > > > > Brian Welcker > > > > Group Program Manager > > > > Microsoft SQL Server Reporting Services > > > > > > > > This posting is provided "AS IS" with no warranties, and confers no > > > rights. > > > > > > > > "Kevin B" <No-SPAM@misnet.info> wrote in message > > > > news:%23TG0bb5gEHA.3864@TK2MSFTNGP10.phx.gbl... > > > > >I follow your explantion, but you didn't follow my problem. I don't > > want > > > > >to > > > > > to total all the records returned by the SQL statement! > Specifically, > > I > > > > > want to count the number of unique values within a group. > Hopefully, > > to > > > > > simply, I have attached a RDL against Northwinds (just change the > > server > > > > > name in the dataset). > > > > > > > > > > Draw your attention to "textbox8". I have used this formula: > > > > > =count(Fields!ShippedDate.Value,"table1_Group2") and it says it is > > out > > > of > > > > > scope...well the scope I want to count is table1_Group2! If I use > > this > > > > > formula: =count(Fields!ShippedDate.Value,"table1_Group2") it > returns > > > 143 > > > > > as the value. The value I am expecting is 6 - the number of months > in > > > the > > > > > report. > > > > > > > > > > Thanks for you help - I have tried everything I can think of. Maybe > > > this > > > > > can help you to help me. > > > > > -KB > > > > > > > > > > > > > > > Here is the rdl referenced above > > > > > > > > > > <?xml version="1.0" encoding="utf-8"?> > > > > > > > > > > <Report > > > > > > > > > > > xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini > > > > > tion" > > > > > > > > > > > xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"> > > > > > > > > > > <RightMargin>1in</RightMargin> > > > > > > > > > > <Body> > > > > > > > > > > <ReportItems> > > > > > > > > > > <Textbox Name="textbox1"> > > > > > > > > > > <Style> > > > > > > > > > > <PaddingLeft>2pt</PaddingLeft> > > > > > > > > > > <FontFamily>Times New Roman</FontFamily> > > > > >
I see what you are trying to do - create a unique ID and then "count". That
works for my "Northwinds" database, but it doesn't work for my "real" data. Which leads me back to square one. Regretfully, I am becoming more convinced that RS can't match up to Crystal - which I would *love* to replace with RS. The report distribution options are by far excellent compared with Crystal (for our environment). BUT...we haven't been sucessful at writing complex reports with many statistics. Most all of our report have executive summarys at the end them to give comparative views or analysis; RS can't genereate even a "simple" count of data. Robert, I appreciate your help - and I haven't completely given up. I have called our TAM with MS and he is researching this for me as well. I am very anxious to see what will develop with RS, but for now, it is a kid's reporting tool. I have spent too much time trying to get blood out of this rock...it just cant do it. We have some reports that are simple - and RS does a great job. Everyone wants to know when the other reports will be like those. I am starting to doubt if it will be anytime soon. Our TAM has offered a conference call with a RS Product Manager to discuss our problems. For us, this is a major show stopper, no matter how good report distribution is - the report has to be "right". Thanks for your help - if you think of anything else that would help - please let me know. -KB [quoted text, click to view] "Robert Bruckner [MSFT]" <robruc@online.microsoft.com> wrote in message
news:%23KYF6o8gEHA.3988@tk2msftngp13.phx.gbl... > Assuming the MonthShipped value is actually a full DateTime object, you > should try this to get 14 distinct months: > =CountDistinct(Year(Fields!MonthShipped.Value) * 100 + > Month(Fields!MonthShipped.Value)) > > -- > This posting is provided "AS IS" with no warranties, and confers no rights. > > > "Kevin B" <No-SPAM@misnet.info> wrote in message > news:O22qvP8gEHA.596@TK2MSFTNGP11.phx.gbl... > > That works, except when I "roll them up" in the table footer, it counts 12 > > distinct months...novel, huh?? > > > > I have tried using the aggregate functions, but it seems they only > recognize > > a scope looking "out", not "in". For example, I can reference an > aggregate > > SUM value on table_Group1 in the table1_Group2 footer - but not vice > versa. > > More to the point, I can't reference an Aggregate sum of table1_Group1 > from > > the table footer. > > > > Regarding the CountDistinct function - I can count the distinct values in > > Group 2, but how do I count each row in the Group 2 Header report? > > > > Example: > > > > Group 1 Header 2003 > > Group 2 Header 07 > > Group 2 Header 08 > > Group 2 Header 09 > > Group 2 Header 10 > > Group 2 Header 11 > > Group 2 Header 12 > > Group 1 Footer Total: 6 months > > Group 1 Header 2004 > > Group 2 Header 01 > > Group 2 Header 02 > > Group 2 Header 03 > > Group 2 Header 04 > > Group 2 Header 05 > > Group 2 Header 06 > > Group 2 Header 07 > > Group 2 Header 08 > > Group 1 Footer Total: 8 months > > Table Footer: Total: 14 months > > > > This is rather simplistic, but if it is "do-able", then it will solve > other > > problem in our attempt to calculate statistics at the end of a report. > > > > GOAL: Be able to calculate the "14" in the table footer...if I try Count, > it > > will count all of the rows in the dataset (way too many)...if I use > > CountDistinct, it returns "12" - not enough! > > > > Thanks - I know you are trying! > > > > -KB > > > > > > "Brian Welcker [MSFT]" <bwelcker@online.microsoft.com> wrote in message > > news:u2KfYj7gEHA.1184@TK2MSFTNGP12.phx.gbl... > > > You need to use the CountDistinct aggregate. The key is what do you want > > to > > > count the distinct values of? If you want months within the year, you > need > > > to specify: > > > > > > =countdistinct(Fields!MonthShipped.Value) > > > > > > as the value of your textbox. > > > > > > -- > > > Brian Welcker > > > Group Program Manager > > > Microsoft SQL Server Reporting Services > > > > > > This posting is provided "AS IS" with no warranties, and confers no > > rights. > > > > > > "Kevin B" <No-SPAM@misnet.info> wrote in message > > > news:%23TG0bb5gEHA.3864@TK2MSFTNGP10.phx.gbl... > > > >I follow your explantion, but you didn't follow my problem. I don't > want > > > >to > > > > to total all the records returned by the SQL statement! Specifically, > I > > > > want to count the number of unique values within a group. Hopefully, > to > > > > simply, I have attached a RDL against Northwinds (just change the > server > > > > name in the dataset). > > > > > > > > Draw your attention to "textbox8". I have used this formula: > > > > =count(Fields!ShippedDate.Value,"table1_Group2") and it says it is > out > > of > > > > scope...well the scope I want to count is table1_Group2! If I use > this > > > > formula: =count(Fields!ShippedDate.Value,"table1_Group2") it returns > > 143 > > > > as the value. The value I am expecting is 6 - the number of months in > > the > > > > report. > > > > > > > > Thanks for you help - I have tried everything I can think of. Maybe > > this > > > > can help you to help me. > > > > -KB > > > > > > > > > > > > Here is the rdl referenced above > > > > > > > > <?xml version="1.0" encoding="utf-8"?> > > > > > > > > <Report > > > > > > > xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini > > > > tion" > > > > > > > xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"> > > > > > > > > <RightMargin>1in</RightMargin> > > > > > > > > <Body> > > > > > > > > <ReportItems> > > > > > > > > <Textbox Name="textbox1"> > > > > > > > > <Style> > > > > > > > > <PaddingLeft>2pt</PaddingLeft> > > > > > > > > <FontFamily>Times New Roman</FontFamily> > > > > > > > > <BackgroundColor>Brown</BackgroundColor> > > > > > > > > <BorderWidth> > > > > > > > > <Bottom>3pt</Bottom> > > > > > > > > </BorderWidth> > > > > > > > > <BorderColor> > > > > > > > > <Bottom>Black</Bottom> > > > > > > > > </BorderColor> > > > > > > > > <BorderStyle> > > > > > > > > <Bottom>Solid</Bottom> > > > > > > > > </BorderStyle> > > > > > > > > <FontSize>18pt</FontSize> > > > > > > > > <TextAlign>Center</TextAlign> > > > > > > > > <Color>White</Color> > > > > > > > > <PaddingBottom>2pt</PaddingBottom> > > > > > > > > <PaddingTop>2pt</PaddingTop> > > > > > > > > <PaddingRight>2pt</PaddingRight> > > > > > > > > <FontWeight>700</FontWeight> > > > > > > > > </Style> > > > > > > > > <ZIndex>1</ZIndex> > > > >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |