Adding Fields into an expression in an SSRS 2008 report

Question

Thank you in advance for taking your time to answer my question.

I am having trouble with expressions in the SSRS reporting system. The Field I am adding required fields from the dataset I provided in the report, however when I try to preview the report I get the Following message:

"A Value expression used for the report parameter ‘Policies_Total’ refers to a field. Fields cannot be used in report parameter expressions."

This is my expression:

=IIF(Sum(Fields!policy_id.Value, "DataSet1") Is Null, 0, Count(Sum(Fields!policy_id.Value, "DataSet1")))

That was suppoed to be converted from Crystal reports which has the following expression:

If IsNull ({usp_rep_agent_cases;1.policy_id}) then
    0
Else
    Count ({usp_rep_agent_cases;1.policy_id}) 

Any help is much appreciated.

Thank you

Answer 1

For SSRS expressions you need to use IsNothing for NULL checking, something like:

=IIF(
  IsNothing(Sum(Fields!policy_id.Value, "DataSet1"))
  , 0
  , Count(Sum(Fields!policy_id.Value, "DataSet1"))
)

In fact the whole expression seems a bit odd; what are you specifically trying to achieve with your expression? Are you just trying to count non-null values?

=Sum(IIf(IsNothing(Fields!policy_id.Value), 1, 0), "DataSet1")

Also, your error seems to be saying that a parameter is referencing a field when this isn't allowed, which may not be solved by changing syntax; I think more information about what you're trying to achieve is required here.

Answer 2

I think it may be as simple as understand that a 'parameter' should be passed into SSRS before fields are created for the most part. If this parameter is DEPENDENT on the value of something else first being chosen you cannot list it first as the field is not yet populated to my knowledge. It appears you are trying to use an expression to count something from a field from a dataset when you just make a dataset and reference that field directly. So instead of trying an expression you may choose a few other options instead:

1. Choose on the left pane of your parameter 'Available Values' selected 'Get values from a query'. You may use your query which is a 'dataset', value is self explanatory, label is what the end user will see display.

2. The option 'Allow null' value will accept a null value

You may run into situations where multiple datasets may need to be used, multiple selects or querying objects. In my experience with SSRS it gets mad at times when you try to reference a dataset used to display data with a dataset used to determine an event or action. SSRS also gets relativity slower the more Expressions you do so doing a whole report with nothing but expressions versus taking the power of the built ins of the RDL language is not really worth it IMHO.