Solved

Evaluate iif in a MAtrix

Posted on 2012-03-16
3
425 Views
Last Modified: 2012-03-25
Does anyone know why this IIF statement only evaluates once when it is in a Matrix but works ok outside the matrix ?

=IIF(LTRIM(RTRIM(Fields!USERDEF1.Value)) <> ("OTHER")
AND (LTRIM(RTRIM(Fields!USERDEF1.Value)) <> "KEY ACCOUNT" AND SUM(Fields!ID0_3_MTHS.Value) >0 )
 ,Fields!NoinGroup.Value, SUM(Fields!ID0_3_MTHS.Value))
0
Comment
Question by:NETFOCUS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 37729313
Depends on the layout of you matrix. But shouldn't the formula be:
=IIF(LTRIM(RTRIM(Fields!USERDEF1.Value)) <> ("OTHER")
AND (LTRIM(RTRIM(Fields!USERDEF1.Value)) <> "KEY ACCOUNT" AND SUM(Fields!ID0_3_MTHS.Value) >0 )
, SUM(Fields!ID0_3_MTHS.Value) ,Fields!NoinGroup.Value)

Open in new window

Now you're only showing the sum if it's <= 0. I you need further help please provide a small sample of your data, the layout of your matrix, the desired result and the version of SQL Server.
0
 

Accepted Solution

by:
NETFOCUS earned 0 total points
ID: 37741725
I had to add another column group with a constant value in it an now it works.
0
 

Author Closing Comment

by:NETFOCUS
ID: 37762348
Making this change worked.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction As you'll probably know, a data region in a SQL Server Reporting Services report can be linked to only one dataset.  This makes it troublesome when you need to display data from more than one dataset in the same data region.  SQL Serve…
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question