Expressions not evaluating values?

Hi,

I use an expression in a column text box to dynamically compute the column title.

The problem must have something linked to the way expressions generally works. I do not understand it clearly.

In this example, I use a SWITCH function to test the numerical value of a 1 row 1 column dataset.

The problem is that I can test the number only if it is lower or equal to the number in the dataset. if I test a number greater than the number in the dataset, I get an error.

How can I get this test to work if the value tested is greater than the value in the dataset?

Thanks

Philippe

Bellow is the code.

-

=switch(

First(Fields!HeaderCount.Value, "HeadersCount") < 2

, nothing

, First(Fields!HeaderCount.Value, "HeadersCount") = 2

, Right(Parameters!Headers.Value, Len(Parameters!Headers.Value) - Parameters!Headers.Value.IndexOf(",2,")-3)

, First(Fields!HeaderCount.Value, "HeadersCount") > 2

, Parameters!Headers.Value.Substring(

Parameters!Headers.Value.IndexOf(",2,")+3

, Parameters!Headers.Value.IndexOf(",3,")-Parameters!Headers.Value.IndexOf(",2,")-3

)

)

Philippe wrote:

is that I can test the number only if it is lower or equal to the number in the dataset. if I test a number greater than the number in the dataset, I get an error.

Note, If the dataset contains 2, the expression will return an error because I try to test >2 in the last case.
if the dataset contains 3 or greater, it works fine.

it is clearly the test which fails because if I replace the action by a fixed string it still return an error.

|||

You are using the switch() function. Since it is a function, all arguments are evaluated before the switch functionality is executed.

I recommend to write a custom code function that uses the VB switch statement and call the custom code function from the expression.

-- Robert

|||

Hi,

I have made some research on the Custom Code however I could not find documentation nor examples that show how to use parameters or dataset values within the custom code. If I create a function like this How can I access the report items?

Public Function Headers(ByVal Column as Integer) As String
Return CStr(Microsoft.VisualBasic.Switch( _
First(Fields!HeaderCount.Value, "HeadersCount") < Column _
, nothing _
, First(Fields!HeaderCount.Value, "HeadersCount") = Column _
, Right(Parameters!Headers.Value, Len(Parameters!Headers.Value) - Parameters!Headers.Value.IndexOf(",Column,")-3) _
, First(Fields!HeaderCount.Value, "HeadersCount") > Column _
, Parameters!Headers.Value.Substring( _
Parameters!Headers.Value.IndexOf(",Column,")+3 _
, Parameters!Headers.Value.IndexOf(",Column + 1,")-Parameters!Headers.Value.IndexOf(",Column,")-3 _
) _
) _
)
End Function

I did find a much simpler solution though.

The string I use contains a variable number of names separated by indexes, i.e.

,1,Charles,2,Tom,3,Laura,4,Rick

I have a report with a fixed number of columns, i.e 50 columns and I populate the columns title by pulling from the string, i.e. Column 3 title will be Laura.

I have converted the string so each name will have trailing spaces. Since now each item has the same lenght, I do not need anymore the switch, I can do simply this in each column with just another index number. Then I use an expression to control the visibility of the column.

Title

=Parameters!Headers.Value.Substring(Parameters!Headers.Value.IndexOf(",3,")+3, 85)

Visibility

=iif(First(Fields!HeaderCount.Value, "HeadersCount")<3,True,False)

If I spend the time to build this instead of using the Matrix report, it is because the Matrix report has 2 majors issues for me.

1) You cannot have column titles for your categories

2) You cannot have the categories values repeated

Because of the strict format requirement I have, I am obliged to use the PIVOT SQL operator and the dynamic column population. My users want a pivoted flat file they can put in Excel and build a pivot table with it. You cannot do that with a matrix report, too bad. That would be so much easy.

Philippe