switch function in reporting services

  • Thread starter Thread starter KC
  • Start date Start date
K

KC

I have a switch function that is not working properly. There is no
syntax error, but it is not displaying any data. The columns come out
empty. I'm trying to change a field dynamically. I have wrote a
function to return a value. Based on the function value returns, I
want the value change.
code.FNC is my function, which I pass 2 parameters in.

=switch(code.FNC(Parameters!MACOM.value, Parameters!FACTTYPE.Value) =
"FACTOR", Fields!FACTOR_WITH_CONOPS.Value,
code.FNC(Parameters!MACOM.Value, Parameters!FACTTYPE.Value) = "ORG",
Fields!ORG_FACTOR_SOLDIER_WCONOPS.Value,
code.FNC(Parameters!MACOM.Value, Parameters!FACTTYPE.Value) = "INST",
Fields!INST_FACTOR_SOLDIER_WCONOPS.Value,
code.FNC(Parameters!MACOM.Value, Parameters!FACTTYPE.Value) = "BOTH",
Fields!ORG_FACTOR_SOLDIER_WCONOPS.Value ).

Thanks for any help I can get, KC
 
This is syntax I have never seen before. Try first to get this down to
something that looks more normal:
=switch(
FNC(MACOM, FACTTYPE) ="FACTOR", FACTOR_WITH_CONOPS,
FNC(MACOM, FACTTYPE) = "ORG",ORG_FACTOR_SOLDIER_WCONOPS,
FNC(MACOM, FACTTYPE) = "INST",INST_FACTOR_SOLDIER_WCONOPS,
FNC(MACOM, FACTTYPE) = "BOTH",ORG_FACTOR_SOLDIER_WCONOPS )

However, we don't understand the fields, the function, or your requirement.
 
Code.Fnc is a bad name for a function. Is that the name of function or are
you trying to specify the module and the function within the module. I
assume that MACOM and FactType are parameters you are inputting and that
Factor_with_Conops is the name of a field.

I would try
FNC(Macom,FactType)="Factor",Factor_With_Conops)

Basic trouble shooting would be to break this down into steps and see what
you get.

Remove the switch and just try the function. See if it returns any value,
If not, then you know that you need to look at the function, if so then add
in the comparison. etc.

So start with:
FNC(Macom,FactType)



Also, where are you doing this? This doesn't look as if you are doing this
in a query unless this is criteria.
 
"FNC" is a name of a function I wrote under the Reporting Properties in
the "code" tab. I pass two parameters MACOM and FACTTYPE, which is use
in the function. If I put this, code.FNC(Parameters!MACOM.value,
Parameters!FACTTYPE.Value) , in the column, it retuns FACTOR, ORG,
INST, BOTH in the column. Based on those returns, I want the the
dataset field to change to Fields!FACTOR_WITH_CONOPS.Value, etc. I try
the code you provide it, it comes back with error "FNC" not declared
and the parameters not declared. I am writing this in my report I
created in Reporting services. I have a stored procedured that I'm
passing the parameters to produce a dataset. MACOM, FACTTYPE, etc are
my report parameters. The "fields" are the column name of the output
from the stored procedure.
 
"FNC" is a name of a function I wrote under the Reporting Properties in
the "code" tab. I pass two parameters MACOM and FACTTYPE, which is use
in the function. If I put this, code.FNC(Parameters!MACOM.value,
Parameters!FACTTYPE.Value) , in the column, it retuns FACTOR, ORG,
INST, BOTH in the column. Based on those returns, I want the the
dataset field to change to Fields!FACTOR_WITH_CONOPS.Value, etc. I try
the code you provide it, it comes back with error "FNC" not declared
and the parameters not declared. I am writing this in my report I
created in Reporting services. I have a stored procedured that I'm
passing the parameters to produce a dataset. MACOM, FACTTYPE, etc are
my report parameters. The "fields" are the column name of the output
from the stored procedure.
 
I'm doing this in RS using Visual Studio .NET 2003. "FNC" is the name
of my function. I have to put "Code." in front of the function name.
"NC(Macom,FactType)="Factor",Factor_With_Conops) " does not work. It
gives an error that I need to declare something. I have to use
"PARAMETERS!" and "FIELDS!", in front of the parameters and fields. If
I use code.FNC(Parameters!MACOM.value, Parameters!FACTTYPE.Value), it
does come back with a value from the FUNCTION. Also if I run
=switch(code.FNC(Parameters!MACOM.value, Parameters!FACTTYPE.Value) =
"FACTOR", Fields!FACTOR_WITH_CONOPS.Value) it will come back with right
value. But when I start to add more conditions, it doesn't return
anything.
 
Doesn't sound like this is an Access question since I assume you are use SQL
Server and Reporting Services.
 
Back
Top