Dynamic Parameters

Iam programatically passing parameters to the rdl file, Right now I have four parameters but if I want to have a choice of giveing only 2 or more that 6 parameters then do I need to have a different RDl files one with 2 parameters & another with 6 parameters.
I dont think it is feasible.
Even I should have the flexibility to add or remove the number of parameters dynamically. is ther any wayhow can I sole this situation.

Example: Iam displaying employee table of AdventureWorks database where Iam passing
Parameter 1.DepartmentId
Parameter 2.Title
and for Hiredate
iam passing i.e
Parameter 3. startdate &
Parameter 4.enddate

now that I have to give all the values in parameters to view the report if I want to all the employees in deptid = 5 having Title="Buyer" but not restricting them with date then it is not possible.

I hope the Above situation is clear please feel free if U have any queries.

HELP ME Dude's

Regards

You could assign default values to all 6 parameters at report design time. If a parameter value isn't specified at runtime the default value will be used instead. Just write your SQL query to take the default values into account and not use the param as a filter value - it generally looks like (@.Title = TableName.Title OR @. Title IS NULL) AND ...
The one caveat is that default values can sometimes throw off the optimizer and make for long running queries.

dynamic grouping

Hi,
I have a report with grouping. I am passing a parameter via asp.net and if
that parameter @.paramrep=7 then i want to group by type and by region
otherwise i want to group only by region.
I have looked at Chris Hay's example but I am still not sure i do get it to
work as I keep on getting an error: the expression referenced a non-existing
field in the fields collection.
This is my expression:
=iif(Parameters!Paramreport.Value
=7,1,Fields(iif(Parameters!Paramreport.Value =7,
"region",Parameters!Paramreport.Value)).Value)
I am not sure what I am doing wrong as basically I didn't really understand
the article.
I would appreciate any help offered.
ThanksWhile I haven't seen the example you mention, I often use functions for
dynamic grouping ie...( my syntax here might be bad...)
Public function GetGroups(Byref Groupid as integer, Byref Paramval as
Integer) as String
Switch Paramval
Case 7 If Groupid = 1 Then
Return("Price")
Else Return("Productname")
End IF
Case Else If Groupid = 1 Then
Return("Othercol1")
Else Return("Othercol2")
End IF
End
End
THen in the Grouping dialog
=Fields(GetGroups(1,Parameters!Parametername.Value)).Value
=Fields(GetGroups(2,Parameters!Parametername.Value)).Value
Hope this helps... By the way, sometimes I have to dynamically change the
sort to match the grouping at the highest level, otherwise I have gotten an
error...But the sort is handled the same way..
--
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
"collie" <collie@.discussions.microsoft.com> wrote in message
news:416E56D8-9856-412A-8381-A7BBD1DCBAC5@.microsoft.com...
> Hi,
> I have a report with grouping. I am passing a parameter via asp.net and if
> that parameter @.paramrep=7 then i want to group by type and by region
> otherwise i want to group only by region.
> I have looked at Chris Hay's example but I am still not sure i do get it
to
> work as I keep on getting an error: the expression referenced a
non-existing
> field in the fields collection.
> This is my expression:
> =iif(Parameters!Paramreport.Value
> =7,1,Fields(iif(Parameters!Paramreport.Value =7,
> "region",Parameters!Paramreport.Value)).Value)
> I am not sure what I am doing wrong as basically I didn't really
understand
> the article.
> I would appreciate any help offered.
> Thanks|||Hi,
Thanks so much for your response.
I have a few questions about your code if you could please clarify (I feel
stupid for asking but...)
Ok here goes :-)
What do you mean by groupid such as groupid=1?
What is price? A field name to group by if groupid=1?
Parmetername in my case would be the parameter that i send from asp.net
@.paramrep=7 correct?
Thanks
"Wayne Snyder" wrote:
> While I haven't seen the example you mention, I often use functions for
> dynamic grouping ie...( my syntax here might be bad...)
> Public function GetGroups(Byref Groupid as integer, Byref Paramval as
> Integer) as String
> Switch Paramval
> Case 7 If Groupid = 1 Then
> Return("Price")
> Else Return("Productname")
> End IF
> Case Else If Groupid = 1 Then
> Return("Othercol1")
> Else Return("Othercol2")
> End IF
> End
> End
>
> THen in the Grouping dialog
> =Fields(GetGroups(1,Parameters!Parametername.Value)).Value
> =Fields(GetGroups(2,Parameters!Parametername.Value)).Value
> Hope this helps... By the way, sometimes I have to dynamically change the
> sort to match the grouping at the highest level, otherwise I have gotten an
> error...But the sort is handled the same way..
> --
> 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
> "collie" <collie@.discussions.microsoft.com> wrote in message
> news:416E56D8-9856-412A-8381-A7BBD1DCBAC5@.microsoft.com...
> > Hi,
> >
> > I have a report with grouping. I am passing a parameter via asp.net and if
> > that parameter @.paramrep=7 then i want to group by type and by region
> > otherwise i want to group only by region.
> > I have looked at Chris Hay's example but I am still not sure i do get it
> to
> > work as I keep on getting an error: the expression referenced a
> non-existing
> > field in the fields collection.
> > This is my expression:
> > =iif(Parameters!Paramreport.Value
> > =7,1,Fields(iif(Parameters!Paramreport.Value =7,
> > "region",Parameters!Paramreport.Value)).Value)
> > I am not sure what I am doing wrong as basically I didn't really
> understand
> > the article.
> >
> > I would appreciate any help offered.
> >
> > Thanks
>
>|||Wayne your code was a great help.
Thanks :-)
"Wayne Snyder" wrote:
> While I haven't seen the example you mention, I often use functions for
> dynamic grouping ie...( my syntax here might be bad...)
> Public function GetGroups(Byref Groupid as integer, Byref Paramval as
> Integer) as String
> Switch Paramval
> Case 7 If Groupid = 1 Then
> Return("Price")
> Else Return("Productname")
> End IF
> Case Else If Groupid = 1 Then
> Return("Othercol1")
> Else Return("Othercol2")
> End IF
> End
> End
>
> THen in the Grouping dialog
> =Fields(GetGroups(1,Parameters!Parametername.Value)).Value
> =Fields(GetGroups(2,Parameters!Parametername.Value)).Value
> Hope this helps... By the way, sometimes I have to dynamically change the
> sort to match the grouping at the highest level, otherwise I have gotten an
> error...But the sort is handled the same way..
> --
> 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
> "collie" <collie@.discussions.microsoft.com> wrote in message
> news:416E56D8-9856-412A-8381-A7BBD1DCBAC5@.microsoft.com...
> > Hi,
> >
> > I have a report with grouping. I am passing a parameter via asp.net and if
> > that parameter @.paramrep=7 then i want to group by type and by region
> > otherwise i want to group only by region.
> > I have looked at Chris Hay's example but I am still not sure i do get it
> to
> > work as I keep on getting an error: the expression referenced a
> non-existing
> > field in the fields collection.
> > This is my expression:
> > =iif(Parameters!Paramreport.Value
> > =7,1,Fields(iif(Parameters!Paramreport.Value =7,
> > "region",Parameters!Paramreport.Value)).Value)
> > I am not sure what I am doing wrong as basically I didn't really
> understand
> > the article.
> >
> > I would appreciate any help offered.
> >
> > Thanks
>
>

Dynamic filter using a UDF

In BOL it says that you can use a UDF in a dynamic filter, passing in one of
the system UDFs. However, when I try this I get a syntax error, e.g.
SELECT * FROM [dbo].[SiteData]
WHERE SiteId IN (SELECT Value FROM dbo.Split(HOST_NAME(), ','))
where dbo.Split is a UDF that splits a list of values into a table
It doesn't seem to like the brackets after HOST_NAME, but the example shown
in BOL is MYUDF(HOSTNAME())
Any ideas what I've got wrong with the syntax?
TIA
Paul
Paul,
the only way I've been able to resolve a system function to multiple values
is to use a linking table and replicate it as well. Have a look at the
example in the merge section of www.replicationanswers.com where there is a
related example.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul
Thanks for that, I can make it work in my scenario - BTW you also quote the
MYUDF(HOST_NAME()) solution as well further down the page.
Regards
Paul
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%236k7vXQxEHA.3612@.tk2msftngp13.phx.gbl...
> Paul,
> the only way I've been able to resolve a system function to multiple
> values
> is to use a linking table and replicate it as well. Have a look at the
> example in the merge section of www.replicationanswers.com where there is
> a
> related example.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Paul,
I'll edit the page to make clearer, but I've only used
the MYUDF(HOST_NAME()) solution for scalars.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I'll need to check this out in SQL 2005 beta to see if it's fixed or if
anyone can give a reason why ...
SELECT * FROM [dbo].[SiteData] WHERE SiteId IN (SELECT Value FROM
dbo.Split('1, 2', ','))
compile (and runs), but
SELECT * FROM [dbo].[SiteData] WHERE SiteId IN (SELECT Value FROM
dbo.Split(HOST_NAME(), ','))
does not, whereas
SELECT * FROM [dbo].[SiteData] WHERE SiteId = MyUdf(HOST_NAME())
compiles again.
It's nothing to do with replication per se, but how the complier is parsing
the statements.
Thanks
Paul
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:4d0d01c4c575$382daee0$a301280a@.phx.gbl...
> Paul,
> I'll edit the page to make clearer, but I've only used
> the MYUDF(HOST_NAME()) solution for scalars.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||why don't you put the hostname within your function as opposed to supplying
it as an argument.
"Paul Hatcher" <phatcher@.nospam.cix.co.uk> wrote in message
news:OHJ9%238YxEHA.2624@.TK2MSFTNGP11.phx.gbl...
> I'll need to check this out in SQL 2005 beta to see if it's fixed or if
> anyone can give a reason why ...
> SELECT * FROM [dbo].[SiteData] WHERE SiteId IN (SELECT Value FROM
> dbo.Split('1, 2', ','))
> compile (and runs), but
> SELECT * FROM [dbo].[SiteData] WHERE SiteId IN (SELECT Value FROM
> dbo.Split(HOST_NAME(), ','))
> does not, whereas
> SELECT * FROM [dbo].[SiteData] WHERE SiteId = MyUdf(HOST_NAME())
> compiles again.
> It's nothing to do with replication per se, but how the complier is
> parsing the statements.
> Thanks
> Paul
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:4d0d01c4c575$382daee0$a301280a@.phx.gbl...
>
|||Because it says in BOL that you can't use dynamic snapshots if neither of
HOST_NAME/SUSER_NAME() functions are not specified in the row filter. The
scenario is a largish central database with a bunch of subsidiary offices,
some of which are on dial-up, so I'm trying everything to minimise the
amount of time taken to do replication and the amount of data on the wire.
I'll try it anyway and see what happens; the other point is that I've come
across Transact-SQL oddities like this before, and it's a good idea to get
rid of them if at all possible.
Thanks
Paul
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OX8lbLaxEHA.1400@.TK2MSFTNGP11.phx.gbl...
> why don't you put the hostname within your function as opposed to
> supplying it as an argument.
> "Paul Hatcher" <phatcher@.nospam.cix.co.uk> wrote in message
> news:OHJ9%238YxEHA.2624@.TK2MSFTNGP11.phx.gbl...
>

Dynamic Column Names?

Using SQL 2000
I am passing in the name of the column I want to update, @.Column. When I
SELECT @.Column it returns the value of @.Column instead of the value of the
actule table column. I have tried to use
Col_Name(OBJECT_ID('antwerp_cutoff'),OBJ
ECT_ID(@.Column)) but this does not
get the column name.
When I pass in Display1 I need to get back the value 2/2/2005
|Display1 | Display2|
--
|2/2/2005 | 2/3/2005|
--
Any help would be greatly appreciated.
Thanks,
Scott R. Butler
Stored Proc:
Create Procedure "Update_Antwerp_Displays"
(
@.Column varchar(256)
)
As
Declare @.i int
Declare @.Count int
set @.Count = (SELECT Count (*) FROM antwerp_trans)
UPDATE antwerp_cutoff
Set @.Column = (select @.Column+(Select Delta from antwerp_cutoff where id =
1 ) from antwerp_cutoff where Delta = 0)
WHERE ID = 1
UPDATE antwerp_cutoff
Set @.Column = (select @.Column+(Select Delta from antwerp_cutoff where id =
2 ) from antwerp_cutoff where Delta = 0)
WHERE ID = 2
UPDATE antwerp_depart
Set @.Column = (select @.Column+(Select Delta from antwerp_depart where id =
1 ) from antwerp_cutoff where Delta = 0)
where id = 1
UPDATE antwerp_arrive
Set @.Column = (select @.Column+(Select Delta from antwerp_arrive where id =
1 ) from antwerp_cutoff where Delta = 0)
where id = 1
While (@.i < @.Count)
BEGIN
UPDATE antwerp_trans
Set @.Column = (select @.Column+(Select Delta from antwerp_cutoff where id =
@.i ) from antwerp_cutoff where Delta = 0)
WHERE ID = @.i
SET @.i = @.i +1
ENDYou cannot use variables for tables, columns, etc. in queries. You'll
need to use dynamic SQL:
http://www.sommarskog.se/dynamic_sql.html
David Gugick
Imceda Software
www.imceda.com|||Thanks for the quick response... even though it was not the response I was
looking for.
Quess I will have to find another way to get it done.
Scott
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OYjcU7uDFHA.3324@.TK2MSFTNGP15.phx.gbl...
> You cannot use variables for tables, columns, etc. in queries. You'll need
> to use dynamic SQL:
> http://www.sommarskog.se/dynamic_sql.html
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Go back to your freshman software engineering books and look up the
concepts of coupling and cohesion.
What you want is a procedure with so little cohesion that any random
user, present or future, has more control over the RDBMS than the DBA.
This is **foundations of programming**, not anything particular to SQL.|||It seems that you are using column names to represent data. This is a
fundamental mistake and causes lots of problems, such as the one you
are having here. Columns should represent a single attribute and
repeating groups of columns are in violation of First Normal Form. Fix
your table design and you won't need messy dynamic SQL to accomplish
this.
David Portas
SQL Server MVP
--