Help on Tables

Is there a link that has good documentation on how to create tables?
Like I'm trying to put one value in a cell like:
=IIF( Fields!PERIOD.Value="Total", Fields!NET_SUBS.Value, Nothing)
but it will create additional rows, because there are multiple periods.If you do not use an aggregate you will get one report row per table row...
Will Sum, or AVG, etc do what you wish...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Cindy Lee" <dangreece@.hotmail.com> wrote in message
news:%23rjJPmhXEHA.716@.TK2MSFTNGP11.phx.gbl...
> Is there a link that has good documentation on how to create tables?
> Like I'm trying to put one value in a cell like:
> =IIF( Fields!PERIOD.Value="Total", Fields!NET_SUBS.Value, Nothing)
>
> but it will create additional rows, because there are multiple periods.
>|||Yeah it still doesn't work:
=SUM(IIF( Fields!PERIOD.Value="Total", Fields!NET_SUBS.Value, Nothing))
=SUM(IIF( Fields!PERIOD.Value="Period1", Fields!NET_SUBS.Value, Nothing))
There are 3 periods: Period 1, Period 2 and Total that come back in the
query
The values are right but it repeats in my table
Period
Total Period 1
NETSUB 3000 1000
NETSUB 3000 1000
NETSUB 3000 1000
I just want it to appear 1 time.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:egp8gSqXEHA.3156@.TK2MSFTNGP12.phx.gbl...
> If you do not use an aggregate you will get one report row per table
row...
> Will Sum, or AVG, etc do what you wish...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Cindy Lee" <dangreece@.hotmail.com> wrote in message
> news:%23rjJPmhXEHA.716@.TK2MSFTNGP11.phx.gbl...
> > Is there a link that has good documentation on how to create tables?
> >
> > Like I'm trying to put one value in a cell like:
> > =IIF( Fields!PERIOD.Value="Total", Fields!NET_SUBS.Value, Nothing)
> >
> >
> >
> > but it will create additional rows, because there are multiple periods.
> >
> >
>|||From your description, it sounds like all of your fields are in the detail
row.
If this is correct, you can supress the duplicates by adding a details group
and set the group expression to use the period.
I have added a sample report to the end of this posting that demonstrates
how to use a table details group. To run it you will need access to the
Northwind sample database.
The table details group dialog can be reached from as follows: Table
Properties Dailog : Groups Tab : Details Grouping Button.
For a fuller discussion about table groups see the 'How to add a group to a
table' topic in Reporting Services Books Online.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave Clark" <daveclark@.hotmail.com> wrote in message
news:usF7VwsXEHA.3292@.TK2MSFTNGP09.phx.gbl...
> Yeah it still doesn't work:
> =SUM(IIF( Fields!PERIOD.Value="Total", Fields!NET_SUBS.Value, Nothing))
> =SUM(IIF( Fields!PERIOD.Value="Period1", Fields!NET_SUBS.Value, Nothing))
> There are 3 periods: Period 1, Period 2 and Total that come back in the
> query
> The values are right but it repeats in my table
> Period
> Total Period 1
> NETSUB 3000 1000
> NETSUB 3000 1000
> NETSUB 3000 1000
> I just want it to appear 1 time.
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:egp8gSqXEHA.3156@.TK2MSFTNGP12.phx.gbl...
> > If you do not use an aggregate you will get one report row per table
> row...
> > Will Sum, or AVG, etc do what you wish...
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Cindy Lee" <dangreece@.hotmail.com> wrote in message
> > news:%23rjJPmhXEHA.716@.TK2MSFTNGP11.phx.gbl...
> > > Is there a link that has good documentation on how to create tables?
> > >
> > > Like I'm trying to put one value in a cell like:
> > > =IIF( Fields!PERIOD.Value="Total", Fields!NET_SUBS.Value, Nothing)
> > >
> > >
> > >
> > > but it will create additional rows, because there are multiple
periods.
> > >
Table Details Group Sample
========================================
<?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>
<Table Name="table1">
<Height>0.75in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Product ID</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Quantity</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="ProductID">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>ProductID</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!ProductID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Quantity">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>Quantity</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Quantity.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<Grouping Name="ProductID_DetailsGroup">
<GroupExpressions>
<GroupExpression>=Fields!ProductID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</Details>
<DataSetName>DataSet1</DataSetName>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
<TableColumns>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>2in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="northwind">
<rd:DataSourceID>1a2ecefc-e377-49f4-a355-2c6133f21447</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="Quantity">
<DataField>Quantity</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="ProductID">
<DataField>ProductID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>northwind</DataSourceName>
<CommandText>SELECT Orders.OrderDate, [Order Details].Quantity,
[Order
Details].ProductID
FROM Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID
ORDER BY [Order Details].ProductID</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>f28ffce0-dff1-4e06-ba21-c683aab528dc</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>