Firehose mode: 2012

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 with non-existent data

I need to build a report that groups data by a given time frame increment (15
min, 30 min, 1 hour). But I need the report to show every increment weather
there is data in my result set or not. So if I was to run my query for the
time frame of 12pm - 1pm and got the following data:
12:00, 1, 1
12:10, 1, 1
12:20, 1, 1
12:50 1, 1
And was asked to display my report using the 15 minute increment I should
get this back:
12:00 - 2 records
12:15 - 1 record
12:30 - 0 record
12:45 - 1 records
Now my grouping expression works to group the data (Hours are limited in the
query):
= ((DATEPART("h", Fields!EntryDate.Value))
* (60 / Parameters!Granularity.Value)
+ (FLOOR(DATEPART("n", Fields!EntryDate.Value) /
Parameters!Granularity.Value)))
However if there isn't any data in one of the incremental groups that group
isn't displayed. How can I make the grouping work so that even if there is
no data for my group I still get a row?
Let me know if this doesn't make any sence and I'll try to clarify it.I have to do this and it is not simple. You need to have the data there to
be able to show it. This means you need to have a stored procedure that
fills in the missing rows (or more likely, puts in all the rows and then
joins it with the query so you have all the times shown).
RS can't group on something that isn't there.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"eoghain" <eoghain@.discussions.microsoft.com> wrote in message
news:E5317CC5-E951-43D0-A784-FE9840B80D8B@.microsoft.com...
> I need to build a report that groups data by a given time frame increment
(15
> min, 30 min, 1 hour). But I need the report to show every increment
weather
> there is data in my result set or not. So if I was to run my query for
the
> time frame of 12pm - 1pm and got the following data:
> 12:00, 1, 1
> 12:10, 1, 1
> 12:20, 1, 1
> 12:50 1, 1
> And was asked to display my report using the 15 minute increment I should
> get this back:
> 12:00 - 2 records
> 12:15 - 1 record
> 12:30 - 0 record
> 12:45 - 1 records
> Now my grouping expression works to group the data (Hours are limited in
the
> query):
> = ((DATEPART("h", Fields!EntryDate.Value))
> * (60 / Parameters!Granularity.Value)
> + (FLOOR(DATEPART("n", Fields!EntryDate.Value) /
> Parameters!Granularity.Value)))
> However if there isn't any data in one of the incremental groups that
group
> isn't displayed. How can I make the grouping work so that even if there
is
> no data for my group I still get a row?
> Let me know if this doesn't make any sence and I'll try to clarify it.
>|||I was afraid of that. I guess I'll have to figure out how to build a
query/stored procedure that will return me the proper time sets given a
start/end and increment.
Thanks for your help.
"Bruce L-C [MVP]" wrote:
> I have to do this and it is not simple. You need to have the data there to
> be able to show it. This means you need to have a stored procedure that
> fills in the missing rows (or more likely, puts in all the rows and then
> joins it with the query so you have all the times shown).
> RS can't group on something that isn't there.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "eoghain" <eoghain@.discussions.microsoft.com> wrote in message
> news:E5317CC5-E951-43D0-A784-FE9840B80D8B@.microsoft.com...
> > I need to build a report that groups data by a given time frame increment
> (15
> > min, 30 min, 1 hour). But I need the report to show every increment
> weather
> > there is data in my result set or not. So if I was to run my query for
> the
> > time frame of 12pm - 1pm and got the following data:
> >
> > 12:00, 1, 1
> > 12:10, 1, 1
> > 12:20, 1, 1
> > 12:50 1, 1
> >
> > And was asked to display my report using the 15 minute increment I should
> > get this back:
> >
> > 12:00 - 2 records
> > 12:15 - 1 record
> > 12:30 - 0 record
> > 12:45 - 1 records
> >
> > Now my grouping expression works to group the data (Hours are limited in
> the
> > query):
> > = ((DATEPART("h", Fields!EntryDate.Value))
> > * (60 / Parameters!Granularity.Value)
> > + (FLOOR(DATEPART("n", Fields!EntryDate.Value) /
> > Parameters!Granularity.Value)))
> >
> > However if there isn't any data in one of the incremental groups that
> group
> > isn't displayed. How can I make the grouping work so that even if there
> is
> > no data for my group I still get a row?
> >
> > Let me know if this doesn't make any sence and I'll try to clarify it.
> >
>
>|||"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
news:%23uBQ2EqRFHA.3296@.TK2MSFTNGP15.phx.gbl...
>I have to do this and it is not simple. You need to have the data there to
> be able to show it. This means you need to have a stored procedure that
> fills in the missing rows (or more likely, puts in all the rows and then
> joins it with the query so you have all the times shown).
> RS can't group on something that isn't there.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "eoghain" <eoghain@.discussions.microsoft.com> wrote in message
> news:E5317CC5-E951-43D0-A784-FE9840B80D8B@.microsoft.com...
>> I need to build a report that groups data by a given time frame increment
> (15
>> min, 30 min, 1 hour). But I need the report to show every increment
> weather
>> there is data in my result set or not. So if I was to run my query for
> the
>> time frame of 12pm - 1pm and got the following data:
>> 12:00, 1, 1
>> 12:10, 1, 1
>> 12:20, 1, 1
>> 12:50 1, 1
>> And was asked to display my report using the 15 minute increment I should
>> get this back:
>> 12:00 - 2 records
>> 12:15 - 1 record
>> 12:30 - 0 record
>> 12:45 - 1 records
>> Now my grouping expression works to group the data (Hours are limited in
> the
>> query):
>> = ((DATEPART("h", Fields!EntryDate.Value))
>> * (60 / Parameters!Granularity.Value)
>> + (FLOOR(DATEPART("n", Fields!EntryDate.Value) /
>> Parameters!Granularity.Value)))
>> However if there isn't any data in one of the incremental groups that
> group
>> isn't displayed. How can I make the grouping work so that even if there
> is
>> no data for my group I still get a row?
>> Let me know if this doesn't make any sence and I'll try to clarify it.
>|||But you can do it with a query, i have done that sometimes, if you giove me
some tim i´ll try to find out if i have it right away, just email me to give
a reminder.
Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
news:%23uBQ2EqRFHA.3296@.TK2MSFTNGP15.phx.gbl...
>I have to do this and it is not simple. You need to have the data there to
> be able to show it. This means you need to have a stored procedure that
> fills in the missing rows (or more likely, puts in all the rows and then
> joins it with the query so you have all the times shown).
> RS can't group on something that isn't there.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "eoghain" <eoghain@.discussions.microsoft.com> wrote in message
> news:E5317CC5-E951-43D0-A784-FE9840B80D8B@.microsoft.com...
>> I need to build a report that groups data by a given time frame increment
> (15
>> min, 30 min, 1 hour). But I need the report to show every increment
> weather
>> there is data in my result set or not. So if I was to run my query for
> the
>> time frame of 12pm - 1pm and got the following data:
>> 12:00, 1, 1
>> 12:10, 1, 1
>> 12:20, 1, 1
>> 12:50 1, 1
>> And was asked to display my report using the 15 minute increment I should
>> get this back:
>> 12:00 - 2 records
>> 12:15 - 1 record
>> 12:30 - 0 record
>> 12:45 - 1 records
>> Now my grouping expression works to group the data (Hours are limited in
> the
>> query):
>> = ((DATEPART("h", Fields!EntryDate.Value))
>> * (60 / Parameters!Granularity.Value)
>> + (FLOOR(DATEPART("n", Fields!EntryDate.Value) /
>> Parameters!Granularity.Value)))
>> However if there isn't any data in one of the incremental groups that
> group
>> isn't displayed. How can I make the grouping work so that even if there
> is
>> no data for my group I still get a row?
>> Let me know if this doesn't make any sence and I'll try to clarify it.
>

Grouping with a full join

Hi,
I would like to know how to group the Amount of both tables while maintaing
all Ids.
-- Correct results for Table1
select
Table1.id
,sum(Table1.Amount) as AmountTable1
from Table1
group by Table1.id
order by 1
-- Correct results for Table2
select
Table2.id
,sum(Table2.Amount) as AmountTable2
from Table2
group by Table2.id
order by 1
-- How do I combine both results?
select
Table1.id
,sum(Table1.Amount) + sum(Table2.Amount) as AmountBoth
from Table1
left join Table2 on Table1.id = Table2.id
group by Table1.id
order by 1
/*
create table Table1 (Id int, Amount int)
create table Table2 (Id int, Amount int)
insert Table1 select 1, 100
insert Table1 select 2, 200
insert Table1 select 3, 300
insert Table1 select 4, 400
insert Table2 select 5, 500
insert Table2 select 2, 100
insert Table2 select 4, 400
insert Table2 select 6, 600
--drop table Table1
--drop table Table2
*/
---
select
coalesce(Table1.id,Table2.id) as id
,sum(coalesce(Table1.Amount,0)) + sum(coalesce(Table2.Amount,0)) as
AmountBoth
from Table1
full outer join Table2 on Table1.id = Table2.id
group by coalesce(Table1.id,Table2.id)
order by 1|||Great, thank you!
<markc600@.hotmail.com> wrote in message
news:1146119352.959456.161230@.t31g2000cwb.googlegroups.com...
>
> select
> coalesce(Table1.id,Table2.id) as id
> ,sum(coalesce(Table1.Amount,0)) + sum(coalesce(Table2.Amount,0)) as
> AmountBoth
> from Table1
> full outer join Table2 on Table1.id = Table2.id
> group by coalesce(Table1.id,Table2.id)
> order by 1
>|||You should be aware that this solution works when there is a one to
one relation ship between the two tables, but not if there is a one to
many (or many to many) relationship.
Here are two alternatives that avoid that problem.
SELECT id, sum(Amount) as Amount
FROM (select id, sum(Amount) as Amount
from Table1
group by id
UNION ALL
select id, sum(Amount) as Amount
from Table1
group by id) as Combo
GROUP BY id
ORDER BY 1
SELECT COALESCE(T1.id,T2.id),
T1.Amount + T2.Amount as Amount
FROM (select id, sum(Amount) as Amount
from Table1
group by id) as T1
FULL OUTER
JOIN (select id, sum(Amount) as Amount
from Table1
group by id) as T2
ON T1.id = T2.id
ORDER BY 1
Roy Harvey
Beacon Falls, CT
On Thu, 27 Apr 2006 09:53:11 +0300, "Yan" <yanive@.rediffmail.com>
wrote:

>Great, thank you!
>
><markc600@.hotmail.com> wrote in message
>news:1146119352.959456.161230@.t31g2000cwb.googlegroups.com...
>

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 using two stored procedures

Hi,

I am creating a set of reports in Crystal showing emails sent and delivered from users within our organisation.

Each report uses a different stored procedure.

Report 1 shows emails sent:

Sender: Person in our org
Recipient: Person outside our org
Number: number of emails sent

Report 2 shows emails recived:

Sender: person outside our org
Recipient: Person in our org
Number: Number of emails received

In crystal, the reports are grouped around the sender for report 1, and the recipient for group 2 (therefore the reports are grouped around the person in our organisation).

Now I need to create a report showing the details of the two reports combined into one, but this creates a problem when i try to group. i need to distinguish between who is in our organisation and who is it, and then to group by them.

does anyone have any idea how this may be done?

i want the report to look like this:

Person in our organisation:
----------
Sent: bob@.yahoo.co.uk 26
sally@.hotmail.com 4
peter@.msn.com 12
Subtotal: 42

Received: fred@.company.co.uk 45
vicky@.hotmail.com 10
Subtotal 55

Total 97

and so on for each person.

Many thanks if you can helpEither create it as two subreports in Crystal, or use a UNION query to create a single dataset from both SQL statements. With the UNION query, you will probably want to add a dummy values that indicates "SENT" or "RECEIVED".|||With the UNION query, you will probably want to add a dummy values that indicates "SENT" or "RECEIVED".

how do i do this?

at the moment, the most i can come up with is

select * from vw_sent
union
select * from vw_received

and that's where my question comes from really, how to determine what addresses in each result are @.mydomain.co.uk and then to group by those. Becuase the resultset of this query is:

Sender Recipient Number
person@.mydomain.co.uk person@.hotmail.com 5
otherperson@.mydomain.co.uk person@.hotmail.com 2
otherperson@.hotmail.com person@.mydomain.co.uk 10

so how do i add an extra field in my resultset to show which email address is in my domain?|||select 'SENT' as Direction, * from vw_sent
union
select 'RECEIVED' as Direction, * from vw_received

...though you really should enumerate your field names instead of using *, especially in an UNION query.

Grouping two similar column names but different data?

Hi All,
I have a need to group a column with he same name.
I have a column called "AccountType" which has data such as :
A1
A2
A3
A4
I am using an aggrate for this column:
SELECT
SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
FROM Tbl1
GROUP BY AccountType
I want to also group by the actual group type. Something like:
SELECT
AccountType,
SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
FROM Tbl1
GROUP BY AccountType, AccountType
Can someone please give me a little help with this?
Thanks very much,
John.John,
Can you post an example of the expected result?
AMB
"John" wrote:

> Hi All,
> I have a need to group a column with he same name.
> I have a column called "AccountType" which has data such as :
> A1
> A2
> A3
> A4
> I am using an aggrate for this column:
> SELECT
> SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
> FROM Tbl1
> GROUP BY AccountType
> I want to also group by the actual group type. Something like:
> SELECT
> AccountType,
> SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
> FROM Tbl1
> GROUP BY AccountType, AccountType
> Can someone please give me a little help with this?
> Thanks very much,
> John.
>
>|||John:
without knowing exactly what you want, its difficult to answer.
Is this what you want:
select t.[account type], count(*)
from (
SELECT
SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
FROM Tbl1
GROUP BY AccountType
) t
group by t.[Account Type]
If not then try posting some sample data set and the required output and i
am sure someone will be able to help you on that.
just incase if you wanna play around and understand what the above code is
doing then use northwind and execute this query
use northwind
go
select t.lessOrMore, count(*) , sum(t.OrderCount)
from (
select orderID, count(*) as OrderCount
, case when orderID < '11000' then 'less' else 'more' end as "LessOrMore"
from [Order Details]
group by OrderID ) t
group by t.LessOrMore
Hope the above helps
Abhishek
"John" wrote:

> Hi All,
> I have a need to group a column with he same name.
> I have a column called "AccountType" which has data such as :
> A1
> A2
> A3
> A4
> I am using an aggrate for this column:
> SELECT
> SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
> FROM Tbl1
> GROUP BY AccountType
> I want to also group by the actual group type. Something like:
> SELECT
> AccountType,
> SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account Type]
> FROM Tbl1
> GROUP BY AccountType, AccountType
> Can someone please give me a little help with this?
> Thanks very much,
> John.
>
>|||My current data result is something like this:
LastName | Account Type | NumCount
Miller | Good | 20
Miller | Not Good | 5
Jones | Not Good | 37
Miller | Not Good | 9
What I would like to see is the following:
LastName | Account Type Actual Type | NumCount
Miller | Good | A1 |
20
Miller | Not Good | A2 | 5
Jones | Not Good | A3 | 37
Miller | Not Good | A4 |
9
In the first example I am grouping by LastName, [Account Type]
In the second example I need to Group by the same and addition to the Actual
Account Type.
The problem here though is that the column "AccountType" needs to be used
twice and I don't know how to handle this. Unfortunately I can not use a
unique alias for each one that can be Grouped.
John.
"Abhishek Pandey" <AbhishekPandey@.discussions.microsoft.com> wrote in
message news:FFCAE864-F9B5-426E-B0FA-8CE9B95B489D@.microsoft.com...
> John:
> without knowing exactly what you want, its difficult to answer.
> Is this what you want:
> select t.[account type], count(*)
> from (
> SELECT
> SUM (CASE WHEN AccountType = 'A1' Then 'Good' END) AS [Account
> Type]
> FROM Tbl1
> GROUP BY AccountType
> ) t
> group by t.[Account Type]
>
> If not then try posting some sample data set and the required output and i
> am sure someone will be able to help you on that.
> just incase if you wanna play around and understand what the above code is
> doing then use northwind and execute this query
> use northwind
> go
> select t.lessOrMore, count(*) , sum(t.OrderCount)
> from (
> select orderID, count(*) as OrderCount
> , case when orderID < '11000' then 'less' else 'more' end as "LessOrMore"
> from [Order Details]
> group by OrderID ) t
> group by t.LessOrMore
>
> Hope the above helps
> Abhishek
> "John" wrote:
>|||John:
It seems you dont need a second groupby.. coz you are not doing another
group by. It seems you just need and extra column. This is what is reflected
in the result set you posted (NumCount remains the same and you just need an
extra column for actual account type)
But then again you will need to be more clear in what exacly you want
is this what you want:
LastName | Account Type | Actual Type | NumCount
Miller | Good | A1 | 20
Miller | Not Good | A2 | 3
Miller | Not Good | A3 | 2
Jones | Not Good | A3 | 30
Jones | Not Good | A4 | 7
Miller | Not Good | A4 | 9
Notice that for miller not good account i have further divided into 2 actual
account type and the sum of count 3+2 = 5.
similarly for Jones it is 30+7 = 37.
If above is what you want then you can simply code it like this
SELECT Lastname
, (CASE
WHEN AccountType = 'A1'
Then 'Good'
ELSE 'Not Good'
END) AS [Account Type]
, [Account type] AS [Actual type]
, count(*) as [NumCount]
FROM Tbl1
GROUP BY LastName, AccountType
Hope the above helps. Do let me know if this is what you were looking for.
Abhishek
"John" wrote:

> My current data result is something like this:
> LastName | Account Type | NumCount
> Miller | Good | 20
> Miller | Not Good | 5
> Jones | Not Good | 37
> Miller | Not Good | 9
> What I would like to see is the following:
> LastName | Account Type Actual Type | NumCount
> Miller | Good | A1 |
> 20
> Miller | Not Good | A2 |
5
> Jones | Not Good | A3 |
37
> Miller | Not Good | A4 |
> 9
> In the first example I am grouping by LastName, [Account Type]
> In the second example I need to Group by the same and addition to the Actu
al
> Account Type.
> The problem here though is that the column "AccountType" needs to be used
> twice and I don't know how to handle this. Unfortunately I can not use a
> unique alias for each one that can be Grouped.
> John.
> "Abhishek Pandey" <AbhishekPandey@.discussions.microsoft.com> wrote in
> message news:FFCAE864-F9B5-426E-B0FA-8CE9B95B489D@.microsoft.com...
>
>

Grouping Sorting String and Numerical Fields

I've got a report built and I'm trying to figure out how sorting and grouping works. I can group the report by Patient, Albumin and it groups as I would expect.

Patient Date Albumin
Adams, John 01/28/2007 4.1
Adams, John 12/30/2007 3.9
Adams, John 01/15/2007 3.2
Barker, Mark 01/18/2007 4.3
Barker, Mark 01/22/2007 4.1
Barker, Mark 01/05/2007 3.9

However, when I try to group by Albumin, Patient, it just sorts by Albumin.
Patient Date Albumin
Barker, Mark 01/18/2007 4.3
Adams, John 01/28/2007 4.1
Barker, Mark 01/22/2007 4.1
Adams, John 12/30/2007 3.9
Barker, Mark 01/05/2007 3.9
Adams, John 01/15/2007 3.2

What I'm looking for is this:
Patient Date Albumin
Barker, Mark 01/18/2007 4.3
Barker, Mark 01/22/2007 4.1
Barker, Mark 01/05/2007 3.9
Adams, John 01/28/2007 4.1
Adams, John 12/30/2007 3.9
Adams, John 01/15/2007 3.2

Is this something that can be done with grouping and sorting?

Thanks,
Chad

Hi,

guess that you did not want to group you wanted to just sort, right ? For getting the results pasted below you will have to Sort by Patient Desc, Albumin Desc. If that is not your intention, please post the RDL and the way you want to display the information.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Grouping SMALL PIE CHART Slices -> Possible ?

Hi,
I have a problem with small pie chart slices. As you know in a pie
chart, very small slices are unreadable.
So I'am looking for a maner to GROUP little values of a graph in one
categorie (slices) called "OTHER". For example all value under 1% will
be grouped in the "OTHER" category
Is someone have an idea in how to do this with Reporting Services ?
THANKSI hope someone will help me coz i'am really desapointed with this
problem :-(|||I think the best way to handle your issue is in the query that provides the
data for your chart. I dont know what database you are using but you can
characterize all your values under 1% as the field "other" in so it comes
from your query that way.
"rebeuapaname@.hotmail.com" wrote:
> Hi,
> I have a problem with small pie chart slices. As you know in a pie
> chart, very small slices are unreadable.
> So I'am looking for a maner to GROUP little values of a graph in one
> categorie (slices) called "OTHER". For example all value under 1% will
> be grouped in the "OTHER" category
>
> Is someone have an idea in how to do this with Reporting Services ?
>
> THANKS
>|||Thank's you
I have already thinked to this way but i was looking for a solution
based on the reporting services features.
If it's not available, I think it's may be interesting to add it on the
futur SP or in the 2005 release :-)sql

grouping similar data

In layout view: how do I group all the company names together who have the same value for a certain field? For instance, 10 companies all have the same booth size, but in Preview mode, it lists each company seprately with the booth size on each line. I need to have the booth size listed once with all the companies who share that common size. I have tried adding a group and using the expression for the company name as well as the booth size and it does not work. Can someone provide some detailed instructions?
Thank you,
Bil
From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comIf I am understanding correctly you want to have the booth size and then
show all companies under that that match that description. Have you tried
grouping it by the booth size and then put your companies in the detail?
"Bkana" <nospam@.developmentnow.com> wrote in message
news:97f68ac4-a6e2-429d-a4e5-3b98fed63020@.developmentnow.com...
> In layout view: how do I group all the company names together who have the
> same value for a certain field? For instance, 10 companies all have the
> same booth size, but in Preview mode, it lists each company seprately with
> the booth size on each line. I need to have the booth size listed once
> with all the companies who share that common size. I have tried adding a
> group and using the expression for the company name as well as the booth
> size and it does not work. Can someone provide some detailed instructions?
> Thank you,
> Bill
> From
> http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com

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 SETS in What's New (Database Engine)

Just as a usability issue, most of the topics mentioned in the "What's New" documents have a link to further information. The GROUPING SETS item under "What's New in CTP 1" at /s10de_0evalplan/html/8f625d5a-763c-4440-97b8-4b823a6e2439.htm should contain a link to the GROUPING SETS topic. Of course, the GROUPING SETS entry in the index currently points to /s10de_6tsql/html/c1050658-b19f-42ee-9a05-ecd6a73b896c.htm which is actually the topic GROUPING_ID (Transact-SQL).

I realize that "What's New in CTP 1" will go away but I assume these groups of topics will be combined into a grander "What's New in SQL Server 2008" section distributed with RTM Books Online.

Hi Aaron,

Thanks for the good suggestions.

Regards,

Gail

GROUPING SETS in What's New (Database Engine)

I realize that "What's New in CTP 1" will go away but I assume these groups of topics will be combined into a grander "What's New in SQL Server 2008" section distributed with RTM Books Online.

Hi Aaron,

Thanks for the good suggestions.

Regards,

Gail

Grouping Select Statements with where clause

Hello

What I need to do is be able to group the results of my select statements in different columns. And end having the result work like this.

campaign Col1 Col2

<<Data>> <<Select counT(*) where field= value>> <<Select counT(*) where field= value>>No you don't. You just think you do. What you really want to do is create a CROSSTAB query. Look it up in Books Online.sql

grouping select query

Hi,
I have data stored as in below sample :
--+--+--
--
DateBegin | DateEnd | Rate
--+--+--
--
2005-11-13 00:00:00 2005-11-14 00:00:00 63.0000
2005-11-14 00:00:00 2005-11-15 00:00:00 63.0000
2005-11-15 00:00:00 2005-11-16 00:00:00 45.0000
2005-11-16 00:00:00 2005-11-17 00:00:00 45.0000
2005-11-17 00:00:00 2005-11-18 00:00:00 45.0000
2005-11-18 00:00:00 2005-11-19 00:00:00 45.0000
2005-11-19 00:00:00 2005-11-20 00:00:00 45.0000
2005-11-20 00:00:00 2005-11-21 00:00:00 63.0000
2005-11-21 00:00:00 2005-11-22 00:00:00 63.0000
--+--+--
--
I have to group the select query in this way :
--+--+--
--
DateBegin | DateEnd | Rate
--+--+--
--
2005-11-13 00:00:00 2005-11-15 00:00:00 63.0000
2005-11-15 00:00:00 2005-11-20 00:00:00 45.0000
2005-11-20 00:00:00 2005-11-22 00:00:00 63.0000
--+--+--
--
When I run below grouped statement, I get follewed result:
SELECT MIN(DateBegin) AS DateBegin, MAX(DateEnd) AS DateEnd,
Rate FROM X GROUP BY Rate
--+--+--
--
DateBegin | DateEnd | Rate
--+--+--
--
2005-11-13 00:00:00 2005-11-22 00:00:00 63.0000
2005-11-15 00:00:00 2005-11-20 00:00:00 45.0000
--+--+--
--
How can I do a query like in 2nd sample from top?
best regards,
rustam bogubaevThis is a periodicity problem, not a SQL syntax problem.
You have to define how the period is to be divided first. In essence,
however you decide to calculate the period, the data would logically contain
the following information.
--+--+--
--
DateBegin | DateEnd | Rate |
Period
--+--+--
--
2005-11-13 00:00:00 2005-11-14 00:00:00 63.0000 1
2005-11-14 00:00:00 2005-11-15 00:00:00 63.0000 1
2005-11-15 00:00:00 2005-11-16 00:00:00 45.0000 2
2005-11-16 00:00:00 2005-11-17 00:00:00 45.0000 2
2005-11-17 00:00:00 2005-11-18 00:00:00 45.0000 2
2005-11-18 00:00:00 2005-11-19 00:00:00 45.0000 2
2005-11-19 00:00:00 2005-11-20 00:00:00 45.0000 2
2005-11-20 00:00:00 2005-11-21 00:00:00 63.0000 3
2005-11-21 00:00:00 2005-11-22 00:00:00 63.0000 3
--+--+--
--
With the periods defined, however that is done, your problem will be easy.
Perhaps something like the following would help find the boundarys of the
periods.
SELECT b.DateBegin
FROM MyTable a JOIN MyTable b
ON a.DateEnd = b.DateBegin
WHERE a.Rate != b.Rate
RLF
<rustam.bogubaev@.gmail.com> wrote in message
news:1131461007.812709.108200@.g49g2000cwa.googlegroups.com...
> Hi,
> I have data stored as in below sample :
> --+--+--
--
> DateBegin | DateEnd | Rate
> --+--+--
--
> 2005-11-13 00:00:00 2005-11-14 00:00:00 63.0000
> 2005-11-14 00:00:00 2005-11-15 00:00:00 63.0000
> 2005-11-15 00:00:00 2005-11-16 00:00:00 45.0000
> 2005-11-16 00:00:00 2005-11-17 00:00:00 45.0000
> 2005-11-17 00:00:00 2005-11-18 00:00:00 45.0000
> 2005-11-18 00:00:00 2005-11-19 00:00:00 45.0000
> 2005-11-19 00:00:00 2005-11-20 00:00:00 45.0000
> 2005-11-20 00:00:00 2005-11-21 00:00:00 63.0000
> 2005-11-21 00:00:00 2005-11-22 00:00:00 63.0000
> --+--+--
--
>
> I have to group the select query in this way :
> --+--+--
--
> DateBegin | DateEnd | Rate
> --+--+--
--
> 2005-11-13 00:00:00 2005-11-15 00:00:00 63.0000
> 2005-11-15 00:00:00 2005-11-20 00:00:00 45.0000
> 2005-11-20 00:00:00 2005-11-22 00:00:00 63.0000
> --+--+--
--
> When I run below grouped statement, I get follewed result:
> SELECT MIN(DateBegin) AS DateBegin, MAX(DateEnd) AS DateEnd,
> Rate FROM X GROUP BY Rate
> --+--+--
--
> DateBegin | DateEnd | Rate
> --+--+--
--
> 2005-11-13 00:00:00 2005-11-22 00:00:00 63.0000
> 2005-11-15 00:00:00 2005-11-20 00:00:00 45.0000
> --+--+--
--
> How can I do a query like in 2nd sample from top?
> best regards,
> rustam bogubaev
>|||On 8 Nov 2005 06:43:27 -0800, rustam.bogubaev@.gmail.com wrote:
(snip)
>I have to group the select query in this way :
>--+--+--
--
> DateBegin | DateEnd | Rate
>--+--+--
--
>2005-11-13 00:00:00 2005-11-15 00:00:00 63.0000
>2005-11-15 00:00:00 2005-11-20 00:00:00 45.0000
>2005-11-20 00:00:00 2005-11-22 00:00:00 63.0000
>--+--+--[/c
olor]
Hi rustam,
If my assumptions about your table and the reasons for your expected
results are correct, then try:
SELECT a.DateBegin, MAX(b.DateEnd), a.Rate
FROM X AS a
INNER JOIN X as b
ON b.Rate = a.Rate
AND b.DateBegin >= a.DateStart
WHERE NOT EXISTS
(SELECT *
FROM X AS c
WHERE c.DateBegin = DATEADD(day, -1, a.DateBegin)
AND c.Rate = a.Rate)
AND NOT EXISTS
(SELECT *
FROM X AS d
WHERE d.DateBegin > a.DateEnd
AND d.DateEnd < b.DateBegin
AND d.Rate <> a.Rate)
GROUP BY a.DateBegin, a.Rate
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

