Adding Comma , after IIF
-
So guys,
I want the output value be like Opening Date, Closing Date
Lets say, OpeningDate Value =2
The output will become , Closing Date
There is a , infront of Closing Date
Any solutions ?
Below is the expression I used.
=IIF(Fields!OpeningDate.Value = 1 , "Opening Date, ", "") &
IIF(Fields!ClosingDate.Value = 1 , "Closing Date, ", "")
-
I think it would help to see current output and expected output.
Looking at what you wrote, I don't see why it would add a comma in front of closing date or opening date. I could see that it puts one at the end, but that is because you put on eat the end.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
Hi Brian,
Current Output is as below
Opening Date, Closing Date, Operation Hour,
Desired Output is as below
Opening Date, Closing Date, Operation Hour (without the comma)
The IIF function is checking for 3 data, Opening Date, Closing Date, Operation Hour
If the all 3 data contains "1" , it would become the output: Opening Date, Closing Date, Operation Hour,
Now, if the last data which is Operation Hour if does not contain "1" it will not show.
The Output would become Opening Date, Closing Date, <---- the comma is there
-
So it sounds like you want to adjust it to return the string minus the last character.
I would use a combination of LEFT and LEN (not 100% sure on the SSRS syntax for those, but they should exist). So basically you are building your string then you are doing a LEFT on the string on the length of the string minus 1. So something like this (note I did not test this so it is likely not syntactically correct):
= LEFT(IIF(Fields!OpeningDate.Value = 1 , "Opening Date, ", "") &
IIF(Fields!ClosingDate.Value = 1 , "Closing Date, ", ""), LEN(IIF(Fields!OpeningDate.Value = 1 , "Opening Date, ", "") &
IIF(Fields!ClosingDate.Value = 1 , "Closing Date, ", "")) - 1)The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. - Mr. Brian Gale wrote:
So it sounds like you want to adjust it to return the string minus the last character.
I would use a combination of LEFT and LEN (not 100% sure on the SSRS syntax for those, but they should exist). So basically you are building your string then you are doing a LEFT on the string on the length of the string minus 1. So something like this (note I did not test this so it is likely not syntactically correct):
= LEFT(IIF(Fields!OpeningDate.Value = 1 , "Opening Date, ", "") &
IIF(Fields!ClosingDate.Value = 1 , "Closing Date, ", ""), LEN(IIF(Fields!OpeningDate.Value = 1 , "Opening Date, ", "") &
IIF(Fields!ClosingDate.Value = 1 , "Closing Date, ", "")) - 1)The problem with this approach is that you have to try to determine the length of the string. I assume there will always be an Opening Date - and may or may not be a Closing Date and/or Operation Hour. If that is the case:
=IIF(Fields!OpeningDate.Value = 1, "Opening Date", "") &
IIF(Fields!ClosingDate.Value = 1, ", Closing Date", "") &
IIF(Fields!OperationHours.Value = 1, ", Operation Hours", "")If there can be a Closing Date with no Opening Date (not sure how that would occur) - then the solution would be to prepend ", " and use SUBSTRING to remove the first 2 characters.
=SUBSTRING(IIF(Fields!OpeningDate.Value = 1, ", Opening Date", "") &
IIF(Fields!ClosingDate.Value = 1, ", Closing Date", "") &
IIF(Fields!OperationHours.Value = 1, ", Operation Hours", ""), 3, 99)This way - it doesn't matter which of the 3 exist.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Hi Jeffrey,
Thanks for the help.
I try using the expression below and I got this error message.
Could you share what was missing out ?
The Value expression for the textrun 'Textbox46.Paragraphs[0].TextRuns[0]' contains an error: [BC30451] 'SUBSTRING' is not declared. It may be inaccessible due to its protection level.
=SUBSTRING(IIF(Fields!OpeningDate.Value = 1, ", Opening Date", "") &
IIF(Fields!ClosingDate.Value = 1, ", Closing Date", "") &
IIF(Fields!OperationHours.Value = 1, ", Operation Hours", ""), 3, 99) -
Sorry - substring is SQL and the corresponding function in SSRS would be MID.
=MID(IIF(Fields!OpeningDate.Value = 1, ", Opening Date", "") &
IIF(Fields!ClosingDate.Value = 1, ", Closing Date", "") &
IIF(Fields!OperationHours.Value = 1, ", Operation Hours", ""), 3, 99)Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Hi Jeff,
Sorry for coming back to you late.
The code below is removing the first 3 character.
Do you know what would be the workaround to only substract the last string ?
Can we like use a variable to calculate what would be the string numbers and then we substract the last one ?
=MID(IIF(Fields!OpeningDate.Value = 1, ", Opening Date", "") &
IIF(Fields!ClosingDate.Value = 1, ", Closing Date", "") &
IIF(Fields!OperationHours.Value = 1, ", Operation Hours", ""), 3, 99) - niteowl wrote:
Hi Jeff,
Sorry for coming back to you late. The code below is removing the first 3 character.
Do you know what would be the workaround to only substract the last string ? Can we like use a variable to calculate what would be the string numbers and then we substract the last one ?
=MID(IIF(Fields!OpeningDate.Value = 1, ", Opening Date", "") &
IIF(Fields!ClosingDate.Value = 1, ", Closing Date", "") &
IIF(Fields!OperationHours.Value = 1, ", Operation Hours", ""), 3, 99)It is supposed to remove the first 2 characters - and start at position 3 and return the rest of the string. If the first character of the string is being removed - try starting at position 2. But I have a feeling that isn't the problem you are asking about - which isn't clear and I would need sample data and expected results.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Below is the output which has Comma,
If Opening Date and Closing Date value is not 1, it will not display.
Operation Hour, will be displayed together with comma (,)
=IIF(Fields!Miss_OpeningDate.Value = 1 , "Opening Date, ", "")
&
IIF(Fields!Miss_ClosingDate.Value = 1 , "Closing Date, ", "")
&
IIF(Fields!Miss_hours.Value = 1 , "Operation Hour, ", "") -
So you did not even try my solution, which takes care of that issue.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Hi Jeff,
I tried, but it was producing something like this in earlier time.
And I tried again just now, it is working
No sure if I messed with the formula at earlier time.
Thank you for the help and also Brian.
Appreciate
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply