Firehose mode: table

Showing posts with label table. Show all posts

Friday, March 30, 2012

Grouping with Reporting services

Can anyone help me with grouping in Reporting services. I am more used to crystal reports drill-down method

For example i have a simple table that has timestamp and three other columns. I want to drill down by (after Grouping) for Day-Then- Hour and then show the details for three columns. And also group by one of the columns, if i get above working.

All i could do with Reporting services was stepped down model, but i have same dates repeated more than once. i would like them to be grouped under day and then show time stamps for times of day .

-Thanks all

You can do this by creating a Table and adding a grouping, which is grouped by the date portion of the timestamp field, and then place the detail fields in the detail section, as you are now. The expressions should be something like the following:

Grouping expression:
=Fields!TimeStamp.Value.Date

Grouping header textbox for the date:

=Fields!TimeStamp.Value.ToString("D")

Detail textbox for the time:
=Fields!TimeStamp.Value.ToString("T")

Ian

Grouping with Page Break

I have a report that shows the monthwise details, i have performed this using the table control. the requirement was to have a page break after each month detail. End of each group display the monthwise total. This is absolutely working fine.

My problem is, I am suppose to display the Report total also. I used the Sum in the Table footer. The sum is coming fine the only problem that i have is it is printing on the a New Page instead of the Last Page.

Please suggest.

Hi,

you have to use the Group Footer, not the Table Footer. Edit Group -> include group footer:

Greez Daniel

|||

I have to display the Report Sub Total of the Group that i am already doing in the Group Footer, Where as I am wanting to display the Main Total of the Report, It wont work in the Group Footer as it is the Full Report total.

I tried to add a new Group fo the report just to have a main group and the other one as sub group, but it also did not work.

|||Try setting the RepeatOnNewPage property in the table footer to true. This will show the Main Total on every page.

Grouping with Page Break

Please suggest.

Hi,

you have to use the Group Footer, not the Table Footer. Edit Group -> include group footer:

Greez Daniel

|||

I tried to add a new Group fo the report just to have a main group and the other one as sub group, but it also did not work.

|||Try setting the RepeatOnNewPage property in the table footer to true. This will show the Main Total on every page.sql

Grouping Views by Dates

I have a table with the date in a full format including hours, minutes and
seconds etc. I would like to group the information by yearmonthday and also
by yearmonth.Hi
CREATE TABLE #Test
(
dt DATETIME NOT NULL
)
--Change the data for your needs
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-01 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-02 13:46:59.707')
INSERT INTO #Test VALUES ('2006-01-01 13:46:59.707')
SELECT Year(dt),Count(*) FROM #Test
GROUP BY Year(dt)
Also lookup DAY(),MONTH() system functions in the BOL
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:2FED6491-6AFE-40B6-AFC5-3E1561D211C1@.microsoft.com...
>I have a table with the date in a full format including hours, minutes and
> seconds etc. I would like to group the information by yearmonthday and
> also
> by yearmonth.|||Thankyou, however perhaps I should have given more detail....
Currently the date is like
NAME TYPE DATE
Rothery Telephone call out 26/09/05 13:00:00
Ginnelly Auto Order 26/09/05 12:55:03
Aldcroft Customer Services 26/09/05 12:55:00
Aldcroft Auto Order 26/09/05 12:54:20
Broadbent Auto Reminder 26/09/05 12:54:07
Broadbent Auto Reminder 26/09/05 12:51:23
Pickles Telephone call out 26/09/05 12:50:00
Broadbent Auto Reminder 26/09/05 12:49:50
Pickles Telephone call in 26/09/05 12:43:00
Broadbent Auto Reminder 26/09/05 12:41:22
Race Auto Appointment 26/09/05 12:41:13
Race Auto Appointment 26/09/05 12:36:27
Broadbent Auto Reminder 26/09/05 12:36:04
Broadbent Auto Reminder 26/09/05 12:33:38
Broadbent Auto Reminder 26/09/05 12:30:01
Booth Telephone call out 26/09/05 12:25:32
Booth Telephone call out 26/09/05 12:23:00
Prior Auto Hire 26/09/05 12:18:40
for 18 months of data....
I would like to group these by like,
26/09/05 Davis Telephone Call 5
25/09/05 Davis Telephone Call 3
24/09/05 Davis Telephone Call 8
Also
2005-10 Davis Telephone Call 123
2005-09 Davis Telephone Call 111
2005-08 Davis Telephone Call 77
and so on...
thanks again.|||Pls post a complete DDL ( like in my example).
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:657D99E0-0C69-4180-B987-15FDE2EFB83F@.microsoft.com...
> Thankyou, however perhaps I should have given more detail....
> Currently the date is like
> NAME TYPE DATE
> Rothery Telephone call out 26/09/05 13:00:00
> Ginnelly Auto Order 26/09/05 12:55:03
> Aldcroft Customer Services 26/09/05 12:55:00
> Aldcroft Auto Order 26/09/05 12:54:20
> Broadbent Auto Reminder 26/09/05 12:54:07
> Broadbent Auto Reminder 26/09/05 12:51:23
> Pickles Telephone call out 26/09/05 12:50:00
> Broadbent Auto Reminder 26/09/05 12:49:50
> Pickles Telephone call in 26/09/05 12:43:00
> Broadbent Auto Reminder 26/09/05 12:41:22
> Race Auto Appointment 26/09/05 12:41:13
> Race Auto Appointment 26/09/05 12:36:27
> Broadbent Auto Reminder 26/09/05 12:36:04
> Broadbent Auto Reminder 26/09/05 12:33:38
> Broadbent Auto Reminder 26/09/05 12:30:01
> Booth Telephone call out 26/09/05 12:25:32
> Booth Telephone call out 26/09/05 12:23:00
> Prior Auto Hire 26/09/05 12:18:40
> for 18 months of data....
> I would like to group these by like,
> 26/09/05 Davis Telephone Call 5
> 25/09/05 Davis Telephone Call 3
> 24/09/05 Davis Telephone Call 8
> Also
> 2005-10 Davis Telephone Call 123
> 2005-09 Davis Telephone Call 111
> 2005-08 Davis Telephone Call 77
> and so on...
> thanks again.
>

Grouping Views by Dates

I have a table with the date in a full format including hours, minutes and
seconds etc. I would like to group the information by yearmonthday and also
by yearmonth.
Hi
CREATE TABLE #Test
(
dt DATETIME NOT NULL
)
--Change the data for your needs
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-01 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-02 13:46:59.707')
INSERT INTO #Test VALUES ('2006-01-01 13:46:59.707')
SELECT Year(dt),Count(*) FROM #Test
GROUP BY Year(dt)
Also lookup DAY(),MONTH() system functions in the BOL
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:2FED6491-6AFE-40B6-AFC5-3E1561D211C1@.microsoft.com...
>I have a table with the date in a full format including hours, minutes and
> seconds etc. I would like to group the information by yearmonthday and
> also
> by yearmonth.
|||Thankyou, however perhaps I should have given more detail....
Currently the date is like
NAME TYPE DATE
RotheryTelephone call out26/09/05 13:00:00
GinnellyAuto Order 26/09/05 12:55:03
AldcroftCustomer Services26/09/05 12:55:00
AldcroftAuto Order 26/09/05 12:54:20
BroadbentAuto Reminder26/09/05 12:54:07
BroadbentAuto Reminder26/09/05 12:51:23
PicklesTelephone call out26/09/05 12:50:00
BroadbentAuto Reminder26/09/05 12:49:50
PicklesTelephone call in26/09/05 12:43:00
BroadbentAuto Reminder26/09/05 12:41:22
RaceAuto Appointment26/09/05 12:41:13
RaceAuto Appointment26/09/05 12:36:27
BroadbentAuto Reminder26/09/05 12:36:04
BroadbentAuto Reminder26/09/05 12:33:38
BroadbentAuto Reminder26/09/05 12:30:01
BoothTelephone call out26/09/05 12:25:32
BoothTelephone call out26/09/05 12:23:00
PriorAuto Hire 26/09/05 12:18:40
for 18 months of data....
I would like to group these by like,
26/09/05 Davis Telephone Call 5
25/09/05 Davis Telephone Call 3
24/09/05 Davis Telephone Call 8
Also
2005-10 Davis Telephone Call 123
2005-09 Davis Telephone Call 111
2005-08 Davis Telephone Call 77
and so on...
thanks again.
|||Pls post a complete DDL ( like in my example).
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:657D99E0-0C69-4180-B987-15FDE2EFB83F@.microsoft.com...
> Thankyou, however perhaps I should have given more detail....
> Currently the date is like
> NAME TYPE DATE
> Rothery Telephone call out 26/09/05 13:00:00
> Ginnelly Auto Order 26/09/05 12:55:03
> Aldcroft Customer Services 26/09/05 12:55:00
> Aldcroft Auto Order 26/09/05 12:54:20
> Broadbent Auto Reminder 26/09/05 12:54:07
> Broadbent Auto Reminder 26/09/05 12:51:23
> Pickles Telephone call out 26/09/05 12:50:00
> Broadbent Auto Reminder 26/09/05 12:49:50
> Pickles Telephone call in 26/09/05 12:43:00
> Broadbent Auto Reminder 26/09/05 12:41:22
> Race Auto Appointment 26/09/05 12:41:13
> Race Auto Appointment 26/09/05 12:36:27
> Broadbent Auto Reminder 26/09/05 12:36:04
> Broadbent Auto Reminder 26/09/05 12:33:38
> Broadbent Auto Reminder 26/09/05 12:30:01
> Booth Telephone call out 26/09/05 12:25:32
> Booth Telephone call out 26/09/05 12:23:00
> Prior Auto Hire 26/09/05 12:18:40
> for 18 months of data....
> I would like to group these by like,
> 26/09/05 Davis Telephone Call 5
> 25/09/05 Davis Telephone Call 3
> 24/09/05 Davis Telephone Call 8
> Also
> 2005-10 Davis Telephone Call 123
> 2005-09 Davis Telephone Call 111
> 2005-08 Davis Telephone Call 77
> and so on...
> thanks again.
>

Grouping Views by Dates

Grouping several items in one group

Hi Everyone,
I am new to reporting services and I am trying to create groups which
contains more then one code .
Table
Name, Code, Amount
paper 1101 Â£10
Pens 1102 Â£5
Shoes 2512 Â£20
Clothes 3455 Â£5
I want to put code 1101 and 1102 as group 1 with total, 2512 and 3455 as
group 2 with total.
At the moment I can only seem to group each one individually.
Please help.
John
--
John HoYou question is more of a SQL problem, and there is more than one way
to solve your problem.
SELECT 'GRP1' as groupcode, amount from paper where code =3D 1101
UNION
SELECT 'GRP1' as groupcode, amount from pens where code =3D 1102
UNION
SELECT 'GRP2' as groupcode, amount from shoes where code =3D 2512
UNION
SELECT 'GRP2' as groupcode, amount from clothes where code =3D 3455
save the above query to a View object. When you open the view, you'll
see this:
<pre>
groupcode | amount
GRP1 | =A310
GRP1 | =A35
GRP2 | =A320
GRP2 | =A35
</pre>
Now you can group & sum on your view for your report. I'm sure there
are more elegant solutions (perhaps using StoredProcs), but this is
dirty and quick...heh.
On Apr 7, 11:05 am, Learner <Lear...@.discussions.microsoft.com> wrote:
> Hi Everyone,
> I am new to reporting services and I am trying to create groups which
> contains more then one code .
> Table
> Name, Code, Amount
> paper 1101 =A310
> Pens 1102 =A35
> Shoes 2512 =A320
> Clothes 3455 =A35
> I want to put code 1101 and 1102 as group 1 with total, 2512 and 3455 as
> group 2 with total.
> At the moment I can only seem to group each one individually.
> Please help.
> John
> --
> John Ho

Grouping Row Border

Hi,
I'm trying to achieve the following in a table. Have border after/before the
grouping changes to the next value.
Nov 1 v1 v2 v3 v4
v1 v2 v3 v4
_____________________
Nov 3 v1 v2 v3 v4
_____________________
Is there an easy way to do this?Yes. Add either a top or bottom border to group footer row in the table. The
report at end of this posting demonstrates how to accomplish this task.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Reg" <reg@.dsl.za.org> wrote in message
news:O%23n$LYymEHA.3684@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm trying to achieve the following in a table. Have border after/before
the
> grouping changes to the next value.
> Nov 1 v1 v2 v3 v4
> v1 v2 v3 v4
> _____________________
> Nov 3 v1 v2 v3 v4
> _____________________
> Is there an easy way to do this?
>
BorderBetweenGroups.rdl
<?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>1.125in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>14</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>13</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>12</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="CompanyName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>CompanyName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CompanyName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="City">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>City</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!City.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>
</Details>
<DataSetName>Northwind</DataSetName>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Country">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>Country</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Country.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>10</ZIndex>
<rd:DefaultName>textbox11</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>9</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!Country.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Footer>
<TableRows>
<TableRow>
<Height>0.125in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox13">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>2pt</Default>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox13</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox14">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>2pt</Default>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox14</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox15">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>2pt</Default>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox15</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
</TableGroup>
</TableGroups>
<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>8</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>7</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>6</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>76d8d0cd-7eef-42fe-aa1e-81920ad6bfed</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="CustomerID">
<DataField>CustomerID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContactName">
<DataField>ContactName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContactTitle">
<DataField>ContactTitle</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Address">
<DataField>Address</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="City">
<DataField>City</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Region">
<DataField>Region</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PostalCode">
<DataField>PostalCode</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Phone">
<DataField>Phone</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Fax">
<DataField>Fax</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>select * from customers</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>2fd0260c-7640-49fb-9128-9a9647b7cdcf</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>

Grouping reports

We have a designed a table report to show the hierarchy like "Locations -> Station -> Prod. category -> Product Name -> Product Model" and the report have some measures like Quantity in stock, Quantity sold.......

By default the report shows all the Locations with sum of all the measures, on expanding any location all the Stations are shown and on expanding Stations all the product names are shown and on expanding any product name all the product models are shown.

Now the requirement is, the same report should show the details of all the Product names in a single group, from all the locations and all the stations. And when i click on All stations link i should be shown the same report with all the stations under all locations.

i.e. the same report should have one row with the static text in columns like All Locations, All Stations, All Prod. Category, All Product Names and All Product Models. When the user click on All Product Names column the report should expand to show all the products in all locations and all stations.

I have looked for many examples but couldn't find such type of report, Is it possible to create such type of grouping using SQL server reporting services (MSSQL 2005). Pl. provide me a example to implement this type of requirement.

Thanks,
Sri

Hi,

Can you explain why you can't use the grouping features of a normal table or matrix? In your query make sure every row has a column indicating the location, station, prod. cat. etc. In your table, make different groups on these columns.

Regards, Jeroen

|||

With grouping i cannot get all the product names under all the locations and stations. With grouping, i need to select specific location and station to see the product names under that location and station. Also i need to show all the product names under all the stations in any specific location and i also need to show the product names under selected station this is the requirement.

I think this is the basic feature any OLAP reporting engine should provide for analysis of data. The report should show how many Products are there in stock in the country and the same report should have options to expand and see which locations in the country have what stock and further drill-down show which station has what products in stock without navigation to other screens.

I am trying to use sub-reports for achiving this requirement, if there are any other simple solutions for this type of reports Pl. suggest with a example.

Thanks & Regards
Sri

|||

Hi Sri,

There have been a few cases in which I managed to built a more dynamic report using multiple tables in a report and using the navigation property of a cell to run the same report again using parameters which get their value based on the cell which was clicked on. So lets say you have a table with a dataset of locations and a table with a dataset of stations. Now clicking a particular location runs the same report again, with the location parameter set to the clicked location. The station dataset is filled with all stations of this location and displayed in the table with stations. But I don't think this approach will cover all your requirements though.

Regards, Jeroen

|||

Sri,

i am thinking that maybe the reason why you are not getting a list of all products when grouping by station is because empty rows are being returned from the cube, these get eliminated from the dataset. To fix this, use a COALESCEEMPTY in the MDX, that way you can return a zero for empty rows, and should get a complete listing of products.

|||

I tried using COALESCEEMPTY in the MDX but still not getting all the products in all the stations as a single group. I know this is becaue all the products are defined under some station and the there are no products with section empty.

In this report i should be able to see all the products in the country with the measures defined (Quanity in stock and quantity sold) and i should be able to select some location and should able to see all the products in that location with measures showing the measures within that location and if i select any station i should be able to show all the products and measures within that location and station.

Looks it is not possible to create such a report using MS SQL 2005 reporting services. If it is possible or if there are any alternative ways (to achive this without navigationg to other report screen) Pl. guide me through some example.

Thanks and regards,
Sri

Grouping record based on a condtion

TechnologyTypeSize

XYZA200

XYZ1A200

XYZ2A300

XYZ3A300

ABC1X238

ABC2X238

PQRB320

MNOC330

I have written a query on a table whose output will look like the above. I need to know if i should store this in a record set or create a temp table to get the following fuctionality.

Now I need to concatenate the Technology based on Type and size.

As you can see in Type A we have two sizes 200 and 300.

We need to group the Technology of type A with same size together.

So the output of the procedure should be

XYZ + XYZ1

XYZ2+ XYZ3

ABC1 + ABC2etc.

We need to concatenate the Technology string with the next technology if they have the same type and size.

Can somebody please help or send any sample code.

Any help is greatly appreciated

Thanks

Swapna

CTE solution for SQL Server 2005:

With MyCTE(Size, Type, col1, col2, myNum) AS

(

SELECT a.Size, a.Type, CONVERT(varchar(50), MIN(RTRIM(a.Technology))) as col1, CONVERT(varchar(50),RTRIM((a.Technology))) as col2, 1 as myNum

FROM techTable AS a GROUP BY a.Size, a.Type, CONVERT(varchar(50),RTRIM(a.Technology))

UNION ALL

SELECT b.Size, b.Type, CONVERT(varchar(50), RTRIM(b.Technology)) as col1, CONVERT(varchar(50), (c.col2 + '+' + RTRIM(b.Technology))) as col2, c.myNum+1 as myNum

FROM techTable AS b INNER JOIN MyCTE c ON b.Size=c.Size AND b.Type= c.Type

WHERE b.Technology>c.col1

)

SELECT a.col2 As Technology_combined, a.Size, a.Type FROM MyCTE a INNER JOIN (SELECT Max(a1.myNum) as myNumMax, a1.Size, a1.Type FROM MyCTE a1

GROUP BY a1.Size, a1.Type) b on b.Size=a.Size AND b.Type= a.Type AND a.myNum= b.myNumMax

|||

you I am new to stored procedures...and working with the databse...so could you please explain the above code...I could not get much from it...Will the loop through the sample table I mentioned and return a set of concatenated Technology values....Please get back.

Thanks for your reply

Swapna

|||

and more over the data in the table is just an example...we are in no way concerned with the data in Technology Column...all we need to do is group the technology column data which have the same Type and Size

TechnologyTypeSize

XYZA200

ABCA200

ABC1A300

XYZ3A300

MNO1X238

ABC2X238

PQRB320

MNOC330

so the output should be XYZ+ABC

ABC1+XYZ3

MNO1+ABC2.... I hope I am clear now.

Please reply...Can we use cursors to do this...can someone explain how to use cursors for the above functionality

Thanks

|||

Hello:

The "techTable" would be the name of your table which holds your data.

The CTE code I posted will work in a recursive fasion.

If you are using SQL Server 2005, you can give the code a try run (remember to change the "techTable" to your table name).

|||

--CREATE TABLE MyTable(Technology VARCHAR(MAX), Type char(10), Size int)

--Enter the values suggested

--Run the following code

DECLARE @.Type CHAR(1)

DECLARE @.Size INT

DECLARE @.MyNewString CHAR(11)

DECLARE @.MyNewString2 VARCHAR(MAX)

SET @.MyNewString2 = ''

--Replace MyTable with your tablename

--Replace Technology, Type, Size with your field names

CREATE TABLE #Temp(MyNewString VARCHAR(MAX))

DECLARE c1 CURSOR FOR

SELECT mt.Type, mt.Size

FROM MyTable mt

OPEN c1

FETCH NEXT FROM c1

INTO @.Type, @.Size

WHILE @.@.FETCH_STATUS = 0

BEGIN

DECLARE c2 CURSOR FOR

SELECT Technology from MyTable Where size = @.Size and type = @.Type

OPEN c2

FETCH NEXT FROM c2

INTO @.MyNewString

WHILE @.@.FETCH_STATUS = 0

BEGIN

SET @.MyNewString2 = LTRIM(RTRIM(@.MyNewString2)) + LTRIM(RTRIM(@.MyNewString))

FETCH NEXT FROM c2

INTO @.MyNewString

END

CLOSE c2

DEALLOCATE c2

INSERT INTO #Temp(MyNewString) VALUES(@.MyNewString2)

SET @.MyNewString2 = ''

FETCH NEXT FROM c1

INTO @.Type, @.Size

END

CLOSE c1

DEALLOCATE c1

SELECT * from #Temp

GROUP BY MyNewString

DROP TABLE #temp

|||

limno wrote:

CTE solution for SQL Server 2005:
With MyCTE(Size, Type, col1, col2, myNum) AS
(
SELECT a.Size, a.Type, CONVERT(varchar(50), MIN(RTRIM(a.Technology))) as col1, CONVERT(varchar(50),RTRIM((a.Technology))) as col2, 1 as myNum
FROM techTable AS a GROUP BY a.Size, a.Type, CONVERT(varchar(50),RTRIM(a.Technology))
UNION ALL
SELECT b.Size, b.Type, CONVERT(varchar(50), RTRIM(b.Technology)) as col1, CONVERT(varchar(50), (c.col2 + '+' + RTRIM(b.Technology))) as col2, c.myNum+1 as myNum
FROM techTable AS b INNER JOIN MyCTE c ON b.Size=c.Size AND b.Type= c.Type
WHERE b.Technology>c.col1
)
SELECT a.col2 As Technology_combined, a.Size, a.Type FROM MyCTE a INNER JOIN (SELECT Max(a1.myNum) as myNumMax, a1.Size, a1.Type FROM MyCTE a1
GROUP BY a1.Size, a1.Type) b on b.Size=a.Size AND b.Type= a.Type AND a.myNum= b.myNumMax

This code is equivalent to my nested cursor approach and works, but I agree is a tad bit confusing...but nice work all the same.|||

You don't need to use cursors to get the results. Using cursors is often inefficient and consumes more resources than necessary. Very few problems require cursor based solutions and if you don't know how to use cursors that is actually good. :-) You can learn the basics of SQL to begin with than cursors.

If you are using SQL Server 2005 you can use below approach which will be faster than CTE and slightly simpler.

select t2.Type

, t2.Size

, max(case t2.seq when 1 then t1.Technology end)

+ max(case t2.seq when 2 then '+' + t2.Technology else '' end) as Technology

from (

select t1.Type, t1.Technology, t1.Size

, ROW_NUMBER() OVER(partition by t1.Type, t1.Size order by t1.Technology) as seq

from tbl as t1

) as t2

group by t2.Type, t2.Size;

You can use similar logic in older versions of SQL Server also since they don't have the ROW_NUMBER() function.

Below working query uses pubs authors table and you can do the same based on your table schema.

select a2.city, a2.state
, max(case a2.seq when 1 then a2.au_id else '' end)
+ max(case a2.seq when 2 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 3 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 4 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 5 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 6 then ', ' + a2.au_id else '' end) as au_ids
from (
select a1.city, a1.state, a1.au_id, row_number() over(partition by a1.city, a1.state order by a1.au_id) as seq
from authors as a1
) as a2
group by a2.city, a2.state
order by a2.state, a2.city;

|||

Umachandar Jayachandran - MS wrote:

You don't need to use cursors to get the results. Using cursors is often inefficient and consumes more resources than necessary. Very few problems require cursor based solutions and if you don't know how to use cursors that is actually good. :-) You can learn the basics of SQL to begin with than cursors.

If you are using SQL Server 2005 you can use below approach which will be faster than CTE and slightly simpler.

select t2.Type
, t2.Size
, min(case t2.seq when 1 then t1.Technology end)
+ min(case t2.seq when 2 then '+' + t2.Technology else '' end) as Technology
from (
select t1.Type, t1.Technology, t1.Size
, ROW_NUMBER() OVER(partition by t1.Type, t1.Size order by t1.Technology) as seq
from tbl as t1
) as t2
group by t2.Type, t2.Size;

You can use similar logic in older versions of SQL Server also since they don't have the ROW_NUMBER() function.

Not knowing cursors is a good thing? Can we go a step further with your logic and say not knowing SQL is a good thing? Use ADO?

...and could you post some working code. I'm interested in this approach but getting errors.

Thanks,

Adamus

|||

Umachandar Jayachandran - MS wrote:

You don't need to use cursors to get the results. Using cursors is often inefficient and consumes more resources than necessary. Very few problems require cursor based solutions and if you don't know how to use cursors that is actually good. :-) You can learn the basics of SQL to begin with than cursors.

If you are using SQL Server 2005 you can use below approach which will be faster than CTE and slightly simpler.

select t2.Type
, t2.Size
, min(case t2.seq when 1 then t1.Technology end)
+ min(case t2.seq when 2 then '+' + t2.Technology else '' end) as Technology
from (
select t1.Type, t1.Technology, t1.Size
, ROW_NUMBER() OVER(partition by t1.Type, t1.Size order by t1.Technology) as seq
from tbl as t1
) as t2
group by t2.Type, t2.Size;

You can use similar logic in older versions of SQL Server also since they don't have the ROW_NUMBER() function.

I unmarked this as the answer because the poster requested a cursor approach.|||

Not using procedural logic when dealing with SQL is a good thing. Yes, you can use ADO/client-side code to do this but it will be very slow and inefficient. If you have a table that contains say millions of rows you will be moving those rows from client to server for each user and performing the logic on the client side. Moreover, you have to implement lot of specific logic on the client side whereas the SQL language has built-in functionality / primitives to solve complex problems easily.

Anyway, here is a query that uses pubs authors table:

The query produces a comma-separated list of author ids for each state and city combination similar to the problem.

Grouping question

I have a report that is actually going to be printed on NCR (carbon) paper to be written on. On this report I have a table that displays items from five different groups plus adds blanks for written entries. The data displays correctly under each group along with the extra spaces for written data, however what I need to know is how to "force" a group that does not have any items.

Is there a way to do this?

Thanks for any information.

If you don't have any items of a group your resultset doesn't contain the group and I think its not possible to add it afterwards, so you have to change your SQL-Query..
If you join use outer joins if you don't have any joins create a table that contains all groups you need and outer join the tables..
take a look at
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=405288&SiteID=1
|||Thanks for the information. This does help greatly!

Grouping question

If I want to get a count of each type of item in a table, I can use a simply GROUP BY and include a Count() of what I want in the SELECT clause.

But how do I get subcounts of that data? Like I want to group by a first value, then show columns for all the possible values in a second column. For instance, let's say I have two columns FirstName and LastName. What would a t-sql statement look like to yield the following output? It's like using the Column gropuing in an Excel pivot table.

Is this possible?

	Totals
LastName	John	Jane
Doe	13	8
Schmoe	6	4

Thanks!

Dave

This is not possible to do in SQL without going through lot of hoops. You should do report generation on the client-side. Given few restrictions, you can generate a pivotted result like above.

Grouping Question

HI there all

Here is my question for the day.

I am creating a report based of a table where i have employee information and hours that he has worked, I need to have the total of all hours, and also the total of Billable hours. there is a field called Billable hours.

Anyways, In my layout section, I get the correct total hours, due to the groupin g which is easy. I also added a new field to my Query for the field hours with an alias of billable hours. I need to put some sort of filter for this field. so that This will add up the hours that are billable, (bullable =true.). I tried to do this with the query and I had no success. I tried thought the report layout, on the properties of that field something along the lines of

=Fields!Billable_Hours.Value where Fields!billable.Value = 1,

but it does not like my where clause and it errors out so I can not run the report.

I I remove the where clause it does not error out, but it prints error on the field on the report.

Any ideas anyone ?

Thanks

Armela,

Try

Sum(iif(Fields!Billable.value>1,Fields!Billable_hours.value,nothing))

I think this should work.

Ham

|||That worked.
Thank you very much

Grouping problems

Hello all,

I am having a problem. See, I have a small table that tracks users visits to my site. And I want to have it show all the people visiting first on the bottom of the page, people just visiting on top. I can solve that with a simple ORDER BY DateVisited clause. My problem is that I also want to group peoples IP addresses together too. If I don't, when two people visit the website around the same time, browse around, I get overlapping of IPs.

Example how it is now:
1. 127.0.0.1 /default.asp 12:15PM
2. 215.394.293.494 /default.asp 12:16PM
3. 127.0.0.1 /contact/default.asp 12:17PM

Example of how I would like it:
1. 215.394.293.494 /default.asp 12:16PM
2. 127.0.0.1 /default.asp 12:15PM
3. 127.0.0.1 /contact/default.asp 12:17PM

I can ORDER BY IpAddress first, but then my dates are wacked out and the people with lower initial IP numbers appear first, which is meaningless. Any advise?Could you add these values to your sample data and show us everything sorted the way you want to see it?

127.0.0.1 /default.asp 11:00AM the next day
127.0.0.1 /default.asp 10:00AM the same day
125.5.5.5 /default.asp 11:00AM the same day
125.5.5.5 /default.asp 11:00PM the same day
215.394.293.494 /default.asp 11:00AM the next day

Terri|||Here is my current SQL statement, notice how I only show the results for 1 day at a time. That way, I just page back and forth through the days...

"SELECT * FROM PageViews WHERE (CONVERT(datetime, CONVERT(varchar, LastVisited, 101)) = '" & dDate & "')" AND Remote_Addr <> '" & User & "' ORDER BY LastVisited,Remote_Addr;"

Variables:
dDate = Date selected
User = IP Address

Does this help? What do you want me to do next? You've helped me before Terri so I know you have a good answer for me. :)|||OK, you are only dealing with one date at a time. That helps.

Where would these fit in -- how would your 6 returned rows be sorted?

127.0.0.1 /contact/default.asp 9:00AM
127.0.0.1 /contact/default.asp 9:00PM
127.0.0.1 /default.asp 9:15PM

Terri
PS - glad I've been able to help in the past :-)|||It would return the results like so...

127.0.0.1 /default.asp 9:15PM
127.0.0.1 /contact/default.asp 9:00PM
127.0.0.1 /contact/default.asp 9:00AM

which is not the wrong thing... the problem is when two IPs visit the website at the same time... then I have something crazy like this...

127.0.0.1 /default.asp 9:15PM
123.456.0.1 /default.asp 5:05PM
127.0.0.1 /contact/default.asp 9:00PM
127.0.0.1 /contact/default.asp 9:00AM

I would prefer a look like this:
127.0.0.1 /default.asp 9:15PM
127.0.0.1 /contact/default.asp 9:00PM
127.0.0.1 /contact/default.asp 9:00AM
123.456.0.1 /default.asp 5:05PM

The 124.456.0.1 is listed last only because 127.0.0.1 visited a page last at 9:15PM. Had 127.0.0.1 not visited the two pages at 9:15PM and 9:00PM in the evening, he/she would actually be listed under 123.456.0.1 like so:
123.456.0.1 /default.asp 5:05PM
127.0.0.1 /contact/default.asp 9:00AM

Does this make any sense or am I just crazy?

Thanks,
B|||Anybody have any ideas? I am really looking forward to making this work!|||I am sorry, I lost site of this.

The problem is I am now completely confused on what you are looking for, because your latest examples seem to be in reverse chronological order, which they weren't at first.

Maybe someone else can make sense of it, or maybe you can make it clearer.

Terri|||I'd want to request for a clearer explaination of what you're trying to do. I'm a bit medicine head today, but reading this thread made me dizzy. Looking at your convert in the sql alone made me cringe.

Be a little more clear as to what you need, and let's hammer this one out.|||I'm sorry that everything seems to be confusing. I will try to explain everything over, and if I still don't make sense, it's probably just me and this crazy thing I'm trying to do. But thanks for looking...

Okay, so I record stats of people visiting my site. When somebody visits, it will record an entry and display it like so:

samplecableuser.comcast.net - 68.12.94.11 - /default.asp - 1 visit - 12:22pm

I turn around and format it in HTML to look similar to this:

samplecableuser.comcast.net (68.12.94.11)
/portfolio.asp (1 visit) at 5:26pm
/contactus.asp (1 visit) at 2:24pm
/default.asp (1 visit) at 11:22am

(notice how the first page that the person visited is towards the bottom, so as the person surfs on the site more, it will list the last page that person visited on top)

Now, this is perfect, and if I sort the table by the LastVisited field, all is well. That is, until two people surf the website at the same time, then my page does the following:

samplecableuser.comcast.net (68.12.94.11)
/portfolio.asp (1 visit) at 5:26pm
/contactus.asp (1 visit) at 2:24pm

anotherperson.bellsouth.net (230.128.43.4)
/test.asp (1 visit) at 1:55pm

samplecableuser.comcast.net (68.12.94.11)
/default.asp (1 visit) at 11:22am

Notice how they overlap? I would like to have it sorted by date, LastVisited is the field. But, I would also like to have it group the users together by IP address. so that the following above looks like this:

samplecableuser.comcast.net (68.12.94.11)
/portfolio.asp (1 visit) at 5:26pm
/contactus.asp (1 visit) at 2:24pm
/default.asp (1 visit) at 11:22am

anotherperson.bellsouth.net (230.128.43.4)
/test.asp (1 visit) at 1:55pm

Now, the samplecableuser.comcast.net person should be at the bottom, but since the last visited was at 5:26pm, he is put at the top. Is there some kind of UBound() on dates? Would that solve it? I am not very good with the GROUP BY clause because you can't really use SELECT * FROM whatever.

Any suggestions?

Thanks,
B|||Honestly, I was afraid you'd reply back so quickly. heh

Alright. I get your drift.

I think it's because you have to convert the date to a varchar. I still don't understand why you're doing that. I hope I'm not just stuck on that one.

What I'd say is group by the Remote_Addr, and sort by DateTime Desc

That should be what you want. Once you group it by the Remote_Addr, there can't be dupes since it's grouped by them anyways.

if you need help with the sql, try out www.sqlcourse.com|||Well the only reason I covert the date to varchar is because its the only way I could get the WHERE statement to pull out the records I wanted on SQL Server. If there is a better way, I would like to know. The dates get saved like so MM/DD/YYYY HH:MM:SS PM, but when I do that varchar conversion it becomes just MM/DD/YYYY, which I match with whatever todays date is.|||Another thing I am having a issue with, you say "group by remote_addr", but it wants me to include them all in the grouping or whatever. I get this message:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Column 'PageViews.TrackerID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

And if I include that in the SQL query, it will go to the next column, and so on until I am done with all the columns, then it will display. But, is that right?|||Instead of doing the date comparison that way, use DATEDIFF.

WHERE DATEDIFF(d,LastVisited,'" & dDate & "') = 0 AND..."

Terri|||See. What Terry mentioned is much sexier than casting to varchar.

Also, in regards to having to select everything properly.. I'm wondering if you're doing this inline sql or through stored procedure.

The reason I ask this is because if you're having so much diffculty in creating one sql statement to do everything for you, you may wish to create a procedure to execute through a few different statements to obtain the desired result.|||Got the date thing fixed, but what about the recordset? I can't do stored procedures... I just need it to group by one field only. Like so:

SELECT * FROM Table
WHERE Date = This
GROUP By IP
ORDER BY Date

If that statement could work, it would be right. But, in SQL Server (2000), you can't group items together unless everything in that * is in the group statement too. WTF?

Thanks,
B

Grouping in Table

Your help on this could make my day a better one ....

I was trying to create groups on a table report item in SRS 2005 report. The criterion I'm using in one group is exact opposite of the criterion I have in the next group. My dataset has records which fulfill both criteria but my table displays only records for the first group.

Is there a limit on the number of groups we can use in SRS reports and/or on building criteria for filters?

Thanks,

Samtadsh

There is no limit on the number of groups.

Can you please explain you problem in detail?

Priyank

|||

Thanks for you're fast response.

I'll try to explain it better.

I have a filter for group A which says Fields!col1.Value = xxxx. Another group, B, has a filter Fields!col1.Value != xxxx.

Can I display the output of these filters in one table?

Currently, I can see the result of group A but not that of group B.

samtadsh

|||

One of us is confused (and it is probably me <g>).

By "filter" do you mean the "Group on" expression, or are these really filters?

The short answer is "yes it is possible to do what you want" but I'm not entirely sure about what your expectations are of groups, and whether that is the correct way to do what you're trying to do.

I described a similar situation in a recent post, actually (http://spacefold.com/lisa/?date=2007-08-09) which calls it "multiple detail bands" for reasons that are irrelevant here <s>.

The part that is relevant here is that if all you want to do is filter the items first by one set and then by the other set, all you really need to do is create a sort order (forget about filters and groups) to do what you want, forcing some records "up" and some "down"

In my example in the post, the order clause, included among others for the table, was this:

=IIF(Left(UCase(Fields!RDLLayoutName.Value),4) = "IMG_","A","Z")

... yours might be IIF(Fields!col1.Value = xxxxx,"A","Z")

... does this ring any bells for you?

>L<

|||

I was refering the actual filters not the 'group on' ones.

But your suggestion of sorting helped me a lot.

I have another problem though. I need to display multiple details with parent-child associations. One set of record in a table needs to have the parent record in one row(top most) and the child records in the following rows but evenly split vertically - all in one table. Something like this:

Parent1: Parent1.Value

Child11: Child11.Value Child12: Child12.Value

Child13: Child11.Value Child14: Child14.Value

Child15: Child15.Value

Parent2: Parent2.Value

Child21: Child21.Value Child22: Child22.Value

Child23: Child23.Value Child24: Child24.Value

The problem I have is all the data is in one dataset and if I try to show it like above, I can only bind one child record per row. Even if the data comes from separate datasets, I will have the same issue. All child values come from the same column in the dataset and Parent values from another column.

Is there a way to do this kind of layout in using table report item?|||

OK -- should I assume you have already read the blog post on this subject that I referred to in my last message ?

-- assuming so, I think that your layout is actually easier than the one I describe there if your data is multiple children all "flattened out" into one dataset. But I am not positive, so can you give me an example SELECT from the parent and 2 children, with a couple of sample rows of what the data table looks like?

>L<

|||

The format of the data looks like this. I didn't show all the columns but these are good samples.

PARENT CHILD

PID NAME CID ROLE PERSONNEL_NAME
- -- -
8 Company1 5 Accountant Jerry Maguire

8 Company1 2 Accountant Doe John

8 Company1 4 Manager Smith John

8 Company1 9 Manager John Q

3 Company2 12 Manager Stuart Little

3 Company2 7 Accountant Erin Brockovich

3 Company2 6 Sales Person Woody Allen

6 Company3 8 Manager Doe John

Thanks.

Samtadsh

|||

SO is the Parent joining to a position table, and the Role value represents your multiple children, is that what you mean?

I thought when you said it was a multi-child that it was separate child tables and that your data would look "flatter".

Do you have a known number of roles (for example, you show three above, which represent three children)? Or do you think of it as "one child per role" where it is dynamic?

As you see, I am guessing. I would feel much better if you supplied a sample SQL statement <s>, so that I could understand what is "child 1" and what is "child 2" from your POV In the data table above, which you want to separate into your separate columns. I have some ideas about how you might do this, but I don't know how to relate your data example to the layout you supplied earlier. I'm thinking the SQL would help, if you don't think it will, can you tell me what columns you want each row above to appear in ?

Or am I terminally confused? <g>

>L<

|||I gave you what I get as a final result set from my stored procedure(one flat table). This will appear as a dataset in my report. I have to find a way to get that layout working off of this flat data. I just labled it 'Parent' and 'Child' on top to give you an idea which set of columns shoud be considered parent and child.

Roles are various. I can't limit myself with some number of roles. The same goes for Personnel_Names. CID will be unique(combination of ROLE and PERSONNEL_NAME will be unique).

I hope this makes it a bit more clear of what I'm trying to say.

Sorry for confusing you.

Thanks for taking the time to respond to my vague questions.

Samtadsh

|||

Lisa,

I think you're making it more complicated than it needs to be and getting a little confused.

From what I've read and seen of the data, the dataset consists of employees who work for a particular company. Withing this company they play a role. I believe the parent ID = company ID and child ID = employee ID and role is just an attribute of an employee.

The layout above seems to be groupped by company and for each company the requirement is to display the employees in multiple columns i.e. more thatn one employee per row.

The problem, as I see it, is that the default behaviour will display 1 employee per row.

I don't mean to butt into your thread but I hope this helps to explain the problem.

In terms of a solution, one thing that springs to mind is a matrix nested in the table with the employee on the columns groupped by =Ceiling(RowNumber(Nothing)/2)

Sorry but I'm not in a position to try this out right now.

Hope this helps.

|||

Sorry, Adam, I am just tring to do what the individual indicated he wanted done. I see the data the same way you do, FWIW, but the trouble is that doesn't explain his/her original question.

And you're not "butting in", and it's not "my thread" <s>. I am glad that somebody else reads the data, as it is presented, the same way I do.

I don't think your solution will work out for him, although matrix was the first thing that came to mind for me as well. I think Pivot might be more flexible here...

The problem for any such solution is what relationship the side-by-side elements really have, though. It bothers me to show stuff going across that has no real relationship to each other. That's why I asked about possible role hierarchies.

>L<

|||

So there are multiple rows (I'm not asking you to limit them, just trying to understand your layout) but you only want two columns -- are you trying to use the order/sort value to determine what goes into the two separate columns, or is this completely separate from your original question <g>?

Do you really want exactly two columns -- Or do you want one column per role, like in a pivot table or a matrix? The latter makes more sense, because you won't be showing the data in a way that that implies a relationship between employees that is not real...

>L<

|||

I have two cases in which this layout should be utilized. IT will be in the same report but different tables.

Case 1: There will be two columns across which employee name together with thier role gets displayed evenly starting from the left column. There is no role heirarchy and no limitation on type of roles. The distribution of the employees should be like this:

COL1 | COL2

Parent1: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

(Role11) - Employee11 | (Role11)- Employee12

(Role13)-Employee13 |

_

Parent2: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

(Role21)-Employee21 | (Role22)- Employee22

(Role23)-Employee23 | (Role24)- Employee24

Case 2: There will be two columns across which employee name together with thier role gets displayed evenly starting from the left column. There are two roles. The distribution of the employees should be like this:

COL1 | COL2

Parent1: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

(RoleA) - EmployeeA1 | (RoleB)- EmployeeB1

- EmployeeA2 |

_

Parent2: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

(RoleA)-EmployeeA1 | (RoleB)- EmployeeB1

-EmployeeA2 | - EmployeeB2

Thanks,

Samtadsh

|||

I think I have actually done this. I'm going to show you how I would do the first one in pure SQL -- I don't have your data but I believe I have modelled the same situation. I think the way to do the second layout is very similar and I will describe it briefly here as well.

This is what my data looks like:

1) an order header table that will serve as the parent/group
2) an order detail table with line item #s in it, which will serve as the child.

The simple SELECT statement below (order -> line item) is the way I am modeling your relationship company -> employee

Code Snippet

SELECT sales_no, line_no FROM orderheader H
JOIN orderdetail D ON H.sales_no = D.sales_no

OK?

To get your first layout, my query creates a result with three columns, like this -- I will explain the critical details below:

Code Snippet

select h.sales_no, Col1.line_no As Col1, Col2.line_no AS Col2

from orderheader h
join
(select sales_no, line_no, row_number() over
( partition by sales_no order by line_no) as orderrank from orderdetail ) Col1

on h.sales_no = col1.sales_no and col1.orderrank % 2 = 1

left join
(select sales_no, line_no, row_number() over
( partition by sales_no order by line_no) as orderrank from orderdetail ) Col2

on h.sales_no = col2.sales_no

and (col2.orderrank = col1.orderrank + 1 )

order by h.sales_no, col1.line_no

Here are the critical things to notice in this query:

The first (inner) join gives you all the odd-number line numbers, from the first line number on up (Employee #s 1,3,5 and so on in your case).

The second join is an *outer* join. It will give you Employee #s 2,4,6 and so on but will be null on the last row where the total number of employees is odd.

I have highlighted the three ordering/sorting components because I'm pretty sure that they have to match up to get this to work properly.

|||

Thanks Lisa.

I think the idea would work for my problem too. Unfortunately, I have SQL2000 back-end.(I should have mentioned this from the on set though ... sorry about that.)

It looks like row_number() and partition by are SQL2005 features. I'm looking into ways to acheive similar results using SQL2000 constructs. If you know have this implementation in SQL2000, I would appreciate it greatly.

Thanks.

S.T

Wednesday, March 28, 2012

Grouping problem

I am trying to get a table to display where my like rows would sum together,
but now matter how I do it there are 2 rows (in my example) that always show
as separate rows and I want to combine them.
For example:
ProductName Balance 30 60 90
-- -- -- -- --
30-Day Posting 1 1 0 0
(10) 90-Day Posting 10 0 0 10
(10) 90-Day Posting 20 0 20 0
(5) 60-Day Posting 5 0 5 0
Should not show 0 0 0 0
Row 2 and 3 should be together and have 30 as the balance and 20 and 10 in
the 60 and 90 column should be on the same line.
This was done with the following statement:
select ProductName,
Balance = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID)),
"30" = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
"60" = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
"90" = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (p1.PurchasedProductID = p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
from purchasedproducts p1
If I add the (ProductTypeID = 1) to the last line:
select ProductName,
Balance = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID)),
"30" = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
"60" = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
"90" = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
from purchasedproducts p1 where (ProductTypeID = 1)
Then I get:
ProductName Balance 30 60 90
-- -- -- -- --
30-Day Posting 1 1 0 0
(10) 90-Day Posting 10 0 0 10
(10) 90-Day Posting 20 0 20 0
(5) 60-Day Posting 5 0 5 0
This gets rid of the last line (which I wanted).
What I would like it to look like is:
ProductName Balance 30 60 90
-- -- -- -- --
30-Day Posting 1 1 0 0
(10) 90-Day Posting 30 0 20 10
(5) 60-Day Posting 5 0 5 0
How can I make it do that?
I assume I have to group it, but I can't seem to make that work with these
subqueries. I get errors, such as you can't use a subquery in a group by
clause.
Here is the table and data (really cut down).
drop table PurchasedProducts
go
CREATE TABLE [dbo].[PurchasedProducts] (
[PurchasedProductID] [int] IDENTITY (1, 1) NOT NULL ,
[ProductTypeID] [int] NULL,
[ProductName] [varchar] (20) NULL ,
[PostingsLeft] [int] NULL ,
[DateExpires] [datetime] NULL
) ON [PRIMARY]
GO
insert
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values
('30-Day Posting',1,1,'11/25/2005')
insert
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values
('(10) 90-Day Posting',1,10,'1/24/2006')
insert
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values
('(10) 90-Day Posting',1,20,'12/25/2005')
insert
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values
('(5) 60-Day Posting',1,5,'12/25/2005')
insert
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values
('Should not show',2,90,'12/01/2005')
go
Thanks,
TomThe easiest way to solve this is to group your results as illustrated below:
SELECT productname, SUM(balance) AS BALANCE, SUM(days30) AS [30],
SUM(days60) AS [60], SUM(days90) AS [90]
FROM (select ProductName,
Balance = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID)),
Days30 = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
Days60 = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
Days90 = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
from purchasedproducts p1 where (ProductTypeID = 1)) AS a
GROUP BY productname
- Peter Ward
WARDY IT Solutions
"tshad" wrote:

> I am trying to get a table to display where my like rows would sum togethe
r,
> but now matter how I do it there are 2 rows (in my example) that always sh
ow
> as separate rows and I want to combine them.
> For example:
> ProductName Balance 30 60 90
> -- -- -- -- --
> 30-Day Posting 1 1 0 0
> (10) 90-Day Posting 10 0 0 10
> (10) 90-Day Posting 20 0 20 0
> (5) 60-Day Posting 5 0 5 0
> Should not show 0 0 0 0
> Row 2 and 3 should be together and have 30 as the balance and 20 and 10 in
> the 60 and 90 column should be on the same line.
> This was done with the following statement:
> select ProductName,
> Balance = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID)),
> "30" = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
> "60" = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
> "90" = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (p1.PurchasedProductID = p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
> from purchasedproducts p1
> If I add the (ProductTypeID = 1) to the last line:
> select ProductName,
> Balance = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID)),
> "30" = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
> "60" = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
> "90" = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
> from purchasedproducts p1 where (ProductTypeID = 1)
> Then I get:
> ProductName Balance 30 60 90
> -- -- -- -- --
> 30-Day Posting 1 1 0 0
> (10) 90-Day Posting 10 0 0 10
> (10) 90-Day Posting 20 0 20 0
> (5) 60-Day Posting 5 0 5 0
> This gets rid of the last line (which I wanted).
> What I would like it to look like is:
> ProductName Balance 30 60 90
> -- -- -- -- --
> 30-Day Posting 1 1 0 0
> (10) 90-Day Posting 30 0 20 10
> (5) 60-Day Posting 5 0 5 0
> How can I make it do that?
> I assume I have to group it, but I can't seem to make that work with these
> subqueries. I get errors, such as you can't use a subquery in a group by
> clause.
> Here is the table and data (really cut down).
> drop table PurchasedProducts
> go
> CREATE TABLE [dbo].[PurchasedProducts] (
> [PurchasedProductID] [int] IDENTITY (1, 1) NOT NULL ,
> [ProductTypeID] [int] NULL,
> [ProductName] [varchar] (20) NULL ,
> [PostingsLeft] [int] NULL ,
> [DateExpires] [datetime] NULL
> ) ON [PRIMARY]
> GO
> insert
> PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)value
s
> ('30-Day Posting',1,1,'11/25/2005')
> insert
> PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)value
s
> ('(10) 90-Day Posting',1,10,'1/24/2006')
> insert
> PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)value
s
> ('(10) 90-Day Posting',1,20,'12/25/2005')
> insert
> PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)value
s
> ('(5) 60-Day Posting',1,5,'12/25/2005')
> insert
> PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)value
s
> ('Should not show',2,90,'12/01/2005')
> go
>
> Thanks,
> Tom
>
>|||"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:89A22616-2111-4C1B-9D6D-F9AF452FB238@.microsoft.com...
> The easiest way to solve this is to group your results as illustrated
below:
Does it!!
You just treated my select as another table. I can never seem to come up
with that myself. I always understand it when I see it, but I can't seem to
see it when I need it.
Not really sure of the thought process to come up with it.
I was almost there, but couldn't quite see it.
Thanks,
Tom
> SELECT productname, SUM(balance) AS BALANCE, SUM(days30) AS [30],
> SUM(days60) AS [60], SUM(days90) AS [90]
> FROM (select ProductName,
> Balance = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID)),
> Days30 = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
> Days60 = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
> Days90 = (select isnull(sum(PostingsLeft),0)
> from Purchasedproducts p2
> where (ProductTypeID = 1) and (p1.PurchasedProductID =
> p2.PurchasedProductID) and
> ((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
> (DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
> from purchasedproducts p1 where (ProductTypeID = 1)) AS a
> GROUP BY productname
>
> - Peter Ward
> WARDY IT Solutions
> "tshad" wrote:
>
together,
show
in
these
by
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values[colo
r=darkred]
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values[colo
r=darkred]
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values[colo
r=darkred]
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values[colo
r=darkred]
PurchasedProducts(ProductName,ProductTyp
eID,PostingsLeft,DateExpires)values[colo
r=darkred]

Grouping Problem

I have a table that contains a region, year, part, and quantity. I want the
query output to be one line per region with the years as columns for the sum
of the quantities. I've tried this, but I get one line per year instead of
one line per region.
SELECT
PART_ID,
REGION,
CASE Report_Year WHEN '2000' THEN SUM(Total_Inbound) ELSE 0 END AS "2000",
CASE Report_Year WHEN '2001' THEN SUM(Total_Inbound) ELSE 0 END AS "2001",
CASE Report_Year WHEN '2002' THEN SUM(Total_Inbound) ELSE 0 END AS "2002",
CASE Report_Year WHEN '2003' THEN SUM(Total_Inbound) ELSE 0 END AS "2003",
CASE Report_Year WHEN '2004' THEN SUM(Total_Inbound) ELSE 0 END AS "2004",
CASE Report_Year WHEN '2005' THEN SUM(Total_Inbound) ELSE 0 END AS "2005",
CASE Report_Year WHEN '2006' THEN SUM(Total_Inbound) ELSE 0 END AS "2006",
SUM(Total_Inbound) AS TOTAL_QTY
FROM dbo.tblGlobalInboundVolumes
GROUP BY PART_ID, Region, Report_Year
HAVING (PART_ID = 'KRC12110/3 R11F')Never mind...I figured it out
"Phill" wrote:

> I have a table that contains a region, year, part, and quantity. I want t
he
> query output to be one line per region with the years as columns for the s
um
> of the quantities. I've tried this, but I get one line per year instead o
f
> one line per region.
> SELECT
> PART_ID,
> REGION,
> CASE Report_Year WHEN '2000' THEN SUM(Total_Inbound) ELSE 0 END AS "2000",
> CASE Report_Year WHEN '2001' THEN SUM(Total_Inbound) ELSE 0 END AS "2001",
> CASE Report_Year WHEN '2002' THEN SUM(Total_Inbound) ELSE 0 END AS "2002",
> CASE Report_Year WHEN '2003' THEN SUM(Total_Inbound) ELSE 0 END AS "2003",
> CASE Report_Year WHEN '2004' THEN SUM(Total_Inbound) ELSE 0 END AS "2004",
> CASE Report_Year WHEN '2005' THEN SUM(Total_Inbound) ELSE 0 END AS "2005",
> CASE Report_Year WHEN '2006' THEN SUM(Total_Inbound) ELSE 0 END AS "2006",
> SUM(Total_Inbound) AS TOTAL_QTY
> FROM dbo.tblGlobalInboundVolumes
> GROUP BY PART_ID, Region, Report_Year
> HAVING (PART_ID = 'KRC12110/3 R11F')|||Try:
SELECT
REGION,
CASE Report_Year WHEN '2000' THEN SUM(Total_Inbound) ELSE 0 END AS "2000",
CASE Report_Year WHEN '2001' THEN SUM(Total_Inbound) ELSE 0 END AS "2001",
CASE Report_Year WHEN '2002' THEN SUM(Total_Inbound) ELSE 0 END AS "2002",
CASE Report_Year WHEN '2003' THEN SUM(Total_Inbound) ELSE 0 END AS "2003",
CASE Report_Year WHEN '2004' THEN SUM(Total_Inbound) ELSE 0 END AS "2004",
CASE Report_Year WHEN '2005' THEN SUM(Total_Inbound) ELSE 0 END AS "2005",
CASE Report_Year WHEN '2006' THEN SUM(Total_Inbound) ELSE 0 END AS "2006",
SUM(Total_Inbound) AS TOTAL_QTY
FROM dbo.tblGlobalInboundVolumes
WHERE (PART_ID = 'KRC12110/3 R11F')
GROUP BY Region
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:457D9064-2B3B-4243-8648-E5884B22102E@.microsoft.com...
I have a table that contains a region, year, part, and quantity. I want the
query output to be one line per region with the years as columns for the sum
of the quantities. I've tried this, but I get one line per year instead of
one line per region.
SELECT
PART_ID,
REGION,
CASE Report_Year WHEN '2000' THEN SUM(Total_Inbound) ELSE 0 END AS "2000",
CASE Report_Year WHEN '2001' THEN SUM(Total_Inbound) ELSE 0 END AS "2001",
CASE Report_Year WHEN '2002' THEN SUM(Total_Inbound) ELSE 0 END AS "2002",
CASE Report_Year WHEN '2003' THEN SUM(Total_Inbound) ELSE 0 END AS "2003",
CASE Report_Year WHEN '2004' THEN SUM(Total_Inbound) ELSE 0 END AS "2004",
CASE Report_Year WHEN '2005' THEN SUM(Total_Inbound) ELSE 0 END AS "2005",
CASE Report_Year WHEN '2006' THEN SUM(Total_Inbound) ELSE 0 END AS "2006",
SUM(Total_Inbound) AS TOTAL_QTY
FROM dbo.tblGlobalInboundVolumes
GROUP BY PART_ID, Region, Report_Year
HAVING (PART_ID = 'KRC12110/3 R11F')

Grouping output

I have a query that drives the generation of a table. The query is filtered
during output. If the filtering results in no rows being output for the
table, i'd like to put some verbage on the table footer indicating "No
Matching Records" or something similar.
Is there an easy way to do this that i'm missing?
Thanks!
BrianTake a look at NoRows table property.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"G" <brian.grant@.si-intl-kc.com> wrote in message
news:e8xqt8gqEHA.324@.TK2MSFTNGP11.phx.gbl...
> I have a query that drives the generation of a table. The query is
filtered
> during output. If the filtering results in no rows being output for the
> table, i'd like to put some verbage on the table footer indicating "No
> Matching Records" or something similar.
> Is there an easy way to do this that i'm missing?
> Thanks!
> Brian
>sql

Grouping on time

Hello,
I have a table with 2 columns, time and amount. I want to be able to group
by an interval and sum the amount see below of a sample of the data.
Time Amount
2005-02-16 05:41:00.000 100
2005-02-16 05:41:01.000 100
2005-02-16 05:41:02.000 100
2005-02-16 05:41:03.000 100
2005-02-16 05:41:04.000 100
2005-02-16 05:41:05.000 100
2005-02-16 05:41:06.000 100
2005-02-16 05:41:07.000 100
2005-02-16 05:41:08.000 100
2005-02-16 05:41:09.000 100
2005-02-16 05:41:10.000 100
2005-02-16 05:41:11.000 100
2005-02-16 05:41:12.000 100
2005-02-16 05:41:13.000 100
2005-02-16 05:41:14.000 100
so the result of the above with an interval of 5 seconds would be
Time Amount
2005-02-16 05:41:04.000 500
2005-02-16 05:41:09.000 500
2005-02-16 05:41:14.000 500
any ideas?
ThanksTry,
use northwind
go
create table t (
[Time] datetime,
Amount int
)
go
insert into t values('2005-02-16 05:41:00.000', 100)
insert into t values('2005-02-16 05:41:01.000', 100)
insert into t values('2005-02-16 05:41:02.000', 100)
insert into t values('2005-02-16 05:41:03.000', 100)
insert into t values('2005-02-16 05:41:04.000', 100)
insert into t values('2005-02-16 05:41:05.000', 100)
insert into t values('2005-02-16 05:41:06.000', 100)
insert into t values('2005-02-16 05:41:07.000', 100)
insert into t values('2005-02-16 05:41:08.000', 100)
insert into t values('2005-02-16 05:41:09.000', 100)
insert into t values('2005-02-16 05:41:10.000', 100)
insert into t values('2005-02-16 05:41:11.000', 100)
insert into t values('2005-02-16 05:41:12.000', 100)
insert into t values('2005-02-16 05:41:13.000', 100)
insert into t values('2005-02-16 05:41:14.000', 100)
go
select
max([time]) as max_time,
sum(amount) as sum_amount
from
t
group by
datediff(second, convert(char(8), [time], 112), [time]) / 5
go
drop table t
go
AMB
"Fab" wrote:

> Hello,
> I have a table with 2 columns, time and amount. I want to be able to group
> by an interval and sum the amount see below of a sample of the data.
> Time Amount
> 2005-02-16 05:41:00.000 100
> 2005-02-16 05:41:01.000 100
> 2005-02-16 05:41:02.000 100
> 2005-02-16 05:41:03.000 100
> 2005-02-16 05:41:04.000 100
> 2005-02-16 05:41:05.000 100
> 2005-02-16 05:41:06.000 100
> 2005-02-16 05:41:07.000 100
> 2005-02-16 05:41:08.000 100
> 2005-02-16 05:41:09.000 100
> 2005-02-16 05:41:10.000 100
> 2005-02-16 05:41:11.000 100
> 2005-02-16 05:41:12.000 100
> 2005-02-16 05:41:13.000 100
> 2005-02-16 05:41:14.000 100
> so the result of the above with an interval of 5 seconds would be
> Time Amount
> 2005-02-16 05:41:04.000 500
> 2005-02-16 05:41:09.000 500
> 2005-02-16 05:41:14.000 500
>
> any ideas?
> Thanks
>
>|||This was responded yesterday ( assumption is that there exists one row for
every monotonically increasing second ):
[url]http://groups.google.ca/groups?selm=%238%23HOtMMFHA.3832%40TK2MSFTNGP12.phx.gbl[/u
rl]
Anith|||use something like that
select dateadd(ss,-datepart(ss,time)%5,time),sum(amount) from @.t group by
dateadd(ss,-datepart(ss,time)%5,time)
"Fab" wrote:

> Hello,
> I have a table with 2 columns, time and amount. I want to be able to group
> by an interval and sum the amount see below of a sample of the data.
> Time Amount
> 2005-02-16 05:41:00.000 100
> 2005-02-16 05:41:01.000 100
> 2005-02-16 05:41:02.000 100
> 2005-02-16 05:41:03.000 100
> 2005-02-16 05:41:04.000 100
> 2005-02-16 05:41:05.000 100
> 2005-02-16 05:41:06.000 100
> 2005-02-16 05:41:07.000 100
> 2005-02-16 05:41:08.000 100
> 2005-02-16 05:41:09.000 100
> 2005-02-16 05:41:10.000 100
> 2005-02-16 05:41:11.000 100
> 2005-02-16 05:41:12.000 100
> 2005-02-16 05:41:13.000 100
> 2005-02-16 05:41:14.000 100
> so the result of the above with an interval of 5 seconds would be
> Time Amount
> 2005-02-16 05:41:04.000 500
> 2005-02-16 05:41:09.000 500
> 2005-02-16 05:41:14.000 500
>
> any ideas?
> Thanks
>
>|||CREATE TABLE ReportPeriods
(period_id CHAR(10) NOT NULL,
start_time DATETIME NOT NULL,
end_time DATETIME NOT NULL,
CHECK (start_time < end_time),
PRIMARY KEY (start_time, end_time));
Load your times into the table then:
SELECT period_id, COUNT(*)
FROM ReportPeriods AS P1, Foobar AS F1
WHERE F1.event_time BETWEEN start_time AND end_time;|||sorry i made a mistake the script should be
select max(time),sum(amount) from @.t group by
dateadd(ss,-datepart(ss,time)%5,time)
the problem with the response of alejandro mesa is that if you have the same
time in different days the two rows will be grouped together
"Fab" wrote:

> Hello,
> I have a table with 2 columns, time and amount. I want to be able to group
> by an interval and sum the amount see below of a sample of the data.
> Time Amount
> 2005-02-16 05:41:00.000 100
> 2005-02-16 05:41:01.000 100
> 2005-02-16 05:41:02.000 100
> 2005-02-16 05:41:03.000 100
> 2005-02-16 05:41:04.000 100
> 2005-02-16 05:41:05.000 100
> 2005-02-16 05:41:06.000 100
> 2005-02-16 05:41:07.000 100
> 2005-02-16 05:41:08.000 100
> 2005-02-16 05:41:09.000 100
> 2005-02-16 05:41:10.000 100
> 2005-02-16 05:41:11.000 100
> 2005-02-16 05:41:12.000 100
> 2005-02-16 05:41:13.000 100
> 2005-02-16 05:41:14.000 100
> so the result of the above with an interval of 5 seconds would be
> Time Amount
> 2005-02-16 05:41:04.000 500
> 2005-02-16 05:41:09.000 500
> 2005-02-16 05:41:14.000 500
>
> any ideas?
> Thanks
>
>|||can you explan this part please?
-datepart(ss,time)%5
"sergiu" <sergiu@.discussions.microsoft.com> wrote in message
news:C3A9AA65-1277-4AEF-A517-60E4E03CED9B@.microsoft.com...
> sorry i made a mistake the script should be
> select max(time),sum(amount) from @.t group by
> dateadd(ss,-datepart(ss,time)%5,time)
> the problem with the response of alejandro mesa is that if you have the
> same
> time in different days the two rows will be grouped together
>
> "Fab" wrote:
>|||your assumption is wrong is my skip a second or two...
any ideas?
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23uL11yVMFHA.568@.TK2MSFTNGP09.phx.gbl...
> This was responded yesterday ( assumption is that there exists one row for
> every monotonically increasing second ):
> [url]http://groups.google.ca/groups?selm=%238%23HOtMMFHA.3832%40TK2MSFTNGP12.phx.gbl[
/url]
> --
> Anith
>|||On Fri, 25 Mar 2005 14:18:16 -0500, Fab wrote:

>your assumption is wrong is my skip a second or two...
>any ideas?
Hi Fab,
So why didn't you indicate that the assumption was wrong in the original
thread? Half an hour ago, I saw the original thread with only Anith's
answer; I took the time to try a solution, write a message and send it.
And now, I find that you reposted the question in a new thread and
already got some replies.
If you had posted a follow-up to your original question instead of
starting a new thread, then I'd have seen the answers and moved on the
the next question, instead of wasting my time and cluttering the group
with yet another answer that isn't really any different from Alejandro's
suggestion.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||so now that you know your assumption was wrong are you still willing to help
me with my issue?
I need to group based on 5 seconds intervals...the result of the table will
roll up based on time not on the values in the table...so the results
should start at second 00 and end at second 04...anything that falls in
that 1st group will be rolled up...and so on for each interal all the way up
to 60.
let me know if you have any questions b4 you provide a solution.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:g45941liog7iufggqi8eqvp8mngammggir@.
4ax.com...
> On Fri, 25 Mar 2005 14:18:16 -0500, Fab wrote:
>
>
> Hi Fab,
> So why didn't you indicate that the assumption was wrong in the original
> thread? Half an hour ago, I saw the original thread with only Anith's
> answer; I took the time to try a solution, write a message and send it.
> And now, I find that you reposted the question in a new thread and
> already got some replies.
> If you had posted a follow-up to your original question instead of
> starting a new thread, then I'd have seen the answers and moved on the
> the next question, instead of wasting my time and cluttering the group
> with yet another answer that isn't really any different from Alejandro's
> suggestion.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Friday, March 30, 2012

Grouping with Reporting services

Grouping with Page Break

Grouping with Page Break

Grouping Views by Dates

Grouping Views by Dates

Grouping Views by Dates

Grouping several items in one group

Grouping Row Border

Grouping reports

Grouping record based on a condtion

Grouping question

Grouping question

Grouping Question

Grouping problems

Grouping in Table

Wednesday, March 28, 2012

Grouping problem

Grouping Problem

Grouping output

Grouping on time

Firehose mode

Blog Archive

About Me