grouping select query

Hi,

I have data stored as in below sample :

----------+----------+-----
DateBegin | DateEnd | Rate
----------+----------+-----
2005-11-13 00:00:002005-11-14 00:00:0063.0000
2005-11-14 00:00:002005-11-15 00:00:0063.0000
2005-11-15 00:00:002005-11-16 00:00:0045.0000
2005-11-16 00:00:002005-11-17 00:00:0045.0000
2005-11-17 00:00:002005-11-18 00:00:0045.0000
2005-11-18 00:00:002005-11-19 00:00:0045.0000
2005-11-19 00:00:002005-11-20 00:00:0045.0000
2005-11-20 00:00:002005-11-21 00:00:0063.0000
2005-11-21 00:00:002005-11-22 00:00:0063.0000
----------+----------+-----

I have to group the select query in this way :

----------+----------+-----
DateBegin | DateEnd | Rate
----------+----------+-----
2005-11-13 00:00:002005-11-15 00:00:0063.0000
2005-11-15 00:00:002005-11-20 00:00:0045.0000
2005-11-20 00:00:002005-11-22 00:00:0063.0000
----------+----------+-----

When I run below grouped statement, I get follewed result:

SELECT MIN(DateBegin) AS DateBegin, MAX(DateEnd) AS DateEnd,
Rate FROM X GROUP BY Rate

----------+----------+-----
DateBegin | DateEnd | Rate
----------+----------+-----
2005-11-13 00:00:002005-11-22 00:00:0063.0000
2005-11-15 00:00:002005-11-20 00:00:0045.0000
----------+----------+-----

How can I do a query like in 2nd sample from top?

best regards,
rustam bogubaevPYCTAM wrote:
> Hi,
> I have data stored as in below sample :
> ----------+----------+--
---
> DateBegin | DateEnd | Rate
> ----------+----------+--
---
> 2005-11-13 00:00:00 2005-11-14 00:00:00 63.0000
> 2005-11-14 00:00:00 2005-11-15 00:00:00 63.0000
> 2005-11-15 00:00:00 2005-11-16 00:00:00 45.0000
> 2005-11-16 00:00:00 2005-11-17 00:00:00 45.0000
> 2005-11-17 00:00:00 2005-11-18 00:00:00 45.0000
> 2005-11-18 00:00:00 2005-11-19 00:00:00 45.0000
> 2005-11-19 00:00:00 2005-11-20 00:00:00 45.0000
> 2005-11-20 00:00:00 2005-11-21 00:00:00 63.0000
> 2005-11-21 00:00:00 2005-11-22 00:00:00 63.0000
> ----------+----------+--
---
>
> I have to group the select query in this way :
> ----------+----------+--
---
> DateBegin | DateEnd | Rate
> ----------+----------+--
---
> 2005-11-13 00:00:00 2005-11-15 00:00:00 63.0000
> 2005-11-15 00:00:00 2005-11-20 00:00:00 45.0000
> 2005-11-20 00:00:00 2005-11-22 00:00:00 63.0000
> ----------+----------+--
---
> When I run below grouped statement, I get follewed result:
> SELECT MIN(DateBegin) AS DateBegin, MAX(DateEnd) AS DateEnd,
> Rate FROM X GROUP BY Rate
> ----------+----------+--
---
> DateBegin | DateEnd | Rate
> ----------+----------+--
---
> 2005-11-13 00:00:00 2005-11-22 00:00:00 63.0000
> 2005-11-15 00:00:00 2005-11-20 00:00:00 45.0000
> ----------+----------+--
---
> How can I do a query like in 2nd sample from top?

Care to explain by what you want to group? I cannot recognize it from
your sample output.

robert|||On 8 Nov 2005 06:42:33 -0800, PYCTAM wrote:

(snip)

Hi rustam,

You posted an exact identical copy of this question in the group
microsoft.public.sqlserver.programming, and I posted a reply there.

Please do not post the same question independently to multiple groups.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

grouping rows by customer

my source flat file has many rows per customer,
but I need to transfer it to database with only one row per customer and accumulated sales (and probably do other calculations and lookups).
I understand how to do stuff with derived columns, but how can I read source file first, calculate, group and then save to database?
As I understand, the script offers only processing row by row: Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Thanks

Vlad

won't A Flat file source and aggregation transform suffice your needs?

Rafael Salas

|||

I tried this, but I do not think it would help :-(

it is more complicated calculation, than just grouping.

I would rather do it in the script load into a Collection, loop, calculate, replace, substitute etc. and then save to database.

|||

Well you know your data...good luck with that!

Rafael Salas

|||

The Aggregate transformation does more than Group By. You don't want to do this in script. You can do SUM, AVG, MIN, MAX with the Aggregrate Transformation. If you need to then do something else combine the Derived Column Transformation with it.

http://msdn2.microsoft.com/en-US/library/ms138031.aspx

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 Results (Summarizing)

I can't figure out how to take display my results like so...
NJ
--Trenton, John Doe
--Voorhees, Jane Smith
Pennsylvania
--Philadelphia, Ken James
--Pittsburgh, Alfred Herms
NY
--Brooklyn, Llyod Banks
--Syracuse, Howard Douglas
within a SQL statement..<vncntj@.hotmail.com> wrote in message
news:1140727759.857021.104520@.v46g2000cwv.googlegroups.com...
>I can't figure out how to take display my results like so...
>
> NJ
> --Trenton, John Doe
> --Voorhees, Jane Smith
> Pennsylvania
> --Philadelphia, Ken James
> --Pittsburgh, Alfred Herms
> NY
> --Brooklyn, Llyod Banks
> --Syracuse, Howard Douglas
> within a SQL statement..
>
Since you haven't told us what the table structure is, what your original
data looks like or what version of SQL Server you are are using I don't
think I can figure it out either. Read my signature.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||CREATE TABLE dbo.Table1
(
state nvarchar(50) NULL,
city nvarchar(50) NULL,
name nvarchar(50) NULL
)
INSERT Into table1 (state, city, name) values ('NJ', 'VOORHEES', 'John
Doe')
INSERT Into table1 (state, city, name) values ('NJ', 'TRENTON', 'John
Smith')
INSERT Into table1 (state, city, name) values ('PENNSLYVANIA',
'PHILADELPHIA', 'Ken Jame')
INSERT Into table1 (state, city, name) values ('PENNSLYVANIA',
'PITTSBURGH', 'Alfred Herms')
INSERT Into table1 (state, city, name) values ('NEW YORK', 'BROOKLYN',
'Lloyd Banks')
INSERT Into table1 (state, city, name) values ('NEW YORK', 'SYRACUSE',
'Howard Douglas')
Is there a way to present the data as so...
NJ
-Trenton
-Voorhees
Pennsylvania
-Philadelphia
-Pittsburgh
NY
-Brooklyn
-Syracuse|||vncntj@.hotmail.com wrote:
> CREATE TABLE dbo.Table1
> (
> state nvarchar(50) NULL,
> city nvarchar(50) NULL,
> name nvarchar(50) NULL
> )
> INSERT Into table1 (state, city, name) values ('NJ', 'VOORHEES', 'John
> Doe')
> INSERT Into table1 (state, city, name) values ('NJ', 'TRENTON', 'John
> Smith')
> INSERT Into table1 (state, city, name) values ('PENNSLYVANIA',
> 'PHILADELPHIA', 'Ken Jame')
> INSERT Into table1 (state, city, name) values ('PENNSLYVANIA',
> 'PITTSBURGH', 'Alfred Herms')
> INSERT Into table1 (state, city, name) values ('NEW YORK', 'BROOKLYN',
> 'Lloyd Banks')
> INSERT Into table1 (state, city, name) values ('NEW YORK', 'SYRACUSE',
> 'Howard Douglas')
> Is there a way to present the data as so...
> NJ
> -Trenton
> -Voorhees
> Pennsylvania
> -Philadelphia
> -Pittsburgh
> NY
> -Brooklyn
> -Syracuse
Given your original table any reporting tool will output data with
formatted bands like that. So unless you want to print or display
direct from your query tool it seems like it would be very inconvenient
to do all that formatting in a result set. SQL isn't a report writing
tool.
If you really have no other option then you could do something like
this:
SELECT s
FROM
(SELECT state, 1, state
FROM Table1
UNION
SELECT state, 2, '-- '+city
FROM Table1) AS T(t,o,s)
ORDER BY t,o ;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--sql

Grouping Results (Summarizing)

I can't figure out how to take display my results like so...
NJ
--Trenton, John Doe
--Voorhees, Jane Smith
Pennsylvania
--Philadelphia, Ken James
--Pittsburgh, Alfred Herms
NY
--Brooklyn, Llyod Banks
--Syracuse, Howard Douglas
within a SQL statement..
<vncntj@.hotmail.com> wrote in message
news:1140727759.857021.104520@.v46g2000cwv.googlegr oups.com...
>I can't figure out how to take display my results like so...
>
> NJ
> --Trenton, John Doe
> --Voorhees, Jane Smith
> Pennsylvania
> --Philadelphia, Ken James
> --Pittsburgh, Alfred Herms
> NY
> --Brooklyn, Llyod Banks
> --Syracuse, Howard Douglas
> within a SQL statement..
>
Since you haven't told us what the table structure is, what your original
data looks like or what version of SQL Server you are are using I don't
think I can figure it out either. Read my signature.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||CREATE TABLE dbo.Table1
(
state nvarchar(50) NULL,
city nvarchar(50) NULL,
name nvarchar(50) NULL
)
INSERT Into table1 (state, city, name) values ('NJ', 'VOORHEES', 'John
Doe')
INSERT Into table1 (state, city, name) values ('NJ', 'TRENTON', 'John
Smith')
INSERT Into table1 (state, city, name) values ('PENNSLYVANIA',
'PHILADELPHIA', 'Ken Jame')
INSERT Into table1 (state, city, name) values ('PENNSLYVANIA',
'PITTSBURGH', 'Alfred Herms')
INSERT Into table1 (state, city, name) values ('NEW YORK', 'BROOKLYN',
'Lloyd Banks')
INSERT Into table1 (state, city, name) values ('NEW YORK', 'SYRACUSE',
'Howard Douglas')
Is there a way to present the data as so...
NJ
-Trenton
-Voorhees
Pennsylvania
-Philadelphia
-Pittsburgh
NY
-Brooklyn
-Syracuse
|||vncntj@.hotmail.com wrote:
> CREATE TABLE dbo.Table1
> (
> state nvarchar(50) NULL,
> city nvarchar(50) NULL,
> name nvarchar(50) NULL
> )
> INSERT Into table1 (state, city, name) values ('NJ', 'VOORHEES', 'John
> Doe')
> INSERT Into table1 (state, city, name) values ('NJ', 'TRENTON', 'John
> Smith')
> INSERT Into table1 (state, city, name) values ('PENNSLYVANIA',
> 'PHILADELPHIA', 'Ken Jame')
> INSERT Into table1 (state, city, name) values ('PENNSLYVANIA',
> 'PITTSBURGH', 'Alfred Herms')
> INSERT Into table1 (state, city, name) values ('NEW YORK', 'BROOKLYN',
> 'Lloyd Banks')
> INSERT Into table1 (state, city, name) values ('NEW YORK', 'SYRACUSE',
> 'Howard Douglas')
> Is there a way to present the data as so...
> NJ
> -Trenton
> -Voorhees
> Pennsylvania
> -Philadelphia
> -Pittsburgh
> NY
> -Brooklyn
> -Syracuse
Given your original table any reporting tool will output data with
formatted bands like that. So unless you want to print or display
direct from your query tool it seems like it would be very inconvenient
to do all that formatting in a result set. SQL isn't a report writing
tool.
If you really have no other option then you could do something like
this:
SELECT s
FROM
(SELECT state, 1, state
FROM Table1
UNION
SELECT state, 2, '-- '+city
FROM Table1) AS T(t,o,s)
ORDER BY t,o ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Grouping Results (Summarizing)

I can't figure out how to take display my results like so...
NJ
--Trenton, John Doe
--Voorhees, Jane Smith
Pennsylvania
--Philadelphia, Ken James
--Pittsburgh, Alfred Herms
NY
--Brooklyn, Llyod Banks
--Syracuse, Howard Douglas
within a SQL statement..<vncntj@.hotmail.com> wrote in message
news:1140727759.857021.104520@.v46g2000cwv.googlegroups.com...
>I can't figure out how to take display my results like so...
>
> NJ
> --Trenton, John Doe
> --Voorhees, Jane Smith
> Pennsylvania
> --Philadelphia, Ken James
> --Pittsburgh, Alfred Herms
> NY
> --Brooklyn, Llyod Banks
> --Syracuse, Howard Douglas
> within a SQL statement..
>
Since you haven't told us what the table structure is, what your original
data looks like or what version of SQL Server you are are using I don't
think I can figure it out either. Read my signature.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||CREATE TABLE dbo.Table1
(
state nvarchar(50) NULL,
city nvarchar(50) NULL,
name nvarchar(50) NULL
)
INSERT Into table1 (state, city, name) values ('NJ', 'VOORHEES', 'John
Doe')
INSERT Into table1 (state, city, name) values ('NJ', 'TRENTON', 'John
Smith')
INSERT Into table1 (state, city, name) values ('PENNSLYVANIA',
'PHILADELPHIA', 'Ken Jame')
INSERT Into table1 (state, city, name) values ('PENNSLYVANIA',
'PITTSBURGH', 'Alfred Herms')
INSERT Into table1 (state, city, name) values ('NEW YORK', 'BROOKLYN',
'Lloyd Banks')
INSERT Into table1 (state, city, name) values ('NEW YORK', 'SYRACUSE',
'Howard Douglas')
Is there a way to present the data as so...
NJ
-Trenton
-Voorhees
Pennsylvania
-Philadelphia
-Pittsburgh
NY
-Brooklyn
-Syracuse|||vncntj@.hotmail.com wrote:
> CREATE TABLE dbo.Table1
> (
> state nvarchar(50) NULL,
> city nvarchar(50) NULL,
> name nvarchar(50) NULL
> )
> INSERT Into table1 (state, city, name) values ('NJ', 'VOORHEES', 'John
> Doe')
> INSERT Into table1 (state, city, name) values ('NJ', 'TRENTON', 'John
> Smith')
> INSERT Into table1 (state, city, name) values ('PENNSLYVANIA',
> 'PHILADELPHIA', 'Ken Jame')
> INSERT Into table1 (state, city, name) values ('PENNSLYVANIA',
> 'PITTSBURGH', 'Alfred Herms')
> INSERT Into table1 (state, city, name) values ('NEW YORK', 'BROOKLYN',
> 'Lloyd Banks')
> INSERT Into table1 (state, city, name) values ('NEW YORK', 'SYRACUSE',
> 'Howard Douglas')
> Is there a way to present the data as so...
> NJ
> -Trenton
> -Voorhees
> Pennsylvania
> -Philadelphia
> -Pittsburgh
> NY
> -Brooklyn
> -Syracuse
Given your original table any reporting tool will output data with
formatted bands like that. So unless you want to print or display
direct from your query tool it seems like it would be very inconvenient
to do all that formatting in a result set. SQL isn't a report writing
tool.
If you really have no other option then you could do something like
this:
SELECT s
FROM
(SELECT state, 1, state
FROM Table1
UNION
SELECT state, 2, '-- '+city
FROM Table1) AS T(t,o,s)
ORDER BY t,o ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Grouping Result

Here's my query:

SELECT col_1, col_2, col_3, col_4 FROM my_table
WHERE (col_1 = @.col_1) AND (col_2 = @.col_2)
ORDER BY col_1

I want my result rows to have an uniqe value in col_3. How can I exclude rows (but one) that have the same value in col_3?

Thanx

/sf

SELECT TOP 1 col_1, col_2, col_3, col_4 FROM my_table
WHERE (col_1 = @.col_1) AND (col_2 = @.col_2)
ORDER BY col_1

|||Thank you for your answer.

But will not this query only output one row?

I'll try to explain my problem better:

Here's my query:

SELECT col_1, col_2, col_3, col_4 FROM my_table
WHERE (col_1 = @.col_1) AND (col_2 = @.col_2)
ORDER BY col_1

What I want is output all rows with unique value in col_3 and if several rows have the same value in col_3, I only want one of those rows. The following could have worked, but it's not valid sql:

SELECT col_1, col_2, col_3, col_4 FROM my_table
WHERE (col_1 = @.col_1) AND (col_2 = @.col_2)
ORDER BY col_1
GROUP BY col_3|||SELECT MAX(col_1), MAX(col_2), col_3, MAX(col_4) FROM my_table
WHERE (col_1 = @.col_1) AND (col_2 = @.col_2)
ORDER BY MAX(col_1)
GROUP BY col_3

I used MAX() for the non-grouped values, but you could use MIN(), SUM(), AVG()

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 Records & Assigning Sequential Number

I need to group records and assign a setid to the group. I have a
table with data that looks like this

ColA ColB
94015 01065
94016 01065
94015 01085
94015 01086
33383 00912
32601 00912

I need to create a resultset using just sql to look like this

ColA ColB GRP
94015 01065 1
94016 01065 1
94015 01085 1
94015 01086 1
33383 00912 2
32601 00912 2

The tricky part is resolving the many to many issue. A value in ColA
can belong to multiple values in ColB and a value in ColB can have
multiple values in ColA.Please explain the logic that determines GRP. What rule makes the first four
rows GRP=1 and the next two GRP=2 ?

--
David Portas
SQL Server MVP
--|||"cjm" <cjm136@.optonline.net> wrote in message news:62be3d63.0402120756.f08195b@.posting.google.co m...
> I need to group records and assign a setid to the group. I have a
> table with data that looks like this
> ColA ColB
> 94015 01065
> 94016 01065
> 94015 01085
> 94015 01086
> 33383 00912
> 32601 00912
> I need to create a resultset using just sql to look like this
> ColA ColB GRP
> 94015 01065 1
> 94016 01065 1
> 94015 01085 1
> 94015 01086 1
> 33383 00912 2
> 32601 00912 2
> The tricky part is resolving the many to many issue. A value in ColA
> can belong to multiple values in ColB and a value in ColB can have
> multiple values in ColA.

Not completely sure I understand your grouping criteria but hopefully
this is helpful.

CREATE TABLE T
(
colA VARCHAR(10) NOT NULL,
colB VARCHAR(10) NOT NULL,
PRIMARY KEY (colA, colB)
)

INSERT INTO T (colA, colB)
VALUES ('94015', '01065')
INSERT INTO T (colA, colB)
VALUES ('94016', '01065')
INSERT INTO T (colA, colB)
VALUES ('94015', '01085')
INSERT INTO T (colA, colB)
VALUES ('94015', '01086')
INSERT INTO T (colA, colB)
VALUES ('33383', '00912')
INSERT INTO T (colA, colB)
VALUES ('32601', '00912')

SELECT T.colA, T.colB, B.grp
FROM (SELECT B1.colB, COUNT(*) AS grp
FROM (SELECT colB
FROM (SELECT colA, MIN(colB) AS colB
FROM T
GROUP BY colA) AS A
GROUP BY colB) AS B1
INNER JOIN
(SELECT colB
FROM (SELECT colA, MIN(colB) AS colB
FROM T
GROUP BY colA) AS A
GROUP BY colB) AS B2
ON B2.colB <= B1.colB
GROUP BY B1.colB) AS B
INNER JOIN
(SELECT colA, MIN(colB) AS colB
FROM T
GROUP BY colA) AS A
ON A.colB = B.colB
INNER JOIN
T
ON T.colA = A.colA
ORDER BY B.grp, T.colB, T.colA

colA colB grp
32601 00912 1
33383 00912 1
94015 01065 2
94016 01065 2
94015 01085 2
94015 01086 2

Regards,
jag|||"John Gilson" <jag@.acm.org> wrote in message news:<W_7Xb.20407$Lp.1268@.twister.nyc.rr.com>...
> "cjm" <cjm136@.optonline.net> wrote in message news:62be3d63.0402120756.f08195b@.posting.google.co m...
Thanks JAG for the clever and clean solution!|||Is a given colB value allowed to belong to more than one group? If so then
John's solution looks good but I wasn't clear on this point from your sample
data.

Here's another solution that may or may not give the result you want (thanks
for the DDL and sample data John). I've added an extra row of sample data:

CREATE TABLE T
(
colA VARCHAR(10) NOT NULL,
colB VARCHAR(10) NOT NULL,
grp INTEGER NULL,
PRIMARY KEY (colA, colB)
)

INSERT INTO T (colA, colB)
VALUES ('32601', '01065')

John's query gives:

colA colB grp
---- ---- ----
32601 00912 1
33383 00912 1
32601 01065 1
94015 01065 2
94016 01065 2
94015 01085 2
94015 01086 2

Notice that 01065 appears in both groups. This iterative solution will put
all rows in the same group:

DECLARE @.grp INTEGER

UPDATE T
SET @.grp = grp = COALESCE(@.grp,0) + 1

WHILE @.@.ROWCOUNT>0
UPDATE T
SET grp =
(SELECT MIN(X.grp)
FROM T AS X
WHERE (T.colB = X.colB OR T.colA = X.colA) AND X.grp<T.grp)
WHERE EXISTS
(SELECT *
FROM T AS X
WHERE (T.colB = X.colB OR T.colA = X.colA) AND X.grp<T.grp)

Note that the group numbers using this method are not "sequential" and may
have gaps but it's not clear from your original post exactly what the
sequence should be (if any).

--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<jNadnasl3vRMQ7DdRVn-tw@.giganews.com>...
> Is a given colB value allowed to belong to more than one group? If so then
> John's solution looks good but I wasn't clear on this point from your sample
> data.
> Here's another solution that may or may not give the result you want (thanks
> for the DDL and sample data John). I've added an extra row of sample data:
> ...
There is no column called GRP in the table T and I don't want to alter
the table or create a temp table or otherwise UPDATE table T. How
would this be rewritten to return the result set as a query?

You made an important observation that a given colB value should
belong to only ONE group and I want to see the results of your code
with the record you added to the example. Sorry if this is a simple
conversion but I'm still learning.|||I'm not sure this is possible as a single query. It doesn't look like you
can avoid an iterative solution although you could turn it into a
table-valued function. I'm cross-posting to
microsoft.public.sqlserver.programming to see if anyone can come up with
better than this.

(http://groups.google.com/groups?sel...8195b%40posting.
google.com)

CREATE TABLE T(colA VARCHAR(10), colB VARCHAR(10) NOT NULL, PRIMARY KEY
(colA, colB))

INSERT INTO T (colA, colB) VALUES ('94015', '01065')
INSERT INTO T (colA, colB) VALUES ('94016', '01065')
INSERT INTO T (colA, colB) VALUES ('94015', '01085')
INSERT INTO T (colA, colB) VALUES ('94015', '01086')
INSERT INTO T (colA, colB) VALUES ('33383', '00912')
INSERT INTO T (colA, colB) VALUES ('32601', '00912')

/* Additional row makes it a single group: */
INSERT INTO T (colA, colB) VALUES ('32601', '01065')

CREATE FUNCTION TGroupings ()
RETURNS @.t TABLE (colA VARCHAR(10) NOT NULL, colB VARCHAR(10) NOT NULL, grp
INTEGER NULL, PRIMARY KEY (colA,colB))

BEGIN
INSERT INTO @.t (colA, colB)
SELECT colA, colB
FROM T

DECLARE @.grp INTEGER

UPDATE @.t
SET @.grp = grp = COALESCE(@.grp,0) + 1

WHILE @.@.ROWCOUNT>0
UPDATE T
SET grp =
(SELECT MIN(X.grp)
FROM @.t AS X
WHERE (T.colB = X.colB OR T.colA = X.colA) AND X.grp<T.grp)
FROM @.t AS T
WHERE EXISTS
(SELECT *
FROM @.t AS X
WHERE (T.colB = X.colB OR T.colA = X.colA) AND X.grp<T.grp)

UPDATE T
SET grp =
(SELECT COUNT(DISTINCT grp)
FROM @.t AS X
WHERE grp <= T.grp)
FROM @.t AS T

RETURN
END

SELECT * FROM TGroupings()

This is the result with your original test-data:

colA colB grp
---- ---- ----
32601 00912 1
33383 00912 1
94015 01065 2
94015 01085 2
94015 01086 2
94016 01065 2

(6 row(s) affected)

And this is it with my extra row added:

colA colB grp
---- ---- ----
32601 00912 1
32601 01065 1
33383 00912 1
94015 01065 1
94015 01085 1
94015 01086 1
94016 01065 1

(7 row(s) affected)

--
David Portas
SQL Server MVP
--

"cjm" <cjm136@.optonline.net> wrote in message
news:62be3d63.0402201048.537be689@.posting.google.c om...
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:<jNadnasl3vRMQ7DdRVn-tw@.giganews.com>...
> > Is a given colB value allowed to belong to more than one group? If so
then
> > John's solution looks good but I wasn't clear on this point from your
sample
> > data.
> > Here's another solution that may or may not give the result you want
(thanks
> > for the DDL and sample data John). I've added an extra row of sample
data:
> > ...
> There is no column called GRP in the table T and I don't want to alter
> the table or create a temp table or otherwise UPDATE table T. How
> would this be rewritten to return the result set as a query?
> You made an important observation that a given colB value should
> belong to only ONE group and I want to see the results of your code
> with the record you added to the example. Sorry if this is a simple
> conversion but I'm still learning.sql

Friday, March 30, 2012

Grouping with Reporting services

Grouping with Page Break

Grouping with Page Break

Grouping with non-existent data

Grouping with a full join

Grouping Views by Dates

Grouping Views by Dates

Grouping Views by Dates

Grouping using two stored procedures

Grouping two similar column names but different data?

Grouping Sorting String and Numerical Fields

Grouping SMALL PIE CHART Slices -> Possible ?

grouping similar data

Grouping several items in one group

GROUPING SETS in What's New (Database Engine)

GROUPING SETS in What's New (Database Engine)

Grouping Select Statements with where clause

grouping select query

grouping select query

grouping rows by customer

Grouping Row Border

Grouping Results (Summarizing)

Grouping Results (Summarizing)

Grouping Results (Summarizing)

Grouping Result

Grouping reports

Grouping Records & Assigning Sequential Number

Firehose mode

Blog Archive

About Me