ssrs 2008 passing parameter value incorrectly

Question

In an SSRS 2008 R2 existing report, I am passing a new parameter called 'SchoolYear' to an existing report. I obtain the value for SchoolYear from the following statement =(Fields!SchoolYear.Value) that is used against the dataset DS_Main.

I am getting the error message, "The 'SchoolYear' parameter is missing a value" when the called SSRS report is running. When I 'hard-code' the school year value in the called ssrs report, the called report runs.

When I run the sql in the dataset DS_Main, I get all kinds of rows and all the rows contains data.

The called report runs when I say to work with a null value. Thus I do not know why the 'SchoolYear' parameter is not passing a value. Would you tell me what I can do to determine what is causing this problem?

Answer 1

You can't reference Fields or Datasets in the Default Values with expressions like you can in reporting objects (e.g. a Tablix). Here is what you want to do:

1. On the SchoolYear Parameter Properties window, Default Values tab, select Get values from a query.
2. Select DS_Main from the Dataset: drop down menu.
3. Select SchoolYear from Value: drop down menu.

If more than one SchoolYear value is returned by DS_Main, and the SchoolYear Parameter doesn't allow mult-selection, only the first row SchoolYear value will be passed to the parameter. If you want to pass all possible SchoolYear values as default (and your parameter allows multi-select), I would create a separate query that only returns a non-null distinct list.