is there a calendar control for custom parameter? ThanksNope! But I heard it may be available in RS released with Yukon.
"VNN" <VNN@.hotmail.com> wrote in message
news:%233vBp8OCFHA.2072@.TK2MSFTNGP10.phx.gbl...
>|||Here's something posted earlier by Brian Welcker
----
No, but this is something we are planning for the next release of Reporting
Services.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
----
--
Adrian M.
MCP
"VNN" <VNN@.hotmail.com> wrote in message
news:%233vBp8OCFHA.2072@.TK2MSFTNGP10.phx.gbl...
>|||Adrian M. wrote:
> Here's something posted earlier by Brian Welcker
> No, but this is something we are planning for the next release of
> Reporting Services.
Meanwhile you can use the viewer from:
http://www.codeproject.com/useritems/SQLRSViewer.asp
It uses exclusively the RS SOAP API. So you can implement your own calendar
control. It's quite easy.
We are using this method for a rich reporting-application in some
customer-solutions.
regards
Frank
www.xax.de|||If you can not wait for Yukon, prepare an html page which shows the
datepicker, and pass the parameters in to the report URL...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"VNN" <VNN@.hotmail.com> wrote in message
news:%233vBp8OCFHA.2072@.TK2MSFTNGP10.phx.gbl...
>
Showing posts with label parameter. Show all posts
Showing posts with label parameter. Show all posts
Monday, March 26, 2012
Friday, March 23, 2012
Is there a better way to do this?
I have a stored proc that returns information depending on the
particular column slelected by a passed in parameter.
For Instance:
Passed in parameters: @.Rep = 876 @.RepType = 2
SELECT *
FROM Table1 T1
INNER JOIN Table2 T2
ON T1.Id = T2.Id
WHERE CASE WHEN @.RepType = 1 THEN T1.Rep1
WHEN @.RepType = 2 THEN T1.Rep2
WHEN @.RepType = 3 THEN T1.Rep3
END = @.Rep
The problem is if I need to join another table that may need to use the
"Rep" columns. Right now, I end up repeating the case statement
wherever I need to compare "Rep". Is there a better way to do this?
SteveYes there is a better solution: normalize the design. Having Rep1...RepN doe
s
not satisfy First Normal Form and is the reason your having difficulty with
your
query. Add a table something akin to:
Create Table Table1Reps
(
Table1Id Int References Table1(Id)
, RepId Int References ?(?)
, Constraint PK_Table1Reps Primary Key (Table1Id, RepId)
)
Now your query is significantly easier:
Select F1...Fn
From Table1 As T1
Join Table2 As T2
On T1.Id = T2.Id
Join Table1Reps As T1Reps
On T1Reps.Table1Id = T1.Id
And T1Reps.RepId = @.Rep
If there really are "types" of reps, then simply add a column to the Table1R
eps
table.
HTH
Thomas
"steve" <stevem2112@.gmail.com> wrote in message
news:1116878895.468831.52980@.g49g2000cwa.googlegroups.com...
>I have a stored proc that returns information depending on the
> particular column slelected by a passed in parameter.
> For Instance:
>
> Passed in parameters: @.Rep = 876 @.RepType = 2
> SELECT *
> FROM Table1 T1
> INNER JOIN Table2 T2
> ON T1.Id = T2.Id
> WHERE CASE WHEN @.RepType = 1 THEN T1.Rep1
> WHEN @.RepType = 2 THEN T1.Rep2
> WHEN @.RepType = 3 THEN T1.Rep3
> END = @.Rep
>
> The problem is if I need to join another table that may need to use the
> "Rep" columns. Right now, I end up repeating the case statement
> wherever I need to compare "Rep". Is there a better way to do this?
>
> Steve
>|||>> Right now, I end up repeating the case statement wherever I need to
Represent rep_types as a single attribute and your queries would be much
simpler. It might also help in enforcing integrity constraints depending on
your business requirements.
Anith|||On 23 May 2005 13:08:15 -0700, steve wrote:
>I have a stored proc that returns information depending on the
>particular column slelected by a passed in parameter.
>For Instance:
>
>Passed in parameters: @.Rep = 876 @.RepType = 2
>SELECT *
>FROM Table1 T1
>INNER JOIN Table2 T2
>ON T1.Id = T2.Id
>WHERE CASE WHEN @.RepType = 1 THEN T1.Rep1
> WHEN @.RepType = 2 THEN T1.Rep2
> WHEN @.RepType = 3 THEN T1.Rep3
> END = @.Rep
>
>The problem is if I need to join another table that may need to use the
>"Rep" columns. Right now, I end up repeating the case statement
>wherever I need to compare "Rep". Is there a better way to do this?
Hi Steve,
You could use a derived table:
SELECT Column list
FROM (SELECT Column list
, CASE @.RepType
WHEN 1 THEN Rep1
WHEN 2 THEN Rep2
WHEN 3 THEN Rep3
END AS Rep
FROM Table1) AS T1
INNER JOIN Table2 AS T2
ON T2.Id = T2.Id
WHERE T1.Rep = @.Rep
However, you might also consider normalizing your data - this query
r
s of a repeating group.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you for the quick response. I have thought about normalizing
this data, there is a condition that has prevented me from doing this.
Passed in parameters: @.Rep = 876 @.RepType = 2
SELECT *
FROM Table1 T1
INNER JOIN Table2 T2
ON T1.Id = T2.Id
WHERE CASE WHEN @.RepType = 1 THEN T1.Rep1
WHEN @.RepType = 2 THEN ISNULL(T1.Rep2, T1.Rep1)
WHEN @.RepType = 3 THEN ISNULL(T1.Rep3, T1.Rep1)
END = @.Rep
If the data is normalized, it would take a second query to lookup an
alternate rep. I suppose my other option is to pre-populate the null
values, if necessary. BTW, what is the performance hit on a query like
this?
Steve|||<snip>
> If the data is normalized, it would take a second query to lookup an
> alternate rep. I suppose my other option is to pre-populate the null
> values, if necessary. BTW, what is the performance hit on a query like
> this?
There are other ways of solving the problem of which you speak. The solution
depends greatly on the logic used to substitute a Rep. Regardless, normalizi
ng
the data is still the best solution for this problem.
Thomas|||Looks like a pair of outer joins would do it but that's just guesswork
without a better spec. Normalize the data, that's a given, then post a
fuller description of the problem: DDL, sample data, required results.
David Portas
SQL Server MVP
--|||
David Portas wrote:
> Looks like a pair of outer joins would do it but that's just guesswork
> without a better spec. Normalize the data, that's a given, then post a
> fuller description of the problem: DDL, sample data, required results.
> --
> David Portas
> SQL Server MVP
> --
Okay, so let's say I do normalize the data into a table. Like this:
Store# RepType Value
1 1 765
1 2 546
2 1 345
2 3 876
How exactly do I tell that Store #2 does not have a Rep of Type 2, and
substitute it with the value from RepType 1' All that I can see
happening is that Store #2 gets dropped in the join regardless of inner
or outer.
Steve|||Is RepType = 1 *always* substituted if a given RepType does not exist? If th
at
is the following would work:
Select Stores.[Store#]
, Coalesce(SR.RepValue, SubstituteReps.RepValue) As RepValue
From Stores
Left Join StoreReps As SR
On SR.[Store#] = Stores.[Store#]
And SR.RepValue = @.Rep
And SR.RepType = @.RepType
Left Join (
Select SR1.[Store#], SR1.RepValue
From StoreReps As SR1
Where SR1.RepType = 1
) As SubstituteReps
On Stores.[Store#] = SubstituteReps.[Store#]
This query assumes that each Store can only have one rep of a given type. Ye
s,
this requires a subquery, but if there is an index on Store# and RepType, th
is
will be very fast.
BTW, I'd avoid using a "#" in a column name. Doing so generally requires tha
t
you encase the column name in square brackets making it more of a pain to co
de
against.
Thomas
"steve" <stevem2112@.gmail.com> wrote in message
news:1116973698.009043.168680@.g43g2000cwa.googlegroups.com...
>
> David Portas wrote:
>
> Okay, so let's say I do normalize the data into a table. Like this:
> Store# RepType Value
> 1 1 765
> 1 2 546
> 2 1 345
> 2 3 876
>
> How exactly do I tell that Store #2 does not have a Rep of Type 2, and
> substitute it with the value from RepType 1' All that I can see
> happening is that Store #2 gets dropped in the join regardless of inner
> or outer.
>
> Steve
>|||
Thomas Coleman wrote:
> Is RepType = 1 *always* substituted if a given RepType does not exist? If
that
> is the following would work:
> Select Stores.[Store#]
> , Coalesce(SR.RepValue, SubstituteReps.RepValue) As RepValue
> From Stores
> Left Join StoreReps As SR
> On SR.[Store#] = Stores.[Store#]
> And SR.RepValue = @.Rep
> And SR.RepType = @.RepType
> Left Join (
> Select SR1.[Store#], SR1.RepValue
> From StoreReps As SR1
> Where SR1.RepType = 1
> ) As SubstituteReps
> On Stores.[Store#] = SubstituteReps.[Store#]
> This query assumes that each Store can only have one rep of a given type.
Yes,
> this requires a subquery, but if there is an index on Store# and RepType,
this
> will be very fast.
> BTW, I'd avoid using a "#" in a column name. Doing so generally requires t
hat
> you encase the column name in square brackets making it more of a pain to
code
> against.
>
> Thomas
>
> "steve" <stevem2112@.gmail.com> wrote in message
> news:1116973698.009043.168680@.g43g2000cwa.googlegroups.com...
You are correct in assuming that RepType 1 will always be the
substitute, however it is only a substitute for RepType 2. The rest of
the reptypes are not substituted at all. If they are null, they are
null.
particular column slelected by a passed in parameter.
For Instance:
Passed in parameters: @.Rep = 876 @.RepType = 2
SELECT *
FROM Table1 T1
INNER JOIN Table2 T2
ON T1.Id = T2.Id
WHERE CASE WHEN @.RepType = 1 THEN T1.Rep1
WHEN @.RepType = 2 THEN T1.Rep2
WHEN @.RepType = 3 THEN T1.Rep3
END = @.Rep
The problem is if I need to join another table that may need to use the
"Rep" columns. Right now, I end up repeating the case statement
wherever I need to compare "Rep". Is there a better way to do this?
SteveYes there is a better solution: normalize the design. Having Rep1...RepN doe
s
not satisfy First Normal Form and is the reason your having difficulty with
your
query. Add a table something akin to:
Create Table Table1Reps
(
Table1Id Int References Table1(Id)
, RepId Int References ?(?)
, Constraint PK_Table1Reps Primary Key (Table1Id, RepId)
)
Now your query is significantly easier:
Select F1...Fn
From Table1 As T1
Join Table2 As T2
On T1.Id = T2.Id
Join Table1Reps As T1Reps
On T1Reps.Table1Id = T1.Id
And T1Reps.RepId = @.Rep
If there really are "types" of reps, then simply add a column to the Table1R
eps
table.
HTH
Thomas
"steve" <stevem2112@.gmail.com> wrote in message
news:1116878895.468831.52980@.g49g2000cwa.googlegroups.com...
>I have a stored proc that returns information depending on the
> particular column slelected by a passed in parameter.
> For Instance:
>
> Passed in parameters: @.Rep = 876 @.RepType = 2
> SELECT *
> FROM Table1 T1
> INNER JOIN Table2 T2
> ON T1.Id = T2.Id
> WHERE CASE WHEN @.RepType = 1 THEN T1.Rep1
> WHEN @.RepType = 2 THEN T1.Rep2
> WHEN @.RepType = 3 THEN T1.Rep3
> END = @.Rep
>
> The problem is if I need to join another table that may need to use the
> "Rep" columns. Right now, I end up repeating the case statement
> wherever I need to compare "Rep". Is there a better way to do this?
>
> Steve
>|||>> Right now, I end up repeating the case statement wherever I need to
Represent rep_types as a single attribute and your queries would be much
simpler. It might also help in enforcing integrity constraints depending on
your business requirements.
Anith|||On 23 May 2005 13:08:15 -0700, steve wrote:
>I have a stored proc that returns information depending on the
>particular column slelected by a passed in parameter.
>For Instance:
>
>Passed in parameters: @.Rep = 876 @.RepType = 2
>SELECT *
>FROM Table1 T1
>INNER JOIN Table2 T2
>ON T1.Id = T2.Id
>WHERE CASE WHEN @.RepType = 1 THEN T1.Rep1
> WHEN @.RepType = 2 THEN T1.Rep2
> WHEN @.RepType = 3 THEN T1.Rep3
> END = @.Rep
>
>The problem is if I need to join another table that may need to use the
>"Rep" columns. Right now, I end up repeating the case statement
>wherever I need to compare "Rep". Is there a better way to do this?
Hi Steve,
You could use a derived table:
SELECT Column list
FROM (SELECT Column list
, CASE @.RepType
WHEN 1 THEN Rep1
WHEN 2 THEN Rep2
WHEN 3 THEN Rep3
END AS Rep
FROM Table1) AS T1
INNER JOIN Table2 AS T2
ON T2.Id = T2.Id
WHERE T1.Rep = @.Rep
However, you might also consider normalizing your data - this query
r
s of a repeating group.Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you for the quick response. I have thought about normalizing
this data, there is a condition that has prevented me from doing this.
Passed in parameters: @.Rep = 876 @.RepType = 2
SELECT *
FROM Table1 T1
INNER JOIN Table2 T2
ON T1.Id = T2.Id
WHERE CASE WHEN @.RepType = 1 THEN T1.Rep1
WHEN @.RepType = 2 THEN ISNULL(T1.Rep2, T1.Rep1)
WHEN @.RepType = 3 THEN ISNULL(T1.Rep3, T1.Rep1)
END = @.Rep
If the data is normalized, it would take a second query to lookup an
alternate rep. I suppose my other option is to pre-populate the null
values, if necessary. BTW, what is the performance hit on a query like
this?
Steve|||<snip>
> If the data is normalized, it would take a second query to lookup an
> alternate rep. I suppose my other option is to pre-populate the null
> values, if necessary. BTW, what is the performance hit on a query like
> this?
There are other ways of solving the problem of which you speak. The solution
depends greatly on the logic used to substitute a Rep. Regardless, normalizi
ng
the data is still the best solution for this problem.
Thomas|||Looks like a pair of outer joins would do it but that's just guesswork
without a better spec. Normalize the data, that's a given, then post a
fuller description of the problem: DDL, sample data, required results.
David Portas
SQL Server MVP
--|||
David Portas wrote:
> Looks like a pair of outer joins would do it but that's just guesswork
> without a better spec. Normalize the data, that's a given, then post a
> fuller description of the problem: DDL, sample data, required results.
> --
> David Portas
> SQL Server MVP
> --
Okay, so let's say I do normalize the data into a table. Like this:
Store# RepType Value
1 1 765
1 2 546
2 1 345
2 3 876
How exactly do I tell that Store #2 does not have a Rep of Type 2, and
substitute it with the value from RepType 1' All that I can see
happening is that Store #2 gets dropped in the join regardless of inner
or outer.
Steve|||Is RepType = 1 *always* substituted if a given RepType does not exist? If th
at
is the following would work:
Select Stores.[Store#]
, Coalesce(SR.RepValue, SubstituteReps.RepValue) As RepValue
From Stores
Left Join StoreReps As SR
On SR.[Store#] = Stores.[Store#]
And SR.RepValue = @.Rep
And SR.RepType = @.RepType
Left Join (
Select SR1.[Store#], SR1.RepValue
From StoreReps As SR1
Where SR1.RepType = 1
) As SubstituteReps
On Stores.[Store#] = SubstituteReps.[Store#]
This query assumes that each Store can only have one rep of a given type. Ye
s,
this requires a subquery, but if there is an index on Store# and RepType, th
is
will be very fast.
BTW, I'd avoid using a "#" in a column name. Doing so generally requires tha
t
you encase the column name in square brackets making it more of a pain to co
de
against.
Thomas
"steve" <stevem2112@.gmail.com> wrote in message
news:1116973698.009043.168680@.g43g2000cwa.googlegroups.com...
>
> David Portas wrote:
>
> Okay, so let's say I do normalize the data into a table. Like this:
> Store# RepType Value
> 1 1 765
> 1 2 546
> 2 1 345
> 2 3 876
>
> How exactly do I tell that Store #2 does not have a Rep of Type 2, and
> substitute it with the value from RepType 1' All that I can see
> happening is that Store #2 gets dropped in the join regardless of inner
> or outer.
>
> Steve
>|||
Thomas Coleman wrote:
> Is RepType = 1 *always* substituted if a given RepType does not exist? If
that
> is the following would work:
> Select Stores.[Store#]
> , Coalesce(SR.RepValue, SubstituteReps.RepValue) As RepValue
> From Stores
> Left Join StoreReps As SR
> On SR.[Store#] = Stores.[Store#]
> And SR.RepValue = @.Rep
> And SR.RepType = @.RepType
> Left Join (
> Select SR1.[Store#], SR1.RepValue
> From StoreReps As SR1
> Where SR1.RepType = 1
> ) As SubstituteReps
> On Stores.[Store#] = SubstituteReps.[Store#]
> This query assumes that each Store can only have one rep of a given type.
Yes,
> this requires a subquery, but if there is an index on Store# and RepType,
this
> will be very fast.
> BTW, I'd avoid using a "#" in a column name. Doing so generally requires t
hat
> you encase the column name in square brackets making it more of a pain to
code
> against.
>
> Thomas
>
> "steve" <stevem2112@.gmail.com> wrote in message
> news:1116973698.009043.168680@.g43g2000cwa.googlegroups.com...
You are correct in assuming that RepType 1 will always be the
substitute, however it is only a substitute for RepType 2. The rest of
the reptypes are not substituted at all. If they are null, they are
null.
Wednesday, March 7, 2012
is problems with hidden parameters solved in sp1?
I have installed the sp1
But the problem with hidden parameters is still there. I could not set the
value of hidden parameter from the querysting.
Let me know how to check whether the sp1 is properly installed or not.
Thanks in advanceThis is onlt supported on the server. In Report Manager, leave the 'prompt
user' checkbox checked but clear our the prompt string. This should not
prompt the user but still allow you to specify them via URL.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"VV" <vineshk@.omegatech.com> wrote in message
news:OkNdQneWEHA.2520@.TK2MSFTNGP12.phx.gbl...
>I have installed the sp1
> But the problem with hidden parameters is still there. I could not set the
> value of hidden parameter from the querysting.
> Let me know how to check whether the sp1 is properly installed or not.
> Thanks in advance
>|||Just a suggestion -
Wouldn't it be better if you provide with a checkbox in Report Manager
that just says "hide parameter" and which in turn will clear the
prompt string an keep the prompt user checked... ' It took me 20 min.
to figure out that. Your readme on sp1 is not clear on this...
Thanks|||Yes, but this required a string change that we didn't want to add in SP1. We
will make it more understandable in the future.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Harsh" <creative@.mailcity.com> wrote in message
news:fa671a26.0406281005.1cf5f133@.posting.google.com...
> Just a suggestion -
> Wouldn't it be better if you provide with a checkbox in Report Manager
> that just says "hide parameter" and which in turn will clear the
> prompt string an keep the prompt user checked... ' It took me 20 min.
> to figure out that. Your readme on sp1 is not clear on this...
> Thanks
But the problem with hidden parameters is still there. I could not set the
value of hidden parameter from the querysting.
Let me know how to check whether the sp1 is properly installed or not.
Thanks in advanceThis is onlt supported on the server. In Report Manager, leave the 'prompt
user' checkbox checked but clear our the prompt string. This should not
prompt the user but still allow you to specify them via URL.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"VV" <vineshk@.omegatech.com> wrote in message
news:OkNdQneWEHA.2520@.TK2MSFTNGP12.phx.gbl...
>I have installed the sp1
> But the problem with hidden parameters is still there. I could not set the
> value of hidden parameter from the querysting.
> Let me know how to check whether the sp1 is properly installed or not.
> Thanks in advance
>|||Just a suggestion -
Wouldn't it be better if you provide with a checkbox in Report Manager
that just says "hide parameter" and which in turn will clear the
prompt string an keep the prompt user checked... ' It took me 20 min.
to figure out that. Your readme on sp1 is not clear on this...
Thanks|||Yes, but this required a string change that we didn't want to add in SP1. We
will make it more understandable in the future.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Harsh" <creative@.mailcity.com> wrote in message
news:fa671a26.0406281005.1cf5f133@.posting.google.com...
> Just a suggestion -
> Wouldn't it be better if you provide with a checkbox in Report Manager
> that just says "hide parameter" and which in turn will clear the
> prompt string an keep the prompt user checked... ' It took me 20 min.
> to figure out that. Your readme on sp1 is not clear on this...
> Thanks
Subscribe to:
Posts (Atom)