I have a table which has many columns but need to query it as rows.
The UNION ALL does the trick
My question is when we do a GROUP BY, is there a way we can generate a
sequence number automatically for columns that are grouped by.
Please see query below.
i.e. If there are many rows for this group
GROUP BY REFERENCE_ID, RECORD_KEY, SEQ_NO
it should start from 1, increment by 1
E.g.
REF1, KEY1, 1 , 1 , EXCEPTION1
REF1, KEY1, 1 , 2 , EXCEPTION2
REF1, KEY1, 1 , 3 , EXCEPTION3
REF1, KEY1, 2 , 1 , EXCEPTION1
REF1, KEY1, 2 , 2 , EXCEPTION2
REF1, KEY1, 3 , 1 , EXCEPTION1
REF1, KEY1, 3 , 2 , EXCEPTION2
REF2, KEY3, 1 , 1 , EXCEPTION1
REF2, KEY3, 2 , 1 , EXCEPTION1
REF2, KEY3, 2 , 2 , EXCEPTION2
REF2, KEY3, 2 , 3 , EXCEPTION3
REF2, KEY3, 3 , 1 , EXCEPTION1
REF2, KEY3, 3 , 2 , EXCEPTION2
Thank you.
Chris
Query:
SELECT REFERENCE_ID, RECORD_KEY, SEQ_NO, EOB_EXCP FROM EXCEPTIONS WHERE
LTRIM(EOB_EXCP) IS NOT NULL
UNION ALL
SELECT REFERENCE_ID, RECORD_KEY, SEQ_NO, PRIC_DIS_EXCP FROM EXCEPTIONS
WHERE LTRIM( PRIC_DIS_EXCP) IS NOT NULL
UNION ALL
SELECT REFERENCE_ID, RECORD_KEY, SEQ_NO, LT_DIS_EXCP FROM EXCEPTIONS WHERE
LTRIM( LT_DIS_EXCP) IS NOT NULL
UNION ALL
SELECT REFERENCE_ID, RECORD_KEY, SEQ_NO, UM_DIS_EXCP FROM EXCEPTIONS WHERE
LTRIM( UM_DIS_EXCP) IS NOT NULL
UNION ALL
SELECT REFERENCE_ID, RECORD_KEY, SEQ_NO, SE_DIS_EXCP FROM EXCEPTIONS WHERE
LTRIM( SE_DIS_EXCP) IS NOT NULL
UNION ALL
SELECT REFERENCE_ID, RECORD_KEY, SEQ_NO, PCA_DIS_EXCP FROM EXCEPTIONS WHERE
LTRIM( PCA_DIS_EXCP) IS NOT NULL
UNION ALL
SELECT REFERENCE_ID, RECORD_KEY, SEQ_NO, CE_DIS_EXCP FROM EXCEPTIONS WHERE
LTRIM( CE_DIS_EXCP) IS NOT NULL
UNION ALL
SELECT REFERENCE_ID, RECORD_KEY, SEQ_NO, PENALTY_EXCP FROM EXCEPTIONS WHERE
LTRIM( PENALTY_EXCP) IS NOT NULL
UNION ALL
SELECT REFERENCE_ID, RECORD_KEY, SEQ_NO, RW_DIS_EXCP FROM EXCEPTIONS WHERE
LTRIM( RW_DIS_EXCP) IS NOT NULL
UNION ALL
SELECT REFERENCE_ID, RECORD_KEY, SEQ_NO, DISC_DIS_EXCP FROM EXCEPTIONS
WHERE LTRIM( DISC_DIS_EXCP) IS NOT NULL
UNION ALL
SELECT REFERENCE_ID, RECORD_KEY, SEQ_NO, SUP_DISC_EXCP FROM EXCEPTIONS
WHERE LTRIM( SUP_DISC_EXCP) IS NOT NULL
UNION ALL
SELECT REFERENCE_ID, RECORD_KEY, SEQ_NO, SD_DIS_EXCP FROM EXCEPTIONS WHERE
LTRIM( SD_DIS_EXCP) IS NOT NULL
GROUP BY REFERENCE_ID, RECORD_KEY, SEQ_NOHi Chris,
Can you elaborate on this. The DDL, sample, result with how the sequence
should be generated'|||Well I can elaborate:)
This is the same question posed a million times most recently in the thread:
microsoft.public.sqlserver.programming
Top 5 Quesiton
Friday, April 21, 2006 11:39 AM
I'm sure you can now do your thing:)
(Super easy in Rac but I won't steal your thunder:)
www.rac4sql.net
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:B4A92362-AD6F-4DC1-8EDD-4B0CED9E0E2A@.microsoft.com...
> Hi Chris,
> Can you elaborate on this. The DDL, sample, result with how the sequence
> should be generated'|||same question.. different context.. :)
Can you elaborate this
I'm sure you can now do your thing:)
(Super easy in Rac but I won't steal your thunder:)|||Hello,
You may (or may not) want to check out the Help file online @.
www.rac4sql.net/onlinehelp.asp
See:
Summarizing and Displaying Data
Working with Grouped Data
8. Obtaining the Top N Rows Using Summary Value(s)
Better yet post a challenge:)
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:84C0A77E-6BDD-4026-BC53-4A04E2D2D199@.microsoft.com...
> same question.. different context.. :)
> Can you elaborate this
> I'm sure you can now do your thing:)
> (Super easy in Rac but I won't steal your thunder:)
>|||There you go changing your name again - I was just getting used to the
last one. At least this one looks like a real-life name. :P
*mike hodgson*
http://sqlnerd.blogspot.com
Steve Dassin wrote:
>Hello,
>You may (or may not) want to check out the Help file online @.
>www.rac4sql.net/onlinehelp.asp
>See:
>Summarizing and Displaying Data
> Working with Grouped Data
> 8. Obtaining the Top N Rows Using Summary Value(s)
>Better yet post a challenge:)
>"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
>news:84C0A77E-6BDD-4026-BC53-4A04E2D2D199@.microsoft.com...
>
>
>|||All the better to add to your 'Other SQL Nerds' :P
Steve Dassin
http://racster.blogspot.com/
:)
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:%23bTZEAcaGHA.454
8@.TK2MSFTNGP04.phx.gbl...
There you go changing your name again - I was just getting used to the last
one. At least this one looks like a real-life name. :P
mike hodgson
http://sqlnerd.blogspot.com
Steve Dassin wrote:
Hello,
You may (or may not) want to check out the Help file online @.
www.rac4sql.net/onlinehelp.asp
See:
Summarizing and Displaying Data
Working with Grouped Data
8. Obtaining the Top N Rows Using Summary Value(s)
Better yet post a challenge:)
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:84C0A77E-6BDD-4026-BC53-4A04E2D2D199@.microsoft.com...
same question.. different context.. :)
Can you elaborate this
I'm sure you can now do your thing:)
(Super easy in Rac but I won't steal your thunder:)
Showing posts with label group. Show all posts
Showing posts with label group. Show all posts
Sunday, March 11, 2012
Thursday, March 8, 2012
Columns in database
Hi Group,
This might sound silly, but how can I find the tablenames in MSSQL2000 with which contain a certain columnname?
For example I want to get all the tablenames in a database which contain the column ProjectID.
Thanks for your helpselect * from INFORMATION_SCHEMA.COLUMNS where column_name='columnname'|||Originally posted by Satya
select * from INFORMATION_SCHEMA.COLUMNS where column_name='columnname'
Your the BEST!!
Thanks a lot.
This might sound silly, but how can I find the tablenames in MSSQL2000 with which contain a certain columnname?
For example I want to get all the tablenames in a database which contain the column ProjectID.
Thanks for your helpselect * from INFORMATION_SCHEMA.COLUMNS where column_name='columnname'|||Originally posted by Satya
select * from INFORMATION_SCHEMA.COLUMNS where column_name='columnname'
Your the BEST!!
Thanks a lot.
Wednesday, March 7, 2012
Columnar Data with Group By
If I had the following table
tblFormData
State Question 1
-- ----
TX cat
TX dog
TX dog
CA cat
CA cat
CA dog
CA bunny
AK bunny
AK dog
and I want the results to look like this
State cat dog bunny
----------
TX 1 2 0
CA 2 1 1
AK 0 1 1
How would I do this?Try something like this:
select state
,isnull((select count(*) from tblFormData where state=a.state and [Question 1]='cat' ),0) as 'cat'
,--repeat the same for anothers
from tblFormData a
group by state|||How About:
USE Northwind
GO
CREATE TABLE myTable99 (Col1 char(2), Col2 varchar(10))
GO
INSERT INTO myTable99 (Col1, Col2)
SELECT 'TX', 'cat' UNION ALL
SELECT 'TX', 'dog' UNION ALL
SELECT 'TX', 'dog' UNION ALL
SELECT 'CA', 'cat' UNION ALL
SELECT 'CA', 'cat' UNION ALL
SELECT 'CA', 'dog' UNION ALL
SELECT 'CA', 'bunny' UNION ALL
SELECT 'AK', 'bunny' UNION ALL
SELECT 'AK', 'dog'
GO
SELECT driver.Col1, COUNT(b.Col2) AS Bunnies, COUNT(c.Col2) AS Cats, COUNT(d.Col2) AS Dogs
FROM (SELECT DISTINCT Col1 FROM myTable99) AS driver
LEFT JOIN myTable99 b
ON driver.Col1 = b.Col1
AND b.Col2 = 'bunny'
LEFT JOIN myTable99 c
ON driver.Col1 = c.Col1
AND c.Col2 = 'cat'
LEFT JOIN myTable99 d
ON driver.Col1 = d.Col1
AND d.Col2 = 'dog'
GROUP BY driver.col1
GO
DROP TABLE myTable99
GO|||Originally posted by Brett Kaiser
How About:
USE Northwind
GO
CREATE TABLE myTable99 (Col1 char(2), Col2 varchar(10))
GO
INSERT INTO myTable99 (Col1, Col2)
SELECT 'TX', 'cat' UNION ALL
SELECT 'TX', 'dog' UNION ALL
SELECT 'TX', 'dog' UNION ALL
SELECT 'CA', 'cat' UNION ALL
SELECT 'CA', 'cat' UNION ALL
SELECT 'CA', 'dog' UNION ALL
SELECT 'CA', 'bunny' UNION ALL
SELECT 'AK', 'bunny' UNION ALL
SELECT 'AK', 'dog'
GO
SELECT driver.Col1, COUNT(b.Col2) AS Bunnies, COUNT(c.Col2) AS Cats, COUNT(d.Col2) AS Dogs
FROM (SELECT DISTINCT Col1 FROM myTable99) AS driver
LEFT JOIN myTable99 b
ON driver.Col1 = b.Col1
AND b.Col2 = 'bunny'
LEFT JOIN myTable99 c
ON driver.Col1 = c.Col1
AND c.Col2 = 'cat'
LEFT JOIN myTable99 d
ON driver.Col1 = d.Col1
AND d.Col2 = 'dog'
GROUP BY driver.col1
GO
DROP TABLE myTable99
GO
USE Northwind
GO
CREATE TABLE myTable99 (Col1 char(2), Col2 varchar(10))
GO
INSERT INTO myTable99 (Col1, Col2)
SELECT 'TX', 'cat' UNION ALL
SELECT 'TX', 'dog' UNION ALL
SELECT 'TX', 'dog' UNION ALL
SELECT 'CA', 'cat' UNION ALL
SELECT 'CA', 'cat' UNION ALL
SELECT 'CA', 'dog' UNION ALL
SELECT 'CA', 'bunny' UNION ALL
SELECT 'AK', 'bunny' UNION ALL
SELECT 'AK', 'dog'
GO
-------
Howabout this ?
-------
Select Col1 as State,
Sum (Case when Col2='Cat' then 1 else 0 end) as Cat,
Sum (Case when Col2='Dog' then 1 else 0 end) as Dog,
Sum (Case when Col2='Bunny' then 1 else 0 end) as Bunny
from myTable99
Group By Col1|||I hope 'jiggle it' has to be happy ;)|||there's always more than one way to skin the cat/dog/bunny ;)
tblFormData
State Question 1
-- ----
TX cat
TX dog
TX dog
CA cat
CA cat
CA dog
CA bunny
AK bunny
AK dog
and I want the results to look like this
State cat dog bunny
----------
TX 1 2 0
CA 2 1 1
AK 0 1 1
How would I do this?Try something like this:
select state
,isnull((select count(*) from tblFormData where state=a.state and [Question 1]='cat' ),0) as 'cat'
,--repeat the same for anothers
from tblFormData a
group by state|||How About:
USE Northwind
GO
CREATE TABLE myTable99 (Col1 char(2), Col2 varchar(10))
GO
INSERT INTO myTable99 (Col1, Col2)
SELECT 'TX', 'cat' UNION ALL
SELECT 'TX', 'dog' UNION ALL
SELECT 'TX', 'dog' UNION ALL
SELECT 'CA', 'cat' UNION ALL
SELECT 'CA', 'cat' UNION ALL
SELECT 'CA', 'dog' UNION ALL
SELECT 'CA', 'bunny' UNION ALL
SELECT 'AK', 'bunny' UNION ALL
SELECT 'AK', 'dog'
GO
SELECT driver.Col1, COUNT(b.Col2) AS Bunnies, COUNT(c.Col2) AS Cats, COUNT(d.Col2) AS Dogs
FROM (SELECT DISTINCT Col1 FROM myTable99) AS driver
LEFT JOIN myTable99 b
ON driver.Col1 = b.Col1
AND b.Col2 = 'bunny'
LEFT JOIN myTable99 c
ON driver.Col1 = c.Col1
AND c.Col2 = 'cat'
LEFT JOIN myTable99 d
ON driver.Col1 = d.Col1
AND d.Col2 = 'dog'
GROUP BY driver.col1
GO
DROP TABLE myTable99
GO|||Originally posted by Brett Kaiser
How About:
USE Northwind
GO
CREATE TABLE myTable99 (Col1 char(2), Col2 varchar(10))
GO
INSERT INTO myTable99 (Col1, Col2)
SELECT 'TX', 'cat' UNION ALL
SELECT 'TX', 'dog' UNION ALL
SELECT 'TX', 'dog' UNION ALL
SELECT 'CA', 'cat' UNION ALL
SELECT 'CA', 'cat' UNION ALL
SELECT 'CA', 'dog' UNION ALL
SELECT 'CA', 'bunny' UNION ALL
SELECT 'AK', 'bunny' UNION ALL
SELECT 'AK', 'dog'
GO
SELECT driver.Col1, COUNT(b.Col2) AS Bunnies, COUNT(c.Col2) AS Cats, COUNT(d.Col2) AS Dogs
FROM (SELECT DISTINCT Col1 FROM myTable99) AS driver
LEFT JOIN myTable99 b
ON driver.Col1 = b.Col1
AND b.Col2 = 'bunny'
LEFT JOIN myTable99 c
ON driver.Col1 = c.Col1
AND c.Col2 = 'cat'
LEFT JOIN myTable99 d
ON driver.Col1 = d.Col1
AND d.Col2 = 'dog'
GROUP BY driver.col1
GO
DROP TABLE myTable99
GO
USE Northwind
GO
CREATE TABLE myTable99 (Col1 char(2), Col2 varchar(10))
GO
INSERT INTO myTable99 (Col1, Col2)
SELECT 'TX', 'cat' UNION ALL
SELECT 'TX', 'dog' UNION ALL
SELECT 'TX', 'dog' UNION ALL
SELECT 'CA', 'cat' UNION ALL
SELECT 'CA', 'cat' UNION ALL
SELECT 'CA', 'dog' UNION ALL
SELECT 'CA', 'bunny' UNION ALL
SELECT 'AK', 'bunny' UNION ALL
SELECT 'AK', 'dog'
GO
-------
Howabout this ?
-------
Select Col1 as State,
Sum (Case when Col2='Cat' then 1 else 0 end) as Cat,
Sum (Case when Col2='Dog' then 1 else 0 end) as Dog,
Sum (Case when Col2='Bunny' then 1 else 0 end) as Bunny
from myTable99
Group By Col1|||I hope 'jiggle it' has to be happy ;)|||there's always more than one way to skin the cat/dog/bunny ;)
Columnar Data with Group By
If I had the following table
tblFormData
State Question 1
-- ----
TX......cat
TX......dog
TX......dog
CA......cat
CA......cat
CA......dog
CA......bunny
AK......bunny
AK......dog
and I want the results to look like this
State cat dog bunny
----------
TX......1......2......0
CA......2......1......1
AK......0......1......1
How would I do this? Ignore the ......, i had to use them because this board erases my spaces...Something like:
Select
State,
Sum(CASE WHEN Question = 'cat' THEN 1 ELSE 0 END) as cat,
Sum(CASE WHEN Question = 'dog' THEN 1 ELSE 0 END) as dog,
Sum(CASE WHEN Question = 'bunny' THEN 1 ELSE 0 END) as bunny
FROM tblFormData
Group BY State
Friday, February 24, 2012
column name as group in a table
Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what'
MangeshGroup is a reserved word. It's a best practice not to use column names that
are reserved words. If you insist on using reserved words, use square
brackets:
[Group]
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what'
Mangesh|||Thanks Tom. So what is the difference when you put a square bracket and
when you don't
"Tom Moreau" wrote:
> Group is a reserved word. It's a best practice not to use column names that
> are reserved words. If you insist on using reserved words, use square
> brackets:
> [Group]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
> message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
> Hi
> I have a table created by user as
> use base
> insert into table1 values ( 'x')
> select * from table1
> where group = 'x'
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'Group'.
> My question is :
> I tried to create a table with
> create table x ( group varchar(10))
> and it gives error
> But same table I Can create from EM with group column.
> But I Can not write a where clause on the group column.
> Is it a bug or what'
> Mangesh
>|||"Mangesh Deshpande" schrieb:
> Thanks Tom. So what is the difference when you put a square bracket and
> when you don't
Object names between square brackets are called 'delimited identifiers', and
delimited identifiers allow you to break each and every restriction of name
creation (e.g. VERY long names, names that contain spaces or other forbidden
characters, names that are reserved words, etc.).
Delimited identifiers are not comfortable to handle though - why don't you
translate the name 'group' into Hindi or Punjab? It is rather unlikely that
it will still conflict with a reserved word ... ;-)
Another solution is using the hungarian notation: the character field
'group' would then be 'cGroup' (or 'vcGroup') which both wouldn't conflict
with the reserved words.|||Hi,
If u understand the process of query execution then u should know that
microsoft compiler detects the reserverd name and from the information
of BOL microsoft suggest to use the reserved names in brakets becasue
on compiltion time microsoft doesnt detects as reserved becasue of
deliminated identifier.
create table ff([group] varchar(20))
from
doller|||Thanks a lot.
"doller" wrote:
> Hi,
> If u understand the process of query execution then u should know that
> microsoft compiler detects the reserverd name and from the information
> of BOL microsoft suggest to use the reserved names in brakets becasue
> on compiltion time microsoft doesnt detects as reserved becasue of
> deliminated identifier.
> create table ff([group] varchar(20))
> from
> doller
>
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what'
MangeshGroup is a reserved word. It's a best practice not to use column names that
are reserved words. If you insist on using reserved words, use square
brackets:
[Group]
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what'
Mangesh|||Thanks Tom. So what is the difference when you put a square bracket and
when you don't
"Tom Moreau" wrote:
> Group is a reserved word. It's a best practice not to use column names that
> are reserved words. If you insist on using reserved words, use square
> brackets:
> [Group]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
> message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
> Hi
> I have a table created by user as
> use base
> insert into table1 values ( 'x')
> select * from table1
> where group = 'x'
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'Group'.
> My question is :
> I tried to create a table with
> create table x ( group varchar(10))
> and it gives error
> But same table I Can create from EM with group column.
> But I Can not write a where clause on the group column.
> Is it a bug or what'
> Mangesh
>|||"Mangesh Deshpande" schrieb:
> Thanks Tom. So what is the difference when you put a square bracket and
> when you don't
Object names between square brackets are called 'delimited identifiers', and
delimited identifiers allow you to break each and every restriction of name
creation (e.g. VERY long names, names that contain spaces or other forbidden
characters, names that are reserved words, etc.).
Delimited identifiers are not comfortable to handle though - why don't you
translate the name 'group' into Hindi or Punjab? It is rather unlikely that
it will still conflict with a reserved word ... ;-)
Another solution is using the hungarian notation: the character field
'group' would then be 'cGroup' (or 'vcGroup') which both wouldn't conflict
with the reserved words.|||Hi,
If u understand the process of query execution then u should know that
microsoft compiler detects the reserverd name and from the information
of BOL microsoft suggest to use the reserved names in brakets becasue
on compiltion time microsoft doesnt detects as reserved becasue of
deliminated identifier.
create table ff([group] varchar(20))
from
doller|||Thanks a lot.
"doller" wrote:
> Hi,
> If u understand the process of query execution then u should know that
> microsoft compiler detects the reserverd name and from the information
> of BOL microsoft suggest to use the reserved names in brakets becasue
> on compiltion time microsoft doesnt detects as reserved becasue of
> deliminated identifier.
> create table ff([group] varchar(20))
> from
> doller
>
column name as group in a table
Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what?
Mangesh
Group is a reserved word. It's a best practice not to use column names that
are reserved words. If you insist on using reserved words, use square
brackets:
[Group]
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what?
Mangesh
|||Thanks Tom. So what is the difference when you put a square bracket and
when you don't
"Tom Moreau" wrote:
> Group is a reserved word. It's a best practice not to use column names that
> are reserved words. If you insist on using reserved words, use square
> brackets:
> [Group]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
> message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
> Hi
> I have a table created by user as
> use base
> insert into table1 values ( 'x')
> select * from table1
> where group = 'x'
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'Group'.
> My question is :
> I tried to create a table with
> create table x ( group varchar(10))
> and it gives error
> But same table I Can create from EM with group column.
> But I Can not write a where clause on the group column.
> Is it a bug or what?
> Mangesh
>
|||"Mangesh Deshpande" schrieb:
> Thanks Tom. So what is the difference when you put a square bracket and
> when you don't
Object names between square brackets are called 'delimited identifiers', and
delimited identifiers allow you to break each and every restriction of name
creation (e.g. VERY long names, names that contain spaces or other forbidden
characters, names that are reserved words, etc.).
Delimited identifiers are not comfortable to handle though - why don't you
translate the name 'group' into Hindi or Punjab? It is rather unlikely that
it will still conflict with a reserved word ... ;-)
Another solution is using the hungarian notation: the character field
'group' would then be 'cGroup' (or 'vcGroup') which both wouldn't conflict
with the reserved words.
|||Hi,
If u understand the process of query execution then u should know that
microsoft compiler detects the reserverd name and from the information
of BOL microsoft suggest to use the reserved names in brakets becasue
on compiltion time microsoft doesnt detects as reserved becasue of
deliminated identifier.
create table ff([group] varchar(20))
from
doller
|||Thanks a lot.
"doller" wrote:
> Hi,
> If u understand the process of query execution then u should know that
> microsoft compiler detects the reserverd name and from the information
> of BOL microsoft suggest to use the reserved names in brakets becasue
> on compiltion time microsoft doesnt detects as reserved becasue of
> deliminated identifier.
> create table ff([group] varchar(20))
> from
> doller
>
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what?
Mangesh
Group is a reserved word. It's a best practice not to use column names that
are reserved words. If you insist on using reserved words, use square
brackets:
[Group]
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what?
Mangesh
|||Thanks Tom. So what is the difference when you put a square bracket and
when you don't
"Tom Moreau" wrote:
> Group is a reserved word. It's a best practice not to use column names that
> are reserved words. If you insist on using reserved words, use square
> brackets:
> [Group]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
> message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
> Hi
> I have a table created by user as
> use base
> insert into table1 values ( 'x')
> select * from table1
> where group = 'x'
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'Group'.
> My question is :
> I tried to create a table with
> create table x ( group varchar(10))
> and it gives error
> But same table I Can create from EM with group column.
> But I Can not write a where clause on the group column.
> Is it a bug or what?
> Mangesh
>
|||"Mangesh Deshpande" schrieb:
> Thanks Tom. So what is the difference when you put a square bracket and
> when you don't
Object names between square brackets are called 'delimited identifiers', and
delimited identifiers allow you to break each and every restriction of name
creation (e.g. VERY long names, names that contain spaces or other forbidden
characters, names that are reserved words, etc.).
Delimited identifiers are not comfortable to handle though - why don't you
translate the name 'group' into Hindi or Punjab? It is rather unlikely that
it will still conflict with a reserved word ... ;-)
Another solution is using the hungarian notation: the character field
'group' would then be 'cGroup' (or 'vcGroup') which both wouldn't conflict
with the reserved words.
|||Hi,
If u understand the process of query execution then u should know that
microsoft compiler detects the reserverd name and from the information
of BOL microsoft suggest to use the reserved names in brakets becasue
on compiltion time microsoft doesnt detects as reserved becasue of
deliminated identifier.
create table ff([group] varchar(20))
from
doller
|||Thanks a lot.
"doller" wrote:
> Hi,
> If u understand the process of query execution then u should know that
> microsoft compiler detects the reserverd name and from the information
> of BOL microsoft suggest to use the reserved names in brakets becasue
> on compiltion time microsoft doesnt detects as reserved becasue of
> deliminated identifier.
> create table ff([group] varchar(20))
> from
> doller
>
column name as group in a table
Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what'
MangeshGroup is a reserved word. It's a best practice not to use column names that
are reserved words. If you insist on using reserved words, use square
brackets:
[Group]
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what'
Mangesh|||Thanks Tom. So what is the difference when you put a square bracket and
when you don't
"Tom Moreau" wrote:
> Group is a reserved word. It's a best practice not to use column names th
at
> are reserved words. If you insist on using reserved words, use square
> brackets:
> [Group]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
> message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
> Hi
> I have a table created by user as
> use base
> insert into table1 values ( 'x')
> select * from table1
> where group = 'x'
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'Group'.
> My question is :
> I tried to create a table with
> create table x ( group varchar(10))
> and it gives error
> But same table I Can create from EM with group column.
> But I Can not write a where clause on the group column.
> Is it a bug or what'
> Mangesh
>|||"Mangesh Deshpande" schrieb:
> Thanks Tom. So what is the difference when you put a square bracket and
> when you don't
Object names between square brackets are called 'delimited identifiers', and
delimited identifiers allow you to break each and every restriction of name
creation (e.g. VERY long names, names that contain spaces or other forbidden
characters, names that are reserved words, etc.).
Delimited identifiers are not comfortable to handle though - why don't you
translate the name 'group' into Hindi or Punjab? It is rather unlikely that
it will still conflict with a reserved word ... ;-)
Another solution is using the hungarian notation: the character field
'group' would then be 'cGroup' (or 'vcGroup') which both wouldn't conflict
with the reserved words.|||Hi,
If u understand the process of query execution then u should know that
microsoft compiler detects the reserverd name and from the information
of BOL microsoft suggest to use the reserved names in brakets becasue
on compiltion time microsoft doesnt detects as reserved becasue of
deliminated identifier.
create table ff([group] varchar(20))
from
doller|||Thanks a lot.
"doller" wrote:
> Hi,
> If u understand the process of query execution then u should know that
> microsoft compiler detects the reserverd name and from the information
> of BOL microsoft suggest to use the reserved names in brakets becasue
> on compiltion time microsoft doesnt detects as reserved becasue of
> deliminated identifier.
> create table ff([group] varchar(20))
> from
> doller
>
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what'
MangeshGroup is a reserved word. It's a best practice not to use column names that
are reserved words. If you insist on using reserved words, use square
brackets:
[Group]
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what'
Mangesh|||Thanks Tom. So what is the difference when you put a square bracket and
when you don't
"Tom Moreau" wrote:
> Group is a reserved word. It's a best practice not to use column names th
at
> are reserved words. If you insist on using reserved words, use square
> brackets:
> [Group]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
> message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
> Hi
> I have a table created by user as
> use base
> insert into table1 values ( 'x')
> select * from table1
> where group = 'x'
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'Group'.
> My question is :
> I tried to create a table with
> create table x ( group varchar(10))
> and it gives error
> But same table I Can create from EM with group column.
> But I Can not write a where clause on the group column.
> Is it a bug or what'
> Mangesh
>|||"Mangesh Deshpande" schrieb:
> Thanks Tom. So what is the difference when you put a square bracket and
> when you don't
Object names between square brackets are called 'delimited identifiers', and
delimited identifiers allow you to break each and every restriction of name
creation (e.g. VERY long names, names that contain spaces or other forbidden
characters, names that are reserved words, etc.).
Delimited identifiers are not comfortable to handle though - why don't you
translate the name 'group' into Hindi or Punjab? It is rather unlikely that
it will still conflict with a reserved word ... ;-)
Another solution is using the hungarian notation: the character field
'group' would then be 'cGroup' (or 'vcGroup') which both wouldn't conflict
with the reserved words.|||Hi,
If u understand the process of query execution then u should know that
microsoft compiler detects the reserverd name and from the information
of BOL microsoft suggest to use the reserved names in brakets becasue
on compiltion time microsoft doesnt detects as reserved becasue of
deliminated identifier.
create table ff([group] varchar(20))
from
doller|||Thanks a lot.
"doller" wrote:
> Hi,
> If u understand the process of query execution then u should know that
> microsoft compiler detects the reserverd name and from the information
> of BOL microsoft suggest to use the reserved names in brakets becasue
> on compiltion time microsoft doesnt detects as reserved becasue of
> deliminated identifier.
> create table ff([group] varchar(20))
> from
> doller
>
column heading is missing on some pdf rendered pages
I am new to RS so this might be a stupid question. Since the page header
can't have table, I have placed the table in the body as table group header.
The table contains column heading for my report. The report produces over a
hundred pages of PDF files. As I scroll through the PDF pages I noticed that
on some pages the column heading is missing. Does anyone know why or is there
a better way to do this?
Thanks,
TimThis might not be the right answer, but my impression is that where pages
break in the web report, and in the PDF exported report, are two different
things. I noticed it when I added a footer with a line to a report. THis
line would show up at the end of each page in the web report, and totally
randomly in the PDF report. And there would be a lot more text on each page
in the web report, than in the PDF. At least this goes for reports with 8pt
font and landscape view.
Try to check if what you see on each page in the PDF corresponds with what
you see in your web report.
Kaisa M. Lindahl
"Tim2Be" <Tim2Be@.discussions.microsoft.com> wrote in message
news:5660FC32-2C75-43F2-BA22-212001F7D535@.microsoft.com...
> I am new to RS so this might be a stupid question. Since the page header
> can't have table, I have placed the table in the body as table group
header.
> The table contains column heading for my report. The report produces over
a
> hundred pages of PDF files. As I scroll through the PDF pages I noticed
that
> on some pages the column heading is missing. Does anyone know why or is
there
> a better way to do this?
> Thanks,
> Tim|||My web report looks perfectly find with all the column heading in tack on all
pages. Only some of the PDF pages that is missing the column heading. I don't
know why it does that.
"Kaisa M. Lindahl" wrote:
> This might not be the right answer, but my impression is that where pages
> break in the web report, and in the PDF exported report, are two different
> things. I noticed it when I added a footer with a line to a report. THis
> line would show up at the end of each page in the web report, and totally
> randomly in the PDF report. And there would be a lot more text on each page
> in the web report, than in the PDF. At least this goes for reports with 8pt
> font and landscape view.
> Try to check if what you see on each page in the PDF corresponds with what
> you see in your web report.
> Kaisa M. Lindahl
> "Tim2Be" <Tim2Be@.discussions.microsoft.com> wrote in message
> news:5660FC32-2C75-43F2-BA22-212001F7D535@.microsoft.com...
> > I am new to RS so this might be a stupid question. Since the page header
> > can't have table, I have placed the table in the body as table group
> header.
> > The table contains column heading for my report. The report produces over
> a
> > hundred pages of PDF files. As I scroll through the PDF pages I noticed
> that
> > on some pages the column heading is missing. Does anyone know why or is
> there
> > a better way to do this?
> >
> > Thanks,
> > Tim
>
>
can't have table, I have placed the table in the body as table group header.
The table contains column heading for my report. The report produces over a
hundred pages of PDF files. As I scroll through the PDF pages I noticed that
on some pages the column heading is missing. Does anyone know why or is there
a better way to do this?
Thanks,
TimThis might not be the right answer, but my impression is that where pages
break in the web report, and in the PDF exported report, are two different
things. I noticed it when I added a footer with a line to a report. THis
line would show up at the end of each page in the web report, and totally
randomly in the PDF report. And there would be a lot more text on each page
in the web report, than in the PDF. At least this goes for reports with 8pt
font and landscape view.
Try to check if what you see on each page in the PDF corresponds with what
you see in your web report.
Kaisa M. Lindahl
"Tim2Be" <Tim2Be@.discussions.microsoft.com> wrote in message
news:5660FC32-2C75-43F2-BA22-212001F7D535@.microsoft.com...
> I am new to RS so this might be a stupid question. Since the page header
> can't have table, I have placed the table in the body as table group
header.
> The table contains column heading for my report. The report produces over
a
> hundred pages of PDF files. As I scroll through the PDF pages I noticed
that
> on some pages the column heading is missing. Does anyone know why or is
there
> a better way to do this?
> Thanks,
> Tim|||My web report looks perfectly find with all the column heading in tack on all
pages. Only some of the PDF pages that is missing the column heading. I don't
know why it does that.
"Kaisa M. Lindahl" wrote:
> This might not be the right answer, but my impression is that where pages
> break in the web report, and in the PDF exported report, are two different
> things. I noticed it when I added a footer with a line to a report. THis
> line would show up at the end of each page in the web report, and totally
> randomly in the PDF report. And there would be a lot more text on each page
> in the web report, than in the PDF. At least this goes for reports with 8pt
> font and landscape view.
> Try to check if what you see on each page in the PDF corresponds with what
> you see in your web report.
> Kaisa M. Lindahl
> "Tim2Be" <Tim2Be@.discussions.microsoft.com> wrote in message
> news:5660FC32-2C75-43F2-BA22-212001F7D535@.microsoft.com...
> > I am new to RS so this might be a stupid question. Since the page header
> > can't have table, I have placed the table in the body as table group
> header.
> > The table contains column heading for my report. The report produces over
> a
> > hundred pages of PDF files. As I scroll through the PDF pages I noticed
> that
> > on some pages the column heading is missing. Does anyone know why or is
> there
> > a better way to do this?
> >
> > Thanks,
> > Tim
>
>
Sunday, February 19, 2012
Column headers for Matrix
I am developing a matrix report in SRS. In columns group there are several values. When report runs they apper in any order based on the first record in row group. I want colums to apeear in specific order all the time. For example the column sequence in one out put is Follwup 1, Initial , Followup 2. I want to column header to be in order of Initial, Folloup 1, Followup 2.
Can someone help?
You'll need to add another column like Sort or Rank and populate that accordingly. Then sort the output based on that column. If you have several columns that are dynamic that would be best.
pseudo-code for the sort column in your SQL would be:
Case
When "Followup 1" Then 1
When "Initial" Then 2
When "Followup 2" Then 3
End as MyCustomRank
You can also use an expression in the matrix sort...same concept. The pseduo-code for that would be
=IIF(Fields!YOURFIELD.Value = "Followup 1", 1, IIF(Fields!YOURField.Value = "Initial", 2..... and so on
|||Thank You very much
It worked!
Column headers for Matrix
I am developing a matrix report in SRS. In columns group there are several values. When report runs they apper in any order based on the first record in row group. I want colums to apeear in specific order all the time. For example the column sequence in one out put is Follwup 1, Initial , Followup 2. I want to column header to be in order of Initial, Folloup 1, Followup 2.
Can someone help?
You'll need to add another column like Sort or Rank and populate that accordingly. Then sort the output based on that column. If you have several columns that are dynamic that would be best.
pseudo-code for the sort column in your SQL would be:
Case
When "Followup 1" Then 1
When "Initial" Then 2
When "Followup 2" Then 3
End as MyCustomRank
You can also use an expression in the matrix sort...same concept. The pseduo-code for that would be
=IIF(Fields!YOURFIELD.Value = "Followup 1", 1, IIF(Fields!YOURField.Value = "Initial", 2..... and so on
|||Thank You very much
It worked!
Column Header for Matrix Report
I have a matrix report, which looks like the following:
Header C Header D
Column A Column B Column C Column D
Column A and B are Row Group columns in the matrix. Column C and D are the details columns. How can I add column header to column A and B. Seems I cannot do it in Reporting Services 2005.
Fan
Yes, you can not able to achieve this with Matrices currently and we are hoping to make it possible in our next release.
Workaround: Now you can get this with placing text boxes in the corner of the Matrix which aligns to these cloumns make it give a similar look.
Column Group Row Alignment - Matrix
I just can not understand why when I add text to a group header the report displays more group row space but when I export to excel the extra space disappears....
What the....Help Microsoft please explain...
Thank You...
Any help on this would be great...
Please
Column Group Footers in a Horizontal Report
Hi all.
I've created a horizontal report using a matrix according to the quite useful article Horizontal Tables, but there's one thing I'm trying to do that I'm not having any luck figuring out how to do, and that is creating a footer column for a column group that contains a sum of the values in some of its columns.
In other words, here's how I would like to have it look...
The fields in the dataset are Month, Tier1, Tier2, Year and Quarter. I've created a group on Year & Quarter, tried right clicking and selecting Subtotal, but that seems to add a subtotal column after every month, not at the end of the quarter group as I want.
Any ideas on what I need to do? Is this even possible?
TIA
If you create a matrix with a row grouping of the Tier field and a two column groupings you can get pretty close to what you are looking for.For the column groupings, create a grouping for the Year and Quarter (2 expressions for the this group) and create another, inner grouping for the Month. Then, place the subtotal month grouping.
You should get something like:
Here is example RDL using the Northwind database.
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="DataSource1">
<ConnectionProperties>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString>Initial Catalog=Northwind; data source=localhost</ConnectString>
<DataProvider>SQL</DataProvider>
</ConnectionProperties>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
</MatrixColumns>
<Left>1in</Left>
<RowGroupings>
<RowGrouping>
<Width>1in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="ShipCountry">
<rd:DefaultName>ShipCountry</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!ShipCountry.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_ShipCountry">
<GroupExpressions>
<GroupExpression>=Fields!ShipCountry.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="OrderDate">
<rd:DefaultName>OrderDate</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!OrderDate.Value.Year & " - " & Fields!OrderDate.Value.Month</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_OrderDate">
<GroupExpressions>
<GroupExpression>=Fields!OrderDate.Value.Year</GroupExpression>
<GroupExpression>=Cint((Fields!OrderDate.Value.Month+1)/3)</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="OrderDate_1">
<rd:DefaultName>OrderDate_1</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!OrderDate.Value.Month</Value>
</Textbox>
</ReportItems>
<Subtotal>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<Color>Red</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Total</Value>
</Textbox>
</ReportItems>
<Style>
<Color>Red</Color>
</Style>
</Subtotal>
<Grouping Name="matrix1_ColumnGroup1">
<GroupExpressions>
<GroupExpression>=Fields!OrderDate.Value.Month</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>DataSet2</DataSetName>
<Top>1.125in</Top>
<Width>3in</Width>
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.75in</Height>
<MatrixRows>
<MatrixRow>
<Height>0.25in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="Quantity">
<rd:DefaultName>Quantity</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Quantity.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>2.25in</Height>
</Body>
<rd:ReportID>69d1d3a8-e0a0-4c9b-b63e-d7204e0f8f21</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet2">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>Select ShipCountry, OrderDate, Quantity from orders inner join [order details] on orders.orderid = [order details].orderid</CommandText>
<DataSourceName>SharedNorthwind</DataSourceName>
</Query>
<Fields>
<Field Name="ShipCountry">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipCountry</DataField>
</Field>
<Field Name="OrderDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>OrderDate</DataField>
</Field>
<Field Name="Quantity">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>Quantity</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>6.5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
Subscribe to:
Posts (Atom)