Comparing Group Totals in a Matrix

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.

All Forums

Development Tools

Reporting Services Development

Comparing Group Totals in a Matrix

Author

Topic

smeganb
Starting Member

3 Posts

Posted - 2005-11-18 : 08:39:05

I have a simple matrix set up that sub-totals by groups:
Year 2004 2005
Month 4 5 6 Total 4 5 6 Total
125.5 206 221 552.5 68 61.5 96 225.5
60.5 79.5 53.5 193.5 48 38 69 155
1 1
5 5 1.5 1 2.5

What I'd like to do is then compare the total's for year, by line, to create a kind of traffic light on the row colour. I know I can use an expression with an IIF statement on the actual backgound colour, but I'm struggling to find the syntax of the IIF statement as the totals are in a matrix and cannot be readily referenced.

Can anyone point me in the right direction for this, or suggest a better way if they know one ?

Thanks

Rich

jhermiz

3564 Posts

Posted - 2005-11-28 : 16:16:42

quote:
Originally posted by smeganb

I have a simple matrix set up that sub-totals by groups:
Year 2004 2005
Month 4 5 6 Total 4 5 6 Total
125.5 206 221 552.5 68 61.5 96 225.5
60.5 79.5 53.5 193.5 48 38 69 155
1 1
5 5 1.5 1 2.5

What I'd like to do is then compare the total's for year, by line, to create a kind of traffic light on the row colour. I know I can use an expression with an IIF statement on the actual backgound colour, but I'm struggling to find the syntax of the IIF statement as the totals are in a matrix and cannot be readily referenced.

Can anyone point me in the right direction for this, or suggest a better way if they know one ?

Thanks

Rich

How are you summing the totals?

You can do this:

=IIF(Sum([MyField]) > 10, "Red", "Blue")

In the background property of the field.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

smeganb
Starting Member

3 Posts

Posted - 2005-11-29 : 11:45:49

Thanks for the reply.

The problem is I want to compare two sums, a little like:

IIF ((SUM(Month4Year2004)Value) > (SUM(Month4Year2005)Value), "Red", "Green"))

But I do not know how to reference the two values as they're dynamic and only appear when the matrix is built.

Is it possible ?

Thanks

Rich

jhermiz

3564 Posts

Posted - 2005-11-29 : 11:57:36

quote:
Originally posted by smeganb

Thanks for the reply.

The problem is I want to compare two sums, a little like:

IIF ((SUM(Month4Year2004)Value) > (SUM(Month4Year2005)Value), "Red", "Green"))

But I do not know how to reference the two values as they're dynamic and only appear when the matrix is built.

Is it possible ?

Thanks

Rich

In any textbox simply reference the field NAME such as :

=Fields!MyFieldName.Value
in your case that field name is just a sum...
then you do:

=IIF(Fields!MyFieldName.Value > 10 && Fields!MyFieldName2.Value > 20, "true", "false")

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

smeganb
Starting Member

3 Posts

Posted - 2005-11-30 : 06:27:02

quote:
Originally posted by jhermiz

In any textbox simply reference the field NAME such as :

=Fields!MyFieldName.Value
in your case that field name is just a sum...
then you do:

=IIF(Fields!MyFieldName.Value > 10 && Fields!MyFieldName2.Value > 20, "true", "false")

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

Again, thanks for the reply.

I've learnt something new (how to reference two fields in an IIF statement) but it's still not the answer I need.

I think the problem I have is I'm trying to compare two calculated fields with in the matrix, based on groupings. The Sum is on a field called DurationDays. these sums are grouped by Month and Year. The Month and Year are extracted form a date field using MONTH(datefield), etc.

The Sum of duration days for each month and year only exist in the matrix as calculated fields, they are not absolute values in their own right on the DB.

Would I still be able to compate them in an iif statement ?

Cheers

Rich

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources