June 19, 2008 at 11:48 pm
G'day all,
I've really hit a brick wall with this one. I've googled all I can google and unless I'm asking the wrong questions I'm not sure where I can go from here.
I have a report that I am very chuffed with that works beautifully by using lookup tables to turn school data: (Semester, Term, Week) into _real_ data that the report pivots from (from datetime/to datetime, number of school days in the particular week etc) which is query driven.
The report works great using the web browser (http://sql-reporter/reports blah), but when I try and create a data driven subscription, I get the error message in the title:
This report requires a default or user-defined value for the report parameter 'blah'. To run or subscribe to this report, you must provide a parameter value. (rsReportParameterValueNotSet)
Here are my fields that need to be "data driven":
* school *get value from database
* semester *get value from database
* term *get value from database
* week *get value from database
Here are fields that pivot on the answers above:
* days this week *use default
* enrol to *use default
* enrol from *use default
* enrolment cuttoff *use default
My query for my data driven subscription looks like:
selecttop 1 vk.campus_id, vk.semester, vk.term, vk.week, vk.sequence
fromv_cent_kcc as vk
wherevk.campus_id = 'hs' and
vk.sequence = (select r.sequence
from v_cent_kcc as r
wherecampus_id = 'hs' and
r.kcckey = DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)) -1
Basically this looks at today's date, matches it to a "sequence" (each reporting week has a different sequence) and minuses that number by 1 so I can pass variables of the last reporting period. (semester, term, week)
The output is basically:
campus_id semester term week sequence
HS 1 2 9 22
So with this background info in place, my question is basically this:
Why when data is inserted into the web browser version (school, semester, term, week) the variables are calculated (days, from, to, cutoff), but when using data driven subscription with "use defaults" checked on the variables, I get an error?
I have just checked though, using the data driven subscription I can hard code those "user defined"/get values from database" values into the settings and the report works fine.
If I get any of the 4 values from database, the process will bomb out with an error:
(example: if I get value from database for school, (should be "HS")
Default value or value provided for the report parameter 'school' is not a valid value. (rsInvalidReportParameter)
But it works when I hard code it!? I would appreciate any help on this 🙂
Thanks! - Damien
April 13, 2009 at 11:09 am
I'm dealing with the same issue. Looks like a bug in SSRS:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=330490
April 13, 2009 at 6:23 pm
Interesting that you're using SRS2005 and I'm on SRS2000 for anyone else experiencing this problem.
I was thinking that I might be experiencing rogue issues because I was using an older version but unfortunately not. Once I make the move to SRS2005 I'll be putting energy back into this issue.
Thanks for the update mate 🙂
February 3, 2020 at 10:15 am
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply