Changing the columns in a table

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)

Changing the columns in a table

Author

Topic

ValuableTime
Starting Member

3 Posts

Posted - 2010-09-22 : 16:52:06

Hi,
I have the follo specification

Table --
ITEM, MONTH, QTY1, QTY2
A , 1 , 10 , 5
B , 2 , 20 , 15
A , 1 , 10 , 15
B , 2 , 5 , 10

Now, I need to display something like this..

ITEM QTY1 JAN QTY2 JAN QTY1 FEB QTY2 FEB ...........for 12 months.
A 20 (10+10) 20(5+15) 0 0
B 0 0 15 15
NOTE: Braces given for explanation. Actual report does'nt have brackets and the values inside it.i.e., under qty1 jan there is just 20 and 0 for A and B resp..

So, I have done it using case and group by.

Now, I need this columns to be changed dynamically, meaning, the user is given an option of selecting the month. Say, if the user selected FEB 2009 , then the report should have follo columns..

ITEM QTY1FEB2009 QTY2FEB2009 QTY1MAR2009 QTY2MAR2009 QTY1APR2009 QTY2APR2009........till.. QTY1JAN2010 QTY2JAN2010

Also, right now, the column headers just show a static value. Is there any way where we can change the column headers with the month and year value.

Thanks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-22 : 17:33:43

If you're using Reporting Services it is far better to do this as a Matrix/Tablix report rather than write SQL to do it. Just add the Month and Year to the columns you return and use them in an expression for the matrix to cross-tabulate.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-23 : 13:47:02

just give row group as item and column group as month and year in matrix and use required aggregate function in expression (sum in your case)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ValuableTime
Starting Member

3 Posts

Posted - 2010-09-24 : 09:42:09

Thanks for the reply and I am trying the same now.
Also, I have another problem.

I want to hide an entire column in the report. I can do it by clicking the particular column in the table -> Properties -> Visible -> Expression -> IsNothing(Fields!FieldName.Value).

This is working only if the first value of the column field is having a value. My column fields consists of both numeric values as well as null values. I want to check the entire column field and see if it has null value. Only then, I want it to be hidden. Otherwise, the column should be visible.
Pls help on this. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources