January 13, 2016 at 8:38 am
Guys,
My specific problem is that I need to check the Disabled status of an ActiveDomain account. The AD user status is made up of a series of bits that returns an Integer between 1 and 512000. I am interested in the second bit.
SSRS does not contain a Bin(int64) function, however it does have an Oct(int64) and a bitshift modifier. by using these I can create the following expression
=IIF(Instr("1357",Right(CSTR(Oct(5>>1)),1)) > 0,"on","off")
=IIF(Instr("1357",Right(CSTR(Oct({ValueToCheck}>>{Bit you want to validate - 1})),1)) > 0,"on","off")
From the inside out:
Take the value to be compared and bitshift to the right by the bit you want to check less one. If you are checking the first bit then there is no need to shift.
Convert the value to base8 - this has the advantage that 8 is a power of 2.
Convert that to a string and get the last digit.
See if the last digit is in the string "1357"
If the last number is a 1, 3, 5 or 7 then the last bit in the binary number is ON, otherwise it is OFF
0: 0000
1: 0001
2: 0010
3: 0011
4: 0100
5: 0101
6: 0110
7: 0111
I thought this was quite a neat solution and I couldn't find a post elsewhere so I thought I would share it here. Is there a better way to find the status of an nth bit from an int?
January 13, 2016 at 9:15 am
I might be misunderstanding you, but if you're starting with the integer value (it sounds like you are?), then I find this method a bit more straightforward, using bitwise comparison:
=IIF((ValueToCheck AND (2^(BitToValidate-1)))>0,"On","OFF")
Just raise 2 to the power of BitToValidate-1, and do a bitwise AND with your value. If it's greater than 0, that bit is set; otherwise it's not.
Cheers!
January 18, 2016 at 2:09 pm
Jacob Wilkins (1/13/2016)
I might be misunderstanding you, but if you're starting with the integer value (it sounds like you are?), then I find this method a bit more straightforward, using bitwise comparison:=IIF((ValueToCheck AND (2^(BitToValidate-1)))>0,"On","OFF")
Just raise 2 to the power of BitToValidate-1, and do a bitwise AND with your value. If it's greater than 0, that bit is set; otherwise it's not.
Cheers!
Hi Jacob,
I haven't tried your solution but looking at it you are just comparing two numbers to see if they are both larger than zero.
E.g. If I take the base 10 (decimal) number 123456, Google says that is binary 11110001001000000 If I want to check the 3rd bit (the bold zero), your formula reads
=IIF((123456 AND 4)>0,"On","OFF")
I don't see how that helps because it is off and your formula will return on
January 18, 2016 at 2:19 pm
Why not use the T-SQL bitwise operators?
https://msdn.microsoft.com/en-us/library/ms174965.aspx
For example:
3 & 0 = 0
3 & 1 = 1
3 & 2 = 2
3 & 4 = 0
123456 & 1 = 0
123456 & 2 = 0
123456 & 4 = 0
123456 & 8 = 0
123456 & 16 = 0
123456 & 32 = 0
123456 & 64 = 64
123456 & 128 = 0
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 18, 2016 at 2:25 pm
That's not quite what that does. 🙂
AND is bitwise AND there.
So it takes 2 to the power of 1 less than the bit to validate (so, if validating the first bit, then 2^0=1, if second bit, then 2^1=2, etc.), and does a bitwise AND with the value you pass.
If that bit is set, then the bitwise AND will return the corresponding power of 2 (again, that bit's position minus one). Otherwise it will be 0.
For example, if you pass 33 (100001), and want to check the 6th bit, then that expression takes 2^5=32 (100000), and does a bitwise AND, which returns 32 (100000). If you repeat that for a bit that isn't set, then the bitwise AND returns 0.
It's then a simple check to see if the result of the bitwise AND is greater than 0. If it is, the bit is set; otherwise it's not.
Cheers!
January 18, 2016 at 2:26 pm
Eric M Russell (1/18/2016)
Why not use the T-SQL bitwise operators?https://msdn.microsoft.com/en-us/library/ms174965.aspx
For example:
3 & 0 = 0
3 & 1 = 1
3 & 2 = 2
3 & 4 = 0
123456 & 1 = 0
123456 & 2 = 0
123456 & 4 = 0
123456 & 8 = 0
123456 & 16 = 0
123456 & 32 = 0
123456 & 64 = 64
123456 & 128 = 0
Yup, that's what my proposed solution does. Just have to account for the fact that the bitwise operator is different in SSRS. 🙂
January 18, 2016 at 2:54 pm
Jacob Wilkins (1/18/2016)
Eric M Russell (1/18/2016)
Why not use the T-SQL bitwise operators?https://msdn.microsoft.com/en-us/library/ms174965.aspx
...
Yup, that's what my proposed solution does. Just have to account for the fact that the bitwise operator is different in SSRS. 🙂
Oh, we're talking about SSRS expression language, not T-SQL. OK. :pinch:
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 18, 2016 at 6:23 pm
Jacob Wilkins (1/13/2016)
I might be misunderstanding you, but if you're starting with the integer value (it sounds like you are?), then I find this method a bit more straightforward, using bitwise comparison:=IIF((ValueToCheck AND (2^(BitToValidate-1)))>0,"On","OFF")
Just raise 2 to the power of BitToValidate-1, and do a bitwise AND with your value. If it's greater than 0, that bit is set; otherwise it's not.
Cheers!
Very nicely done on the simplicity and convenience, Jacob. Well done!
--Jeff Moden
Change is inevitable... Change for the better is not.
January 18, 2016 at 6:42 pm
aaron.reese (1/18/2016)
Jacob Wilkins (1/13/2016)
I might be misunderstanding you, but if you're starting with the integer value (it sounds like you are?), then I find this method a bit more straightforward, using bitwise comparison:=IIF((ValueToCheck AND (2^(BitToValidate-1)))>0,"On","OFF")
Just raise 2 to the power of BitToValidate-1, and do a bitwise AND with your value. If it's greater than 0, that bit is set; otherwise it's not.
Cheers!
Hi Jacob,
I haven't tried your solution but looking at it you are just comparing two numbers to see if they are both larger than zero.
E.g. If I take the base 10 (decimal) number 123456, Google says that is binary 11110001001000000 If I want to check the 3rd bit (the bold zero), your formula reads
=IIF((123456 AND 4)>0,"On","OFF")
I don't see how that helps because it is off and your formula will return on
You're still thinking in decimal instead of binary bits. Jacob's code would need to be feed a "3" (third bit from the right) and then he calculates the correct power of 2 from that which is 2^(3-1) or 2^2 or 4. That's the DECIMAL value that represents the BIT 3. Then the two are ANDed (a bitwise BINARY AND behind the scenes) together like this...
11111111
76543210987654[font="Arial Black"]3[/font]21 = Bit Position
--------------[font="Arial Black"]-[/font]--
11110001001000[font="Arial Black"]0[/font]00 = 123456
00000000000000[font="Arial Black"]1[/font]00 = 4
--------------[font="Arial Black"]-[/font]--
00000000000000[font="Arial Black"]0[/font]00 = 0 because 1 AND 0 = 0
... ending with the proper condition off "0" or "OFF" for Bit 3.
Don't forget that the powers of two start at the right just like the "bit numbers" but they're always one less because the furthest bit to the right is 2^0.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 19, 2016 at 7:46 am
Jeff Moden (1/18/2016)
Jacob Wilkins (1/13/2016)
I might be misunderstanding you, but if you're starting with the integer value (it sounds like you are?), then I find this method a bit more straightforward, using bitwise comparison:=IIF((ValueToCheck AND (2^(BitToValidate-1)))>0,"On","OFF")
Just raise 2 to the power of BitToValidate-1, and do a bitwise AND with your value. If it's greater than 0, that bit is set; otherwise it's not.
Cheers!
Very nicely done on the simplicity and convenience, Jacob. Well done!
Thanks Jeff!
January 19, 2016 at 3:17 pm
SSRS is not my natural home 😀 so I was not aware that IIF(x AND y) is a bitwise operation. Maybe I wasn't asking google the right question but I never came across that as a solution to checking the bit value of a decimal number.
I was reading the AND in the manner it would be used in Excel. Just to clarify, if I was looking for the interpretation that I put on the function in my second post then the formula would need to look like
=IIF (({ValueToCheck}>0) AND ({someOtherValue}>0),"on","off" )
But it is good to know that there is more than one solution (as usual with Microsoft!)
January 19, 2016 at 3:32 pm
aaron.reese (1/19/2016)
@Jacob and @jeffSSRS is not my natural home 😀 so I was not aware that IIF(x AND y) is a bitwise operation. Maybe I wasn't asking google the right question but I never came across that as a solution to checking the bit value of a decimal number.
I was reading the AND in the manner it would be used in Excel. Just to clarify, if I was looking for the interpretation that I put on the function in my second post then the formula would need to look like
=IIF (({ValueToCheck}>0) AND ({someOtherValue}>0),"on","off" )
But it is good to know that there is more than one solution (as usual with Microsoft!)
Hmm, that's not a bitwise operation, in this case you can simply use IIF((X * Z > 0),"on","off")
😎
January 19, 2016 at 3:35 pm
Eirikur Eiriksson (1/19/2016)
aaron.reese (1/19/2016)
@Jacob and @jeffSSRS is not my natural home 😀 so I was not aware that IIF(x AND y) is a bitwise operation. Maybe I wasn't asking google the right question but I never came across that as a solution to checking the bit value of a decimal number.
I was reading the AND in the manner it would be used in Excel. Just to clarify, if I was looking for the interpretation that I put on the function in my second post then the formula would need to look like
=IIF (({ValueToCheck}>0) AND ({someOtherValue}>0),"on","off" )
But it is good to know that there is more than one solution (as usual with Microsoft!)
Hmm, that's not a bitwise operation, in this case you can simply use IIF((X * Z > 0),"on","off")
😎
Yeah, I think he was just pointing out that was what he thought my proposed solution earlier in the thread (which does use bitwise AND) was equivalent to. That reading caused a minor confusion before being cleared up 🙂
January 19, 2016 at 5:17 pm
Jacob Wilkins (1/19/2016)
Eirikur Eiriksson (1/19/2016)
aaron.reese (1/19/2016)
@Jacob and @jeffSSRS is not my natural home 😀 so I was not aware that IIF(x AND y) is a bitwise operation. Maybe I wasn't asking google the right question but I never came across that as a solution to checking the bit value of a decimal number.
I was reading the AND in the manner it would be used in Excel. Just to clarify, if I was looking for the interpretation that I put on the function in my second post then the formula would need to look like
=IIF (({ValueToCheck}>0) AND ({someOtherValue}>0),"on","off" )
But it is good to know that there is more than one solution (as usual with Microsoft!)
Hmm, that's not a bitwise operation, in this case you can simply use IIF((X * Z > 0),"on","off")
😎
Yeah, I think he was just pointing out that was what he thought my proposed solution earlier in the thread (which does use bitwise AND) was equivalent to. That reading caused a minor confusion before being cleared up 🙂
Are you sure? The code you posted does say...
=IIF((ValueToCheck AND (2^(BitToValidate-1)))>0,"On","OFF")
I don't know SSRS at all. Are you sure that the AND in your solution is a bitwise AND?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 19, 2016 at 6:24 pm
Jeff Moden (1/19/2016)
Jacob Wilkins (1/19/2016)
Eirikur Eiriksson (1/19/2016)
aaron.reese (1/19/2016)
@Jacob and @jeffSSRS is not my natural home 😀 so I was not aware that IIF(x AND y) is a bitwise operation. Maybe I wasn't asking google the right question but I never came across that as a solution to checking the bit value of a decimal number.
I was reading the AND in the manner it would be used in Excel. Just to clarify, if I was looking for the interpretation that I put on the function in my second post then the formula would need to look like
=IIF (({ValueToCheck}>0) AND ({someOtherValue}>0),"on","off" )
But it is good to know that there is more than one solution (as usual with Microsoft!)
Hmm, that's not a bitwise operation, in this case you can simply use IIF((X * Z > 0),"on","off")
😎
Yeah, I think he was just pointing out that was what he thought my proposed solution earlier in the thread (which does use bitwise AND) was equivalent to. That reading caused a minor confusion before being cleared up 🙂
Are you sure? The code you posted does say...
=IIF((ValueToCheck AND (2^(BitToValidate-1)))>0,"On","OFF")
I don't know SSRS at all. Are you sure that the AND in your solution is a bitwise AND?
I am indeed quite certain. I'm a paranoid sort, so I pretty much never post anything without testing it first, and this was not one of the exceptions 🙂
For an extra layer of certainty, https://msdn.microsoft.com/en-us/library/dd255271.aspx can be consulted.
Cheers!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply