?
Solved

Report Expression using SWITCH

Posted on 2010-01-11
19
Medium Priority
?
428 Views
Last Modified: 2012-05-08
Hi Experts,
     Please find the report expression mentioned here under . I believe that there is a syntax error . Please correct my code . It is working , only for the first case.It is not working for the other 3 cases. Please let me know if i can use the  IIF statement for this report expression.
=SWITCH(
        (Parameters!Inv_Type_Code.Value = "CAZ" AND Fields!IS_AQGT73200.Value =1) , First(Fields!LDZ_IDENTIFIER.Value, "SV_Exception_Report"),
        (Parameters!Inv_Type_Code.Value = "CAZ" AND Fields!IS_AQGT73200.Value =0) ,First(Fields!LCH_LDZ_IDENTIFIER.Value, "SV_Exception_Report"),
        (Parameters!Inv_Type_Code.Value = "COM" AND Fields!IS_AQGT73200.Value =1) , First(Fields!LDZ_IDENTIFIER.Value, "SV_Exception_Report"),
        (Parameters!Inv_Type_Code.Value = "COM" AND Fields!IS_AQGT73200.Value =0) ,First(Fields!LCH_LDZ_IDENTIFIER.Value, "SV_Exception_Report")  
         )


Regards,

Sreekanth.
0
Comment
Question by:n_srikanth4
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 5
19 Comments
 
LVL 11

Expert Comment

by:rajvja
ID: 26282616
Hi,

  Is there any data that satisfies the conditions 2 to 3?
0
 
LVL 4

Expert Comment

by:TMarkham1
ID: 26282638
Have you tried taking out the parenthesis... like this?

=SWITCH(Parameters!Inv_Type_Code.Value = "CAZ" AND Fields!IS_AQGT73200.Value =1 , First(Fields!LDZ_IDENTIFIER.Value, "SV_Exception_Report"),
        Parameters!Inv_Type_Code.Value = "CAZ" AND Fields!IS_AQGT73200.Value =0),First(Fields!LCH_LDZ_IDENTIFIER.Value, "SV_Exception_Report"),
        Parameters!Inv_Type_Code.Value = "COM" AND Fields!IS_AQGT73200.Value =1 , First(Fields!LDZ_IDENTIFIER.Value, "SV_Exception_Report"),
        Parameters!Inv_Type_Code.Value = "COM" AND Fields!IS_AQGT73200.Value =0 ,First(Fields!LCH_LDZ_IDENTIFIER.Value, "SV_Exception_Report")  
         )
0
 

Author Comment

by:n_srikanth4
ID: 26283255
I will explain what eactly i require then you can correct my expression mentioned in the comment above.

IF   (Parameters!Inv_Type_Code.Value = "CAZ" AND Fields!IS_AQGT73200.Value =1 )   THEN

Fields!LDZ_IDENTIFIER.Value

ELSE IF  (Parameters!Inv_Type_Code.Value = "CAZ" AND Fields!IS_AQGT73200.Value =0 )   THEN

Fields!LCH_LDZ_IDENTIFIER.Value

ELSE IF   (Parameters!Inv_Type_Code.Value = "COM" AND Fields!IS_AQGT73200.Value =1 )   THEN

Fields!LDZ_IDENTIFIER.Value

ELSE IF  (Parameters!Inv_Type_Code.Value = "COM" AND Fields!IS_AQGT73200.Value =0 )   THEN

Fields!LCH_LDZ_IDENTIFIER.Value

Your comments are greatly appreciated.

Regards,

Sreekanth.


0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:rajvja
ID: 26283356
Hi,

 Try conditions 1 by 1. Dont type the entire expression at once.
You said that first condition is met and displaying the result. So, try with second condition.
If so, try with first and second.
Then you can figureout the problem
0
 

Author Comment

by:n_srikanth4
ID: 26283484
Hi rajvja,

The switch case is not working. i don't know how to implement multiple conditions using IIF.
Can you put  the above mentioned functionality in to an IIF Statement and send me the  Expression please.

Regards,

Sreekanth.
0
 
LVL 11

Expert Comment

by:rajvja
ID: 26283655
Hi,

 It will work. I dont know why you are not getting. Or else try this way

Write code which return the value based on the parameters

public function GetResult(ByVal InvTypeCode as string, ByVal AQGTVal as String) as String
    dim retVal as string
   write your if condition
  RETURN retVal
end function

From the expression, instead of switch,
=Code!GetResult(Parameters!Inv_Type_Code.Value,Fields!IS_AQGT73200.Value)
0
 
LVL 4

Expert Comment

by:TMarkham1
ID: 26283937
That's exactly what I was going to suggest next. Looks like rajvja has your answer.
0
 

Author Comment

by:n_srikanth4
ID: 26284154
Rajvja,


I have used  the function  mentioned here under in the report expression  as per your guidelines  using the code property. But it is no good and will not work . Please correct my  custom code  and send me the solution. Please check the parameters i have passed, one is string and other is decimal.

Public shared function GetResult(ByVal Inv_Type_Code as string, ByVal IS_AQGT73200 as Decimal) as String
   
Dim LDZ_IDENTIFIER as string
Dim LCH_LDZ_IDENTIFIER as string
   
IF(Inv_Type_Code = "CAZ" AND IS_AQGT73200 = 1) THEN

RETURN LDZ_IDENTIFIER

ELSE IF(Inv_Type_Code = "CAZ" AND IS_AQGT73200 =0) THEN

RETURN LCH_LDZ_IDENTIFIER

ELSE IF(Inv_Type_Code = "COM" AND IS_AQGT73200 =1)THEN

RETURN LDZ_IDENTIFIER

ELSE IF(Inv_Type_Code = "COM" AND IS_AQGT73200 =0) THEN

RETURN LCH_LDZ_IDENTIFIER

END IF

End Function


Using this code in the Report Expression:

Code.GetResult(Parameters!Inv_Type_Code.Value,Fields!IS_AQGT73200.Value)


Regards,

Sreekanth.


0
 
LVL 11

Expert Comment

by:rajvja
ID: 26284277
Hi
Where are you assigning the value(return value)
Public shared function GetResult(ByVal Inv_Type_Code as string, ByVal IS_AQGT73200 as Decimal) as String

Dim retVal as string

IF(Inv_Type_Code = "CAZ" AND IS_AQGT73200 = 1) THEN

retVal = "LDZ"

ELSE IF(Inv_Type_Code = "CAZ" AND IS_AQGT73200 =0) THEN

retVal="LCHLDZ"

ELSE IF(Inv_Type_Code = "COM" AND IS_AQGT73200 =1)THEN

retVal="LDZ"

ELSE IF(Inv_Type_Code = "COM" AND IS_AQGT73200 =0) THEN

retVal="LCHLDZ"

END IF

End Function


expression
=IIF(Code.GetResult(Parameters!Inv_Type_Code.Value,Fields!IS_AQGT73200.Value)="LDZ",Fields!LDZ_IDENTIFIER.Value,Fields!LCH_LDZ_IDENTIFIER.Value)

 
Hope this works
0
 
LVL 11

Expert Comment

by:rajvja
ID: 26284291
Hi,

 I forgot the RETURN retVal at the end of the function i.e before End Function
0
 

Author Comment

by:n_srikanth4
ID: 26284737
Rajvja,
            I have implemented exactly what you have mentioned above putting the retval before the End Function. I won't work. I took that off an tried, still won't return me what I am expecting . Is there any way , i can implement using the switch case.

Regards,

Sreekanth.
0
 
LVL 4

Expert Comment

by:TMarkham1
ID: 26284984
Try this for your function:
Public Shared Function GetResult(ByVal Inv_Type_Code as String, ByVal IS_AQGT73200 as Decimal) as String
   Dim retVal as string
   IF (Inv_Type_Code = "CAZ" AND IS_AQGT73200 = 1) THEN
      retVal = "LDZ"
   ELSEIF(Inv_Type_Code = "CAZ" AND IS_AQGT73200 =0) THEN
      retVal="LCHLDZ"
   ELSEIF(Inv_Type_Code = "COM" AND IS_AQGT73200 =1) THEN
      retVal="LDZ"
   ELSEIF(Inv_Type_Code = "COM" AND IS_AQGT73200 =0) THEN
      retVal="LCHLDZ"
   END IF
   Return retVal
End Function

Open in new window

0
 
LVL 4

Expert Comment

by:TMarkham1
ID: 26285010
Also, since SSRS doesn't provide any troubleshoot mechanisms as the VS IDE does, I usually write and test the functions I use in SSRS in a test application first to make sure they work before copying them to the report.
0
 
LVL 4

Expert Comment

by:TMarkham1
ID: 26285093
And now that I actually look at your logic... why are you even testing the parameter Inv_Type_Code if you're returning LDZ or LCHLDZ based the value of IS_AQGT73200 being 1 or 0? What not just test for IS_AQGT73200 as follows... unless I'm missing something?
Public Shared Function GetResult(ByVal Inv_Type_Code as String, ByVal IS_AQGT73200 as Decimal) as String 
   Dim retVal as string 
   IF (Inv_Type_Code = "CAZ" AND IS_AQGT73200 = 1) THEN 
      retVal = "LDZ" 
   ELSEIF(Inv_Type_Code = "CAZ" AND IS_AQGT73200 =0) THEN 
      retVal="LCHLDZ" 
   ELSEIF(Inv_Type_Code = "COM" AND IS_AQGT73200 =1) THEN 
      retVal="LDZ" 
   ELSEIF(Inv_Type_Code = "COM" AND IS_AQGT73200 =0) THEN 
      retVal="LCHLDZ" 
   END IF 
   Return retVal 
End Function

Open in new window

0
 
LVL 4

Accepted Solution

by:
TMarkham1 earned 2000 total points
ID: 26285106
Ooops.. I mean like this:
Public Shared Function GetResult(ByVal IS_AQGT73200 as Decimal) as String 
   Dim retVal as string 
   IF IS_AQGT73200 = 1 THEN 
      retVal = "LDZ" 
   ELSEIF IS_AQGT73200 =0 THEN 
      retVal="LCHLDZ" 
   END IF 
   Return retVal 
End Function

Open in new window

0
 

Author Comment

by:n_srikanth4
ID: 26285210
TMarkham1:

    Thanks for the immediate reply in the first place. You mean to say  that  I have to write the above mentioned two functions  in the custom code .
   in the data base ,  actually IS_AQGT73200 is a bit which either returns 1 or 0 , is it ok to declare this parameter  as decimal or do i have to declare it as a bit.  

Regards,

Sreekanth.
0
 

Author Comment

by:n_srikanth4
ID: 26285256
TMarkham1,
   
                HOW TO USE THE ABOVE TWO FUNCTIONS IN THE REPORT EXPRESSION ??

Regards,

Sreekanth.
0
 
LVL 4

Expert Comment

by:TMarkham1
ID: 26285423
First of all, you might over-thinking this whole task. I feel like I might be telling you to build a Boeing 787 when all you may really need is a bicycle. I don't know... you'll have to make that decision yourself.

Anyhow, the function itself isn't written in the expression area... you call the function using the expression. The actually function gets created in the "Code" page of the Report Properties dialog box. You can access the Report Properties from the Report menu. See these links for more info:

http://msdn.microsoft.com/en-us/library/ms155798.aspx

http://odetocode.com/articles/130.aspx

http://bryantlikes.com/articles/824.aspx
0
 

Author Closing Comment

by:n_srikanth4
ID: 31675459
Excellent
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question