Max Dates For Cost Query

Hi,

I am trying to identify the costs for products with the latest cost date. I am unable to run the query. Is there something that I can do?

SELECT PART,COST, DATE FROM DATA/COSTFILE AS T1 WHERE T1.DATE= (SELECT MAX(DATE) AS T2 FROM DATA/COSTFILE AS T2 WHERE T2.PART=T1.PART)

Thanks,

DavidOriginally posted by hidvegi
Hi,

I am trying to identify the costs for products with the latest cost date. I am unable to run the query. Is there something that I can do?

SELECT PART,COST, DATE FROM DATA/COSTFILE AS T1 WHERE T1.DATE= (SELECT MAX(DATE) AS T2 FROM DATA/COSTFILE AS T2 WHERE T2.PART=T1.PART)

Thanks,

David

SELECT
T1.PART,
T1.COST,
T1.DATE
FROM
[DATA/COSTFILE] AS T1
WHERE
T1.DATE= (
SELECT MAX(T2.DATE) AS MAXDATE
FROM DATA/COSTFILE AS T2
WHERE T2.PART=T1.PART)

FYI, you shouldn't use ANY special characters in your column names or table names, especially "/\-_.,&%@.+"=".

The only exception to this is "_" which can be used after for sp_name on stored procedures that must reside in master and run on every database.

max date select stmt problem

Hello Everybody,

I have a problem, with select stmt:

SELECT TOP 15 *
FROM oaVIEW_MainData AS TOP_VIEW,
oaLanguageData_TAB AS RwQualifierJoin with (nolock)
WHERE (c_dateTime>='2007.01.10 00:00:00' AND c_dateTime<='2007.01.10
23:59:59')
AND RwQualifierJoin.text_id = c_cfgRegPoint
AND (((RwQualifierJoin.local1 LIKE N'Position of any bubu')))
AND TOP_VIEW.c_dateTime=(SELECT MAX(SUB_VIEW.c_dateTime)
FROM oaVIEW_MainData AS SUB_VIEW,oaLanguageData_TAB AS
RwQualifierJoin1 with (nolock)
WHERE (c_dateTime>='2007.01.10 00:00:00' AND c_dateTime<='2007.01.10
23:59:59')
AND RwQualifierJoin1.text_id = c_cfgRegPoint
AND (((RwQualifierJoin1.local1 LIKE N'Position of any bubu')))
AND TOP_VIEW.c_dsmIdent=SUB_VIEW.c_dsmIdent)
order by c_dateTime desc

Please consider:
- top doesn't metter, if I will use one or 10000 result is always the
same.
- oaVIEW_MainData, is a view on major big table, holding lot of records
joinden with small table containing configuration data, over left outer
join; both tables are with nolock option,
- quersy supose to return last record from major table/view, in given
time, additionaly, with other where conditions (like in this case with
text),
- on major table, are indexes which one is on id field (not used in
this query at all), which is a pk clustered, and other is on dateEvt
(c_dateTime) which is a desc index with fill level 90%
- table has also other indexes, on three different fields, one of
theses is dsmIdent,

Now, if I'm using max(id) works very fast, and ok for me, but the
problem is, I should not use id, because might be, that the records
will be written in the table with random order, so the only one saying
which is newest, will be dateEvt.

Using dateEvt as max(), dramaticly slows query, so I'm acctualy unable
to get result. What is much more funny, server is totaly busy with this
query, and it's procesor jumps on 100%.

Now, because the query is builded dynamicly, by a user selections,
that's why we decided on such a parser ... problem is, it is not
working :(

Can I change index on dateEvt somehow, to sped this up?
Maybe construct query somehow different, to get this over max() date?

Please help

MatikMatik (marzec@.sauron.xo.pl) writes:

Quote:

Originally Posted by

- oaVIEW_MainData, is a view on major big table, holding lot of records
joinden with small table containing configuration data, over left outer
join; both tables are with nolock option,

NOLOCK in a view? That's about criminal in my opinion.

Quote:

Originally Posted by

- on major table, are indexes which one is on id field (not used in
this query at all), which is a pk clustered, and other is on dateEvt
(c_dateTime) which is a desc index with fill level 90%
- table has also other indexes, on three different fields, one of
theses is dsmIdent,
>
Now, if I'm using max(id) works very fast, and ok for me, but the
problem is, I should not use id, because might be, that the records
will be written in the table with random order, so the only one saying
which is newest, will be dateEvt.
>
Using dateEvt as max(), dramaticly slows query, so I'm acctualy unable
to get result. What is much more funny, server is totaly busy with this
query, and it's procesor jumps on 100%.

Well, the easy fix would be to make the index on dateEvt() clustered
rather than the index on id. That may of course have repercussions
elsewhere.

The query looks funny to me, as it repeats the entire outer query in
the subquery. Somehome I feel that that should not be necessary. But
to say for sure I would need to know the view definition and the
definition of the underlying tables, including their key and check
constraints.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Max date in a row

Hi,
I would like to know how to find the max of 4 dates in one row.
so if we have ssn, date1, date2, date3, date4
456123789 12/3/2005, 12/5/2005,11/8/2005,1/2/2006
my output should give me
456123789, 1/2/2006
ThanksCREATE TABLE #foo
(
ssn CHAR(9),
d1 SMALLDATETIME,
d2 SMALLDATETIME,
d3 SMALLDATETIME,
d4 SMALLDATETIME
);
SET NOCOUNT ON;
INSERT #foo SELECT '111111111', '20050101', '20050505', '20050603',
'20050401';
INSERT #foo SELECT '222222222', '20050601', '20050505', '20050203',
'20050201';
INSERT #foo SELECT '333333333', '20050601', '20050601', '20050203',
'20050201';
INSERT #foo SELECT '333333333', '20050601', '20050602', '20050603',
'20050604';
SELECT ssn, d = MAX(d)
FROM
(
SELECT ssn, d = d1 FROM #foo
UNION ALL SELECT ssn, d = d2 FROM #foo
UNION ALL SELECT ssn, d = d3 FROM #foo
UNION ALL SELECT ssn, d = d4 FROM #foo
) x
GROUP BY ssn;
DROP TABLE #foo;
Can I recommend this structure instead:
CREATE TABLE dbo.People
(
ssn CHAR(9) PRIMARY KEY
);
CREATE TABLE dbo.PeopleDates
(
ssn CHAR(9) NOT NULL FOREIGN KEY REFERENCES dbo.People(ssn),
dateInstance TINYINT NOT NULL, -- check for 1-4?
dateValue SMALLDATETIME
);
INSERT dbo.People
SELECT '111111111'
UNION ALL SELECT '222222222'
UNION ALL SELECT '333333333';
INSERT dbo.PeopleDates
SELECT '111111111', 1, '20050101'
UNION ALL SELECT '111111111', 2, '20050505';
/* ...... */
More work up front, and slightly larger storage cost (though you could
offset that a bit by using an INT for the key), but it is more relational in
nature, and look how easy it makes your queries:
SELECT ssn, MAX(dateValue)
FROM dbo.PeopleDates
GROUP BY ssn;
And as well as making this type of query much simpler, you don't have to go
change things when you add a 5th date. (In your current model, you need to
change the schema *and* change the query.)
In addition, I encourage not thinking about dates in these string formats,
or at least when you are explaining an issue to other people, to avoid
confusion and ambiguity. Are your dates:
(a) Mar 12 2005, May 12 2005, Aug 11 2005, Feb 1 2006
or
(b) Dec 3 2005, Dec 5 2005, Nov 8 2005, Jan 2 2005
?
In this case, it was easy to pick out the latest date you expected in the
result, because it was the only one in 2006. But if you included 2/1/2006
as well, I'd be at a loss without requesting further clarification.
You should strive to use string representations of dates that are 100%
unambiguous to both people and code. For example, 'YYYYMMDD' will always
work, no matter who you're talking to or what your SQL Server's regional
settings, dateformat, language, etc.
A
"Amit" <Amit@.discussions.microsoft.com> wrote in message
news:8E25E6E0-E4E0-4739-908A-B995314FFFF6@.microsoft.com...
> Hi,
> I would like to know how to find the max of 4 dates in one row.
> so if we have ssn, date1, date2, date3, date4
> 456123789 12/3/2005, 12/5/2005,11/8/2005,1/2/2006
> my output should give me
> 456123789, 1/2/2006
> Thanks|||Amit wrote:
> Hi,
> I would like to know how to find the max of 4 dates in one row.
> so if we have ssn, date1, date2, date3, date4
> 456123789 12/3/2005, 12/5/2005,11/8/2005,1/2/2006
> my output should give me
> 456123789, 1/2/2006
> Thanks
Table design aside, you can use a scalar function here:
Select
ssn,
dbo.fnGetMaxDate(date1, date2, date3, date4)
From
dbo.MyTable
Create Function dbo.fnGetMaxDate (
@.date1 datetime, @.date2 datetime, @.date3 datetime, @.date4 datetime )
Returns datetime
as
Begin
declare @.datefinal datetime
set @.datefinal = @.date1
If @.date2 > @.datefinal
set @.datefinal = @.date2
If @.date3 > @.datefinal
set @.datefinal = @.date3
If @.date4 > @.datefinal
set @.datefinal = @.date4
Return @.datefinal
End
David Gugick - SQL Server MVP
Quest Software|||SELECT SSN,
CASE WHEN date1 > date2
AND date1 > date3
AND date1 > date4
THEN date1
WHEN date2 > date3
AND date2 > date4
THEN date2
WHEN date3 > date4
THEN date3
ELSE date4
END as MaxDate
FROM SomeTable
Roy Harvey
Beacon Falls, CT
On Thu, 2 Mar 2006 11:35:02 -0800, "Amit"
<Amit@.discussions.microsoft.com> wrote:

>Hi,
>I would like to know how to find the max of 4 dates in one row.
>so if we have ssn, date1, date2, date3, date4
> 456123789 12/3/2005, 12/5/2005,11/8/2005,1/2/2006
>my output should give me
>456123789, 1/2/2006
>Thanks|||Another approach:
SELECT ssn,
MAX( CASE n WHEN 1 THEN dt1
WHEN 2 THEN dt2
WHEN 3 THEN dt3
WHEN 4 THEN dt4
END )
FROM tbl, ( SELECT 1 UNION SELECT 2 UNION
SELECT 3 UNION SELECT 4 ) N ( n )
GROUP BY ssn ;
Anith

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 Date

How do i find out the max date of a particular month in a table.

I have a records in the table like this

DATE
--
01 Jan 2004
02 Jan 2004
05 Jan 2004
07 Jan 2004
04 Feb 2004
06 Feb 2004
19 Feb 2004
04 Mar 2004
28 Mar 2004
03 Apr 2004
05 Apr 2004

My output should be something like this
OUTPUT
---
07 Jan 2004
06 Feb 2004
28 Mar 2004
05 Apr 2004

I want a SQL statement that can do this for me

Thanks
ShankarHehe, just answered you on SQLServerCentral :)

select Max(datepart(dd,datecolumn)) as Day,datepart(mm,datecolumn) as month, datepart(yy,datecolumn) as Year
from table
group by datepart(mm,datecolumn),datepart(yy,datecolumn)

HTH|||thanks for your help,

i got a kind off tricky requirement here.

the query that i write has to return me 6 records always.

The first record that will be displayed will be the initial record that is created. The last record in these 6 records will be the latest record that is created. so now my question is i want to retrieve the remaining 4 records.

my conditions are like this.

i want to display the maximum date of a particular month in the 4 records. now if this condition does not give me 4 records, then i need to
pick up the current month's record also and make sure the count is equal to 4. What if i have only one record in the current month.
then i need to go to current - 1 month and pick up that record.

I need to keep doing this until i get the 4 records that i want.

Let me give u the example for this.

Lets take the example that i have given previously.

DATE
--
01 Jan 2004
02 Jan 2004
05 Jan 2004
07 Jan 2004
04 Feb 2004
06 Feb 2004
19 Feb 2004
04 Mar 2004
28 Mar 2004
03 Apr 2004
05 Apr 2004

My output should be something like this

01 Jan 2004 --> First Record that is created
07 Jan 2004 --> Max of Jan
06 Feb 2004 --> Max of Feb
28 Mar 2004 --> Max of March
03 Apr 2004 --> I choose this record becoz the count for max months
is not equal to 4 and hence to make it 4 , i add this
05 Apr 2004 --> The most recent record

Hope this helps in understanding the requirement

Thanks
Shankar

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 () query problem

Say I have a table like this (just for example)

ID Date Name
__ ____ _____
1 1/2/2004 Store A
2 1/1/2004 Store A
3 1/3/2004 Store B
4 1/2/2004 Store B

Say I wanted to get the rows for the maximum dates for stores A and B
aka this record set..

ID Date Name
__ ____ _____
1 1/2/2004 Store A
3 1/3/2004 Store B

How would I got about doing this in sql?

thanks

-JimSELECT id, date, name
FROM SomeTable AS T
WHERE date =
(SELECT MAX(date)
FROM SomeTable
WHERE name = T.name)

--
David Portas
SQL Server MVP
--

Matrix trouble.

Hi all.
I got a dataset with a person and a date and i need to make a report on this.
but i want it like this:
day(date): 1 2 3 4 5 6 7
initials x x . . x x x
I have this now:
day(date): 1 2 5 6 7
initials x x x x x
Problem is i dont have a record from the 3rd and 4th, but they need to be in
the report aswell.
So i need the report to show a " . " the days they havent been there.
Can someone help me?If you don't have any data rows with values 3 and 4, they 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 7 rows with one column and these values: 1, 2, 3,
4, 5, 6, 7
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
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Christian Larsen" <ChristianLarsen@.discussions.microsoft.com> wrote in
message news:E5AEA9A4-0E2A-40ED-B11A-FE0839C3DF04@.microsoft.com...
> Hi all.
> I got a dataset with a person and a date and i need to make a report on
this.
> but i want it like this:
> day(date): 1 2 3 4 5 6 7
> initials x x . . x x x
> I have this now:
> day(date): 1 2 5 6 7
> initials x x x x x
> Problem is i dont have a record from the 3rd and 4th, but they need to be
in
> the report aswell.
> So i need the report to show a " . " the days they havent been there.
> Can someone help me?

Matrix Row Headers After Data

I have a report that uses Analysis Services data in a matrix. The column
groups in the matrix are based on date, and the number of column groups is
variable based on the start time and end time entered as report parameters.
I'd like to display the row headers after the column groups, regardless of
the number of column groups. The "GroupsBeforeRowHeaders" property looks
like it will work, but it only takes an integer - I would like to set it to
"All" or something similar.
Is there any way to do this without writing code that will calculate the
number of column groups and then using that in an expression for the
"GroupsBeforeRowHeaders" property? I'd like to avoid doing this because the
start and end times may be weeks, months, or years, so calculating the
number of columns would be a pain.
Thanks for any help,
Sean Carpenter
ProMetrics Consulting, Inc.Did you try setting the integer to a very large value, e.g. 100000000
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sean Carpenter" <stcarpenter2005@.community.nospam> wrote in message
news:u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl...
> I have a report that uses Analysis Services data in a matrix. The column
> groups in the matrix are based on date, and the number of column groups is
> variable based on the start time and end time entered as report
parameters.
> I'd like to display the row headers after the column groups, regardless of
> the number of column groups. The "GroupsBeforeRowHeaders" property looks
> like it will work, but it only takes an integer - I would like to set it
to
> "All" or something similar.
> Is there any way to do this without writing code that will calculate the
> number of column groups and then using that in an expression for the
> "GroupsBeforeRowHeaders" property? I'd like to avoid doing this because
the
> start and end times may be weeks, months, or years, so calculating the
> number of columns would be a pain.
> Thanks for any help,
> Sean Carpenter
> ProMetrics Consulting, Inc.
>|||Yes. If I set it to anything larger than the actual number of column
groups, it doesn't appear to do anything - the headers stay to the left of
all of the column groups.
Sean Carpenter
ProMetrics Consulting, Inc.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:u7k3Tj%235EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Did you try setting the integer to a very large value, e.g. 100000000
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Sean Carpenter" <stcarpenter2005@.community.nospam> wrote in message
> news:u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl...
>> I have a report that uses Analysis Services data in a matrix. The column
>> groups in the matrix are based on date, and the number of column groups
>> is
>> variable based on the start time and end time entered as report
> parameters.
>> I'd like to display the row headers after the column groups, regardless
>> of
>> the number of column groups. The "GroupsBeforeRowHeaders" property looks
>> like it will work, but it only takes an integer - I would like to set it
> to
>> "All" or something similar.
>> Is there any way to do this without writing code that will calculate the
>> number of column groups and then using that in an expression for the
>> "GroupsBeforeRowHeaders" property? I'd like to avoid doing this because
> the
>> start and end times may be weeks, months, or years, so calculating the
>> number of columns would be a pain.
>> Thanks for any help,
>> Sean Carpenter
>> ProMetrics Consulting, Inc.
>>
>|||I have the same thing and I did not set the group header number. It is set
to 0 and the Matrix Columns expand Right to left is selected. Thus all of my
columns appear to the left and then my rows appear to right.
Hope this is not to late to help, if it works for you.
"Sean Carpenter" wrote:
> Yes. If I set it to anything larger than the actual number of column
> groups, it doesn't appear to do anything - the headers stay to the left of
> all of the column groups.
> Sean Carpenter
> ProMetrics Consulting, Inc.
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:u7k3Tj%235EHA.1408@.TK2MSFTNGP10.phx.gbl...
> > Did you try setting the integer to a very large value, e.g. 100000000
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> > "Sean Carpenter" <stcarpenter2005@.community.nospam> wrote in message
> > news:u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl...
> >> I have a report that uses Analysis Services data in a matrix. The column
> >> groups in the matrix are based on date, and the number of column groups
> >> is
> >> variable based on the start time and end time entered as report
> > parameters.
> >> I'd like to display the row headers after the column groups, regardless
> >> of
> >> the number of column groups. The "GroupsBeforeRowHeaders" property looks
> >> like it will work, but it only takes an integer - I would like to set it
> > to
> >> "All" or something similar.
> >>
> >> Is there any way to do this without writing code that will calculate the
> >> number of column groups and then using that in an expression for the
> >> "GroupsBeforeRowHeaders" property? I'd like to avoid doing this because
> > the
> >> start and end times may be weeks, months, or years, so calculating the
> >> number of columns would be a pain.
> >>
> >> Thanks for any help,
> >> Sean Carpenter
> >> ProMetrics Consulting, Inc.
> >>
> >>
> >
> >
>
>|||I didn't know about that property of the matrix. I tried it and it works,
except that it reverses the order of all of the columns (which makes sense
considering what the property does). I can reverse the order of the columns
in the query result so that when the matrix reverses them again they display
correctly, but it would be nice to have a "clean" way of doing this. This
will definitely get me through the current report, though.
Sean Carpenter
ProMetrics Consulting, Inc.
"HBWAL" wrote:
> I have the same thing and I did not set the group header number. It is set
> to 0 and the Matrix Columns expand Right to left is selected. Thus all of my
> columns appear to the left and then my rows appear to right.
> Hope this is not to late to help, if it works for you.
> "Sean Carpenter" wrote:
> > Yes. If I set it to anything larger than the actual number of column
> > groups, it doesn't appear to do anything - the headers stay to the left of
> > all of the column groups.
> >
> > Sean Carpenter
> > ProMetrics Consulting, Inc.
> >
> > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> > news:u7k3Tj%235EHA.1408@.TK2MSFTNGP10.phx.gbl...
> > > Did you try setting the integer to a very large value, e.g. 100000000
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > >
> > > "Sean Carpenter" <stcarpenter2005@.community.nospam> wrote in message
> > > news:u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl...
> > >> I have a report that uses Analysis Services data in a matrix. The column
> > >> groups in the matrix are based on date, and the number of column groups
> > >> is
> > >> variable based on the start time and end time entered as report
> > > parameters.
> > >> I'd like to display the row headers after the column groups, regardless
> > >> of
> > >> the number of column groups. The "GroupsBeforeRowHeaders" property looks
> > >> like it will work, but it only takes an integer - I would like to set it
> > > to
> > >> "All" or something similar.
> > >>
> > >> Is there any way to do this without writing code that will calculate the
> > >> number of column groups and then using that in an expression for the
> > >> "GroupsBeforeRowHeaders" property? I'd like to avoid doing this because
> > > the
> > >> start and end times may be weeks, months, or years, so calculating the
> > >> number of columns would be a pain.
> > >>
> > >> Thanks for any help,
> > >> Sean Carpenter
> > >> ProMetrics Consulting, Inc.
> > >>
> > >>
> > >
> > >
> >
> >
> >|||Regarding the order - you should add a sort expression on the column
grouping of the matrix. The RS processing engine does not change any order
if there is no sorting applied in the report. You get the data in the order
returned by the data provider.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sean Carpenter" <SeanCarpenter@.discussions.microsoft.com> wrote in message
news:34267414-52D0-45ED-8C0F-14C174F5D1D8@.microsoft.com...
> I didn't know about that property of the matrix. I tried it and it works,
> except that it reverses the order of all of the columns (which makes sense
> considering what the property does). I can reverse the order of the
columns
> in the query result so that when the matrix reverses them again they
display
> correctly, but it would be nice to have a "clean" way of doing this. This
> will definitely get me through the current report, though.
> Sean Carpenter
> ProMetrics Consulting, Inc.
> "HBWAL" wrote:
> > I have the same thing and I did not set the group header number. It is
set
> > to 0 and the Matrix Columns expand Right to left is selected. Thus all
of my
> > columns appear to the left and then my rows appear to right.
> >
> > Hope this is not to late to help, if it works for you.
> >
> > "Sean Carpenter" wrote:
> >
> > > Yes. If I set it to anything larger than the actual number of column
> > > groups, it doesn't appear to do anything - the headers stay to the
left of
> > > all of the column groups.
> > >
> > > Sean Carpenter
> > > ProMetrics Consulting, Inc.
> > >
> > > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in
message
> > > news:u7k3Tj%235EHA.1408@.TK2MSFTNGP10.phx.gbl...
> > > > Did you try setting the integer to a very large value, e.g.
100000000
> > > >
> > > > --
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > > > rights.
> > > >
> > > > "Sean Carpenter" <stcarpenter2005@.community.nospam> wrote in message
> > > > news:u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl...
> > > >> I have a report that uses Analysis Services data in a matrix. The
column
> > > >> groups in the matrix are based on date, and the number of column
groups
> > > >> is
> > > >> variable based on the start time and end time entered as report
> > > > parameters.
> > > >> I'd like to display the row headers after the column groups,
regardless
> > > >> of
> > > >> the number of column groups. The "GroupsBeforeRowHeaders" property
looks
> > > >> like it will work, but it only takes an integer - I would like to
set it
> > > > to
> > > >> "All" or something similar.
> > > >>
> > > >> Is there any way to do this without writing code that will
calculate the
> > > >> number of column groups and then using that in an expression for
the
> > > >> "GroupsBeforeRowHeaders" property? I'd like to avoid doing this
because
> > > > the
> > > >> start and end times may be weeks, months, or years, so calculating
the
> > > >> number of columns would be a pain.
> > > >>
> > > >> Thanks for any help,
> > > >> Sean Carpenter
> > > >> ProMetrics Consulting, Inc.
> > > >>
> > > >>
> > > >
> > > >
> > >
> > >
> > >|||I understand that RS doesn't change the order - the problem is that when I
set the matrix to expand from right to left, this effectively changes the
order of the data. I can't sort in the matrix because the groups need to be
sorted correctly by date, by the column grouping is based on the date name
returned from Analysis Services (which doesn't always sort the same as the
actual date).
Either way, it turns out this doesn't help since when I export to Excel, the
row headers are still on the left of the data - it seems the excel export
ignores the "LayoutDirection" property of the matrix.
I guess I'll be stuck with calculating the number of column groups and
setting the "GroupsBeforeRowHeaders" property using an expression.
Sean Carpenter
ProMetrics Consulting, Inc.
"Robert Bruckner [MSFT]" wrote:
> Regarding the order - you should add a sort expression on the column
> grouping of the matrix. The RS processing engine does not change any order
> if there is no sorting applied in the report. You get the data in the order
> returned by the data provider.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Sean Carpenter" <SeanCarpenter@.discussions.microsoft.com> wrote in message
> news:34267414-52D0-45ED-8C0F-14C174F5D1D8@.microsoft.com...
> > I didn't know about that property of the matrix. I tried it and it works,
> > except that it reverses the order of all of the columns (which makes sense
> > considering what the property does). I can reverse the order of the
> columns
> > in the query result so that when the matrix reverses them again they
> display
> > correctly, but it would be nice to have a "clean" way of doing this. This
> > will definitely get me through the current report, though.
> >
> > Sean Carpenter
> > ProMetrics Consulting, Inc.
> >
> > "HBWAL" wrote:
> >
> > > I have the same thing and I did not set the group header number. It is
> set
> > > to 0 and the Matrix Columns expand Right to left is selected. Thus all
> of my
> > > columns appear to the left and then my rows appear to right.
> > >
> > > Hope this is not to late to help, if it works for you.
> > >
> > > "Sean Carpenter" wrote:
> > >
> > > > Yes. If I set it to anything larger than the actual number of column
> > > > groups, it doesn't appear to do anything - the headers stay to the
> left of
> > > > all of the column groups.
> > > >
> > > > Sean Carpenter
> > > > ProMetrics Consulting, Inc.
> > > >
> > > > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in
> message
> > > > news:u7k3Tj%235EHA.1408@.TK2MSFTNGP10.phx.gbl...
> > > > > Did you try setting the integer to a very large value, e.g.
> 100000000
> > > > >
> > > > > --
> > > > > This posting is provided "AS IS" with no warranties, and confers no
> > > > > rights.
> > > > >
> > > > > "Sean Carpenter" <stcarpenter2005@.community.nospam> wrote in message
> > > > > news:u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl...
> > > > >> I have a report that uses Analysis Services data in a matrix. The
> column
> > > > >> groups in the matrix are based on date, and the number of column
> groups
> > > > >> is
> > > > >> variable based on the start time and end time entered as report
> > > > > parameters.
> > > > >> I'd like to display the row headers after the column groups,
> regardless
> > > > >> of
> > > > >> the number of column groups. The "GroupsBeforeRowHeaders" property
> looks
> > > > >> like it will work, but it only takes an integer - I would like to
> set it
> > > > > to
> > > > >> "All" or something similar.
> > > > >>
> > > > >> Is there any way to do this without writing code that will
> calculate the
> > > > >> number of column groups and then using that in an expression for
> the
> > > > >> "GroupsBeforeRowHeaders" property? I'd like to avoid doing this
> because
> > > > > the
> > > > >> start and end times may be weeks, months, or years, so calculating
> the
> > > > >> number of columns would be a pain.
> > > > >>
> > > > >> Thanks for any help,
> > > > >> Sean Carpenter
> > > > >> ProMetrics Consulting, Inc.
> > > > >>
> > > > >>
> > > > >
> > > > >
> > > >
> > > >
> > > >
>
>|||It looks like my solution from the previous post won't work. I wrote a
function in the Custom Code section of the report to return the correct
number of column groups based on the report parameters. When I try to set
the "GroupsBeforeRowHeaders" property to "=Code.ColumnCount()", it won't let
me since it requires an Int32 value for the property.
Does anyone have any ideas on this one? I'd like to have the row headers to
the right of the column groups and have it appear that way on Excel export as
well.
Thanks,
Sean Carpenter
ProMetrics Consulting, Inc.
"Robert Bruckner [MSFT]" wrote:
> Regarding the order - you should add a sort expression on the column
> grouping of the matrix. The RS processing engine does not change any order
> if there is no sorting applied in the report. You get the data in the order
> returned by the data provider.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Sean Carpenter" <SeanCarpenter@.discussions.microsoft.com> wrote in message
> news:34267414-52D0-45ED-8C0F-14C174F5D1D8@.microsoft.com...
> > I didn't know about that property of the matrix. I tried it and it works,
> > except that it reverses the order of all of the columns (which makes sense
> > considering what the property does). I can reverse the order of the
> columns
> > in the query result so that when the matrix reverses them again they
> display
> > correctly, but it would be nice to have a "clean" way of doing this. This
> > will definitely get me through the current report, though.
> >
> > Sean Carpenter
> > ProMetrics Consulting, Inc.
> >
> > "HBWAL" wrote:
> >
> > > I have the same thing and I did not set the group header number. It is
> set
> > > to 0 and the Matrix Columns expand Right to left is selected. Thus all
> of my
> > > columns appear to the left and then my rows appear to right.
> > >
> > > Hope this is not to late to help, if it works for you.
> > >
> > > "Sean Carpenter" wrote:
> > >
> > > > Yes. If I set it to anything larger than the actual number of column
> > > > groups, it doesn't appear to do anything - the headers stay to the
> left of
> > > > all of the column groups.
> > > >
> > > > Sean Carpenter
> > > > ProMetrics Consulting, Inc.
> > > >
> > > > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in
> message
> > > > news:u7k3Tj%235EHA.1408@.TK2MSFTNGP10.phx.gbl...
> > > > > Did you try setting the integer to a very large value, e.g.
> 100000000
> > > > >
> > > > > --
> > > > > This posting is provided "AS IS" with no warranties, and confers no
> > > > > rights.
> > > > >
> > > > > "Sean Carpenter" <stcarpenter2005@.community.nospam> wrote in message
> > > > > news:u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl...
> > > > >> I have a report that uses Analysis Services data in a matrix. The
> column
> > > > >> groups in the matrix are based on date, and the number of column
> groups
> > > > >> is
> > > > >> variable based on the start time and end time entered as report
> > > > > parameters.
> > > > >> I'd like to display the row headers after the column groups,
> regardless
> > > > >> of
> > > > >> the number of column groups. The "GroupsBeforeRowHeaders" property
> looks
> > > > >> like it will work, but it only takes an integer - I would like to
> set it
> > > > > to
> > > > >> "All" or something similar.
> > > > >>
> > > > >> Is there any way to do this without writing code that will
> calculate the
> > > > >> number of column groups and then using that in an expression for
> the
> > > > >> "GroupsBeforeRowHeaders" property? I'd like to avoid doing this
> because
> > > > > the
> > > > >> start and end times may be weeks, months, or years, so calculating
> the
> > > > >> number of columns would be a pain.
> > > > >>
> > > > >> Thanks for any help,
> > > > >> Sean Carpenter
> > > > >> ProMetrics Consulting, Inc.
> > > > >>
> > > > >>
> > > > >
> > > > >
> > > >
> > > >
> > > >
>
>|||Hello Sean,
GroupsBeforeRowHeaders property is indeed an integer and you will need to
know how many instances you want to move before row headings.
The matrix LayoutDirection = RTL should help you to achieve what you want
and adding a sort expression in the report (the sorting will be done by our
processing component) should help you to sort them in the order you want.
Could you provide the report (created by CU) and a screenshot with the
desired result? You could send them to me at petery@.microsoft.com
Thanks & Regards,
Peter Yang
MCSE2000, MCSA, MCDBA
Microsoft Partner Online Support
Get Secure! - www.microsoft.com/security
=====================================================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.
| Thread-Topic: Matrix Row Headers After Data
| thread-index: AcTuES8Dac2k+nRYQjO7EsINx2zh4g==| X-WBNR-Posting-Host: 141.151.17.169
| From: "=?Utf-8?B?U2VhbiBDYXJwZW50ZXI=?="
<SeanCarpenter@.discussions.microsoft.com>
| References: <u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl>
<u7k3Tj#5EHA.1408@.TK2MSFTNGP10.phx.gbl>
<emm2uuE6EHA.3336@.TK2MSFTNGP11.phx.gbl>
<3F5F013D-5CBD-4D63-AB93-1FAE12CD78FC@.microsoft.com>
<34267414-52D0-45ED-8C0F-14C174F5D1D8@.microsoft.com>
<etWT1$g6EHA.992@.TK2MSFTNGP12.phx.gbl>
| Subject: Re: Matrix Row Headers After Data
| Date: Wed, 29 Dec 2004 17:45:04 -0800
| Lines: 113
| Message-ID: <1032A92D-A366-4E70-8A5C-9734EB226004@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:38404
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| It looks like my solution from the previous post won't work. I wrote a
| function in the Custom Code section of the report to return the correct
| number of column groups based on the report parameters. When I try to
set
| the "GroupsBeforeRowHeaders" property to "=Code.ColumnCount()", it won't
let
| me since it requires an Int32 value for the property.
|
| Does anyone have any ideas on this one? I'd like to have the row headers
to
| the right of the column groups and have it appear that way on Excel
export as
| well.
|
| Thanks,
| Sean Carpenter
| ProMetrics Consulting, Inc.
|
| "Robert Bruckner [MSFT]" wrote:
|
| > Regarding the order - you should add a sort expression on the column
| > grouping of the matrix. The RS processing engine does not change any
order
| > if there is no sorting applied in the report. You get the data in the
order
| > returned by the data provider.
| >
| > --
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| >
| >
| > "Sean Carpenter" <SeanCarpenter@.discussions.microsoft.com> wrote in
message
| > news:34267414-52D0-45ED-8C0F-14C174F5D1D8@.microsoft.com...
| > > I didn't know about that property of the matrix. I tried it and it
works,
| > > except that it reverses the order of all of the columns (which makes
sense
| > > considering what the property does). I can reverse the order of the
| > columns
| > > in the query result so that when the matrix reverses them again they
| > display
| > > correctly, but it would be nice to have a "clean" way of doing this.
This
| > > will definitely get me through the current report, though.
| > >
| > > Sean Carpenter
| > > ProMetrics Consulting, Inc.
| > >
| > > "HBWAL" wrote:
| > >
| > > > I have the same thing and I did not set the group header number.
It is
| > set
| > > > to 0 and the Matrix Columns expand Right to left is selected. Thus
all
| > of my
| > > > columns appear to the left and then my rows appear to right.
| > > >
| > > > Hope this is not to late to help, if it works for you.
| > > >
| > > > "Sean Carpenter" wrote:
| > > >
| > > > > Yes. If I set it to anything larger than the actual number of
column
| > > > > groups, it doesn't appear to do anything - the headers stay to the
| > left of
| > > > > all of the column groups.
| > > > >
| > > > > Sean Carpenter
| > > > > ProMetrics Consulting, Inc.
| > > > >
| > > > > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in
| > message
| > > > > news:u7k3Tj%235EHA.1408@.TK2MSFTNGP10.phx.gbl...
| > > > > > Did you try setting the integer to a very large value, e.g.
| > 100000000
| > > > > >
| > > > > > --
| > > > > > This posting is provided "AS IS" with no warranties, and
confers no
| > > > > > rights.
| > > > > >
| > > > > > "Sean Carpenter" <stcarpenter2005@.community.nospam> wrote in
message
| > > > > > news:u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl...
| > > > > >> I have a report that uses Analysis Services data in a matrix.
The
| > column
| > > > > >> groups in the matrix are based on date, and the number of
column
| > groups
| > > > > >> is
| > > > > >> variable based on the start time and end time entered as report
| > > > > > parameters.
| > > > > >> I'd like to display the row headers after the column groups,
| > regardless
| > > > > >> of
| > > > > >> the number of column groups. The "GroupsBeforeRowHeaders"
property
| > looks
| > > > > >> like it will work, but it only takes an integer - I would like
to
| > set it
| > > > > > to
| > > > > >> "All" or something similar.
| > > > > >>
| > > > > >> Is there any way to do this without writing code that will
| > calculate the
| > > > > >> number of column groups and then using that in an expression
for
| > the
| > > > > >> "GroupsBeforeRowHeaders" property? I'd like to avoid doing
this
| > because
| > > > > > the
| > > > > >> start and end times may be weeks, months, or years, so
calculating
| > the
| > > > > >> number of columns would be a pain.
| > > > > >>
| > > > > >> Thanks for any help,
| > > > > >> Sean Carpenter
| > > > > >> ProMetrics Consulting, Inc.
| > > > > >>
| > > > > >>
| > > > > >
| > > > > >
| > > > >
| > > > >
| > > > >
| >
| >
| >
||||Peter,
I have emailed you an example report that exhibits the problem I'm having.
Thanks for you help.
Sean Carpenter
ProMetrics Consulting, Inc.
"Peter Yang [MSFT]" wrote:
> Hello Sean,
> GroupsBeforeRowHeaders property is indeed an integer and you will need to
> know how many instances you want to move before row headings.
> The matrix LayoutDirection = RTL should help you to achieve what you want
> and adding a sort expression in the report (the sorting will be done by our
> processing component) should help you to sort them in the order you want.
> Could you provide the report (created by CU) and a screenshot with the
> desired result? You could send them to me at petery@.microsoft.com
> Thanks & Regards,
> Peter Yang
> MCSE2000, MCSA, MCDBA
> Microsoft Partner Online Support
> Get Secure! - www.microsoft.com/security
> =====================================================> 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.
>
> --
> | Thread-Topic: Matrix Row Headers After Data
> | thread-index: AcTuES8Dac2k+nRYQjO7EsINx2zh4g==> | X-WBNR-Posting-Host: 141.151.17.169
> | From: "=?Utf-8?B?U2VhbiBDYXJwZW50ZXI=?="
> <SeanCarpenter@.discussions.microsoft.com>
> | References: <u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl>
> <u7k3Tj#5EHA.1408@.TK2MSFTNGP10.phx.gbl>
> <emm2uuE6EHA.3336@.TK2MSFTNGP11.phx.gbl>
> <3F5F013D-5CBD-4D63-AB93-1FAE12CD78FC@.microsoft.com>
> <34267414-52D0-45ED-8C0F-14C174F5D1D8@.microsoft.com>
> <etWT1$g6EHA.992@.TK2MSFTNGP12.phx.gbl>
> | Subject: Re: Matrix Row Headers After Data
> | Date: Wed, 29 Dec 2004 17:45:04 -0800
> | Lines: 113
> | Message-ID: <1032A92D-A366-4E70-8A5C-9734EB226004@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:38404
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | It looks like my solution from the previous post won't work. I wrote a
> | function in the Custom Code section of the report to return the correct
> | number of column groups based on the report parameters. When I try to
> set
> | the "GroupsBeforeRowHeaders" property to "=Code.ColumnCount()", it won't
> let
> | me since it requires an Int32 value for the property.
> |
> | Does anyone have any ideas on this one? I'd like to have the row headers
> to
> | the right of the column groups and have it appear that way on Excel
> export as
> | well.
> |
> | Thanks,
> | Sean Carpenter
> | ProMetrics Consulting, Inc.
> |
> | "Robert Bruckner [MSFT]" wrote:
> |
> | > Regarding the order - you should add a sort expression on the column
> | > grouping of the matrix. The RS processing engine does not change any
> order
> | > if there is no sorting applied in the report. You get the data in the
> order
> | > returned by the data provider.
> | >
> | > --
> | > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> | >
> | >
> | >
> | > "Sean Carpenter" <SeanCarpenter@.discussions.microsoft.com> wrote in
> message
> | > news:34267414-52D0-45ED-8C0F-14C174F5D1D8@.microsoft.com...
> | > > I didn't know about that property of the matrix. I tried it and it
> works,
> | > > except that it reverses the order of all of the columns (which makes
> sense
> | > > considering what the property does). I can reverse the order of the
> | > columns
> | > > in the query result so that when the matrix reverses them again they
> | > display
> | > > correctly, but it would be nice to have a "clean" way of doing this.
> This
> | > > will definitely get me through the current report, though.
> | > >
> | > > Sean Carpenter
> | > > ProMetrics Consulting, Inc.
> | > >
> | > > "HBWAL" wrote:
> | > >
> | > > > I have the same thing and I did not set the group header number.
> It is
> | > set
> | > > > to 0 and the Matrix Columns expand Right to left is selected. Thus
> all
> | > of my
> | > > > columns appear to the left and then my rows appear to right.
> | > > >
> | > > > Hope this is not to late to help, if it works for you.
> | > > >
> | > > > "Sean Carpenter" wrote:
> | > > >
> | > > > > Yes. If I set it to anything larger than the actual number of
> column
> | > > > > groups, it doesn't appear to do anything - the headers stay to the
> | > left of
> | > > > > all of the column groups.
> | > > > >
> | > > > > Sean Carpenter
> | > > > > ProMetrics Consulting, Inc.
> | > > > >
> | > > > > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in
> | > message
> | > > > > news:u7k3Tj%235EHA.1408@.TK2MSFTNGP10.phx.gbl...
> | > > > > > Did you try setting the integer to a very large value, e.g.
> | > 100000000
> | > > > > >
> | > > > > > --
> | > > > > > This posting is provided "AS IS" with no warranties, and
> confers no
> | > > > > > rights.
> | > > > > >
> | > > > > > "Sean Carpenter" <stcarpenter2005@.community.nospam> wrote in
> message
> | > > > > > news:u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl...
> | > > > > >> I have a report that uses Analysis Services data in a matrix.
> The
> | > column
> | > > > > >> groups in the matrix are based on date, and the number of
> column
> | > groups
> | > > > > >> is
> | > > > > >> variable based on the start time and end time entered as report
> | > > > > > parameters.
> | > > > > >> I'd like to display the row headers after the column groups,
> | > regardless
> | > > > > >> of
> | > > > > >> the number of column groups. The "GroupsBeforeRowHeaders"
> property
> | > looks
> | > > > > >> like it will work, but it only takes an integer - I would like
> to
> | > set it
> | > > > > > to
> | > > > > >> "All" or something similar.
> | > > > > >>
> | > > > > >> Is there any way to do this without writing code that will
> | > calculate the
> | > > > > >> number of column groups and then using that in an expression
> for
> | > the
> | > > > > >> "GroupsBeforeRowHeaders" property? I'd like to avoid doing
> this
> | > because
> | > > > > > the
> | > > > > >> start and end times may be weeks, months, or years, so
> calculating
> | > the
> | > > > > >> number of columns would be a pain.
> | > > > > >>
> | > > > > >> Thanks for any help,
> | > > > > >> Sean Carpenter
> | > > > > >> ProMetrics Consulting, Inc.
> | > > > > >>
> | > > > > >>
> | > > > > >
> | > > > > >
> | > > > >
> | > > > >
> | > > > >
> | >
> | >
> | >
> |
>|||Hello Sean,
After consulting the proudct team, there are no plans to change
GroupsBeforeRowHeaders to be an expression.
One possible workaround is to add an outer column grouping with a
GroupExpression like "=1" (so you will have only on column), set its height
to zero and set GroupsBeforeRowHeaders to 1.
Hope this is helpful.
Thanks & Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================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.
| Thread-Topic: Matrix Row Headers After Data
| thread-index: AcTuiwA5dD+6t9c8QheMRd1ZJCE6Wg==| X-WBNR-Posting-Host: 65.126.12.173
| From: "=?Utf-8?B?U2VhbiBDYXJwZW50ZXI=?="
<SeanCarpenter@.discussions.microsoft.com>
| References: <u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl>
<u7k3Tj#5EHA.1408@.TK2MSFTNGP10.phx.gbl>
<emm2uuE6EHA.3336@.TK2MSFTNGP11.phx.gbl>
<3F5F013D-5CBD-4D63-AB93-1FAE12CD78FC@.microsoft.com>
<34267414-52D0-45ED-8C0F-14C174F5D1D8@.microsoft.com>
<etWT1$g6EHA.992@.TK2MSFTNGP12.phx.gbl>
<1032A92D-A366-4E70-8A5C-9734EB226004@.microsoft.com>
<v9rQBQi7EHA.2768@.cpmsftngxa10.phx.gbl>
| Subject: Re: Matrix Row Headers After Data
| Date: Thu, 30 Dec 2004 08:17:04 -0800
| Lines: 208
| Message-ID: <B598246D-BBB6-4269-A943-F082FC51E5DD@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:38437
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Peter,
| I have emailed you an example report that exhibits the problem I'm having.
|
| Thanks for you help.
|
| Sean Carpenter
| ProMetrics Consulting, Inc.
|
| "Peter Yang [MSFT]" wrote:
|
| > Hello Sean,
| >
| > GroupsBeforeRowHeaders property is indeed an integer and you will need
to
| > know how many instances you want to move before row headings.
| >
| > The matrix LayoutDirection = RTL should help you to achieve what you
want
| > and adding a sort expression in the report (the sorting will be done by
our
| > processing component) should help you to sort them in the order you
want.
| >
| > Could you provide the report (created by CU) and a screenshot with the
| > desired result? You could send them to me at petery@.microsoft.com
| >
| > Thanks & Regards,
| >
| > Peter Yang
| > MCSE2000, MCSA, MCDBA
| > Microsoft Partner Online Support
| >
| > Get Secure! - www.microsoft.com/security
| >
| > =====================================================| > 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.
| >
| >
| > --
| > | Thread-Topic: Matrix Row Headers After Data
| > | thread-index: AcTuES8Dac2k+nRYQjO7EsINx2zh4g==| > | X-WBNR-Posting-Host: 141.151.17.169
| > | From: "=?Utf-8?B?U2VhbiBDYXJwZW50ZXI=?="
| > <SeanCarpenter@.discussions.microsoft.com>
| > | References: <u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl>
| > <u7k3Tj#5EHA.1408@.TK2MSFTNGP10.phx.gbl>
| > <emm2uuE6EHA.3336@.TK2MSFTNGP11.phx.gbl>
| > <3F5F013D-5CBD-4D63-AB93-1FAE12CD78FC@.microsoft.com>
| > <34267414-52D0-45ED-8C0F-14C174F5D1D8@.microsoft.com>
| > <etWT1$g6EHA.992@.TK2MSFTNGP12.phx.gbl>
| > | Subject: Re: Matrix Row Headers After Data
| > | Date: Wed, 29 Dec 2004 17:45:04 -0800
| > | Lines: 113
| > | Message-ID: <1032A92D-A366-4E70-8A5C-9734EB226004@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
| > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| > | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > | Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.reportingsvcs:38404
| > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
| > |
| > | It looks like my solution from the previous post won't work. I wrote
a
| > | function in the Custom Code section of the report to return the
correct
| > | number of column groups based on the report parameters. When I try
to
| > set
| > | the "GroupsBeforeRowHeaders" property to "=Code.ColumnCount()", it
won't
| > let
| > | me since it requires an Int32 value for the property.
| > |
| > | Does anyone have any ideas on this one? I'd like to have the row
headers
| > to
| > | the right of the column groups and have it appear that way on Excel
| > export as
| > | well.
| > |
| > | Thanks,
| > | Sean Carpenter
| > | ProMetrics Consulting, Inc.
| > |
| > | "Robert Bruckner [MSFT]" wrote:
| > |
| > | > Regarding the order - you should add a sort expression on the column
| > | > grouping of the matrix. The RS processing engine does not change
any
| > order
| > | > if there is no sorting applied in the report. You get the data in
the
| > order
| > | > returned by the data provider.
| > | >
| > | > --
| > | > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| > | >
| > | >
| > | >
| > | > "Sean Carpenter" <SeanCarpenter@.discussions.microsoft.com> wrote in
| > message
| > | > news:34267414-52D0-45ED-8C0F-14C174F5D1D8@.microsoft.com...
| > | > > I didn't know about that property of the matrix. I tried it and
it
| > works,
| > | > > except that it reverses the order of all of the columns (which
makes
| > sense
| > | > > considering what the property does). I can reverse the order of
the
| > | > columns
| > | > > in the query result so that when the matrix reverses them again
they
| > | > display
| > | > > correctly, but it would be nice to have a "clean" way of doing
this.
| > This
| > | > > will definitely get me through the current report, though.
| > | > >
| > | > > Sean Carpenter
| > | > > ProMetrics Consulting, Inc.
| > | > >
| > | > > "HBWAL" wrote:
| > | > >
| > | > > > I have the same thing and I did not set the group header
number.
| > It is
| > | > set
| > | > > > to 0 and the Matrix Columns expand Right to left is selected.
Thus
| > all
| > | > of my
| > | > > > columns appear to the left and then my rows appear to right.
| > | > > >
| > | > > > Hope this is not to late to help, if it works for you.
| > | > > >
| > | > > > "Sean Carpenter" wrote:
| > | > > >
| > | > > > > Yes. If I set it to anything larger than the actual number
of
| > column
| > | > > > > groups, it doesn't appear to do anything - the headers stay
to the
| > | > left of
| > | > > > > all of the column groups.
| > | > > > >
| > | > > > > Sean Carpenter
| > | > > > > ProMetrics Consulting, Inc.
| > | > > > >
| > | > > > > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote
in
| > | > message
| > | > > > > news:u7k3Tj%235EHA.1408@.TK2MSFTNGP10.phx.gbl...
| > | > > > > > Did you try setting the integer to a very large value, e.g.
| > | > 100000000
| > | > > > > >
| > | > > > > > --
| > | > > > > > This posting is provided "AS IS" with no warranties, and
| > confers no
| > | > > > > > rights.
| > | > > > > >
| > | > > > > > "Sean Carpenter" <stcarpenter2005@.community.nospam> wrote
in
| > message
| > | > > > > > news:u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl...
| > | > > > > >> I have a report that uses Analysis Services data in a
matrix.
| > The
| > | > column
| > | > > > > >> groups in the matrix are based on date, and the number of
| > column
| > | > groups
| > | > > > > >> is
| > | > > > > >> variable based on the start time and end time entered as
report
| > | > > > > > parameters.
| > | > > > > >> I'd like to display the row headers after the column
groups,
| > | > regardless
| > | > > > > >> of
| > | > > > > >> the number of column groups. The "GroupsBeforeRowHeaders"
| > property
| > | > looks
| > | > > > > >> like it will work, but it only takes an integer - I would
like
| > to
| > | > set it
| > | > > > > > to
| > | > > > > >> "All" or something similar.
| > | > > > > >>
| > | > > > > >> Is there any way to do this without writing code that will
| > | > calculate the
| > | > > > > >> number of column groups and then using that in an
expression
| > for
| > | > the
| > | > > > > >> "GroupsBeforeRowHeaders" property? I'd like to avoid
doing
| > this
| > | > because
| > | > > > > > the
| > | > > > > >> start and end times may be weeks, months, or years, so
| > calculating
| > | > the
| > | > > > > >> number of columns would be a pain.
| > | > > > > >>
| > | > > > > >> Thanks for any help,
| > | > > > > >> Sean Carpenter
| > | > > > > >> ProMetrics Consulting, Inc.
| > | > > > > >>
| > | > > > > >>
| > | > > > > >
| > | > > > > >
| > | > > > >
| > | > > > >
| > | > > > >
| > | >
| > | >
| > | >
| > |
| >
| >
||||I guess the bigger problem for me right now is that the Excel export ignores
the "LayoutDirection" property of the matrix. Is it possible to get this
fixed as a bug?
My overall problem (which is displaying row headers to the right of the
columns) still exists. Does anyone have any ideas? The whole idea of the
matrix is to have a variable number of columns - if I knew how many columns I
had I could use a table instead.
Sean Carpenter
ProMetrics Consulting, Inc.
"Peter Yang [MSFT]" wrote:
> Hello Sean,
> After consulting the proudct team, there are no plans to change
> GroupsBeforeRowHeaders to be an expression.
> One possible workaround is to add an outer column grouping with a
> GroupExpression like "=1" (so you will have only on column), set its height
> to zero and set GroupsBeforeRowHeaders to 1.
> Hope this is helpful.
> Thanks & Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> =====================================================> 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.
>
> --
> | Thread-Topic: Matrix Row Headers After Data
> | thread-index: AcTuiwA5dD+6t9c8QheMRd1ZJCE6Wg==> | X-WBNR-Posting-Host: 65.126.12.173
> | From: "=?Utf-8?B?U2VhbiBDYXJwZW50ZXI=?="
> <SeanCarpenter@.discussions.microsoft.com>
> | References: <u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl>
> <u7k3Tj#5EHA.1408@.TK2MSFTNGP10.phx.gbl>
> <emm2uuE6EHA.3336@.TK2MSFTNGP11.phx.gbl>
> <3F5F013D-5CBD-4D63-AB93-1FAE12CD78FC@.microsoft.com>
> <34267414-52D0-45ED-8C0F-14C174F5D1D8@.microsoft.com>
> <etWT1$g6EHA.992@.TK2MSFTNGP12.phx.gbl>
> <1032A92D-A366-4E70-8A5C-9734EB226004@.microsoft.com>
> <v9rQBQi7EHA.2768@.cpmsftngxa10.phx.gbl>
> | Subject: Re: Matrix Row Headers After Data
> | Date: Thu, 30 Dec 2004 08:17:04 -0800
> | Lines: 208
> | Message-ID: <B598246D-BBB6-4269-A943-F082FC51E5DD@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:38437
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Peter,
> | I have emailed you an example report that exhibits the problem I'm having.
> |
> | Thanks for you help.
> |
> | Sean Carpenter
> | ProMetrics Consulting, Inc.
> |
> | "Peter Yang [MSFT]" wrote:
> |
> | > Hello Sean,
> | >
> | > GroupsBeforeRowHeaders property is indeed an integer and you will need
> to
> | > know how many instances you want to move before row headings.
> | >
> | > The matrix LayoutDirection = RTL should help you to achieve what you
> want
> | > and adding a sort expression in the report (the sorting will be done by
> our
> | > processing component) should help you to sort them in the order you
> want.
> | >
> | > Could you provide the report (created by CU) and a screenshot with the
> | > desired result? You could send them to me at petery@.microsoft.com
> | >
> | > Thanks & Regards,
> | >
> | > Peter Yang
> | > MCSE2000, MCSA, MCDBA
> | > Microsoft Partner Online Support
> | >
> | > Get Secure! - www.microsoft.com/security
> | >
> | > =====================================================> | > 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.
> | >
> | >
> | > --
> | > | Thread-Topic: Matrix Row Headers After Data
> | > | thread-index: AcTuES8Dac2k+nRYQjO7EsINx2zh4g==> | > | X-WBNR-Posting-Host: 141.151.17.169
> | > | From: "=?Utf-8?B?U2VhbiBDYXJwZW50ZXI=?="
> | > <SeanCarpenter@.discussions.microsoft.com>
> | > | References: <u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl>
> | > <u7k3Tj#5EHA.1408@.TK2MSFTNGP10.phx.gbl>
> | > <emm2uuE6EHA.3336@.TK2MSFTNGP11.phx.gbl>
> | > <3F5F013D-5CBD-4D63-AB93-1FAE12CD78FC@.microsoft.com>
> | > <34267414-52D0-45ED-8C0F-14C174F5D1D8@.microsoft.com>
> | > <etWT1$g6EHA.992@.TK2MSFTNGP12.phx.gbl>
> | > | Subject: Re: Matrix Row Headers After Data
> | > | Date: Wed, 29 Dec 2004 17:45:04 -0800
> | > | Lines: 113
> | > | Message-ID: <1032A92D-A366-4E70-8A5C-9734EB226004@.microsoft.com>
> | > | MIME-Version: 1.0
> | > | Content-Type: text/plain;
> | > | charset="Utf-8"
> | > | Content-Transfer-Encoding: 7bit
> | > | X-Newsreader: Microsoft CDO for Windows 2000
> | > | Content-Class: urn:content-classes:message
> | > | Importance: normal
> | > | Priority: normal
> | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | > | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | > | Xref: cpmsftngxa10.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:38404
> | > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> | > |
> | > | It looks like my solution from the previous post won't work. I wrote
> a
> | > | function in the Custom Code section of the report to return the
> correct
> | > | number of column groups based on the report parameters. When I try
> to
> | > set
> | > | the "GroupsBeforeRowHeaders" property to "=Code.ColumnCount()", it
> won't
> | > let
> | > | me since it requires an Int32 value for the property.
> | > |
> | > | Does anyone have any ideas on this one? I'd like to have the row
> headers
> | > to
> | > | the right of the column groups and have it appear that way on Excel
> | > export as
> | > | well.
> | > |
> | > | Thanks,
> | > | Sean Carpenter
> | > | ProMetrics Consulting, Inc.
> | > |
> | > | "Robert Bruckner [MSFT]" wrote:
> | > |
> | > | > Regarding the order - you should add a sort expression on the column
> | > | > grouping of the matrix. The RS processing engine does not change
> any
> | > order
> | > | > if there is no sorting applied in the report. You get the data in
> the
> | > order
> | > | > returned by the data provider.
> | > | >
> | > | > --
> | > | > This posting is provided "AS IS" with no warranties, and confers no
> | > rights.
> | > | >
> | > | >
> | > | >
> | > | > "Sean Carpenter" <SeanCarpenter@.discussions.microsoft.com> wrote in
> | > message
> | > | > news:34267414-52D0-45ED-8C0F-14C174F5D1D8@.microsoft.com...
> | > | > > I didn't know about that property of the matrix. I tried it and
> it
> | > works,
> | > | > > except that it reverses the order of all of the columns (which
> makes
> | > sense
> | > | > > considering what the property does). I can reverse the order of
> the
> | > | > columns
> | > | > > in the query result so that when the matrix reverses them again
> they
> | > | > display
> | > | > > correctly, but it would be nice to have a "clean" way of doing
> this.
> | > This
> | > | > > will definitely get me through the current report, though.
> | > | > >
> | > | > > Sean Carpenter
> | > | > > ProMetrics Consulting, Inc.
> | > | > >
> | > | > > "HBWAL" wrote:
> | > | > >
> | > | > > > I have the same thing and I did not set the group header
> number.
> | > It is
> | > | > set
> | > | > > > to 0 and the Matrix Columns expand Right to left is selected.
> Thus
> | > all
> | > | > of my
> | > | > > > columns appear to the left and then my rows appear to right.
> | > | > > >
> | > | > > > Hope this is not to late to help, if it works for you.
> | > | > > >
> | > | > > > "Sean Carpenter" wrote:
> | > | > > >
> | > | > > > > Yes. If I set it to anything larger than the actual number
> of
> | > column
> | > | > > > > groups, it doesn't appear to do anything - the headers stay
> to the
> | > | > left of
> | > | > > > > all of the column groups.
> | > | > > > >
> | > | > > > > Sean Carpenter
> | > | > > > > ProMetrics Consulting, Inc.
> | > | > > > >
> | > | > > > > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote
> in
> | > | > message
> | > | > > > > news:u7k3Tj%235EHA.1408@.TK2MSFTNGP10.phx.gbl...
> | > | > > > > > Did you try setting the integer to a very large value, e.g.
> | > | > 100000000
> | > | > > > > >
> | > | > > > > > --
> | > | > > > > > This posting is provided "AS IS" with no warranties, and
> | > confers no
> | > | > > > > > rights.
> | > | > > > > >
> | > | > > > > > "Sean Carpenter" <stcarpenter2005@.community.nospam> wrote
> in
> | > message
> | > | > > > > > news:u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl...
> | > | > > > > >> I have a report that uses Analysis Services data in a
> matrix.
> | > The
> | > | > column
> | > | > > > > >> groups in the matrix are based on date, and the number of
> | > column
> | > | > groups
> | > | > > > > >> is
> | > | > > > > >> variable based on the start time and end time entered as
> report
> | > | > > > > > parameters.
> | > | > > > > >> I'd like to display the row headers after the column
> groups,
> | > | > regardless
> | > | > > > > >> of
> | > | > > > > >> the number of column groups. The "GroupsBeforeRowHeaders"
> | > property
> | > | > looks
> | > | > > > > >> like it will work, but it only takes an integer - I would
> like
> | > to
> | > | > set it
> | > | > > > > > to
> | > | > > > > >> "All" or something similar.
> | > | > > > > >>
> | > | > > > > >> Is there any way to do this without writing code that will
> | > | > calculate the
> | > | > > > > >> number of column groups and then using that in an
> expression
> | > for
> | > | > the
> | > | > > > > >> "GroupsBeforeRowHeaders" property? I'd like to avoid
> doing
> | > this
> | > | > because
> | > | > > > > > the
> | > | > > > > >> start and end times may be weeks, months, or years, so
> | > calculating
> | > | > the
> | > | > > > > >> number of columns would be a pain.
> | > | > > > > >>
> | > | > > > > >> Thanks for any help,
> | > | > > > > >> Sean Carpenter
> | > | > > > > >> ProMetrics Consulting, Inc.
> | > | > > > > >>
> | > | > > > > >>
> | > | > > > > >
> | > | > > > > >
> | > | > > > >
> | > | > > > >
> | > | > > > >
> | > | >
> | > | >
> | > | >
> | > |
> | >
> | >
> |
>|||Hello Sean,
I'd like to know if you have try the workaround as I suggested on the
sample report you send to me.
Set outer column grouping with a GroupExpression like "=1"
1). Change the dataset query to: (remove the sorting function)
SELECT { Measures.[Unit Sales] } on columns,
NON EMPTY CROSSJOIN({ Store.[Store State].Members },
Time.[1997].[Q1].[1]:Time.[1997].[Q4].[12] ) on rows
from Sales
2). Right click the Matrix up-right corner->Properties
3). On Group tab, select a Column group, click Add to add a new column
group. Name it Columngroup2
4). On the General tab, type "=1" (without quotos) in Expression textbox
under "Group on"
5). Move it to the first one in the column group.
6). On General tab of Matrix property dialog, select "Left to right"
7). Select "1" under Groups before row headers.
8). Right click cell with "=1" in matrix->Properties->Advanced->Visibility,
select Hidden.
9). Preview the report, you shall see the proper sequece of rows and row
headers
10). Deploy and export the report to excel, the sequence is also correct.
Thus, we need not set LayoutDirection from "right to left". You can sort it
as you want in dataset query and it will properly shown in the report.
Because there is a hidden column group with only 1 column, you can always
set "Groups before row headers" to 1 no matter how many real column in the
secondary column group.
Hope this is helpful.
Thanks & Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security|||Peter -
I'm sorry - when I first read your reply with the workaround, I misread
adding the hidden column group as adding a hidden row group and didn't see
how it would work. This workaround did work for me and my report now works
the way I need it to.
Thanks for your help,
Sean Carpenter
ProMetrics Consulting, Inc.
"Peter Yang [MSFT]" wrote:
> Hello Sean,
> I'd like to know if you have try the workaround as I suggested on the
> sample report you send to me.
> Set outer column grouping with a GroupExpression like "=1"
> 1). Change the dataset query to: (remove the sorting function)
> SELECT { Measures.[Unit Sales] } on columns,
> NON EMPTY CROSSJOIN({ Store.[Store State].Members },
> Time.[1997].[Q1].[1]:Time.[1997].[Q4].[12] ) on rows
> from Sales
> 2). Right click the Matrix up-right corner->Properties
> 3). On Group tab, select a Column group, click Add to add a new column
> group. Name it Columngroup2
> 4). On the General tab, type "=1" (without quotos) in Expression textbox
> under "Group on"
> 5). Move it to the first one in the column group.
> 6). On General tab of Matrix property dialog, select "Left to right"
> 7). Select "1" under Groups before row headers.
> 8). Right click cell with "=1" in matrix->Properties->Advanced->Visibility,
> select Hidden.
> 9). Preview the report, you shall see the proper sequece of rows and row
> headers
> 10). Deploy and export the report to excel, the sequence is also correct.
> Thus, we need not set LayoutDirection from "right to left". You can sort it
> as you want in dataset query and it will properly shown in the report.
> Because there is a hidden column group with only 1 column, you can always
> set "Groups before row headers" to 1 no matter how many real column in the
> secondary column group.
> Hope this is helpful.
> Thanks & Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
>|||Hello Sean,
Glad to hear this work for you. Have a great day!
Thanks & Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================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.
| Thread-Topic: Matrix Row Headers After Data
| thread-index: AcTz8/os9oJcCNQXSAetcHm6AQwaQQ==| X-WBNR-Posting-Host: 65.126.12.173
| From: "=?Utf-8?B?U2VhbiBDYXJwZW50ZXI=?="
<SeanCarpenter@.discussions.microsoft.com>
| References: <u2lwLU55EHA.1632@.tk2msftngp13.phx.gbl>
<u7k3Tj#5EHA.1408@.TK2MSFTNGP10.phx.gbl>
<emm2uuE6EHA.3336@.TK2MSFTNGP11.phx.gbl>
<3F5F013D-5CBD-4D63-AB93-1FAE12CD78FC@.microsoft.com>
<34267414-52D0-45ED-8C0F-14C174F5D1D8@.microsoft.com>
<etWT1$g6EHA.992@.TK2MSFTNGP12.phx.gbl>
<1032A92D-A366-4E70-8A5C-9734EB226004@.microsoft.com>
<v9rQBQi7EHA.2768@.cpmsftngxa10.phx.gbl>
<B598246D-BBB6-4269-A943-F082FC51E5DD@.microsoft.com>
<kBQgVJy8EHA.3520@.cpmsftngxa10.phx.gbl>
<1ED5A573-D4EE-43A6-B0F9-E9389D41DBC1@.microsoft.com>
<vRavHH78EHA.3520@.cpmsftngxa10.phx.gbl>
| Subject: Re: Matrix Row Headers After Data
| Date: Thu, 6 Jan 2005 05:31:07 -0800
| Lines: 65
| Message-ID: <36BE3E3D-63BE-45EE-9A5E-2737921B3644@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:38820
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Peter -
| I'm sorry - when I first read your reply with the workaround, I misread
| adding the hidden column group as adding a hidden row group and didn't
see
| how it would work. This workaround did work for me and my report now
works
| the way I need it to.
|
| Thanks for your help,
| Sean Carpenter
| ProMetrics Consulting, Inc.
|
| "Peter Yang [MSFT]" wrote:
|
| > Hello Sean,
| >
| > I'd like to know if you have try the workaround as I suggested on the
| > sample report you send to me.
| >
| > Set outer column grouping with a GroupExpression like "=1"
| >
| > 1). Change the dataset query to: (remove the sorting function)
| >
| > SELECT { Measures.[Unit Sales] } on columns,
| > NON EMPTY CROSSJOIN({ Store.[Store State].Members },
| > Time.[1997].[Q1].[1]:Time.[1997].[Q4].[12] ) on rows
| > from Sales
| >
| > 2). Right click the Matrix up-right corner->Properties
| >
| > 3). On Group tab, select a Column group, click Add to add a new column
| > group. Name it Columngroup2
| >
| > 4). On the General tab, type "=1" (without quotos) in Expression
textbox
| > under "Group on"
| >
| > 5). Move it to the first one in the column group.
| >
| > 6). On General tab of Matrix property dialog, select "Left to right"
| >
| > 7). Select "1" under Groups before row headers.
| >
| > 8). Right click cell with "=1" in
matrix->Properties->Advanced->Visibility,
| > select Hidden.
| >
| > 9). Preview the report, you shall see the proper sequece of rows and
row
| > headers
| >
| > 10). Deploy and export the report to excel, the sequence is also
correct.
| >
| > Thus, we need not set LayoutDirection from "right to left". You can
sort it
| > as you want in dataset query and it will properly shown in the report.
| > Because there is a hidden column group with only 1 column, you can
always
| > set "Groups before row headers" to 1 no matter how many real column in
the
| > secondary column group.
| >
| > Hope this is helpful.
| >
| > Thanks & Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner Support
| >
| > Get Secure! - www.microsoft.com/security
| >
| >
|