how can i show date column headers in a matrix report even when there are no rows for the specified date

Question

how can i show date column headers in a matrix report even when there are no rows for the specified date

Nickson Asiimwe

SSC Eights!

Points: 812
More actions
September 21, 2009 at 2:42 am

#214588

i have a matrix report with the startDate and endDate parameters. the matrix's column corresponds to the source table's Date field. Now the problem is, if the number of dates between startDate and endDate is say 5 days, then i would like my matrix report to show 5 columns(for the 5 days) even if data for some days in the date range does not exist.
If i want to retrieve data for say monday to friday but in the database table, there is only data Monday and Tuesday, i still want the matrix report columns for wensday, thursday and friday to be displayed such that some one viewing the report can easily tell that data for other days included in the query is not available.
i'm trying to think of how i can achieve this but i'm currently failing to get a work around. Any suggestion is welcome.

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

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

MWise SSCarpal Tunnel Points: 4074 More actions · Answer 1

Basically you need to get those dates with empty values into your dataset so the matrix can display them. How are you retrieving the data? If it is from a cube you can set the Include Empty Cells option in your dataset. If you are querying against a SQL source, can you edit the SQL that feeds the dataset? If so you can use a dates or tally table to generate all of the dates needed and either do a left outer join to your data or a UNION to tack on the dates that don't have data in your table.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Just so that more than one voice is heard on this... I agree with Mhlewis on this. An outer join to a Calendar table or a derived table genned by a Tally table would certainly do the trick.

Please post back if you need help on either but, in the meantime, take a look at the following article... it has code to generate dates near the end of the article.

http://www.sqlservercentral.com/articles/T-SQL/62867/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 3

A smal hint.

Having the same situation I created the tally table with dates in Excel.

Set the format to Date for a column.

Start with in (sweden) 2009-01-01 row 1

2009-01-02 row 2.

Fill down and you will have all dates in 2009 and so on

Save as tab text file and import to a table in the server.

Quick and dirty?

/Gosta

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 4

A small hint more.

If you want to remove all saturdays and sundays (not working days).

Put a column B besides A.

Write thursday for 2009-01-01, friday for 2009-01-02

and fill down.

All weekdays per date will be shown and you can sort and delete

not wanted weekdays (and manually remove holidays).

Save in tab text fiel date and weekday to be imported into the server.

/Gosta

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

Gosta Munktell (9/23/2009)Quick and dirty?

Heh... very... it's quite easy to do exactly the same thing in T-SQL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)