Dynamic Subscription Parameters

Ok I've seen this question asked often, but it appears no one has come
up with an answer.
I have a report that is viewed most often though a portal with the
default parameters of startdate (yesterday) and enddate (today). Now
the request has come in for a different group of people to be able to
create their own subscription for this report, but they want to see the
data on a weekly basis.
So the question is... Are there any expressions, bits of code, code
words etc that will be accepted on a user subscript to set the date for
a report (without using the default)
I've tried...
=Today()
=datetime.today.adddays(-1).tostring("mm/dd/yy")
datetime.today
datetime.today(adddays(-7)
NOW
but every time I enter one of those in the parameter box, they revert
to the default value.Why not have the parameter as a flag instead of dates, 1 = daily, 2 =weekly then default the dates used in the query based on the flag?
If the report must be available for ad-hoc runs with date parameters
available to users as well, simply have the flag parameter as well as
date parameters, hide the flag parameter in Report Manager, and default
it to zero to indicate that the date parameter be used instead. Your
data query will be a little more complex but it shouldn't be too bad.
--
Regards
Chris
Ches Weldishofer wrote:
> Ok I've seen this question asked often, but it appears no one has come
> up with an answer.
> I have a report that is viewed most often though a portal with the
> default parameters of startdate (yesterday) and enddate (today). Now
> the request has come in for a different group of people to be able to
> create their own subscription for this report, but they want to see
> the data on a weekly basis.
> So the question is... Are there any expressions, bits of code, code
> words etc that will be accepted on a user subscript to set the date
> for a report (without using the default)
> I've tried...
> =Today()
> =datetime.today.adddays(-1).tostring("mm/dd/yy")
> datetime.today
> datetime.today(adddays(-7)
> NOW
> but every time I enter one of those in the parameter box, they revert
> to the default value.|||Oh I love it when people think out of the box!!!!
Great suggestion. I'll look into implementing today.|||Oh I love it when people think out of the box!!!!
Great suggestion. I'll look into implementing today.

Dynamic SQL!

As much as i would prefer not to..it appears the only way i can acheive
something is to use dynamic sql.
I have constructed the test query below, but only recieve invalid column
name errors.
Can someone tell me where i am going wrong?
I am obvsiouly missing something quite simple'
DECLARE @.sqlquery VARCHAR(400)
SET @.sqlquery = "SELECT asmt_v2_question_id FROM asmt_v2_questions"
PRINT @.sqlqueryAdam,
Why do you believe Dynamic SQL is the only way?
Try :
DECLARE @.sqlquery VARCHAR(400)
SET @.sqlquery = 'SELECT asmt_v2_question_id FROM asmt_v2_questions'
EXEC (@.sqlquery)
HTH
Jerry
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:e2RGkH7xFHA.3892@.TK2MSFTNGP12.phx.gbl...
> As much as i would prefer not to..it appears the only way i can acheive
> something is to use dynamic sql.
> I have constructed the test query below, but only recieve invalid column
> name errors.
> Can someone tell me where i am going wrong?
> I am obvsiouly missing something quite simple'
> DECLARE @.sqlquery VARCHAR(400)
> SET @.sqlquery = "SELECT asmt_v2_question_id FROM asmt_v2_questions"
> PRINT @.sqlquery
>|||Use single quotes to enclose your SQL statement literal like the example
below. Double-quotes should be used only to enclose identifiers when SET
QUOTED_IDENTIFIER is on. The 'invalid column' error results because SQL
Server thinks you are specifying a column name.
SET @.sqlquery = 'SELECT asmt_v2_question_id FROM asmt_v2_questions'
Hope this helps.
Dan Guzman
SQL Server MVP
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:e2RGkH7xFHA.3892@.TK2MSFTNGP12.phx.gbl...
> As much as i would prefer not to..it appears the only way i can acheive
> something is to use dynamic sql.
> I have constructed the test query below, but only recieve invalid column
> name errors.
> Can someone tell me where i am going wrong?
> I am obvsiouly missing something quite simple'
> DECLARE @.sqlquery VARCHAR(400)
> SET @.sqlquery = "SELECT asmt_v2_question_id FROM asmt_v2_questions"
> PRINT @.sqlquery
>|||Also,
If you haven't already read it, you might want to take a look at Erland's
article:
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
HTH
Jerry
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:e2RGkH7xFHA.3892@.TK2MSFTNGP12.phx.gbl...
> As much as i would prefer not to..it appears the only way i can acheive
> something is to use dynamic sql.
> I have constructed the test query below, but only recieve invalid column
> name errors.
> Can someone tell me where i am going wrong?
> I am obvsiouly missing something quite simple'
> DECLARE @.sqlquery VARCHAR(400)
> SET @.sqlquery = "SELECT asmt_v2_question_id FROM asmt_v2_questions"
> PRINT @.sqlquery
>|||Hi Jerry,
The query below is the one in question.
I am still learning the functions and logic structures of TSQL .
Consequently, so far i can't think of anyway of doing the following without
Dynamic Sql (which i would prefer not to).
A previous post also dealt with this issue (been trying to nut this query
out for a while).
I received some responses, though quite helpful and very much appreciated, i
am not sure they really dealt with my particular problem.
(This would be my fault for providing an overly simplified query).
The issue is parameterizing the "TOP" statement .
Or somehow limiting dynamically (via parameter, variable) the number of
rows returned by the SECOND (Only) query in the UNION.
-- select question details where questions are compulsory, within a given
area
-- and not answered in a previous completed assessment attempt
SELECT
NewID(),asmt_v2_question_id, qtext, qindex, qtype, answer_url
FROM
asmt_v2_questions
WHERE qtype = 'c' AND asmt_v2_area_id = @.aid AND asmt_v2_question_id
NOT IN (
-- retreive question ids (compulsory) in 'asmt_question_results' table
-- that are associated with a given set of attempt_ids
SELECT DISTINCT
asmt_v2_questions.asmt_v2_question_id
FROM
asmt_v2_questions
INNER JOIN
asmt_v2_question_results
ON
asmt_v2_questions.asmt_v2_question_id =
asmt_v2_question_results.asmt_v2_question_id
WHERE
asmt_v2_questions.qtype = 'c'
AND
asmt_v2_question_results.asmt_v2_attempt_id
IN (
-- retreive attempt_ids that are associated with completed assessments
SELECT
asmt_v2_completed.asmt_v2_attempt_id
FROM
asmt_v2_completed
INNER JOIN
asmt_v2_attempts
ON
asmt_v2_completed.asmt_v2_attempt_id =
asmt_v2_attempts.asmt_v2_attempt_id
WHERE
asmt_v2_attempts.asmt_v2_employee_id = @.eid AND
asmt_v2_attempts.asmt_v2_area_id = @.aid
)
)
UNION ALL
-- select question details from results returned in sub query
SELECT
NEWID() AS ID, asmt_v2_question_id, qtext, qindex, qtype, answer_url
FROM (
-- select question details where questions are optional, within a given area
-- and not answered in a previous completed assessment attempt
-- only return a specified number of rows
SELECT
TOP 2 NEWID() AS ID,asmt_v2_question_id, qtext, qindex, qtype,
answer_url /* TOP STATEMENT TO BE PARAMETERISE */
FROM
asmt_v2_questions
WHERE
qtype = 'o'
AND
asmt_v2_area_id = @.aid
AND
asmt_v2_question_id
NOT IN (
-- retreive question ids (optional questions) in 'asmt_question_results'
table
-- that are associated with a given set of attempt_ids
SELECT DISTINCT
asmt_v2_questions.asmt_v2_question_id
FROM
asmt_v2_questions
INNER JOIN
asmt_v2_question_results
ON
asmt_v2_questions.asmt_v2_question_id =
asmt_v2_question_results.asmt_v2_question_id
WHERE
asmt_v2_questions.qtype = 'o'
AND
asmt_v2_question_results.asmt_v2_attempt_id
IN (
-- retreive attempt_ids that are associated with completed assessments
SELECT
asmt_v2_completed.asmt_v2_attempt_id
FROM
asmt_v2_completed
INNER JOIN
asmt_v2_attempts
ON
asmt_v2_completed.asmt_v2_attempt_id =
asmt_v2_attempts.asmt_v2_attempt_id
WHERE
asmt_v2_attempts.asmt_v2_employee_id = @.eid
AND
asmt_v2_attempts.asmt_v2_area_id = @.aid
)
)
ORDER BY 1|||Try SET ROWCOUNT in BOL.
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:eEmek27xFHA.2556@.TK2MSFTNGP10.phx.gbl...
> Hi Jerry,
> The query below is the one in question.
> I am still learning the functions and logic structures of TSQL .
> Consequently, so far i can't think of anyway of doing the following
> without Dynamic Sql (which i would prefer not to).
> A previous post also dealt with this issue (been trying to nut this query
> out for a while).
> I received some responses, though quite helpful and very much appreciated,
> i am not sure they really dealt with my particular problem.
> (This would be my fault for providing an overly simplified query).
> The issue is parameterizing the "TOP" statement .
> Or somehow limiting dynamically (via parameter, variable) the number of
> rows returned by the SECOND (Only) query in the UNION.
>
> -- select question details where questions are compulsory, within a given
> area
> -- and not answered in a previous completed assessment attempt
> SELECT
> NewID(),asmt_v2_question_id, qtext, qindex, qtype, answer_url
> FROM
> asmt_v2_questions
> WHERE qtype = 'c' AND asmt_v2_area_id = @.aid AND asmt_v2_question_id
> NOT IN (
> -- retreive question ids (compulsory) in 'asmt_question_results' table
> -- that are associated with a given set of attempt_ids
> SELECT DISTINCT
> asmt_v2_questions.asmt_v2_question_id
> FROM
> asmt_v2_questions
> INNER JOIN
> asmt_v2_question_results
> ON
> asmt_v2_questions.asmt_v2_question_id =
> asmt_v2_question_results.asmt_v2_question_id
> WHERE
> asmt_v2_questions.qtype = 'c'
> AND
> asmt_v2_question_results.asmt_v2_attempt_id
> IN (
> -- retreive attempt_ids that are associated with completed assessments
> SELECT
> asmt_v2_completed.asmt_v2_attempt_id
> FROM
> asmt_v2_completed
> INNER JOIN
> asmt_v2_attempts
> ON
> asmt_v2_completed.asmt_v2_attempt_id =
> asmt_v2_attempts.asmt_v2_attempt_id
> WHERE
> asmt_v2_attempts.asmt_v2_employee_id = @.eid AND
> asmt_v2_attempts.asmt_v2_area_id = @.aid
> )
> )
> UNION ALL
> -- select question details from results returned in sub query
> SELECT
> NEWID() AS ID, asmt_v2_question_id, qtext, qindex, qtype, answer_url
> FROM (
> -- select question details where questions are optional, within a given
> area
> -- and not answered in a previous completed assessment attempt
> -- only return a specified number of rows
> SELECT
> TOP 2 NEWID() AS ID,asmt_v2_question_id, qtext, qindex, qtype,
> answer_url /* TOP STATEMENT TO BE PARAMETERISE */
> FROM
> asmt_v2_questions
> WHERE
> qtype = 'o'
> AND
> asmt_v2_area_id = @.aid
> AND
> asmt_v2_question_id
> NOT IN (
> -- retreive question ids (optional questions) in
> 'asmt_question_results' table
> -- that are associated with a given set of attempt_ids
> SELECT DISTINCT
> asmt_v2_questions.asmt_v2_question_id
> FROM
> asmt_v2_questions
> INNER JOIN
> asmt_v2_question_results
> ON
> asmt_v2_questions.asmt_v2_question_id =
> asmt_v2_question_results.asmt_v2_question_id
> WHERE
> asmt_v2_questions.qtype = 'o'
> AND
> asmt_v2_question_results.asmt_v2_attempt_id
> IN (
> -- retreive attempt_ids that are associated with completed assessments
> SELECT
> asmt_v2_completed.asmt_v2_attempt_id
> FROM
> asmt_v2_completed
> INNER JOIN
> asmt_v2_attempts
> ON
> asmt_v2_completed.asmt_v2_attempt_id =
> asmt_v2_attempts.asmt_v2_attempt_id
> WHERE
> asmt_v2_attempts.asmt_v2_employee_id = @.eid
> AND
> asmt_v2_attempts.asmt_v2_area_id = @.aid
> )
> )
> ORDER BY 1
>|||Umm..as mentioned in my post i only want to limit the number of rows
returned in the SECOND query of the UNION.
Unless i am MISSING something, SET ROWCOUNT witll apply to all results
returned in the query!
Adam
"Michael C#" <xyz@.abcdef.com> wrote in message
news:Vg10f.2316$Hd2.2048@.fe11.lga...
> Try SET ROWCOUNT in BOL.
> "Adam Knight" <adam@.pertrain.com.au> wrote in message
> news:eEmek27xFHA.2556@.TK2MSFTNGP10.phx.gbl...
>|||Adam Knight wrote:
> Umm..as mentioned in my post i only want to limit the number of rows
> returned in the SECOND query of the UNION.
> Unless i am MISSING something, SET ROWCOUNT witll apply to all results
> returned in the query!
>
You can store the smaller result set (probably the one with the TOP in a
temp table) and then union the temp table with the other query and avoid
dynamic sql altogether.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||THANK YOU!!!!!
I think we may have a useable option.
One last question to nail this.
I am trying to create the temp table and insert the relevant data.
I am recieving this error:
Insert Error: Column name or number of supplied values does not match table
definition.
Any thoughts?
As far as i can tell the column definitions are the same..
CREATE TABLE #tempTable (
[asmt_v2_question_id] [int] NOT NULL,
[qtext] [varchar](255) NULL,
[qindex] [tinyint] NULL,
[qtype] [char](1) NULL,
[answer_url] [varchar](255) NULL
)
INSERT INTO #tempTable
SELECT
asmt_v2_question_id, qtext, qindex, qtype, answer_url
FROM
asmt_v2_questions
WHERE
qtype = 'o'
AND
asmt_v2_area_id = @.aid
AND
asmt_v2_question_id
NOT IN (
SELECT DISTINCT
asmt_v2_questions.asmt_v2_question_id
FROM
asmt_v2_questions
INNER JOIN
asmt_v2_question_results
ON
asmt_v2_questions.asmt_v2_question_id =
asmt_v2_question_results.asmt_v2_question_id
WHERE
asmt_v2_questions.qtype = 'o'
AND
asmt_v2_question_results.asmt_v2_attempt_id
IN (
SELECT
asmt_v2_completed.asmt_v2_attempt_id
FROM
asmt_v2_completed
INNER JOIN
asmt_v2_attempts
ON
asmt_v2_completed.asmt_v2_attempt_id =
asmt_v2_attempts.asmt_v2_attempt_id
WHERE
asmt_v2_attempts.asmt_v2_employee_id = @.eid
AND
asmt_v2_attempts.asmt_v2_area_id = @.aid
)
)
ORDER BY NEWID()
SELECT * FROM #tempTable
CREATE TABLE [dbo].[asmt_v2_questions](
[asmt_v2_question_id] [int] IDENTITY(1,1) NOT NULL,
[asmt_v2_area_id] [int] NULL,
[qtext] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[qindex] [tinyint] NULL,
[qtype] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[answer_url] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_assmnt_questions] PRIMARY KEY CLUSTERED
(
[asmt_v2_question_id] ASC
) ON [PRIMARY]
) ON [PRIMARY]|||Inside the INSERT...SELECT statement you must explicitly state which columns
you're inserting into.
INSERT INTO #tempTable
(
asmt_v2_question_id
,qtext
,qindex
,qtype
,answer_url
)
SELECT
asmt_v2_question_id, qtext, qindex, qtype, answer_url
FROM
asmt_v2_questions
<snip>
ML