December 1, 2011 at 7:19 am
I want to write an RS report for our business just to let them pick a sales figure for all or specific region(s) of their choice ie in RS terms, using Multiple values.
So I’ve tried to test this with an extremely simple query.
I’ve set up a Dataset, DS1 as
select Region, sales from dbo.bsi_DailySalesUpdateRS
where dbo.bsi_DailySalesUpdateRS.Region IN (@Region)
In the parameter called Region, I’ve allowed Multiple Values.
Then I’ve set ‘Available Values’ to refer to this same dataset, DS1, and in the ‘Value Field’, selected the drop down box and picked Region. (I don't want to have to list the regions as they change all the time, so I used ' get values from a query' in the parameter as opposed to 'specify values.')
However when I preview this all the fields under Dataset1 disappear and I get the error…
"The value expression for the text box region refers to the field region. report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case"
The query does only ‘refer to fields within the current dataset scope’, it’s not inside an aggregate and all the letters are in the correct case as I’ve used drop down options only to set this up all the way along!?!?
I then thought I’d try to refer to another dataset, so set up DS2 as
SELECT Region FROM Region_Table
…and changed the parameter to refer to DS2. Unfortunately I got exactly the same error.
Any help pleaseee!!
December 1, 2011 at 11:16 am
create a seperate dataset DSRegion that just returns the possible regions, and use that as the source for the available values for DS1
December 1, 2011 at 11:52 am
simon.letts (12/1/2011)
I want to write an RS report for our business just to let them pick a sales figure for all or specific region(s) of their choice ie in RS terms, using Multiple values.So I’ve tried to test this with an extremely simple query.
I’ve set up a Dataset, DS1 as
select Region, sales from dbo.bsi_DailySalesUpdateRS
where dbo.bsi_DailySalesUpdateRS.Region IN (@Region)
In the parameter called Region, I’ve allowed Multiple Values.
Then I’ve set ‘Available Values’ to refer to this same dataset, DS1, and in the ‘Value Field’, selected the drop down box and picked Region. (I don't want to have to list the regions as they change all the time, so I used ' get values from a query' in the parameter as opposed to 'specify values.')
However when I preview this all the fields under Dataset1 disappear and I get the error…
"The value expression for the text box region refers to the field region. report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case"
The query does only ‘refer to fields within the current dataset scope’, it’s not inside an aggregate and all the letters are in the correct case as I’ve used drop down options only to set this up all the way along!?!?
I then thought I’d try to refer to another dataset, so set up DS2 as
SELECT Region FROM Region_Table
…and changed the parameter to refer to DS2. Unfortunately I got exactly the same error.
Any help pleaseee!!
You need to put the field as Region not region...SSRS is case sensitive. and thats what seems to be the problem.
December 2, 2011 at 3:01 am
Springtown - Thanks, but thats exactly what I did and got the same message....see last paragraph re DS2 I set up. I also tried it using the Region column from the same table and got exactly the same message.
SQL_Nw, thank-you too. However this was just down to my typo. All occurences of the column Region are called Region (upper R) in the Dataset, Parameter, Table etc.. The error message displayed in this thread was me typing it in lower by mistake (it wasn't cut and pasted in).
So I still have this very basic issue I just can't seem to get around? :crying:
December 5, 2011 at 4:44 am
Just fyi....I changed the parameter name so it wasn't the same name as the column it was retrieving data from and now it works ok ?!!?! Another RS idiosyncracy??
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply