ssrs get single value from dataset into parameter default value

Question

I'm using SSRS 2008R2 and i try to get single value from dataset to use it as default value of report's parameter.

Dataset filled with this query:

select distinct
    o.organization_id,
    o.mean_name,
    o.short_name
from
    dbo.organization o
    inner join
        dbo.identifier i on i.organization_id = o.organization_id and
                            i.market <> 3

I have report's parameter organization_id, whose value user selects. And now I want get value of field short_name from dataset where field organization_id equal to parameter organization_id. But I can't do this.

I tried to use Lookup function like this: =Lookup(Fields!organization_id.Value, Parameters!organization_id.Value, Fields!short_name.Value, "dsOrganizations"), but server gave me error "Fields cannot be used in report parameter expressions".

I tried to make variable with this Lookup function, but variables can't be used in report parameters.

And now I have no idea how set parameter default value from dataset. I will welcome any tips or suggestions.

Indian Indian · Accepted Answer · 2014-05-13T09:29:17

I create one more dataset with this query: select short_name from organization where organization_id = @organization_id and set parameter value equals to parameter organization_id. After there I set default value to parameter short_name just as "Get values from a query" and as dataset set new dataset.

I hope, that my experience will be useful to someone.

ssrs get single value from dataset into parameter default value

1 Answers