Search code examples
visual-studioreporting-servicesdatasetreporting

Report Designer Select appropriate value

I am using Visual Studio to create a Report. I am having a problem getting the appropriate value into a text field.

I have a DataSet with these values:
InfoPathFormsWorkflow
AvgProcessTime_Sum
AvgProcessTime_Count

This DataSet will only have 1 or 2 values in it. It is being populated by an InfoPath form (the values in the DataSet are all accurate and working).

InfoPathFormsWorkflow is either "Close" or "Open". I currently have 2 text fields with these expressions:

=IIF(Fields!InfoPathFormsWorkflow.Value = "Closed",Fields!AvgProcessTime_Sum.Value / Fields!AvgProcessTime_Count.Value,"No Value")

=IIF(Fields!InfoPathFormsWorkflow.Value = "Open",Fields!AvgProcessTime_Sum.Value / Fields!AvgProcessTime_Count.Value,"No Value")

When the DataSet only has 1 value (either with "Open" or "Close") that text field will display correctly, but when the DataSet has 2 values (both "Close" and "Open" entries) then only the fist one will display correctly and the second one will display "No Value". So if the first row has InfoPathFormsWorkflow.value = "Close" then it will work but the "Open" will not. Or visa versa.

Is there a trick to selecting the right row entry? I have read a bit about using Parameters, but I cant seem to understand how that works. Any help would be great! This got handed to me after another worker here spend 80 hours on it.

Solution

  • These are TextFields inside or outside your Tablix?

    If they're outside, which I think seems to be the case (although I feel like that expression shouldn't be working at all then, so maybe I'm wrong...), this gets a little unwieldy, but is still entirely possible (assuming you only have those two rows):

    For the Closed TextField:

    =IIF(First(Fields!InfoPathFormsWorkflow.Value, "YourDataSetName") = "Closed", 
     First(Fields!AvgProcessTime_Sum.Value, "YourDataSetName") / 
       First(Fields!AvgProcessTime_Count.Value, "YourDataSetName"),
     IIF(Last(Fields!InfoPathFormsWorkflow.Value, "YourDataSetName") = "Closed",
         Last(Fields!AvgProcessTime_Sum.Value, "YourDataSetName") /
           Last(Fields!AvgProcessTime_Count.Value, "YourDataSetName")
         "No Value"
     )
)

    Likewise for the Open TextField:

    =IIF(First(Fields!InfoPathFormsWorkflow.Value, "YourDataSetName") = "Open", 
     First(Fields!AvgProcessTime_Sum.Value, "YourDataSetName") / 
       First(Fields!AvgProcessTime_Count.Value, "YourDataSetName"),
     IIF(Last(Fields!InfoPathFormsWorkflow.Value, "YourDataSetName") = "Open",
         Last(Fields!AvgProcessTime_Sum.Value, "YourDataSetName") /
           Last(Fields!AvgProcessTime_Count.Value, "YourDataSetName")
         "No Value"
     )
)

    If you wanted something different, let me know. Report Services is a bit stubborn about making things easy sometimes, but most things can eventually be made to behave as anticipated with the right (lengthy) expression.