blank Columns?

Question

blank Columns?

newbie_new

SSC Enthusiast

Points: 162
More actions
March 6, 2009 at 4:02 pm

#207241

Is is possible to get a matrix to create a blank column? I have a dataset that should have a specific row of data in it, but because there was no data availabe for that month it's not in the table, but i'd like to make the report show it's column of blank values?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jack Corbett SSC Guru Points: 184388 More actions · Answer 1

Can you share an example of your query, actual output, and desired output? I believe you would need to adjust your query to output a row for any months that have no data by using an outer or cross join depending on you data structures.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

newbie_new SSC Enthusiast Points: 162 More actions · Answer 2

Jack:

thanks for your reply!

heres the dataset:

CREATE TABLE #TempStorage (

[pkRecordKey] [char] (17) ,

[SignalID] [smallint] NOT NULL ,

[LocalTime] [datetime] NULL ,

[fValue] [float] NULL ,

[bValue] [bit] NULL ,

[sValue] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[GoodQual] [bit] NOT NULL,

[ManualValue] [float] NULL,

[EffectiveValue] [float] NULL ,

[Operator] [varchar] (255) NULL,

[SignalName] [varchar] (50) NOT NULL )

INSERT #TempStorage exec spGetData2009 CP3,@StartTime,@EndTime,60

INSERT #TempStorage exec spGetData2009 FLT,@StartTime,@EndTime,60

Select * from #TempStorage

DROP TABLE #TempStorage

The report matrix is set up to let in about 28 SignalName columns of data by filter, but only shows about 23. My question is can we make blanks columns for the remainder 5 columns?

Luke L SSC-Dedicated Points: 35545 More actions · Answer 3

The way I've done this in the past has either been with a cross join as Jack suggested or you could use a union with a mostly blank dataset as per the following psuedocode. YMMV but due to some indexing issues I sometimes find the union to work more efficiently.

SELECT * FROM your actual data

Union

SELECT '' or NULL , First Day of Month

This can all be done fairly easily if you have a calendar table or tally table too.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

newbie_new SSC Enthusiast Points: 162 More actions · Answer 4

Yeah, i figured out a way to do it that might not be as fast as i want, but it does the trick.

thanks for the idea