This time I'm writing an expression in VS BI Reporting, and I need to sort records based on actual and potential ratings, which can be NA, 0, 1, 2, 3, 4 or 5. There are four ratings, Safety, Environment, Plant and Outrage. So I figure "Yeah I'll just make a bit string to sort with", like so:
=CInt(Fields!SafetyActCode.Value = "5") &CInt(Fields!EnvActCode.Value = "5") &CInt(Fields!PlantActCode.Value = "5") &CInt(Fields!OutrageActCode.Value = "5") &CInt(Fields!SafetyActCode.Value = "4") &CInt(Fields!EnvActCode.Value = "4") &CInt(Fields!PlantActCode.Value = "4") &CInt(Fields!OutrageActCode.Value = "4") &CInt(Fields!SafetyActCode.Value = "3") &CInt(Fields!EnvActCode.Value = "3") &CInt(Fields!PlantActCode.Value = "3") &CInt(Fields!OutrageActCode.Value = "3") &CInt(Fields!SafetyActCode.Value = "2") &CInt(Fields!EnvActCode.Value = "2") &CInt(Fields!PlantActCode.Value = "2") &CInt(Fields!OutrageActCode.Value = "2") &CInt(Fields!SafetyActCode.Value = "1") &CInt(Fields!EnvActCode.Value = "1") &CInt(Fields!PlantActCode.Value = "1") &CInt(Fields!OutrageActCode.Value = "1") &CInt(Fields!SafetyActCode.Value = "0" OR Fields!SafetyActCode.Value = "NA") &CInt(Fields!EnvActCode.Value = "0" OR Fields!EnvActCode.Value = "NA") &CInt(Fields!PlantActCode.Value = "0" OR Fields!PlantActCode.Value = "NA") &CInt(Fields!OutrageActCode.Value = "0" OR Fields!OutrageActCode.Value = "NA")
The expected output for something that has Safety 5, Env 3, Plant NA and Outrage 2 would be:
1000000001000001000000000010
Sounds right doesn't it? Convert a boolean value to an int, it converts true to 1 and false to 0. Concatenate all those ints as strings, and you get a nice sortable bit valued string.
Except here we are in the land of Microsoft, where down is up and up is down. So the actual string I get back is this:
-100000000-100000-10000000000-10
...resulting in me changing my code to this:
=(-CInt(Fields!SafetyActCode.Value = "5")) &(-CInt(Fields!EnvActCode.Value = "5")) &(-CInt(Fields!PlantActCode.Value = "5")) &(-CInt(Fields!OutrageActCode.Value = "5")) &(-CInt(Fields!SafetyActCode.Value = "4")) &(-CInt(Fields!EnvActCode.Value = "4")) &(-CInt(Fields!PlantActCode.Value = "4")) &(-CInt(Fields!OutrageActCode.Value = "4")) &(-CInt(Fields!SafetyActCode.Value = "3")) &(-CInt(Fields!EnvActCode.Value = "3")) &(-CInt(Fields!PlantActCode.Value = "3")) &(-CInt(Fields!OutrageActCode.Value = "3")) &(-CInt(Fields!SafetyActCode.Value = "2")) &(-CInt(Fields!EnvActCode.Value = "2")) &(-CInt(Fields!PlantActCode.Value = "2")) &(-CInt(Fields!OutrageActCode.Value = "2")) &(-CInt(Fields!SafetyActCode.Value = "1")) &(-CInt(Fields!EnvActCode.Value = "1")) &(-CInt(Fields!PlantActCode.Value = "1")) &(-CInt(Fields!OutrageActCode.Value = "1")) &(-CInt(Fields!SafetyActCode.Value = "0" OR Fields!SafetyActCode.Value = "NA")) &(-CInt(Fields!EnvActCode.Value = "0" OR Fields!EnvActCode.Value = "NA")) &(-CInt(Fields!PlantActCode.Value = "0" OR Fields!PlantActCode.Value = "NA")) &(-CInt(Fields!OutrageActCode.Value = "0" OR Fields!OutrageActCode.Value = "NA"))
Thanks Microsoft, you really do chomp down the fat one.