Help needed with expression

Question

Help needed with expression

Peter Newman-311337

Old Hand

Points: 394
More actions
February 21, 2012 at 8:27 am

#257076

Report server 2008 R2
I have a tablix which i am trying to hide show dependant on field values.
IsPending BIT, IsSales BIT, NewBos Int
If IsSales = 1 Hide Tablix
If IsPending = 1 Hide Tablix
IF IsSales = 0 And IsPending = 1 Hide Tablix
If IsSales = 1 and Is Pending = 0 Hide Tablix
If NewBos > 0 And IsSale = 0 and IsPending = 0 Show Tablix
If NewBos > 0 And IsSale = 1 and IsPending = 0 Hide Tablix
If NewBos > 0 And IsSale = 0 and IsPending = 1 HideTablix
If NewBos = 0 And IsSale = 0 and IsPending = 0 Hide Tablix
confused .. I am

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 1

Hi

Right click on the column that you want to hide Column Visibility>Show or Hide based on an expression the expression you want is:

=IIF(Fields!IsSales.Value = "1",TRUE,FALSE)

If you want to do this for values based on more than one column then you will have to wrap the first IIF in another IIF

Andy

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

Peter Newman-311337 Old Hand Points: 394 More actions · Answer 2

Hi, thanks for the reply, I did try your suggestion but failed. Is it possible for you to give me an example, im not asking for the complete solution, just an example of two or three IIf so I can see how it is done,

cheers

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 3

Peter Newman-311337 (2/21/2012)
Hi, thanks for the reply, I did try your suggestion but failed. Is it possible for you to give me an example, im not asking for the complete solution, just an example of two or three IIf so I can see how it is done,
cheers

There's already an IIF example in Andy's reply.

What was the error that you got?

I think it failed on the fact that IsSales is a BIT field, so IsSales = "1" will probably lead to conversion issues.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Peter Newman-311337 Old Hand Points: 394 More actions · Answer 4

Hi,

I understood the example Any gave, but what i dont follow and needed an example of is where he says

'If you want to do this for values based on more than one column then you will have to wrap the first IIF in another IIF'

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 5

Peter Newman-311337 (2/22/2012)
Hi,
I understood the example Any gave, but what i dont follow and needed an example of is where he says
'If you want to do this for values based on more than one column then you will have to wrap the first IIF in another IIF'

Ah OK.

For example, replace FALSE in the IFF with an entire new IFF statement. That way you nest them and you can have multiple conditions at once.

Example:

http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/13dc06bd-3387-494b-a9b4-ee382a0d5c84/

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Peter Newman-311337 Old Hand Points: 394 More actions · Answer 6

Thank you, I have just read that thread and should be able to sort this issue out from that example

Peter Newman-311337 Old Hand Points: 394 More actions · Answer 7

Can someone have a look at this as i keep getting an error

=iif((First(Fields!IsSales.Value, "spBOSS2012_Reports_InvoiceHeader") = 1 or (Fields!NEWBOSS.Value, "spBOSS2012_Reports_InvoiceHeader") = 0 ), True ,False)

If im right in my thinking, If IsSales = 1 the tablix is hidden, if IsSales = 1 and NEWBOSS = 0 the tablix is hidden, but if IsSales = 0 and NewBoss = 1 then the tablix is shown

error

The Visibility.Hidden expression for the tablix ‘WepAppBreakdown’ contains an error: [BC30455] Argument not specified for parameter 'FalsePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 8

Hi Peter

Does this help?

=IIF((Fields!IsSales.Value) = 1 OR (Fields!NEWBOSS.Value)= 0,True,False)

Although your logic will show as visible if either Sales = 1 OR NEWBOSS = 0, not necessarily both

Andy

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 9

I think there's something wrong with that First function.

It has the entire boolean statement as a parameter:

Fields!IsSales.Value, "spBOSS2012_Reports_InvoiceHeader") = 1 or (Fields!NEWBOSS.Value, "spBOSS2012_Reports_InvoiceHeader") = 0

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Peter Newman-311337 Old Hand Points: 394 More actions · Answer 10

Thanks Andy,

That works for that part and has shown me how to build on it.

thanks again

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 11

No Worrirs Peter glad to have been of help 🙂

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe