Non-aggregate matrix? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Non-aggregate matrix?

Author

Topic

Mr Zero Percent
Starting Member

2 Posts

Posted - 2009-05-17 : 09:48:58

Hi there,

Could someone please help me out?

I'm looking to build a matrix report but think this might not be possible.

What it is, I need to cross-tabulate non-aggregate values from a dataset (easy enough). However i'm also looking to display additional non-aggregate data (dates) in the row field of the cross-tab as the report would be visually too large to display date information horizontally. I also need to do this so I can performs some date/string comparisons so the and need the data in a constant format.

So to cut the story short, is it possible to force data that should ordinarily be displayed vertically in the row field of the matrix as horizontally across several row fields? A bit like transposing columns to rows so all data relevant to the column is displayed on one row? Or is it possible at the very least to concatenate vertical row values into one row field?

I know this wouldn't be good practice but i'm trying to stop a project ballooning in size and effort.

THANKS ALOT oh wise ones!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-17 : 09:51:13

its possible. would be able to give sample of your data & output you need so that we can understand how you want it to appear in report

Mr Zero Percent
Starting Member

2 Posts

Posted - 2009-05-17 : 10:07:25

Hi Vishka,

Thanks for your reply. Sorry I can't show you real data at the moment - all my data is in the office and i'm at home 'relaxing'. Would it be possible for you to briefly explain how I might go about it?

Does this small example help?

Existing data (but on a MUCH bigger scale) - Excuse the dashes, just trying to keep it ordered.

Name--Dates---------String
Chris --01/09/2008 ---//A//B
Chris--02/08/2009----/A/B/C
Will----23/04/2008---/B/A///
James--31/05/2008---/C///A/
James --24/10/2008--/A//A/B

I would like it to look like:
row------row------------row------------column---column
Name----Date 1--------Date 2----------String----String
Chris ---01/09/2008----02/08/2009-----//A//B---/A/B/C
Will-----23/04/2008----NULL------------/B/A///--NULL
James---31/05/2007----24/10/2008-----/C///A/--/A//A/B

Is that a suitable example?

The key point is I need a consistent output and the date fields not transposed as part of the matrix column. Also, I'm no master of SQL!

Many thanks. :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-17 : 13:23:39

What you can do is to make a query like this

SELECT Name,Dates,String,ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Dates) AS Seq
FROM Table

Then create a dataset of below.
Now add a matrix to report and link it to above created dataset. Add RowGroup AS Name AND ColumnGroup AS Seq. Then add two data fields one as MAX(Fields!Dates.Value) and other as MAX(Fields!String.Value)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources