grouping and sorting in matrix control

I have a dataset returned from sql server that can be represented for the
purpose of this discussion with 2 columns. From the server all the data is
sorted first by column 1 and then by column 2 so that the resultset looks
like the following:
column1, column2, column3
a, 1/1/2007, 10
a, 2/1/2007, 30
a, 3/1/2007, 15
b, 10/1/2006, 5
b, 11/1/2006, 1
b, 12/1/2006, 100
b, 1/1/2007, 10
b, 2/1/2007, 9
c, 11/1/2006, 22
c, 12/1/2006, 33
c, 1/1/2007, 44
When I put this data into a matrix with the dates making the columns and
column1 values for each row I get the following
1/1/2007 2/1/2007 3/1/2007 10/1/2006 11/1/2006 12/1/2006
a 10 30 15
b 10 9 5 1
100
c 44 22
33
what I want is the following:
10/1/2006 11/1/2006 12/1/2006 1/1/2007 2/1/2007 3/1/2007
a 10
30 15
b 5 1 100 10 9
c 22 33 44
With the dates sorted. I know I can do it by changing the stored proc but
that opens up all sorts of issues with other things. Is there any way to get
the data looking like I want using reporting services and not modifying the
stored proc?
thanksOn Feb 28, 2:11 pm, Brian <B...@.discussions.microsoft.com> wrote:
> I have a dataset returned from sql server that can be represented for the
> purpose of this discussion with 2 columns. From the server all the data is
> sorted first by column 1 and then by column 2 so that the resultset looks
> like the following:
> column1, column2, column3
> a, 1/1/2007, 10
> a, 2/1/2007, 30
> a, 3/1/2007, 15
> b, 10/1/2006, 5
> b, 11/1/2006, 1
> b, 12/1/2006, 100
> b, 1/1/2007, 10
> b, 2/1/2007, 9
> c, 11/1/2006, 22
> c, 12/1/2006, 33
> c, 1/1/2007, 44
> When I put this data into a matrix with the dates making the columns and
> column1 values for each row I get the following
> 1/1/2007 2/1/2007 3/1/2007 10/1/2006 11/1/2006 12/1/2006
> a 10 30 15
> b 10 9 5 1
> 100
> c 44 22
> 33
> what I want is the following:
> 10/1/2006 11/1/2006 12/1/2006 1/1/2007 2/1/2007 3/1/2007
> a 10
> 30 15
> b 5 1 100 10 9
> c 22 33 44
> With the dates sorted. I know I can do it by changing the stored proc but
> that opens up all sorts of issues with other things. Is there any way to get
> the data looking like I want using reporting services and not modifying the
> stored proc?
> thanks
>From your results, it looks like column2 is sorting aphabetically (I'm
assuming that column2 is not defined as a datetime field in the report
or dataset). You should be able to use the conversion function CDate()
in your sort expression. Something like this should work: CDate(Fields!
column2.Value) and the direction should be ascending. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer

Grouping and counting

Hi,
I will try to explain:
I want the proper grouping and display counts. This query
works fine, see below some of the returned results.
SELECT TempD.state, TempD.City, TempD.Zip, count(TempE.ID)AS total
FROM
TempE INNER JOIN TempD
ON TempE.Numb = TempD.Numb
WHERE(TempE.sp in('bm','pm') or
TempE.sp2 in('bm','pm'))
GROUP BY TempD.state,TempD.City,TempD.Zip
ORDER BY TempD.state, TempD.City
Some of the results returned:
State City Zip total
Alabama ALBASTER 35007 2
Alabama BIRMINGHAM 35292 1
Arizona YUMA 85364 1
California PALMDALE 93550 1
Connecticut NEW LONDON 06320 2
Connecticut WOODBRIDGE 06525 1
Delaware CLAYMONT 19703 1
Delaware WILMINGTON 19801 3
North Carolina ASHEVILLE 28815 10
North Carolina ASHEVILLE 28816 3
South Carolina CHAPIN 29036 1
South Carolina CHARLESTON 29401 116
South Carolina CHARLESTON 29402 8
I want two more columns after total for "bm" and "pm" and with
the totals broke down like the following
State City Zip total
bm pm
Alabama ALBASTER 35007 2 1
1
Alabama BIRMINGHAM 35292 1 1 0
Arizona YUMA 85364 1 0
1
California PALMDALE 93550 1 1
0
Connecticut NEW LONDON 06320 2 2 0
Connecticut WOODBRIDGE 06525 1 0 1
Delaware CLAYMONT 19703 1 1 0
Delaware WILMINGTON 19801 3 3 0
North Carolina ASHEVILLE 28815 10 7 3
North Carolina ASHEVILLE 28816 3 0 3
South Carolina CHAPIN 29036 1 1
0
South Carolina CHARLESTON 29401 116 86 30
South Carolina CHARLESTON 29402 8 2 6
hope I was clear, Thanks
gvTry using a "case" expression.
SELECT
TempD.state,
TempD.City,
TempD.Zip,
count(TempE.ID)AS total,
sum(case when TempE.sp = 'bm' or TempE.sp2 = 'bm' then 1 else 0 end) as bm,
sum(case when TempE.sp = 'pm' or TempE.sp2 = 'pm' then 1 else 0 end) as pm
from
..
AMB
"gv" wrote:

> Hi,
> I will try to explain:
> I want the proper grouping and display counts. This query
> works fine, see below some of the returned results.
> SELECT TempD.state, TempD.City, TempD.Zip, count(TempE.ID)AS total
> FROM
> TempE INNER JOIN TempD
> ON TempE.Numb = TempD.Numb
> WHERE(TempE.sp in('bm','pm') or
> TempE.sp2 in('bm','pm'))
> GROUP BY TempD.state,TempD.City,TempD.Zip
> ORDER BY TempD.state, TempD.City
> Some of the results returned:
> State City Zip total
> Alabama ALBASTER 35007 2
> Alabama BIRMINGHAM 35292 1
> Arizona YUMA 85364 1
> California PALMDALE 93550 1
> Connecticut NEW LONDON 06320 2
> Connecticut WOODBRIDGE 06525 1
> Delaware CLAYMONT 19703 1
> Delaware WILMINGTON 19801 3
> North Carolina ASHEVILLE 28815 10
> North Carolina ASHEVILLE 28816 3
> South Carolina CHAPIN 29036 1
> South Carolina CHARLESTON 29401 116
> South Carolina CHARLESTON 29402 8
> I want two more columns after total for "bm" and "pm" and with
> the totals broke down like the following
> State City Zip total
> bm pm
> Alabama ALBASTER 35007 2 1
> 1
> Alabama BIRMINGHAM 35292 1 1 0
> Arizona YUMA 85364 1
0
> 1
> California PALMDALE 93550 1 1
> 0
> Connecticut NEW LONDON 06320 2 2 0
> Connecticut WOODBRIDGE 06525 1 0 1
> Delaware CLAYMONT 19703 1 1
0
> Delaware WILMINGTON 19801 3 3 0
> North Carolina ASHEVILLE 28815 10 7 3
> North Carolina ASHEVILLE 28816 3 0
3
> South Carolina CHAPIN 29036 1 1
> 0
> South Carolina CHARLESTON 29401 116 86 30
> South Carolina CHARLESTON 29402 8 2 6
> hope I was clear, Thanks
> gv
>
>
>|||Thanks!!
gv
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:1996E862-0C8C-4C22-A154-57D5B728A650@.microsoft.com...
> Try using a "case" expression.
> SELECT
> TempD.state,
> TempD.City,
> TempD.Zip,
> count(TempE.ID)AS total,
> sum(case when TempE.sp = 'bm' or TempE.sp2 = 'bm' then 1 else 0 end) as
> bm,
> sum(case when TempE.sp = 'pm' or TempE.sp2 = 'pm' then 1 else 0 end) as
> pm
> from
> ...
>
> AMB
> "gv" wrote:
>

GROUP BY Clause Problem.

I have a query that has a number of fields being returned from two tables (a
company table and a contact table), and I want to make sure that only one
record per company is returned. But when I run the query I get the
following error.
Column 'Contact.name' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
(As well as the same error for every other field in the query)
How can you work around this limitation? The DISTINCT clause isn't the
answer, because I could get multiple records with the same company but
different contacts at that company. I also want to try to avoid subqueries.
Thanks!!!
BobIf you want to group the query and just need one, you will have to
apply at least on aggregate to the displayed columns, even if its MIN
or MAX, but unless you don=B4t post the query, its hard to help you.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Here is the Query, I am still getting the same error when I add a "token"
min() field.
SELECT Company.company_name, LEFT(Contact.name,20) AS NAME, Contact.state,
LEFT(Company.potential_number_of_users,10) AS USERS, Company.original_date,
Company.if_exported, dbo.displayphone(contact.phone_number_1) AS PHONE,
Contact.address_line_1, Contact.address_line_2, Contact.city, Contact.dear,
Contact.email_address, Contact.last_name, Contact.phone_number_1,
Contact.phone_number_fax, Contact.title, Contact.zip,
LEFT(RTRIM(contact.name)+', ' +company.company_name,50) AS COMPLETED_WITH,
MIN(Company.owner) AS GROUPCLAUSE FROM Company Inner Join Contact ON
Company.record_id = Contact.parent_id WHERE UPPER(Company.status) Like '0
LEAD%' And Company.call_back_date <= GETDATE() And Company.owner Like
'BOB%' GROUP BY Company.company_name ORDER BY Company.original_date DESC
What do you think?
Bob
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1145988891.820648.197700@.u72g2000cwu.googlegroups.com...
If you want to group the query and just need one, you will have to
apply at least on aggregate to the displayed columns, even if its MIN
or MAX, but unless you dont post the query, its hard to help you.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Oh, do you mean min() on "each" displayed column? I'll give it a try...
Bob
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1145988891.820648.197700@.u72g2000cwu.googlegroups.com...
If you want to group the query and just need one, you will have to
apply at least on aggregate to the displayed columns, even if its MIN
or MAX, but unless you dont post the query, its hard to help you.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Yes.

GROUP BY Clause Problem.

I have a query that has a number of fields being returned from two tables (a
company table and a contact table), and I want to make sure that only one
record per company is returned. But when I run the query I get the
following error.
Column 'Contact.name' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
(As well as the same error for every other field in the query)
How can you work around this limitation? The DISTINCT clause isn't the
answer, because I could get multiple records with the same company but
different contacts at that company. I also want to try to avoid subqueries.
Thanks!!!
BobIf you want to group the query and just need one, you will have to
apply at least on aggregate to the displayed columns, even if its MIN
or MAX, but unless you don=B4t post the query, its hard to help you.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Here is the Query, I am still getting the same error when I add a "token"
min() field.
SELECT Company.company_name, LEFT(Contact.name,20) AS NAME, Contact.state,
LEFT(Company.potential_number_of_users,10) AS USERS, Company.original_date,
Company.if_exported, dbo.displayphone(contact.phone_number_1) AS PHONE,
Contact.address_line_1, Contact.address_line_2, Contact.city, Contact.dear,
Contact.email_address, Contact.last_name, Contact.phone_number_1,
Contact.phone_number_fax, Contact.title, Contact.zip,
LEFT(RTRIM(contact.name)+', ' +company.company_name,50) AS COMPLETED_WITH,
MIN(Company.owner) AS GROUPCLAUSE FROM Company Inner Join Contact ON
Company.record_id = Contact.parent_id WHERE UPPER(Company.status) Like '0
LEAD%' And Company.call_back_date <= GETDATE() And Company.owner Like
'BOB%' GROUP BY Company.company_name ORDER BY Company.original_date DESC
What do you think?
Bob
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1145988891.820648.197700@.u72g2000cwu.googlegroups.com...
If you want to group the query and just need one, you will have to
apply at least on aggregate to the displayed columns, even if its MIN
or MAX, but unless you don´t post the query, its hard to help you.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Oh, do you mean min() on "each" displayed column? I'll give it a try...
Bob
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1145988891.820648.197700@.u72g2000cwu.googlegroups.com...
If you want to group the query and just need one, you will have to
apply at least on aggregate to the displayed columns, even if its MIN
or MAX, but unless you don´t post the query, its hard to help you.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Yes.