Help with SQL Pivit Function values to Design Chart

Question

Help with SQL Pivit Function values to Design Chart

mustansarhussain

SSC Enthusiast

Points: 118
More actions
May 14, 2009 at 8:16 am

#134236

SQL 2005 has brilliant Pivot function and this helped me a lot in solvin my problems. Now when i can display my data in a very nice and tiday way, I'm havin problem displayin the same data in Charts, here is the code and output
Select * From (Select FinYear, FinMonthNm, LoS From Surgery) BS
PIVOT (avg(LoS) For FinMonthNm IN ([Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec],[Jan],[Feb],[Mar]))P
This display data as below which is great, but when i add these months in my Chart i get this error "RowIndex Property Value Should be in range from 0 to 10".. I there anyway i could group these months as one series in this function
Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar
2007/08 8 8 7 8 15 8 18 9 8 7 7 8
2008/09 17 11 8 12 10 14 15 12 38 8 12 16
2009/10 9 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

could you post an example of what you want your output to be?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

mustansarhussain SSC Enthusiast Points: 118 More actions · Answer 2

In SQL 2000 i solve using this code

Select FinYear, FinMonth, FinMonthNm,

Case When FinYear = '2007/08' Then AvgLoS End As AvgLoS200708,

Case When FinYear = '2008/09' Then AvgLoS End As AvgLoS200809,

Case When FinYear = '2009/10' Then AvgLoS End As AvgLoS200910

From (Select FinYear , FinMonth, FinMonthNm, avg(LoS) As AvgLoS From Patient

And this generate chart with FinYear 2007/08, 2008/09 As value and Group Data by each month in that finincial year..

I've tried copied the exact chart but coudnt, bcoz forum dosent allow that... please let me know if u need any further information..

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 3

that could should work in 2005 as well.

I'm still not 100% sure what the problem is.

Could you show me for example

something like this:

--HOW TO CREATE TABLE

DECLARE @Patient TABLE

(

FinYear VARCHAR(10),

FinMonthNm VARCHAR(10),

FinMonth INT,

LoS MONEY

)

--SAMPLE DATA

INSERT INTO @Patient

SELECT '2007/08','Jan',1,5.0 UNION ALL

SELECT '2007/08','Jan',1,10.0 UNION ALL

SELECT '2007/08','Dec',12,5.0 UNION ALL

SELECT '2008/09','Mar',3,99.9 UNION ALL

SELECT '2009/10','Dec',12,NULL

--RESULT SET

Select * From (Select FinYear, FinMonthNm, LoS From @Patient) BS

PIVOT (avg(LoS) For FinMonthNm IN ([Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec],[Jan],[Feb],[Mar]))P

--NOW DISPLAY ALSO THE LAYOUT OF EXAMPLE FOR THE ABOVE DATA OR WHAT YOU WANT

Please aslo refer to my sig for tips on how to post and get the most help you can from your posts

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

mustansarhussain SSC Enthusiast Points: 118 More actions · Answer 4

The problem is not displayin data, it is to to add this month in Reporting Services Charts, what i was trying to do is to group these month as one serious e.g. doing this

Select * From (Select FinYear As FinYear, FinMonthNm, LoS From Bowel_Surgery)BS

Pivot (Avg(LoS) For FinMonthNm As AllFinMonths IN ([Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec],[Jan],[Feb],[Mar]))P or

Select * From (Select FinYear As FinYear, FinMonthNm, LoS From Bowel_Surgery)BS

Pivot (Avg(LoS) For FinMonthNm IN ([Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec],[Jan],[Feb],[Mar] As AllFinMonth))P

So can add AllFinMonth in chart series as one, rather then adding 12 month one by one

I get error near As in all cases

Any idea how i can group these month as one..

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 5

are you saying that you only want 2 columns return

YEAR ALL_MONTHS(AVG)

2008/09 209

2009/10 117

etc

?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

mustansarhussain SSC Enthusiast Points: 118 More actions · Answer 6

Apologies for not being very clear about what i want, but i'm sure this image will help

This is how i want to display my chart, using sql2000 my data was all over the place, but now i'm tryin to modify these using sql2005

In SQL2005 Reporting services you can add chart Catagory fields in the case FinMonth and Series Fields which are FinYears, this gives me year on year activity or length of stays in each month