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
