Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jlafargue
Starting Member
2 Posts |
 Posted - 2009-10-21 : 17:24:30
|
| I have to do a report using a matrix control. The report is to put out a matrix tracking sales for a date range by store, hour of the day and day of the week. Each store is to have its own chart which is to be broken down this way: There is a row for each hour of the day and a column for each day of the week plus a row for sales with invalid sales times and a column for sales with invalid sales days. There is to be a totals column for rows and a totals row for each column. Cells in the chart/matrix which have no sales are to have '0' in them.I've been able so far to do a select that pulls out the hour of the sale and day of the week as it retrieves the date of the sale and store from which the sale was made, but I don't know how to total across for each hour of the day and total down for each day of the week. The number in each The matrix control leaves out columns where each cell is 0 and rows where each cell is 0. I need to have blank rows and columns to show. Is there a way to force a matrix to be a certain size? They've thrown us in to this with no training or experience, and it's sink or swim. Any help will be greatly appreciated. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-10-22 : 13:07:44
|
| because matrix builds column values dynamically based on data, you wont have any columns if you dont have data for a particular day of week. if you want to get all days, you need to have a master table containing all days and then left join it with your table. |
 |
|
|
jlafargue
Starting Member
2 Posts |
Posted - 2009-10-23 : 17:25:07
|
| I was afraid of that. How should I set up the master table? I have two tables set up now, one for hours of the day and one for days of the week, but I'm thinking I should combine those into one table so that I can match on the hour and day of the week on one table rather than to match on fields in two tables at the same time.This is my signature |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-10-24 : 04:47:06
|
| i think you need to have a calendar table with month,year,date,... which you could use as the master. |
|
|
|
|
|
|
Matrix Control - Sql Server Reporting Service