Max date from three different columns

I have a query that returns three different columns
ex: select a.date1, b.date2, c.date3
from table1 a,
table2 b,
table3 c
I need to return the result of the largest of the dates
I thought something like this would work, but it doesn't
select max([thisdate])
from
(select a.date1 [thisdate]
from table1 a
unionselect b.date1 [thisdate]
from table1 b
unionselect c.date1 [thisdate]
from table1 c)
I cannot change the database structure, and I am hoping that a huge if
statement can be avoided.
Thanks
EricAre all these columns in the one table? If so, you want to unpivot:
select
max (case x.seq
when 1 then Col1
when 2 then Col2
when 3 then Col3
end)
from
(
select 1 union all
select 2 union all
select 3
) x (seq)
cross join
MyTable
If these are across 3 tables, try:
select max([thisdate])
from
(select a.date1 [thisdate]
from table1 a
union
select b.date1 [thisdate]
from table1 b
union
select c.date1 [thisdate]
from table1 c
) as x
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
news:eo1G$7lXGHA.1204@.TK2MSFTNGP04.phx.gbl...
I have a query that returns three different columns
ex: select a.date1, b.date2, c.date3
from table1 a,
table2 b,
table3 c
I need to return the result of the largest of the dates
I thought something like this would work, but it doesn't
select max([thisdate])
from
(select a.date1 [thisdate]
from table1 a
unionselect b.date1 [thisdate]
from table1 b
unionselect c.date1 [thisdate]
from table1 c)
I cannot change the database structure, and I am hoping that a huge if
statement can be avoided.
Thanks
Eric|||> ex: select a.date1, b.date2, c.date3
> from table1 a,
> table2 b,
> table3 c
Eeks, is this supposed to be a cross join? How are these three tables
related? Did you mean to query against columns in three different tables,
or against three different columns in the same table? Did you want the MAX
date from any of the three columns in ALL the rows of the table, or the
greater of the three column values in every row in the table?
Can you provide decent requirements so that we don't have to ask 40
questions to figure out what you're talking about? Please see
http://www.aspfaq.com/5006|||select max([thisdate])
from
(select a.date1 [thisdate]
from table1 a
union
select b.date1 [thisdate]
from table1 b
union
select c.date1 [thisdate]
from table1 c
) as x
BEAUTIFUL!!!1
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23gxFRBmXGHA.4120@.TK2MSFTNGP03.phx.gbl...
> Are all these columns in the one table? If so, you want to unpivot:
> select
> max (case x.seq
> when 1 then Col1
> when 2 then Col2
> when 3 then Col3
> end)
> from
> (
> select 1 union all
> select 2 union all
> select 3
> ) x (seq)
> cross join
> MyTable
> If these are across 3 tables, try:
> select max([thisdate])
> from
> (select a.date1 [thisdate]
> from table1 a
> union
> select b.date1 [thisdate]
> from table1 b
> union
> select c.date1 [thisdate]
> from table1 c
> ) as x
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
> news:eo1G$7lXGHA.1204@.TK2MSFTNGP04.phx.gbl...
> I have a query that returns three different columns
> ex: select a.date1, b.date2, c.date3
> from table1 a,
> table2 b,
> table3 c
> I need to return the result of the largest of the dates
> I thought something like this would work, but it doesn't
> select max([thisdate])
> from
> (select a.date1 [thisdate]
> from table1 a
> unionselect b.date1 [thisdate]
> from table1 b
> unionselect c.date1 [thisdate]
> from table1 c)
> I cannot change the database structure, and I am hoping that a huge if
> statement can be avoided.
> Thanks
> Eric
>|||Okay, this works great in the small test case, but now I am attempting to
put it into my larger query, and it looks something similar to this
select a.column1, a.column2, a.date
,b.column1, b.column2, b.date
,c.column1, c.column2
,max([thisdate])
from
table1 a
,table2 b
,table3 c
,(select a.date1 [thisdate]
union
select b.date1 [thisdate]
union
select c.date1 [thisdate]
) as x
where
......
And it states:
The column prefix 'a' does not match with a table name or alias name used in
the query
The column prefix 'b' does not match with a table name or alias name used in
the query
The column prefix 'c' does not match with a table name or alias name used in
the query
if I put table1.date1 it gives a similar error
Eric
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23gxFRBmXGHA.4120@.TK2MSFTNGP03.phx.gbl...
> Are all these columns in the one table? If so, you want to unpivot:
> If these are across 3 tables, try:
> select max([thisdate])
> from
> (select a.date1 [thisdate]
> from table1 a
> union
> select b.date1 [thisdate]
> from table1 b
> union
> select c.date1 [thisdate]
> from table1 c
> ) as x
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
> news:eo1G$7lXGHA.1204@.TK2MSFTNGP04.phx.gbl...
> I have a query that returns three different columns
> ex: select a.date1, b.date2, c.date3
> from table1 a,
> table2 b,
> table3 c
> I need to return the result of the largest of the dates
> I thought something like this would work, but it doesn't
> select max([thisdate])
> from
> (select a.date1 [thisdate]
> from table1 a
> unionselect b.date1 [thisdate]
> from table1 b
> unionselect c.date1 [thisdate]
> from table1 c)
> I cannot change the database structure, and I am hoping that a huge if
> statement can be avoided.
> Thanks
> Eric
>|||What is it that you're really trying to achieve? We're seeing cross joins
over 3 tables + 1 derived table. Give us a spec + DDL + INSERT statements
of sample data + desired results.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
news:%23Yim%23VmXGHA.4988@.TK2MSFTNGP05.phx.gbl...
Okay, this works great in the small test case, but now I am attempting to
put it into my larger query, and it looks something similar to this
select a.column1, a.column2, a.date
,b.column1, b.column2, b.date
,c.column1, c.column2
,max([thisdate])
from
table1 a
,table2 b
,table3 c
,(select a.date1 [thisdate]
union
select b.date1 [thisdate]
union
select c.date1 [thisdate]
) as x
where
......
And it states:
The column prefix 'a' does not match with a table name or alias name used in
the query
The column prefix 'b' does not match with a table name or alias name used in
the query
The column prefix 'c' does not match with a table name or alias name used in
the query
if I put table1.date1 it gives a similar error
Eric
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23gxFRBmXGHA.4120@.TK2MSFTNGP03.phx.gbl...
> Are all these columns in the one table? If so, you want to unpivot:
> If these are across 3 tables, try:
> select max([thisdate])
> from
> (select a.date1 [thisdate]
> from table1 a
> union
> select b.date1 [thisdate]
> from table1 b
> union
> select c.date1 [thisdate]
> from table1 c
> ) as x
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
> news:eo1G$7lXGHA.1204@.TK2MSFTNGP04.phx.gbl...
> I have a query that returns three different columns
> ex: select a.date1, b.date2, c.date3
> from table1 a,
> table2 b,
> table3 c
> I need to return the result of the largest of the dates
> I thought something like this would work, but it doesn't
> select max([thisdate])
> from
> (select a.date1 [thisdate]
> from table1 a
> unionselect b.date1 [thisdate]
> from table1 b
> unionselect c.date1 [thisdate]
> from table1 c)
> I cannot change the database structure, and I am hoping that a huge if
> statement can be avoided.
> Thanks
> Eric
>|||I am joining 6 different tables, and for each row that is selected, I need
to get the latest of the two dates along with Janurary 1 06 and return the
latest of the three dates mentioned as part of the query.
Unfortunately getting tables/data would be impractical, sorry.
Eric
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eZfHlamXGHA.1200@.TK2MSFTNGP03.phx.gbl...
> What is it that you're really trying to achieve? We're seeing cross joins
> over 3 tables + 1 derived table. Give us a spec + DDL + INSERT statements
> of sample data + desired results.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
> news:%23Yim%23VmXGHA.4988@.TK2MSFTNGP05.phx.gbl...
> Okay, this works great in the small test case, but now I am attempting to
> put it into my larger query, and it looks something similar to this
> select a.column1, a.column2, a.date
> ,b.column1, b.column2, b.date
> ,c.column1, c.column2
> ,max([thisdate])
> from
> table1 a
> ,table2 b
> ,table3 c
> ,(select a.date1 [thisdate]
> union
> select b.date1 [thisdate]
> union
> select c.date1 [thisdate]
> ) as x
> where
> ......
> And it states:
> The column prefix 'a' does not match with a table name or alias name used
> in
> the query
> The column prefix 'b' does not match with a table name or alias name used
> in
> the query
> The column prefix 'c' does not match with a table name or alias name used
> in
> the query
> if I put table1.date1 it gives a similar error
> Eric
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23gxFRBmXGHA.4120@.TK2MSFTNGP03.phx.gbl...
>|||Well, cross joins certainly aren't the answer. If you can't produce a
simplified DDL then we can't be of much help.
Help us help you.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
news:u9HCy3mXGHA.1200@.TK2MSFTNGP03.phx.gbl...
I am joining 6 different tables, and for each row that is selected, I need
to get the latest of the two dates along with Janurary 1 06 and return the
latest of the three dates mentioned as part of the query.
Unfortunately getting tables/data would be impractical, sorry.
Eric
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eZfHlamXGHA.1200@.TK2MSFTNGP03.phx.gbl...
> What is it that you're really trying to achieve? We're seeing cross joins
> over 3 tables + 1 derived table. Give us a spec + DDL + INSERT statements
> of sample data + desired results.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Eric Stott" <eric@.stottcreations_nospam.com> wrote in message
> news:%23Yim%23VmXGHA.4988@.TK2MSFTNGP05.phx.gbl...
> Okay, this works great in the small test case, but now I am attempting to
> put it into my larger query, and it looks something similar to this
> select a.column1, a.column2, a.date
> ,b.column1, b.column2, b.date
> ,c.column1, c.column2
> ,max([thisdate])
> from
> table1 a
> ,table2 b
> ,table3 c
> ,(select a.date1 [thisdate]
> union
> select b.date1 [thisdate]
> union
> select c.date1 [thisdate]
> ) as x
> where
> ......
> And it states:
> The column prefix 'a' does not match with a table name or alias name used
> in
> the query
> The column prefix 'b' does not match with a table name or alias name used
> in
> the query
> The column prefix 'c' does not match with a table name or alias name used
> in
> the query
> if I put table1.date1 it gives a similar error
> Eric
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23gxFRBmXGHA.4120@.TK2MSFTNGP03.phx.gbl...
>|||I would use a case expression:
case
when b.date1<=a.date1 and c.date1<=a.date1 then a.date1
when a.date1<=b.date1 and c.date1<=b.date1 then b.date1
when b.date1<=c.date1 and a.date1<=c.date1 then c.date1
end
BTW, as a rule of thumb, use UNION ALL, not UNION whenever possible -
usually performs better.|||CREATE TABLE C1(F DATETIME)
INSERT INTO C1 SELECT '2005-04-05'
CREATE TABLE C2(F DATETIME)
INSERT INTO C2 SELECT '2004-02-01'
CREATE TABLE C3(F DATETIME)
INSERT INTO C3 SELECT '2006-4-13'
SELECT MAX(X.F) AS F FROM
(
SELECT MAX(C1.F) AS F FROM C1
UNION ALL
SELECT MAX(C2.F) AS F FROM C2
UNION ALL
SELECT MAX(C3.F) AS F FROM C3
) X
DROP TABLE C1
DROP TABLE C2
DROP TABLE C3
"Eric Stott" wrote:

> I have a query that returns three different columns
> ex: select a.date1, b.date2, c.date3
> from table1 a,
> table2 b,
> table3 c
> I need to return the result of the largest of the dates
> I thought something like this would work, but it doesn't
> select max([thisdate])
> from
> (select a.date1 [thisdate]
> from table1 a
> unionselect b.date1 [thisdate]
> from table1 b
> unionselect c.date1 [thisdate]
> from table1 c)
> I cannot change the database structure, and I am hoping that a huge if
> statement can be avoided.
> Thanks
> Eric
>
>

Max Date for multiple columns

Hi all,
Okay... this should be a pretty question, but, can't seem to figure out
how to do it. In this database I'm working with, they have created 8 column
s
(Version1, Date1, Version2, Date2, Version3,Date3, Version4, Date4).
I need to get the max date (Date1, Date2, Date3 or Date4) and the
information from the appropriate column (So, if Date2 is the max date, then
return the result set containing Version2 and Date2 data)
Any idea about how to approach this problem?
Any help is greatly apprectiated.
DougSure...
CREATE TABLE [dbo].[TEST] (
[ACCOUNT] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SYSTEM1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PURCHASEDATE1] [datetime] NULL ,
[SYSTEM2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PURCHASEDATE2] [datetime] NULL ,
[SYSTEM3] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PURCHASEDATE3] [datetime] NULL ,
[SYSTEM4] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PURCHASEDATE4] [datetime] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
"Doug" wrote:

> Hi all,
> Okay... this should be a pretty question, but, can't seem to figure ou
t
> how to do it. In this database I'm working with, they have created 8 colu
mns
> (Version1, Date1, Version2, Date2, Version3,Date3, Version4, Date4).
> I need to get the max date (Date1, Date2, Date3 or Date4) and the
> information from the appropriate column (So, if Date2 is the max date, the
n
> return the result set containing Version2 and Date2 data)
> Any idea about how to approach this problem?
> Any help is greatly apprectiated.
> Doug|||I see several solutions
A. Self joins
B. Big if statement
or
C. Temp table
Create a temp table that has the record number, Version, and Date. Each
row represents a single Version / Date pair. Each row in your original
table would then become 4 rows in the temp table.
Then you could do
select recordid, max(datecol)
from tempTable
group by recordid|||>> Any idea about how to approach this problem?
The very fact that a simple query as this requires a complex solution itself
suggest that the table design could be improved. Rather than using column
names to represent data, consider something along the lines of:
CREATE TABLE tbl (
key_col ...
version ..
date_col DATETIME ) ;
This will allow you to add more versions without having to alter the schema.
Moreover the design is more flexible & allows for better constraint
enforcement as well.
If you are somehow forced to stick with the existing schema consider using a
view/ derived table to logically abstract the data like:
SELECT key_col,
CASE n WHEN 1 THEN version1
WHEN 2 THEN version2
WHEN 3 THEN version3
WHEN 4 THEN version4
END AS "version",
CASE n WHEN 1 THEN date1
WHEN 2 THEN date2
WHEN 3 THEN date3
WHEN 4 THEN date4
END AS "version_date"
FROM tbl, ( SELECT 1 UNION SELECT 2 UNION
SELECT 3 UNION SELECT 4 ) T ( n );
Now, it is just a matter of using aggregate function MAX() on the
version_date column to get the required value.
Anith|||On Mon, 20 Mar 2006 06:59:42 -0800, Doug wrote:

>Hi all,
> Okay... this should be a pretty question, but, can't seem to figure out
>how to do it. In this database I'm working with, they have created 8 colum
ns
>(Version1, Date1, Version2, Date2, Version3,Date3, Version4, Date4).
> I need to get the max date (Date1, Date2, Date3 or Date4) and the
>information from the appropriate column (So, if Date2 is the max date, then
>return the result set containing Version2 and Date2 data)
> Any idea about how to approach this problem?
Hi Doug,
Normalise your design. You should have a seperate table with Date and
Version as columns, plus a foreign key to the table where these 8
columns now are. Then, it's quite easy.
Assuming the normalised table looks like this
CREATE TABLE YourTable
(CustomerID int NOT NULL,
TheDate datetime NOT NULL,
Version varchar(20) NOT NULL,
PRIMARY KEY (CustomerID, TheDate),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
)
The query is like this
SELECT a.CustomerID, a.TheDate, a.Version
FROM YourTable AS a
INNER JOIN (SELECT CustomerID, MAX(TheDate) AS MaxDate
FROM YourTable
GROUP BY CustomerID) AS b
ON b.Customer = a.Customer
AND b.MaxDate = a.TheDate
Hugo Kornelis, SQL Server MVP|||Hi all,
Thanks for all of the help! Unfortunately, the design can't be
normalised. We are using the Goldmine application (commercial product) and
they designed the tables to work this way. This design has caused me a
number of headaches.
Overall, I went with a stored procedure to get the data into a
temporary table that was more normalized and then got my information using
standard techniques.
Dougie
"Hugo Kornelis" wrote:

> On Mon, 20 Mar 2006 06:59:42 -0800, Doug wrote:
>
> Hi Doug,
> Normalise your design. You should have a seperate table with Date and
> Version as columns, plus a foreign key to the table where these 8
> columns now are. Then, it's quite easy.
> Assuming the normalised table looks like this
> CREATE TABLE YourTable
> (CustomerID int NOT NULL,
> TheDate datetime NOT NULL,
> Version varchar(20) NOT NULL,
> PRIMARY KEY (CustomerID, TheDate),
> FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
> )
> The query is like this
> SELECT a.CustomerID, a.TheDate, a.Version
> FROM YourTable AS a
> INNER JOIN (SELECT CustomerID, MAX(TheDate) AS MaxDate
> FROM YourTable
> GROUP BY CustomerID) AS b
> ON b.Customer = a.Customer
> AND b.MaxDate = a.TheDate
> --
> Hugo Kornelis, SQL Server MVP
>

Max columns in subquery VIEW

When I try to query a view in a subquery, I experience
problems if the view returns more than 63 columns.
Ex: SELECT * FROM (SELECT * FROM X_View)
If X_View returns more than 63 fields, the result of the
entire query is empty.
Any hotfix/quickfix for this?
Stefan
Stefan
What do you mean by 'empty' ? If you return 62 columns does it mean that
you will get not empty result?
Also , with your code it will be generate a syntax error . Add alias to the
subquery.
SELECT * FROM (SELECT * FROM X_View) AS d
"Stefan Nilsson" <sen@.syscomworld.com> wrote in message
news:203b01c42776$8472b270$a301280a@.phx.gbl...
> When I try to query a view in a subquery, I experience
> problems if the view returns more than 63 columns.
> Ex: SELECT * FROM (SELECT * FROM X_View)
> If X_View returns more than 63 fields, the result of the
> entire query is empty.
> Any hotfix/quickfix for this?
> Stefan
|||Jeps .. if the VIEW returns 62 or 63 columns, everything works just
fine.
(Sorry ... I forgot to copy the alias part)
Stefan
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Max columns in subquery VIEW

When I try to query a view in a subquery, I experience
problems if the view returns more than 63 columns.
Ex: SELECT * FROM (SELECT * FROM X_View)
If X_View returns more than 63 fields, the result of the
entire query is empty.
Any hotfix/quickfix for this?
StefanStefan
What do you mean by 'empty' ? If you return 62 columns does it mean that
you will get not empty result?
Also , with your code it will be generate a syntax error . Add alias to the
subquery.
SELECT * FROM (SELECT * FROM X_View) AS d
"Stefan Nilsson" <sen@.syscomworld.com> wrote in message
news:203b01c42776$8472b270$a301280a@.phx.gbl...
> When I try to query a view in a subquery, I experience
> problems if the view returns more than 63 columns.
> Ex: SELECT * FROM (SELECT * FROM X_View)
> If X_View returns more than 63 fields, the result of the
> entire query is empty.
> Any hotfix/quickfix for this?
> Stefan|||Jeps .. if the VIEW returns 62 or 63 columns, everything works just
fine.
(Sorry ... I forgot to copy the alias part)
Stefan
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Max columns in subquery VIEW

When I try to query a view in a subquery, I experience
problems if the view returns more than 63 columns.
Ex: SELECT * FROM (SELECT * FROM X_View)
If X_View returns more than 63 fields, the result of the
entire query is empty.
Any hotfix/quickfix for this?
StefanStefan
What do you mean by 'empty' ? If you return 62 columns does it mean that
you will get not empty result?
Also , with your code it will be generate a syntax error . Add alias to the
subquery.
SELECT * FROM (SELECT * FROM X_View) AS d
"Stefan Nilsson" <sen@.syscomworld.com> wrote in message
news:203b01c42776$8472b270$a301280a@.phx.gbl...
> When I try to query a view in a subquery, I experience
> problems if the view returns more than 63 columns.
> Ex: SELECT * FROM (SELECT * FROM X_View)
> If X_View returns more than 63 fields, the result of the
> entire query is empty.
> Any hotfix/quickfix for this?
> Stefan|||Jeps .. if the VIEW returns 62 or 63 columns, everything works just
fine.
(Sorry ... I forgot to copy the alias part)
Stefan
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!sql

Max Column Total in SQL Server 2005

Hello,

Is there a maximum total of columns that SQL Server 2005 limits? I have a rather lengthy import file in regards to column count, and was wondering if there was a total number of columns that a table is limited to.

Thanks.

I think it is 255, I am not so sure

|||

please check this, it may help you.

http://msdn2.microsoft.com/en-us/library/ms143432.aspx

Max [date] from DIFFERENT columns?

Hi,
I have a table in which there are several date columns recording different
event points occuring to a record (e.g., date opened, date action-1, etc).
I need to find the most recent date (MAX(date-n)) from across all these
columns to compare with a final closure date.
Is there a (simple/sensible) mechanism that makes this possible?
I have considered using IF/ELSE to try and determine if one is later than
the other, but this seems like a no-go (too complex to implement sensibly).
CASE statement instead maybe?
Any pointers gratefully received...Thx
Al
Alec,
If you do not have a lot of columns, you can use the following approach:
Please let me kow if it helps...
-- BEGIN SCRIPT
declare @.table table
(RecordID int
, Created datetime
, Opened datetime
, Updated datetime
)
insert into @.table
values (1, getdate(), getdate()+ .10, getdate()+.15)
insert into @.table
values (2, getdate()+.3, getdate()+ .40, getdate()+.45)
-- Preview of the table
select * from @.table
-- Actual query
select RecordID
, MAX(ActionDate) LatestActionDate
from(
select RecordId
, Created ActionDate
, 'Created' Action
from @.table
union
select RecordId
, Opened ActionDate
, 'Opened' Action
from @.table
union
select RecordId
, Updated ActionDate
, 'Updated' Action
from @.table
) t1
group by RecordID
-- END SCRIPT
"Alec MacLean" wrote:

> Hi,
> I have a table in which there are several date columns recording different
> event points occuring to a record (e.g., date opened, date action-1, etc).
> I need to find the most recent date (MAX(date-n)) from across all these
> columns to compare with a final closure date.
> Is there a (simple/sensible) mechanism that makes this possible?
> I have considered using IF/ELSE to try and determine if one is later than
> the other, but this seems like a no-go (too complex to implement sensibly).
> CASE statement instead maybe?
> Any pointers gratefully received...Thx
> Al
>
>

Max [date] from DIFFERENT columns?

Hi,
I have a table in which there are several date columns recording different
event points occuring to a record (e.g., date opened, date action-1, etc).
I need to find the most recent date (MAX(date-n)) from across all these
columns to compare with a final closure date.
Is there a (simple/sensible) mechanism that makes this possible?
I have considered using IF/ELSE to try and determine if one is later than
the other, but this seems like a no-go (too complex to implement sensibly).
CASE statement instead maybe?
Any pointers gratefully received...Thx
AlAlec,
If you do not have a lot of columns, you can use the following approach:
Please let me kow if it helps...
-- BEGIN SCRIPT
declare @.table table
(RecordID int
, Created datetime
, Opened datetime
, Updated datetime
)
insert into @.table
values (1, getdate(), getdate()+ .10, getdate()+.15)
insert into @.table
values (2, getdate()+.3, getdate()+ .40, getdate()+.45)
-- Preview of the table
select * from @.table
-- Actual query
select RecordID
, MAX(ActionDate) LatestActionDate
from (
select RecordId
, Created ActionDate
, 'Created' Action
from @.table
union
select RecordId
, Opened ActionDate
, 'Opened' Action
from @.table
union
select RecordId
, Updated ActionDate
, 'Updated' Action
from @.table
) t1
group by RecordID
-- END SCRIPT
"Alec MacLean" wrote:

> Hi,
> I have a table in which there are several date columns recording different
> event points occuring to a record (e.g., date opened, date action-1, etc).
> I need to find the most recent date (MAX(date-n)) from across all these
> columns to compare with a final closure date.
> Is there a (simple/sensible) mechanism that makes this possible?
> I have considered using IF/ELSE to try and determine if one is later than
> the other, but this seems like a no-go (too complex to implement sensibly)
.
> CASE statement instead maybe?
> Any pointers gratefully received...Thx
> Al
>
>

Max [date] from DIFFERENT columns?

Hi,
I have a table in which there are several date columns recording different
event points occuring to a record (e.g., date opened, date action-1, etc).
I need to find the most recent date (MAX(date-n)) from across all these
columns to compare with a final closure date.
Is there a (simple/sensible) mechanism that makes this possible?
I have considered using IF/ELSE to try and determine if one is later than
the other, but this seems like a no-go (too complex to implement sensibly).
CASE statement instead maybe?
Any pointers gratefully received...Thx
AlAlec,
If you do not have a lot of columns, you can use the following approach:
Please let me kow if it helps...
-- BEGIN SCRIPT
declare @.table table
(RecordID int
, Created datetime
, Opened datetime
, Updated datetime
)
insert into @.table
values (1, getdate(), getdate()+ .10, getdate()+.15)
insert into @.table
values (2, getdate()+.3, getdate()+ .40, getdate()+.45)
-- Preview of the table
select * from @.table
-- Actual query
select RecordID
, MAX(ActionDate) LatestActionDate
from (
select RecordId
, Created ActionDate
, 'Created' Action
from @.table
union
select RecordId
, Opened ActionDate
, 'Opened' Action
from @.table
union
select RecordId
, Updated ActionDate
, 'Updated' Action
from @.table
) t1
group by RecordID
-- END SCRIPT
"Alec MacLean" wrote:
> Hi,
> I have a table in which there are several date columns recording different
> event points occuring to a record (e.g., date opened, date action-1, etc).
> I need to find the most recent date (MAX(date-n)) from across all these
> columns to compare with a final closure date.
> Is there a (simple/sensible) mechanism that makes this possible?
> I have considered using IF/ELSE to try and determine if one is later than
> the other, but this seems like a no-go (too complex to implement sensibly).
> CASE statement instead maybe?
> Any pointers gratefully received...Thx
> Al
>
>sql

Max # of columns in Excel Export

I am exporting a report which consists of one simple table. The data is
simply exported to excel.
I am noticing that if I add more than 65 columns to the report (i.e. 66 or
more) then, after exporting to excel, there is a problem re-sizing the
columns in excel. For some reason the data automatically gets a line wrap
character within the cell, and double-clicking the column to resize it
effectively does nothing.
Specifically, my data in column #34 looks like:
"Here is a really really really long sentence." <- there are no carriage
returns
If you double click on the cell the data goes into, in Excel, it ends up
displaying like:
"Here is a" <--
"really really" <-- all of this is in ONE cell in the spreadsheet.
"really long" <--
"sentence" <--
How can I overcome this? Or how can I change how the expansion is handled
in Excel?
Thanks!
DavidMore specifically, if I have more than 65 columns, then the "wrap text"
checkbox is checked (and applied) for all columns/rows in Excel. If I have
65 or less, it is not.
"david boardman" wrote:
> I am exporting a report which consists of one simple table. The data is
> simply exported to excel.
> I am noticing that if I add more than 65 columns to the report (i.e. 66 or
> more) then, after exporting to excel, there is a problem re-sizing the
> columns in excel. For some reason the data automatically gets a line wrap
> character within the cell, and double-clicking the column to resize it
> effectively does nothing.
> Specifically, my data in column #34 looks like:
> "Here is a really really really long sentence." <- there are no carriage
> returns
> If you double click on the cell the data goes into, in Excel, it ends up
> displaying like:
> "Here is a" <--
> "really really" <-- all of this is in ONE cell in the spreadsheet.
> "really long" <--
> "sentence" <--
> How can I overcome this? Or how can I change how the expansion is handled
> in Excel?
> Thanks!
> David|||> Or how can I change how the expansion is handled in Excel?
David,
In Excel, hit Ctrl-A to select the entire active range then go to
Format/Cells.../Alignment and uncheck 'Wrap text'.
HTH,
Chris|||Hi Chris - thanks for the reply. I'm curious as to why this happens with
more than 65 columns of data. It's just an annoying extra little step that
the receivers of this report have to do. If at all possible I'd like to
eliminate it as, like I said, this doesn't happen with less columns of data.
Strange...
Dave
"Chris" wrote:
> > Or how can I change how the expansion is handled in Excel?
> David,
> In Excel, hit Ctrl-A to select the entire active range then go to
> Format/Cells.../Alignment and uncheck 'Wrap text'.
> HTH,
> Chris|||Yeah, I would agree that it is annoying and odd that it only happens when
there are more than 65 columns in the output. Sorry I can't be of more help.
Chris

Matriz SQL

Hi, I have the following problem
In a DB exist this inf:
(I can use many tables... and/or many columns)
-->
1 2 3 4
5 6 7 8
9 a b c
d e f g
<--
In need write a select sentence that move in "circle" all info, for
example
5 1 2 3
9 a 6 4
d b 7 8
e f g cDoes this help:
http://spaces.msn.com/drsql/Blog/cns!80677FB08B3162E4!908.entry
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Alejandro" <jalejandro0211@.gmail.com> wrote in message
news:1148327031.695609.65500@.g10g2000cwb.googlegroups.com...
> Hi, I have the following problem
> In a DB exist this inf:
> (I can use many tables... and/or many columns)
> -->
> 1 2 3 4
> 5 6 7 8
> 9 a b c
> d e f g
> <--
> In need write a select sentence that move in "circle" all info, for
> example
> 5 1 2 3
> 9 a 6 4
> d b 7 8
> e f g c
>|||What is your table structure and how are you getting this output to begin
with?
Post DDL and an explanation of how the original data is generated/selected.
http://www.aspfaq.com/etiquette.asp?id=5006
Also, this sounds like a class assignment. If so, what sort of class is it?
"Alejandro" <jalejandro0211@.gmail.com> wrote in message
news:1148327031.695609.65500@.g10g2000cwb.googlegroups.com...
> Hi, I have the following problem
> In a DB exist this inf:
> (I can use many tables... and/or many columns)
> -->
> 1 2 3 4
> 5 6 7 8
> 9 a b c
> d e f g
> <--
> In need write a select sentence that move in "circle" all info, for
> example
> 5 1 2 3
> 9 a 6 4
> d b 7 8
> e f g c
>|||the DB has not been created by now, the table structure can be anyone,
4 tables, 1 table/ 1 columns, 1 table 16 columns, etc.
Not class assignment.|||Can you explain the situation/application for this logic? It will certainly
help in determining a valid approach. Also, I am very curious as to how
this might be useful in a real world situation.
Anyway, if your matrix will always be 4x4, you can try this table setup. It
involves two tables, one storing the data with its original location in the
matrix (as columns and rows) and a second storing the matrix shift (original
cell and new cell). We use a case statement with an agregate function to
produce the matrix, and we join to the matrix shift table to determine the
new location. To shift the cells more than once we could join to the matrix
shift table as many times as we need to rotate the matrix values. We can
permanently update the values at each position in the matrix if needed.
Lastly, we could change the Value stored in the Data table to a FK pointing
to another table with as many columns as we need. This would still really
only work a column at a time, but you could use any column you wanted in the
matrix.
On SQL 2005, you could probably use the pivot/unpivot functions to
accomplish this.
/*
Create our table/view structure which has data in rows and columns (matrix
format)
*/
CREATE TABLE #tmpData (ColNum integer, RowNum Integer, Value varchar(10)
primary key(ColNum,RowNum))
go
INSERT #tmpData(ColNum,RowNum,Value) VALUES (1,1,'A');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (2,1,'B');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (3,1,'C');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (4,1,'D');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (1,2,'E');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (2,2,'F');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (3,2,'G');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (4,2,'H');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (1,3,'I');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (2,3,'J');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (3,3,'K');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (4,3,'L');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (1,4,'M');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (2,4,'N');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (3,4,'O');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (4,4,'P');
go
/*
Display data in the matrix format
*/
select
max(case when ColNum=1 then Value end) as Col1
,max(case when ColNum=2 then Value end) as Col2
,max(case when ColNum=3 then Value end) as Col3
,max(case when ColNum=4 then Value end) as Col4
from #tmpData
group by RowNum;
/*
Create our matrix mapping, showing how the matrix cells will move
*/
Create table #tmpMatrixShift
(ColNum1 integer,RowNum1 integer,ColNum2 integer,RowNum2 integer);
go
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(1,1,2,1);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(2,1,3,1);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(3,1,4,1);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(4,1,4,2);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(4,2,4,3);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(4,3,4,4);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(4,4,3,4);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(3,4,2,4);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(2,4,1,4);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(1,4,1,3);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(1,3,1,2);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(1,2,1,1);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(2,2,3,2);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(3,2,3,3);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(2,3,2,2);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(3,3,2,3);
go
/*
Join our data table to the matrix mapping table in order to get the new cell
locations
Display data in the matrix format
*/
select
max(case when b.ColNum2=1 then a.Value end) as Col1
,max(case when b.ColNum2=2 then a.Value end) as Col2
,max(case when b.ColNum2=3 then a.Value end) as Col3
,max(case when b.ColNum2=4 then a.Value end) as Col4
from #tmpData a
inner join #tmpMatrixShift b
on a.colnum = b.colnum1
and a.rownum = b.rownum1
group by b.RowNum2;
DROP TABLE #tmpData;
DROP TABLE #tmpMatrixShift;
"Alejandro" <jalejandro0211@.gmail.com> wrote in message
news:1148328455.125472.120050@.j55g2000cwa.googlegroups.com...
> the DB has not been created by now, the table structure can be anyone,
> 4 tables, 1 table/ 1 columns, 1 table 16 columns, etc.
> Not class assignment.
>|||I have a program in the office (very very old) don't have objects of
this program, but this take a sentence select from .ini to execute,
this program transform a values returned (contability) and calculate
some, I can modify that select to --select transformed tables...
=BF?...
Well I do this
CREATE TABLE "MATRIX" ( COL1 VARCHAR(10) NOT NULL ,
COL2 VARCHAR(10) NOT NULL ,
COL3 VARCHAR(10) NOT NULL ,
COL4 VARCHAR(10) NOT NULL );
insert into matrix values ('1','2','3','4');
insert into matrix values ('5','6','7','8');
insert into matrix values ('9','10','11','12');
insert into matrix values ('13','14','15','16');
now?|||Have you tried the approach I posted? As long as you are fixed at 4 columns
and 4 rows in the matrix, I believe it should do what you want. However,
some of the more math-intensive folks may be able to come up with an
algorithm that is more effective.
"Alejandro" <jalejandro0211@.gmail.com> wrote in message
news:1148333584.761203.102270@.j73g2000cwa.googlegroups.com...
I have a program in the office (very very old) don't have objects of
this program, but this take a sentence select from .ini to execute,
this program transform a values returned (contability) and calculate
some, I can modify that select to --select transformed tables...
?...
Well I do this
CREATE TABLE "MATRIX" ( COL1 VARCHAR(10) NOT NULL ,
COL2 VARCHAR(10) NOT NULL ,
COL3 VARCHAR(10) NOT NULL ,
COL4 VARCHAR(10) NOT NULL );
insert into matrix values ('1','2','3','4');
insert into matrix values ('5','6','7','8');
insert into matrix values ('9','10','11','12');
insert into matrix values ('13','14','15','16');
now?|||The link I posted shows a pretty interesting method as well... But it looks
like the OP just wants the answer handed to him without investing any of his
own thought.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%232JeHYpfGHA.4864@.TK2MSFTNGP05.phx.gbl...
> Have you tried the approach I posted? As long as you are fixed at 4
> columns
> and 4 rows in the matrix, I believe it should do what you want. However,
> some of the more math-intensive folks may be able to come up with an
> algorithm that is more effective.
> "Alejandro" <jalejandro0211@.gmail.com> wrote in message
> news:1148333584.761203.102270@.j73g2000cwa.googlegroups.com...
> I have a program in the office (very very old) don't have objects of
> this program, but this take a sentence select from .ini to execute,
> this program transform a values returned (contability) and calculate
> some, I can modify that select to --select transformed tables...
> ?...
> Well I do this
> CREATE TABLE "MATRIX" ( COL1 VARCHAR(10) NOT NULL ,
> COL2 VARCHAR(10) NOT NULL ,
> COL3 VARCHAR(10) NOT NULL ,
> COL4 VARCHAR(10) NOT NULL );
> insert into matrix values ('1','2','3','4');
> insert into matrix values ('5','6','7','8');
> insert into matrix values ('9','10','11','12');
> insert into matrix values ('13','14','15','16');
> now?
>|||Your link looked more like a cross tab solution, flipping the columns and
rows. I tried to apply it to this situation, but couldn't think of how to
do it, since the cells are being rotated rather than flipped. I am still
trying to think of a valid application for this sort of thing...
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23$qSMGqfGHA.1456@.TK2MSFTNGP04.phx.gbl...
> The link I posted shows a pretty interesting method as well... But it
looks
> like the OP just wants the answer handed to him without investing any of
his
> own thought.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:%232JeHYpfGHA.4864@.TK2MSFTNGP05.phx.gbl...
However,
>

Matrix: static column which calculates from dynamic column

Hello,
I have a matrix for Turnover that looks like this:
Rows: Department
Columns: Status
Data: Count of employees
It runs beautifully to look like this:
Department Active Terminated
________________________________
Cleaners 6 2
Maintenance 5 1
Painters 4 0
TOTAL 15 3
I would like to add another column to take the # of Active employees
and divide it by the number of Terminated Employees to look like this:
Department Active Terminated Turnover
___________________________________________
Cleaners 6 2 33%
Maintenance 5 1 20%
Painters 4 0 0%
TOTAL 15 3 20%
Does anyone know how I can do that, if possible?
Thank you!
MichelleTo add a new column right click on the last column of the table header
and select *Insert column to the right*. Next, right click on the new
cell, select *Expression*, in the text box on the right enter:
=Fields!Active.Value / Fields.Terminated.Value,
and click ok. Next, right click on the new cell again and this time
select *Properties*, in the Format section on the right select
percentage, click ok and you are done!|||Hi Patrick,
Thank you for your quick reply, unfortunately this doesn't work in my
situation. I cannot have an expression of "=Fields!Active.Value /
Fields.Terminated.Value" because these fields do not exist. There is 1
field called Status which can be either Active or Inactive. Status is
a dynamic colunm on my matrix.
Thank you,
Michelle|||Then reference the value of the textbox,
i.e. =ReportItems!active.Value / ReportItems!terminated.Value|||Add a column to the right, then type = (Fields!Terminated.Value) /
(Fields!Active.Value) in the textbox expression. You may have to format the
percentage by right click the mouse and Select "Properties" and choose
percentage.
or = sum(Fields!Terminated.Value) / sum(Fields!Active.Value).
If you in matrix report, then the formula won't work. you have to
use difference function like the "InScope function" More information is
available at
http://msdn.microsoft.com/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp
Good luck!
--
This posting is provided "AS IS" with no warranties, and confers no rights
"Michelle@.bwalk.com" wrote:
> Hello,
> I have a matrix for Turnover that looks like this:
>
> Rows: Department
> Columns: Status
> Data: Count of employees
>
> It runs beautifully to look like this:
>
> Department Active Terminated
> ________________________________
> Cleaners 6 2
> Maintenance 5 1
> Painters 4 0
> TOTAL 15 3
>
> I would like to add another column to take the # of Active employees
> and divide it by the number of Terminated Employees to look like this:
>
> Department Active Terminated Turnover
> ___________________________________________
> Cleaners 6 2 33%
> Maintenance 5 1 20%
> Painters 4 0 0%
> TOTAL 15 3 20%
>
> Does anyone know how I can do that, if possible?
>
> Thank you!
> Michelle
>

MATRIX: Need different count of cols in (Sub)total

Please need help!
How to do, to have different count of columns in (Sub)-Total?
eg: The normal (non-subtotal)-Column should only show one value. eg: a count
of something.
In the Subtotal there should be a Sum of this count-field AND a second col
with eg a percentage of this sum to the sum-over-all.
As asked a week before.
IS THIS POSSIBLE '?
Need Help.
--
LG HOLANThe trick is using the InScope function. InScope works with groups and
datasets.
The following expression will check which "part" of the matrix your are:
=IIF(
InScope("matrix1_Time_Year"),
IIF(
InScope("matrix1_Time_Month"),
Fields!Measures_Store_Sales.Value,
sum(cint( Fields!Measures_Store_Sales.Value))
),
avg(cint(Fields!Measures_Store_Sales.Value)))
matrix1_Time_Year is a column group, matrix1_Time_Month is a row group.
In your case, you probably want something a bit more simple, like
=IIF(InScope("ColGroup"), SUM(fields!MyValue.Value"), fields!MyValue.Value)
The IIF(InScope("ColGroup") checks if you are in the subtotal of the column
group. If you are, it will sum your fields. If you're in the detail, it will
ounly show the field.
The following code is a small matrix with data from the Foodmart 2000 OLAP
cube, where the cells are filled according to my first expression.
Kaisa M. Lindahl Lervik
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<RightMargin>2.5cm</RightMargin>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>2.53968cm</Height>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="Measures_Store_Sales">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>N0</Format>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>Measures_Store_Sales</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=IIF(
InScope("matrix1_Time_Year"),
IIF(
InScope("matrix1_Time_Month"),
Fields!Measures_Store_Sales.Value,
sum(cint( Fields!Measures_Store_Sales.Value))
),
avg(cint(Fields!Measures_Store_Sales.Value)))</Value>
</Textbox>
</ReportItems>
</MatrixCell>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>P0</Format>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>= Fields!Measures_Store_Cost.Value/
Fields!Measures_Store_Sales.Value</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.63492cm</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>2.53968cm</Width>
</MatrixColumn>
<MatrixColumn>
<Width>2.53968cm</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>DataSet1</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="matrix1_Time_Year">
<GroupExpressions>
<GroupExpression>=Fields!Time_Year.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="Time_Year">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Center</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>Time_Year</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Time_Year.Value</Value>
</Textbox>
</ReportItems>
<Subtotal>
<Style>
<BorderStyle>
<Left>Solid</Left>
</BorderStyle>
</Style>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Left>Solid</Left>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Total</Value>
</Textbox>
</ReportItems>
</Subtotal>
</DynamicColumns>
<Height>0.63492cm</Height>
</ColumnGrouping>
<ColumnGrouping>
<Height>0.63492cm</Height>
<StaticColumns>
<StaticColumn>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Left>Solid</Left>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Measures Store Sales</Value>
</Textbox>
</ReportItems>
</StaticColumn>
<StaticColumn>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>%</Value>
</Textbox>
</ReportItems>
</StaticColumn>
</StaticColumns>
</ColumnGrouping>
</ColumnGroupings>
<Width>12.6984cm</Width>
<Top>1.75cm</Top>
<Left>1cm</Left>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_Time_Month">
<GroupExpressions>
<GroupExpression>=Fields!Time_Month.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="Time_Month">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Right>None</Right>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>Time_Month</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Time_Month.Value</Value>
</Textbox>
</ReportItems>
<Subtotal>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Total</Value>
</Textbox>
</ReportItems>
</Subtotal>
</DynamicRows>
<Width>2.53968cm</Width>
</RowGrouping>
</RowGroupings>
</Matrix>
</ReportItems>
<Style />
<Height>5cm</Height>
<ColumnSpacing>1cm</ColumnSpacing>
</Body>
<TopMargin>2.5cm</TopMargin>
<DataSources>
<DataSource Name="FoodMart 2000">
<rd:DataSourceID>dc66e45a-32ae-46c1-a7c7-228bd141338c</rd:DataSourceID>
<DataSourceReference>FoodMart 2000</DataSourceReference>
</DataSource>
</DataSources>
<Width>16cm</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="Time_Year">
<DataField>[Time].[Year].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Time_Quarter">
<DataField>[Time].[Quarter].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Time_Month">
<DataField>[Time].[Month].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Measures_Store_Cost">
<DataField>[Measures].[Store Cost]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
<Field Name="Measures_Store_Sales">
<DataField>[Measures].[Store Sales]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>FoodMart 2000</DataSourceName>
<CommandText>with set MonBud as
'{[Time].[1997].[Q3].[7]:[Time].[1997].[Q3].[7].lag(3),
[Time].[1998].[Q3].[7]:[Time].[1998].[Q3].[7].lag(3)}'
Cell Calculation [ForceNull] for '(Measures.AllMembers)' as '0', CONDITION ='IsEmpty(CalculationpassValue(Measures.CurrentMember, -1, RELATIVE))'
select
{[Measures].[Store Cost],[Measures].[Store Sales]} on columns,
{MonBud} on rows
from [Sales]</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>2.5cm</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<PageHeight>29.7cm</PageHeight>
<rd:DrawGrid>true</rd:DrawGrid>
<PageWidth>21cm</PageWidth>
<rd:ReportID>df4660d2-024e-426b-8c3a-d23ae01c8e60</rd:ReportID>
<BottomMargin>2.5cm</BottomMargin>
<Language>en-US</Language>
</Report>
"holan" <holan@.noemaol.noemail> wrote in message
news:6A8B3192-1978-4C76-82F0-49965BB847B8@.microsoft.com...
> Please need help!
> How to do, to have different count of columns in (Sub)-Total?
> eg: The normal (non-subtotal)-Column should only show one value. eg: a
> count
> of something.
> In the Subtotal there should be a Sum of this count-field AND a second col
> with eg a percentage of this sum to the sum-over-all.
> As asked a week before.
> IS THIS POSSIBLE '?
> Need Help.
> --
> LG HOLAN
>|||Thank you for this trick!
I know this trick from you from other postings you have done in this forum.
This works fine and was a great help for me.
BUT I can not solve my problem with this.
(I could not find a clean way)
To use this Inscope in the Value-Property will work.
But I need a way to have a different Width depending, if the cell is InScope
of
the Subtotal or not.
Or mutch better to have 2 Colums (a static group under the databound
Col-Group) for eg: Count and Percent. And to hide the Percent-Col if it is
not in the Scope of the Subtotal.
So I can habe a lot of Cols with only a few Subtotals, and only in the
Subtotal I have also (additional to the Sum(Count)) a Percent-Value, and use
ONLY the place I need.
If I can't hide the Percent-Col in not-Subtotal-Scope, I can hide the
content in this Col (with InScope) but this will use to much space in the
width, if I have a lot of cols. This is my current workaround. This with
right-border-color = white in the left col and left-border-color=white in the
right col will simulate one big col, that show in non-subtotal-scope only one
value (the Count) and in subtotal-scope the count and the percent-value.
But this is not a clean way. And it waste a lot of space in the report.
We have tested the same scenario with crystal-reports (we had an old version
inhouse). There its very simple to have in the subtotal-area a different
width and to place there a different count of Textboxes (that will be cols
then).
BUT crystal-report will not be an alternative to us.
We are a new MS ISV and want to use this tools from one hand.
ReportViewer (with local and remote, with WinForms and WebForms) will be the
future.
I think that MAYBE this is a design-weakness of the current version of
ReportViewer/ReportService. (I hope I am wrong and that someone have an other
trick). But if is not possible by design, I hope that MS will find a clean
solution for the next release. Maybe YOU have a good channel to MS to place
this wish, or maybe you can give me the info where to place such a wish, so
that it will also been heared.
LG HOLAN
"Kaisa M. Lindahl Lervik" wrote:
> The trick is using the InScope function. InScope works with groups and
> datasets.|||Not quite sure if I did understand everything you want to, so this is a
fairly general reply.
I think I've read that you can't have dynamic column widths in neither the
2000 or the 2005 edition. I've tried to use the increase / decrease textbox
parameters before, but no luck. You might want to try it though. As long as
the report is rendered as HTML, you are sort of limited to what is possible
in generic HTML. So you can do different expressions for showing and hiding
columns, but if you've already showed a column, it will "be there", visible
or not, in the whole container.
Kaisa M. Lindahl Lervik
"holan" <holan@.noemaol.noemail> wrote in message
news:42646D94-5CA2-4BA4-9A56-819F01CCBAE7@.microsoft.com...
> Thank you for this trick!
> I know this trick from you from other postings you have done in this
> forum.
> This works fine and was a great help for me.
> BUT I can not solve my problem with this.
> (I could not find a clean way)
> To use this Inscope in the Value-Property will work.
> But I need a way to have a different Width depending, if the cell is
> InScope
> of
> the Subtotal or not.
> Or mutch better to have 2 Colums (a static group under the databound
> Col-Group) for eg: Count and Percent. And to hide the Percent-Col if it is
> not in the Scope of the Subtotal.
> So I can habe a lot of Cols with only a few Subtotals, and only in the
> Subtotal I have also (additional to the Sum(Count)) a Percent-Value, and
> use
> ONLY the place I need.
> If I can't hide the Percent-Col in not-Subtotal-Scope, I can hide the
> content in this Col (with InScope) but this will use to much space in the
> width, if I have a lot of cols. This is my current workaround. This with
> right-border-color = white in the left col and left-border-color=white in
> the
> right col will simulate one big col, that show in non-subtotal-scope only
> one
> value (the Count) and in subtotal-scope the count and the percent-value.
> But this is not a clean way. And it waste a lot of space in the report.
> We have tested the same scenario with crystal-reports (we had an old
> version
> inhouse). There its very simple to have in the subtotal-area a different
> width and to place there a different count of Textboxes (that will be cols
> then).
> BUT crystal-report will not be an alternative to us.
> We are a new MS ISV and want to use this tools from one hand.
> ReportViewer (with local and remote, with WinForms and WebForms) will be
> the
> future.
> I think that MAYBE this is a design-weakness of the current version of
> ReportViewer/ReportService. (I hope I am wrong and that someone have an
> other
> trick). But if is not possible by design, I hope that MS will find a clean
> solution for the next release. Maybe YOU have a good channel to MS to
> place
> this wish, or maybe you can give me the info where to place such a wish,
> so
> that it will also been heared.
>
> --
> LG HOLAN
>
> "Kaisa M. Lindahl Lervik" wrote:
>> The trick is using the InScope function. InScope works with groups and
>> datasets.
>|||Hi Kaisa,
Maybe not relative to your comments, but we have an issue to hide a Matrix
column in a report in runtime. So user can select an option on report and we
hide a columnl. Do you have any advice?
Thanks,
Masoud
"Kaisa M. Lindahl Lervik" <kaisaml@.hotmail.com> wrote in message
news:%23HUhnc4YGHA.4144@.TK2MSFTNGP04.phx.gbl...
> Not quite sure if I did understand everything you want to, so this is a
> fairly general reply.
> I think I've read that you can't have dynamic column widths in neither the
> 2000 or the 2005 edition. I've tried to use the increase / decrease
textbox
> parameters before, but no luck. You might want to try it though. As long
as
> the report is rendered as HTML, you are sort of limited to what is
possible
> in generic HTML. So you can do different expressions for showing and
hiding
> columns, but if you've already showed a column, it will "be there",
visible
> or not, in the whole container.
> Kaisa M. Lindahl Lervik
>
> "holan" <holan@.noemaol.noemail> wrote in message
> news:42646D94-5CA2-4BA4-9A56-819F01CCBAE7@.microsoft.com...
> > Thank you for this trick!
> >
> > I know this trick from you from other postings you have done in this
> > forum.
> > This works fine and was a great help for me.
> >
> > BUT I can not solve my problem with this.
> > (I could not find a clean way)
> > To use this Inscope in the Value-Property will work.
> > But I need a way to have a different Width depending, if the cell is
> > InScope
> > of
> > the Subtotal or not.
> > Or mutch better to have 2 Colums (a static group under the databound
> > Col-Group) for eg: Count and Percent. And to hide the Percent-Col if it
is
> > not in the Scope of the Subtotal.
> > So I can habe a lot of Cols with only a few Subtotals, and only in the
> > Subtotal I have also (additional to the Sum(Count)) a Percent-Value, and
> > use
> > ONLY the place I need.
> > If I can't hide the Percent-Col in not-Subtotal-Scope, I can hide the
> > content in this Col (with InScope) but this will use to much space in
the
> > width, if I have a lot of cols. This is my current workaround. This with
> > right-border-color = white in the left col and left-border-color=white
in
> > the
> > right col will simulate one big col, that show in non-subtotal-scope
only
> > one
> > value (the Count) and in subtotal-scope the count and the percent-value.
> > But this is not a clean way. And it waste a lot of space in the report.
> > We have tested the same scenario with crystal-reports (we had an old
> > version
> > inhouse). There its very simple to have in the subtotal-area a different
> > width and to place there a different count of Textboxes (that will be
cols
> > then).
> > BUT crystal-report will not be an alternative to us.
> > We are a new MS ISV and want to use this tools from one hand.
> > ReportViewer (with local and remote, with WinForms and WebForms) will be
> > the
> > future.
> > I think that MAYBE this is a design-weakness of the current version of
> > ReportViewer/ReportService. (I hope I am wrong and that someone have an
> > other
> > trick). But if is not possible by design, I hope that MS will find a
clean
> > solution for the next release. Maybe YOU have a good channel to MS to
> > place
> > this wish, or maybe you can give me the info where to place such a wish,
> > so
> > that it will also been heared.
> >
> >
> > --
> > LG HOLAN
> >
> >
> > "Kaisa M. Lindahl Lervik" wrote:
> >
> >> The trick is using the InScope function. InScope works with groups and
> >> datasets.
> >
>|||"Kaisa M. Lindahl Lervik" wrote:
> I think I've read that you can't have dynamic column widths in neither the
> 2000 or the 2005 edition. I've tried to use the increase / decrease textbox
> parameters before, but no luck. You might want to try it though. As long as
> the report is rendered as HTML, you are sort of limited to what is possible
> in generic HTML. So you can do different expressions for showing and hiding
> columns, but if you've already showed a column, it will "be there", visible
> or not, in the whole container.
I want to show 2 cols in the subtotal (Count and Percent of this Count to
Count of this RowGroup)
AND I want in all ColGroups, that are not in the Scope of Subtotal, ONLY 1
Col (the Count but not the Percent).
I am sure that this is not a problem of HTML-rendering.
Of Course if you have some cell in a col that is visible then the whole col
will be shown. But I the case of not-subtotal-cols I want to hide the whole
col of percent. So the HTML-renderer could hide the whole col and this will
mean that only had to produce NO tags for this col to hide. I have tested it
with the Crystal-Report from VS2005 and there it is possible to have
different counts of cols in the scope of subtotal and not-subtotal. So it
could not be an issue of HTML-rendering.
But we could not (will not) use crystal, because we think that
MS-ReportService will be the future.
Here again my text of posting before (where know one aswers), that will have
more details:
I need something like this:
I Col1 I Col2 I Col3 I ColSum+% I
Row1 I 10 I 20 I 30 I 60 28% I
Row2 I 40 I 50 I 60 I 150 72% I
RowSum I 50 I 70 I 90 I 210 100% I
OR better
I Col1 I Col2 I Col3 I Total I
I Count I Count I Count I Count I % I
Row1 I 10 I 20 I 30 I 60 I 28% I
Row2 I 40 I 50 I 60 I 150 I 72% I
RowSum I 50 I 70 I 90 I 210 I 100% I
So I need a Subtotal with a greater Width like the normal Col.
Because there could be a lot of Cols. If I make the Width of the Col great
enough
that it will hold the 2 Values, then the Report would be to width to print
on one page.
Now I have a formular in the Value-Property like this:
=IIF(InScope("RowGroupName") AND InScope("ColGroupName"),
FormatNumber(Fields!ItemCount.Value,0,False,False,True),
FormatNumber(Sum(Fields!ItemCount.Value),0,True,False,True)
& Chr(13) & Chr(10)
& FormatNumber(Sum(Fields!ItemCount.Value) * 100 /
Sum(Fields!ItemCount.Value, "RowGroupName"),1,True,False,False )
& "%"
)
This is my workaround. Its similar to that what I need.
But the 2 Values in the Subtotal will be placed in 2 rows.
So the Width for the Col/Subtotal could be smaller.
BUT then the Height of the Report will be greater then it must be.
I don´t find a way to have different Width for Subtotal and normal Cols.
In the Properties for Subtotal (green triangle) there is no Width.
In the Properties for the TextBox of the Subtotal there will be a Width but
I could not set a value. (It will always change back to the value of the
Width of
the normal Col)
I also tried to make 2nd Detail-Cols and to show only the second Col if in
scope
of a Subtotal. This would be a nice solution, because of the Headers and
the Subtotal would have 2 seperate Cols instead of 2 values in one col.
I tried this with Visibility.Hidden but this will only hide the content of
this col
but not the whole Col.
I tried to set the Width of the second col with IIF(InScope..., "0pt",
"10pt").
But a formular is not allowed there.
PLEASE has anyone a trick to do this!
Or is this a design-limitation of MS?
So if this is a design-limitation, then this will be a GREAT WISH
for the next release. I hope MS will read this.
--
LG HOLAN

Matrix won't collapse

I have a Matrix displaying my OLAP based dataset. The dataset looks correct
and the MAtrix displays properly with all rows and columns expanded.
I don't have the ability to collapse or expand any of the rows or columns.
No + - icon displays either.
I have tried changing settings for the initial display of columns as
collapsed and expanded.
Any ideas on the problem?
JimOK... I got it.
The visibility needs to be set on the groups.
Jim
"Jim L" <jim@.noaddress.com> wrote in message
news:uwYeUKXrEHA.1204@.TK2MSFTNGP12.phx.gbl...
>I have a Matrix displaying my OLAP based dataset. The dataset looks correct
>and the MAtrix displays properly with all rows and columns expanded.
> I don't have the ability to collapse or expand any of the rows or columns.
> No + - icon displays either.
> I have tried changing settings for the initial display of columns as
> collapsed and expanded.
> Any ideas on the problem?
> Jim
>

Matrix with more than one table possible?

Hi,
I would like to use a Matrix where the first table defines the
Columns, the second table the rows and a third table holds the data -
which needs to be associated by the values from column and row. But
for some reason I can only set one table from a data set as the data
source (e.g.: dsData_tblDefinitions).
How can this be done? If I drag and drop the other fields from the
same or other datasets it can't find the tables / fields.
Thanks,
OlcayOn Apr 30, 10:37 am, olc...@.gmail.com wrote:
> Hi,
> I would like to use a Matrix where the first table defines the
> Columns, the second table the rows and a third table holds the data -
> which needs to be associated by the values from column and row. But
> for some reason I can only set one table from a data set as the data
> source (e.g.: dsData_tblDefinitions).
> How can this be done? If I drag and drop the other fields from the
> same or other datasets it can't find the tables / fields.
> Thanks,
> Olcay
Normally, matrix reports cannot include multiple datasets by default
(if its possible at all, outside of specific referenced aggregates in
a particular cell), etc. Why is the standard matrix report not an
option in this case (one pivot column for the column names, one column
for the row info and the remaining columns for the data)?
Enrique Martinez
Sr. Software Consultant|||On 1 Mai, 05:37, EMartinez <emartinez...@.gmail.com> wrote:
> On Apr 30, 10:37 am, olc...@.gmail.com wrote:
> > Hi,
> > I would like to use a Matrix where the first table defines the
> > Columns, the second table the rows and a third table holds the data -
> > which needs to be associated by the values from column and row. But
> > for some reason I can only set one table from a data set as the data
> > source (e.g.: dsData_tblDefinitions).
> > How can this be done? If I drag and drop the other fields from the
> > same or other datasets it can't find the tables / fields.
> > Thanks,
> >Olcay
> Normally, matrix reports cannot include multiple datasets by default
> (if its possible at all, outside of specific referenced aggregates in
> a particular cell), etc. Why is the standard matrix report not an
> option in this case (one pivot column for the column names, one column
> for the row info and the remaining columns for the data)?
> Enrique Martinez
> Sr. Software Consultant
Hi Enrique,
the problem is, that my colum names are also dynamic. Columns
(tblTasks), Rows (tblObjects) and Data (tblValues - the result of
applying the tasks on those objects) are all dynamic.
Thanks,
Olcay

Matrix with fixed column values (months 1-12)

I have a matrix that shows sales per year (rows) and month (columns).
If the data being used contains no records for a particular month for any
year (e.g. November), that column (i.e. column 11) is completely missing
from the matrix.
Can the matrix be configured to always show a given set of columns, even if
there is no underlying data?
ThanksHi Laurence,
If you don't have any data rows for a certain month, then it will not show
up in the matrix grouping. To ensure that certain groups/data values are
always present, you will need an outer join in your dataset query e.g. with
a simple table that just has 12 rows with one column and values from 1 to
12.
Details on how to use outer joins are available here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_09_0zqr.asp
http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_09_1h6b.asp
HTH,
Robert
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:O8YpoRDuEHA.1296@.TK2MSFTNGP10.phx.gbl...
> I have a matrix that shows sales per year (rows) and month (columns).
> If the data being used contains no records for a particular month for any
> year (e.g. November), that column (i.e. column 11) is completely missing
> from the matrix.
> Can the matrix be configured to always show a given set of columns, even
if
> there is no underlying data?
> Thanks
>|||OK thanks, I know how to do that.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:uWTPFZyuEHA.3228@.TK2MSFTNGP12.phx.gbl...
> Hi Laurence,
> If you don't have any data rows for a certain month, then it will not show
> up in the matrix grouping. To ensure that certain groups/data values are
> always present, you will need an outer join in your dataset query e.g.
> with
> a simple table that just has 12 rows with one column and values from 1 to
> 12.
> Details on how to use outer joins are available here:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_09_0zqr.asp
> http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_09_1h6b.asp
> HTH,
> Robert
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:O8YpoRDuEHA.1296@.TK2MSFTNGP10.phx.gbl...
>> I have a matrix that shows sales per year (rows) and month (columns).
>> If the data being used contains no records for a particular month for any
>> year (e.g. November), that column (i.e. column 11) is completely missing
>> from the matrix.
>> Can the matrix be configured to always show a given set of columns, even
> if
>> there is no underlying data?
>> Thanks
>>
>

Matrix with custom row total - is it possible?

Hell Everybody,
Let's suppose on my report I have a matrix with sales data - regions in
columns, years in rows. Now I need to add an additional total column with,
let's say, total profit.
I tried to accomplish that adding a hidden value. Unfortunately it seems
like I have no control over which total is displayed and which is not.
Theoretically I could place table object next to my matrix, with the same
row and header size but when it comes to pagination results are disastrous.
For any reason renderer breaks my matrix and table at different row.
Sometimes the difference is more than one row, sometimes it does not break
the table but breaks matrix - I am aware of KeepTogether property.
Please advise.
TomaszHi Tomasz,
Thank you for your post.
Have you tried SubTotal column? To add a subtotal to a matrix, add a
subtotal to an individual group within the matrix. Groups do not have
subtotals by default. To add a subtotal to a group, right-click the group
column or row header and then click Subtotal. This will open a new header
for the subtotal. Reporting Services will calculate the subtotal based on
the aggregate in the data cell for the group.
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei,
Thanks for the answer. I have tried this. The better problem definition is
this: I have a matrix with two different measures. I want to show one of
measures with no subtotals while for the other one I want subtotals only. So
the result would look like this:
year/regional sales, USA, Canada, Total Profit
2004, $29334.00, $23232.00, ($6552.00)
2005, $534435.00, $387745.00, $223445.00
Notice that data in the last column has nothing to do with data in other
columns - it is a different measure for which region/year details are not
visible - I want subtotals only. In contrast, for the region/year sales
measure I do NOT want to show subtotals (yearly sales in this case).
Thanks,
Tomasz
"Wei Lu" <weilu@.online.microsoft.com> wrote in message
news:uLCdsGKdGHA.5024@.TK2MSFTNGXA01.phx.gbl...
> Hi Tomasz,
> Thank you for your post.
> Have you tried SubTotal column? To add a subtotal to a matrix, add a
> subtotal to an individual group within the matrix. Groups do not have
> subtotals by default. To add a subtotal to a group, right-click the group
> column or row header and then click Subtotal. This will open a new header
> for the subtotal. Reporting Services will calculate the subtotal based on
> the aggregate in the data cell for the group.
> Hope this will be helpful.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Tomasz,
Thanks for the update.
How about hide the column you just want Subtotals?
If possible, would you please provide some test data so I could test on my
side?
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Matrix w/ Subtotals Export to Excel rrRenderingError

Is it possible to export a matrix report that has subtotals on multiple
columns to Excel? With just the innermost group subtotaled, I don't get an
exception, but when I add subtotals for outer groups it will not export.
I've seen this question posted elsewhere, but no answer to it. Thanks!Magpie,
I have same problem. Do you have any idea of solution?
Regards,
Loretta
"Magpie" wrote:
> Is it possible to export a matrix report that has subtotals on multiple
> columns to Excel? With just the innermost group subtotaled, I don't get an
> exception, but when I add subtotals for outer groups it will not export.
> I've seen this question posted elsewhere, but no answer to it. Thanks!

Matrix Total Line till the end of the dynamic columns & Bold totals and sub-totals

I am using Matrix in one of my reports and I would like to have my
sub-total & total line strech until the end of the last 'dynamic'
column.
Also I would like to display the totals and sub-totals in bold.
Is this possible?
If yes, please let me know the workaround as soon as possible.
Thanking you in advance.Hi Alkesh,
I also want the same thing to be done. Do let me know if you do get a
solution to it.
Thanks,
Param
Capgemini India|||Param,
One way out is to calculate the totals and the subtotals in SP itself.
Then put a conditional iif to get it formatted. Let me know your views.
Rgds,
Alkesh
alkesh.patel@.math.netsql

Matrix Sub-Totals

Hi,
I have one column group and 2 columns (one Amount & other Text) under
it in a matrix. I added subtotal to that column group and now Amount
total appears fine but first TEXT value appears in total coloumn. I
would like to hide the TEXT value appearing in Subtotal column.
I sure there are lot of threads addressing this sub-total issue but i
was unable to find answer for my query.
Any help would be appreciated.
-SGYou must use the InScope function if you only want text to be displayed in
the details and not the subtotal. There was another posting addressing this
issue. I use something like the following in the expression :
=iif(inscope("ProductGroup"),first(Fields!Price.value,"ProductGroup"),nothing)
"SG" wrote:
> Hi,
> I have one column group and 2 columns (one Amount & other Text) under
> it in a matrix. I added subtotal to that column group and now Amount
> total appears fine but first TEXT value appears in total coloumn. I
> would like to hide the TEXT value appearing in Subtotal column.
> I sure there are lot of threads addressing this sub-total issue but i
> was unable to find answer for my query.
> Any help would be appreciated.
> -SG
>|||Dawie wrote:
> You must use the InScope function if you only want text to be displayed in
> the details and not the subtotal. There was another posting addressing this
> issue. I use something like the following in the expression :
> =iif(inscope("ProductGroup"),first(Fields!Price.value,"ProductGroup"),nothing)
>
> "SG" wrote:
> > Hi,
> >
> > I have one column group and 2 columns (one Amount & other Text) under
> > it in a matrix. I added subtotal to that column group and now Amount
> > total appears fine but first TEXT value appears in total coloumn. I
> > would like to hide the TEXT value appearing in Subtotal column.
> >
> > I sure there are lot of threads addressing this sub-total issue but i
> > was unable to find answer for my query.
> > Any help would be appreciated.
> >
> > -SG
> >
> >Where can i write the Expression for the subtotal?
Actually i am struggling to find a way to find where i can write the
Expression for SubTotal?