March 8, 2006 at 2:22 pm
I hope this one is pretty easy, and my co-worker and I have just been overlooking it, but it might not be...
We have a couple of reports that list all the months (in a user selected range), a metric like units (shipped, cost, etc.), and the years (in a user selected range). The result should look like this:
2001 2002 2003 2004
Jan 0 0 50 100
Feb 0 22 23 88
Mar 0 0 0 66
Apr 501 44 4 1
May 0 0 0 0
We have the queries setup properly, but we still have a couple problems. Any place there is no value for a month/year combo (i.e. no orders that month), we get a blank. This doesn't fly, since the user wants to see a zero, and they want to see every year and month in the selected range.
So, the questions are:
1. Can we set a default value for cells in the matrix?
2. Is there any way to ensure every value appears in both the column and row headers?
3. Is there any other way around this besides a union in the SQL?
Currently, we have been trying an IIf, but it hasn't been working for some reason, so we would like all the help we can get.
Thanks in advance
March 9, 2006 at 2:42 am
1. use a custom format in the value cell of
#;#;0
to ensure that zeroes are printed out.
2. I've had the same problem with specifying column headers; would be happy to learn an answer to that one! To get all the row headers, add in a row for eah one with a blank column value and blank cell value. That'll just give you an extra blank column.
3. If you populate a temporary table rather than do a union, you can easily pick up the rows which aren't present in the data by left joining to what's already in your table, picking up the ones with a null foreign key then inserting them (as above).
March 9, 2006 at 4:14 am
hmmm you've got a bit of a problem there... the best way to ensure that a column will always exist even if there's no data is to greate a group on Month and for each year create a calc field, then you can populate these dynamically by getting the user to enter a start year and then the report runs for x number of years. eg
@StartYear = 2001
@NoOfyears = 2
Yr2001 = IIF (Year = @StartYear, 1, 0)
Yr2002 = IIF (Year = @StartYear +1, 1, 0) ... etc
Then you do a Sum(Yr2001) in the group header to display the results. Basically creating the matrix manually....
But the group on months is more difficult if the data just isn't there... you could try inserting rows in a temp table to force the report to evaluate them...
OR you could try doing my above solution for columns down for rows as well. Like
Jan2001 = IIF (Year = 2001 and Month = 1, 1, 0)
Matrices are great but this is the one down side they have...
Maybe the user will accept a more generic date range ie a full years results, or YTD rather than a specific range... not easy i know but neither is the report :0)
HTH
Martin
March 10, 2006 at 11:54 am
Chris,
Try this expression in the aggrevating cell.
=IIF(Sum(Fields!FieldName.Value)=0,0,Sum(Fields!FieldName.Value))
Mike
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply