January 20, 2009 at 11:23 am
Hi,
Is there an easy way to show months with zero values where data source is a pivot table?
So basically I've got a data source which contains rows with data on products sold for a given month. The report pivots months so they are displayed as columns. The problem is if there is no data for Febuary for example the column doesn't appear.
The customer wants the columns January to December displaying irrespective or whether or not they contain any values.
Can anyone help?
Kind Regards,
Kieran
January 20, 2009 at 11:30 am
Well, with only a description of your problem, you probably won't get many useful responses. Please read the first article I have referenced below in my signature block regarding asking for assistance. If you follow the guidelines for posting questions in that article, I am sure you will get much better responses.
January 20, 2009 at 11:43 am
If you join the results to a table with all the months in it, and use IsNull for the results, that might do what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 21, 2009 at 5:51 am
As far as i am aware, this is not possible if there is no data for any of your rows for a one of your column groups
There are two options
Option 1
An alternative is to re-write the query to cater for the number of months you require (ie do not use a pivot\matrix) but use a table instead
Option 2.
Use an Sp to write to a Temp table for each Month. This will ensure that there is always a row for each month
January 27, 2009 at 2:57 am
OK Thanks. There are some helpful hints here.
Kind Regards,
Kieran.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply