April 15, 2016 at 10:01 am
I have a report that runs groups from lots of different business Areas dbo.group
For Example here is the underlying table
Area Cohort DataItem DataItem1 DataItemParam Total
Demographics Age 1 NULL 1 5
Demographics Age 3 NULL 3 7
Demographics Age 10 NULL 10 16
ExclusionsYear Month of Exclusion 2016 March 2016 March 50
So for the above data Set in the Groupings Report I would have the following SQL
SELECT * FROM dbo.Group
Where Area = @Area
AND Cohort = @Cohort
In the table created in Reporting Services using that dataset. The link to drill down report has the following filters:
Area = Area
Cohort = Cohort
DataParam = DataParam
The drill down report contains the following data sets (Data Tables) 2 tables have been created using the data sets
DemographicsFlatFile
ExclusionFlatFile
Both DataSets contain the same data items along with other information, Name Age etc.
The parameters in, for example Demographics are as follows
SELECT * FROM dbo.DemographicsDrilldown
Where Area = @Area
And Cohort =@ Cohort
And DataParam = @DataParam
If you go in via Demographics Area the Demographics table is shown. If you go in as exclusions Area the exclusions table is shown.
When I go in via Exclusions it works fine. When I go in via Demographics and click on an Age (e.g. 10) it brings up the drill down report and the DataParam is empty. I choose 10 from my drop down and the report is fine. However occasionally when I rebuild something in the report and then redeploy it works once and then doesn’t work again.
I have made sure that in the drill down and top level tables DataParam matches. I tried to do a workaround where I renamed my parameters and rejigged them in the dataset AND in the links (Top report and Drill down but I started to get the following
•An attempt was made to set a report parameter 'Cohort' that is not defined in this report. (rsUnknownReportParameter)
(I had changed Cohort to Cohort 1 which worked fine in Reporting services but no matter what I do I can’t now get it working in the browser.
So then I changed all the parameters and everything back and redeployed and I'm now getting the following
•An attempt was made to set a report parameter 'Area1' that is not defined in this report. (rsUnknownReportParameter)
But this has since gone and has been redeployed. Its like something is going on with Caching or something but I've been stuck on this now for two days and I'm at a loss. Has anyone else seen anything like this before?
Debbie
April 18, 2016 at 6:33 am
As an extra to this. I made two brand new reports, very simple and added the demographics table and the drill down works fine. Its when you introduce more that one table.
Does the order of the data sets matter at all? Its looking like there is some kind of bug when there are lots of data sets all with the same parameters
April 21, 2016 at 7:41 am
It would appear that the order of the data sets is important.
I created the first on and tested
Then the next. For example making sure any drill downs of data reports go after the first report. This seemed to work
So for example Person Attendances
Comes before the tests within an attendance drill down
April 21, 2016 at 10:02 am
If you're using derived parameters, most likely from a dataset being passed parameters, then yes SSRS will need to resolve these sequentially.
You determine the order this is done in the Parameters section of the Report Data pane. Any parameters that are used to determine others, and don't rely on others themselves, must be at the top of the list. Then follow up with any parameters that are determined by these, and so on. If any parameter can't be determined because another parameter it is reliant hasn't been, the report will simply fail giving you the error you received.
Clicking on the parameter and then clicking the up and down arrows at the top of the pane moves the parameter ordering so that you can have the right sequence.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply