Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Characters in IIF statements Expand / Collapse
Author
Message
Posted Thursday, January 13, 2011 8:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 8:56 AM
Points: 93, Visits: 193
Hi All

Im using BIDS to design a report.

In a calculated field I am using the below statement:

=iif(Fields!COSTYPE.Value = M,"Available", "Reconciled")

But it keeps errors on the M Character.

Ive checked the character definately exsist in the field and I have tried "" '' {} [] around the M to no avail. Just says unidentified Character.
How ever if i put a number there, any random number its ok.

any Advise?

Thanks
R
Post #1047356
Posted Thursday, January 13, 2011 9:59 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:24 AM
Points: 1,264, Visits: 3,555
All the sites I'm seeing show that the conditional variable, if characters, needs to be in quotes.
=iif(Fields!COSTYPE.Value = "M","Available","Reconciled")

looks like the right syntax.
(not an ssrs expert)
perhaps it's a data type error, hence the error for "M" and not numbers. maybe convert the field before evaluation?


______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Post #1047415
Posted Friday, January 14, 2011 2:20 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 8:56 AM
Points: 93, Visits: 193
How would I convert it to string, Ive tried var(Field) and char(field) to no avail.
Post #1047779
Posted Friday, January 14, 2011 6:29 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:24 AM
Points: 1,264, Visits: 3,555
format(expression,[optional string format])
So perhaps you could format the whole thing like this?
=iif(format(Fields!COSTYPE.Value = "M","Available","Reconciled"))

I believe format is the appropriate function.
Again, I'm just trying to throw ideas out there until someone more knowledgable comes along. This is the kind of thing that I would spend a while playing with until I got it to work.


______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Post #1047850
Posted Friday, January 14, 2011 6:33 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 8:56 AM
Points: 93, Visits: 193
thanks, the reason I needed it as a field was so I could group, but I found if I can create a group by an expression, so I added the oringal coe =iif(Fields!COSTYPE.Value = M,"Available", "Reconciled") as a group expression and it worked!
Post #1047853
Posted Friday, January 14, 2011 6:46 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:24 AM
Points: 1,264, Visits: 3,555
Hooray! I'll do a celebratory happy dance for your success. Glad you got it working.

______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Post #1047866
Posted Friday, January 14, 2011 8:44 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 8:56 AM
Points: 93, Visits: 193
I know after looking at it for ages I feel relieved.

But it is still would be a problem if i wanted to use this code as a field on the report.

So if anyone has any ideas please do share!

Thanks
R
Post #1047973
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse