September 21, 2009 at 2:42 am
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.
September 22, 2009 at 2:31 pm
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.
September 22, 2009 at 5:28 pm
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://qa.sqlservercentral.com/articles/T-SQL/62867/
--Jeff Moden
September 23, 2009 at 12:29 pm
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
September 23, 2009 at 12:44 pm
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
September 23, 2009 at 4:14 pm
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply