Friday, November 17, 2017

SSRS: The Hidden Expression For the Tablix Has a Scope Parameter That Is Not Valid For an Aggregate Function

A while ago I ran into an issue when creating an SSRS report. It was a bit of a cryptic error message for me at first, as you can see below. After struggling with it for a while I finally found the issue and I thought I could as well share it so you can find it faster than I did. :)

Problem

When running my report I encountered this error message. It talks about a Hidden expression of Tablix1, and that the scope parameter used is not valid. After finding the solution the message was kind of clear to me, but the thing is, when running the report at first I was a little lost.

nickyvv.com - SSRS: The Hidden Expression For the Tablix Has a Scope Parameter That Is Not Valid For an Aggregate Function - Report error

Solution

It is very obvious the message is about Tablix1, so I start with looking at aggregate functions I am using in that tablix. Finally, I end up checking the visibility of the tablix and I notice the expression for the display option of the tablix.

nickyvv.com - SSRS: The Hidden Expression For the Tablix Has a Scope Parameter That Is Not Valid For an Aggregate Function - Display options

This feature is used to show or hide the tablix based on an expression. You can see the expression I used below.

nickyvv.com - SSRS: The Hidden Expression For the Tablix Has a Scope Parameter That Is Not Valid For an Aggregate Function - Expression Editor

As the Expression dialog states, you have to Set the expression for: Hidden. This actually means the following: if the expression evaluates to true, the tablix will be hidden. So if the expression evaluates to false, the tablix will be shown.
The problem however, is in my scope parameter: “DataSet1”. This scope parameter is optional for an aggregate function that can evaluate the aggregate function over that parameter. It can be a Row or Column group, a data region or (as it is here) a dataset.

TL;DR

The problem is, when I started with the report I entered this expression, but afterwards I altered the name of the dataset. This makes the above Hidden expression for the tablix invalid because Dataset1 doesn’t exist anymore.
So after updating the name of my dataset in the expression for the tablix my report ran just fine again.


If you have any remarks or questions, feel free to contact me on Twitter:
@NickyvV