Easy Report parameter Question

Im trying to hard code a Year in as an available value for the user to pick out of a drop down box. This is what i have so far.

Label Value

Travel Year 2006

well i want to go ahead and put two value for that one label , like this:

Label Value

Travel Year 2006, 2007

How do i do this? I tried putting a comma, and i tried putting a semi colon, but it always just grabs the first number "2006".

I know it cant be this hard! please help! THanks!

Enter another Label/Value combination like this:

Label Value

TravelYear 2006

TravelYear 2007

or you could write a little query to do it.

-Mike

|||

ok, this is actually not what im writing, i just tried to explain it in a simpler way, this is what i want

Label Value

Task National TN001, TN002, TN003, TN004, TN005, TN006, TN007

Task Vet TV001,TV002, TV003, TV004, TV005

Survey National SN001, SN002, SN003, SN004, SN005

and so on

Its a way of grouping the same type of tasks together in the parameter, so the user doesnt have to individually go through a long list of codes.

THere should be away to put one option with multiple values.

|||

You could manually create a dataset like this:

Select 'Task National' as Label, 'TN001, TN002, TN003, TN004, TN005, TN006, TN007' as Value

union

Select 'Task Vet' as Label, 'TV001,TV002, TV003, TV004, TV005' as Value

union

Select 'Survey National' as Label, 'SN001, SN002, SN003, SN004, SN005' as Value

Then parse the values and use them.

|||

I ended up just putting it in the where clause like this

Code Snippet

WHERE REGION_KEY=@.Region_Key

AND LEFT(Qry_Questions.[Question Code],2)IN (@.QuestionCode)

so it grouped the ones with the same 2 first letters. Works great!|||Nice job. Sometimes you have to be a little creative to get things to work right. :-)

Easy Report parameter Question

Im trying to hard code a Year in as an available value for the user to pick out of a drop down box. This is what i have so far.

Label Value

Travel Year 2006

well i want to go ahead and put two value for that one label , like this:

Label Value

Travel Year 2006, 2007

How do i do this? I tried putting a comma, and i tried putting a semi colon, but it always just grabs the first number "2006".

I know it cant be this hard! please help! THanks!

Enter another Label/Value combination like this:

Label Value

TravelYear 2006

TravelYear 2007

or you could write a little query to do it.

-Mike

|||

ok, this is actually not what im writing, i just tried to explain it in a simpler way, this is what i want

Label Value

Task National TN001, TN002, TN003, TN004, TN005, TN006, TN007

Task Vet TV001,TV002, TV003, TV004, TV005

Survey National SN001, SN002, SN003, SN004, SN005

and so on

Its a way of grouping the same type of tasks together in the parameter, so the user doesnt have to individually go through a long list of codes.

THere should be away to put one option with multiple values.

|||

You could manually create a dataset like this:

Select 'Task National' as Label, 'TN001, TN002, TN003, TN004, TN005, TN006, TN007' as Value

union

Select 'Task Vet' as Label, 'TV001,TV002, TV003, TV004, TV005' as Value

union

Select 'Survey National' as Label, 'SN001, SN002, SN003, SN004, SN005' as Value

Then parse the values and use them.

|||

I ended up just putting it in the where clause like this

Code Snippet

WHERE REGION_KEY=@.Region_Key

AND LEFT(Qry_Questions.[Question Code],2)IN (@.QuestionCode)

so it grouped the ones with the same 2 first letters. Works great!|||Nice job. Sometimes you have to be a little creative to get things to work right. :-)

Easy Report parameter Question

Im trying to hard code a Year in as an available value for the user to pick out of a drop down box. This is what i have so far.

Label Value

Travel Year 2006

well i want to go ahead and put two value for that one label , like this:

Label Value

Travel Year 2006, 2007

How do i do this? I tried putting a comma, and i tried putting a semi colon, but it always just grabs the first number "2006".

I know it cant be this hard! please help! THanks!

Enter another Label/Value combination like this:

Label Value

TravelYear 2006

TravelYear 2007

or you could write a little query to do it.

-Mike

|||

ok, this is actually not what im writing, i just tried to explain it in a simpler way, this is what i want

Label Value

Task National TN001, TN002, TN003, TN004, TN005, TN006, TN007

Task Vet TV001,TV002, TV003, TV004, TV005

Survey National SN001, SN002, SN003, SN004, SN005

and so on

Its a way of grouping the same type of tasks together in the parameter, so the user doesnt have to individually go through a long list of codes.

THere should be away to put one option with multiple values.

|||

You could manually create a dataset like this:

Select 'Task National' as Label, 'TN001, TN002, TN003, TN004, TN005, TN006, TN007' as Value

union

Select 'Task Vet' as Label, 'TV001,TV002, TV003, TV004, TV005' as Value

union

Select 'Survey National' as Label, 'SN001, SN002, SN003, SN004, SN005' as Value

Then parse the values and use them.

|||

I ended up just putting it in the where clause like this

Code Snippet

WHERE REGION_KEY=@.Region_Key

AND LEFT(Qry_Questions.[Question Code],2)IN (@.QuestionCode)

so it grouped the ones with the same 2 first letters. Works great!|||Nice job. Sometimes you have to be a little creative to get things to work right. :-)

Easy question

Hi,

I have a query that I need to pass as a string as the second parameter of the OpenQuery method. Here's my query:

SELECT * FROM mytable WHERE last_name = 'DOE'

Thing is that a string is set with apostrophies, so I don't know how to set my string since apostrophies are alse in my query. Normally, it would look like this:

DECLARE @.CQUERY VARCHAR(100)
SET @.CQUERY = 'SELECT * FROM mytable WHERE last_name = 'DOE''

But of course this fails. How can I do it then?

Thanks,

Skip.I always do it thid way

DECLARE @.CQUERY VARCHAR(100)
SET @.CQUERY = 'SELECT * FROM mytable WHERE last_name = '+ '''' + 'DOE' + ''''
SELECT @.cquery

Only cause it's easier for me to read.....|||SET @.CQUERY = "SELECT * FROM mytable WHERE last_name = 'DOE'"|||What's the order of the characters (I can't see them correctly with these fonts)?

Is it 2 double quotes, 3 apostrophies, etc.

Thanks again,

Skip|||I do

1 quote text message 1 quoye + 4 quotes + 1 qoute value 1 quote + 4 quotes...

but you should be able to cut and paste the code into QA...|||OK, I've tried a couple of solutions and I can see that something like this works fine:

DECLARE @.CQUERY VARCHAR(100)
SET @.CQUERY = 'SELECT * FROM mytable WHERE last_name = ' + '''' + 'DOE' + ''''

Here, '''' = 4 apostrophies.

Now, I find it wierd that this worked because shoudn't 4 apostrophies open-close empty strings twice (hence creating an error because the + sign is not between them)?

Is this a special case programmed for text appending in SQL Server?

Thanks,

Skip.

easy parameter Q

I think this is probably easy but haven't found the setting yet and am
running out of time.
My report has a column I want to use as a parameter(parameter query). The
value of the field repeats several times and I want to limit the drop down
list to the first occurance of each value.
Hope this is an easy one.Have you tried select Distinct(column) ?
"HollyylloH" wrote:
> I think this is probably easy but haven't found the setting yet and am
> running out of time.
> My report has a column I want to use as a parameter(parameter query). The
> value of the field repeats several times and I want to limit the drop down
> list to the first occurance of each value.
> Hope this is an easy one.|||Darwin,
Thanks for your reply. I am using report parameters and am not sure how to
use a distinct() within the confines of the the parameter options. If you can
help I would much appriciate it.
I don't want to affect the report query but rather the parameter drop-down
menu options.
"darwin" wrote:
> Have you tried select Distinct(column) ?
> "HollyylloH" wrote:
> > I think this is probably easy but haven't found the setting yet and am
> > running out of time.
> >
> > My report has a column I want to use as a parameter(parameter query). The
> > value of the field repeats several times and I want to limit the drop down
> > list to the first occurance of each value.
> >
> > Hope this is an easy one.|||create a new dataset to use to populate the parameter. You can create
multiple datasets to populate your parameters.
then change your parameter properties to use the new data set. select the
parameter you want to change, then click the From Query radio button, select
the new dataset name under dataset, select the Value Field value and the
label field. This is generally an Id and description.
hope that helps.. there should be something thats helps in the help files
"HollyylloH" wrote:
> Darwin,
> Thanks for your reply. I am using report parameters and am not sure how to
> use a distinct() within the confines of the the parameter options. If you can
> help I would much appriciate it.
> I don't want to affect the report query but rather the parameter drop-down
> menu options.
> "darwin" wrote:
> > Have you tried select Distinct(column) ?
> >
> > "HollyylloH" wrote:
> >
> > > I think this is probably easy but haven't found the setting yet and am
> > > running out of time.
> > >
> > > My report has a column I want to use as a parameter(parameter query). The
> > > value of the field repeats several times and I want to limit the drop down
> > > list to the first occurance of each value.
> > >
> > > Hope this is an easy one.|||Thanks a million! That did it for me!
"darwin" wrote:
> create a new dataset to use to populate the parameter. You can create
> multiple datasets to populate your parameters.
> then change your parameter properties to use the new data set. select the
> parameter you want to change, then click the From Query radio button, select
> the new dataset name under dataset, select the Value Field value and the
> label field. This is generally an Id and description.
> hope that helps.. there should be something thats helps in the help files
>
> "HollyylloH" wrote:
> > Darwin,
> >
> > Thanks for your reply. I am using report parameters and am not sure how to
> > use a distinct() within the confines of the the parameter options. If you can
> > help I would much appriciate it.
> >
> > I don't want to affect the report query but rather the parameter drop-down
> > menu options.
> >
> > "darwin" wrote:
> >
> > > Have you tried select Distinct(column) ?
> > >
> > > "HollyylloH" wrote:
> > >
> > > > I think this is probably easy but haven't found the setting yet and am
> > > > running out of time.
> > > >
> > > > My report has a column I want to use as a parameter(parameter query). The
> > > > value of the field repeats several times and I want to limit the drop down
> > > > list to the first occurance of each value.
> > > >
> > > > Hope this is an easy one.

Easiest method for moving databases from one partition to another

The first SQL install went on the smaller partition and we need to
move it from C: to D:. It seems to me there should be a startup
parameter we can change, stop the server, move the databases, and
restart the server. Our DB admin is saying we need a full reinstall.
It seems to me this should be easiert. I am seeing pathing
information in the database parameters in the server properties tabs
in enterprize manager. Can I just change those, do my move, and
restart?
suggestions greatly appreciated
HalHi
If you want to move the databases, look at sp_attachdb and sp_detachdb in BOL.
No need for re-install. (The location of master DB is in the registry so
moving that takes a bit more effort).
If you want to move the SQL EXE's, then un-install and re-install is required.
Regards
Mike
"hal@.nospam.com" wrote:
> The first SQL install went on the smaller partition and we need to
> move it from C: to D:. It seems to me there should be a startup
> parameter we can change, stop the server, move the databases, and
> restart the server. Our DB admin is saying we need a full reinstall.
> It seems to me this should be easiert. I am seeing pathing
> information in the database parameters in the server properties tabs
> in enterprize manager. Can I just change those, do my move, and
> restart?
> suggestions greatly appreciated
> Hal
>|||If you want to move the master database, you can add some options to the
sqlservr.exe program that is started as a service, you should use something
like
sqlservr -d<new masterdatafilepath> -l<new master log path>
Marc
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:21A185C6-89AD-4DC6-901F-B770BD9CA72D@.microsoft.com...
> Hi
> If you want to move the databases, look at sp_attachdb and sp_detachdb in
BOL.
> No need for re-install. (The location of master DB is in the registry so
> moving that takes a bit more effort).
> If you want to move the SQL EXE's, then un-install and re-install is
required.
> Regards
> Mike
> "hal@.nospam.com" wrote:
> > The first SQL install went on the smaller partition and we need to
> > move it from C: to D:. It seems to me there should be a startup
> > parameter we can change, stop the server, move the databases, and
> > restart the server. Our DB admin is saying we need a full reinstall.
> > It seems to me this should be easiert. I am seeing pathing
> > information in the database parameters in the server properties tabs
> > in enterprize manager. Can I just change those, do my move, and
> > restart?
> >
> > suggestions greatly appreciated
> >
> > Hal
> >

Dynmaic parameter on Subscription report

Is it possible to have a dynamic parameter value such as current date
to a subscription base report?
Thanks,
Voss.Yes. You can set the default parameter =today() in the designer.
"Voss" wrote:
> Is it possible to have a dynamic parameter value such as current date
> to a subscription base report?
> Thanks,
> Voss.
>sql

Dynamicly displaying Page Header and Page Footer

I need to show or hide Page Header and Footer on the last page of a report
based on a parameter. Is there a way of doing this?
Thanks in advanceIt seems that you can't dynamically choose visibility for the header or
footer. It's either on or off. But you can control the visibility of
elements on the header and footer.
Create a footer. Set DisplayOnFirstPage and DisplayOnLastPage = Yes
Add a rectangle. Add controls to the footer inside the rectangle.
On the Visibility parameter of the rectangle, type
=IIF(Globals!PageNumber>1, False, True)
If your report stretches over 2 or more pages, you will see the content of
the rectangle on page 2 and out. You can change the expression according to
your needs, this was just a quick example.
Kaisa M. Lindahl Lervik
"Wendy" <Wendy@.discussions.microsoft.com> wrote in message
news:A80B4773-F77A-43B5-BA3C-C2662073B2FD@.microsoft.com...
>I need to show or hide Page Header and Footer on the last page of a report
> based on a parameter. Is there a way of doing this?
> Thanks in advance
>|||the same way :
From http://www.developmentnow.com
Posted via DevelopmentNow.com Group
http://www.developmentnow.com

Dynamicly create report

Hi All,
I would like to create report like below:
For example: I have a stored procedure spDeptEmp, which has a Dept ID
as input parameter, Once the Dept ID has been passed in, I will get
all the employees on that dept. The question is: I would like to
generate the report, which will display each employee's detail
information, one person per page.
Could you let me know how can I do that using reporting services?
Thanks in advance!
--BillWhat do you mean by dynamic? I don't see the report being dynamic (i.e. that
you show different columns at different times or some such thing). It seems
to me that you are just returning different data depending on the parameter
but the format/layout etc of the report is unchanged. Everything you
describe here is very vanilla report generation for RS. You can easily add
page breaks, you can easily have a query based on a parameter.
Bruce L-C
"bill" <bli2001@.hotmail.com> wrote in message
news:2a3a3975.0408250950.723ae518@.posting.google.com...
> Hi All,
> I would like to create report like below:
> For example: I have a stored procedure spDeptEmp, which has a Dept ID
> as input parameter, Once the Dept ID has been passed in, I will get
> all the employees on that dept. The question is: I would like to
> generate the report, which will display each employee's detail
> information, one person per page.
> Could you let me know how can I do that using reporting services?
> Thanks in advance!
> --Bill|||"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uUeQVAtiEHA.3612@.TK2MSFTNGP12.phx.gbl>...
> What do you mean by dynamic? I don't see the report being dynamic (i.e. that
> you show different columns at different times or some such thing). It seems
> to me that you are just returning different data depending on the parameter
> but the format/layout etc of the report is unchanged. Everything you
> describe here is very vanilla report generation for RS. You can easily add
> page breaks, you can easily have a query based on a parameter.
> Bruce L-C
> "bill" <bli2001@.hotmail.com> wrote in message
> news:2a3a3975.0408250950.723ae518@.posting.google.com...
> > Hi All,
> >
> > I would like to create report like below:
> > For example: I have a stored procedure spDeptEmp, which has a Dept ID
> > as input parameter, Once the Dept ID has been passed in, I will get
> > all the employees on that dept. The question is: I would like to
> > generate the report, which will display each employee's detail
> > information, one person per page.
> >
> > Could you let me know how can I do that using reporting services?
> >
> > Thanks in advance!
> >
> > --Bill
The dynamic means you don't know how many employees inside one dept.
until you get the input parameter(dept ID). Different dept. will have
different number of employees. i.e. the report will be different.
Also, for one employee's information, it will come from different
dataset.
Thanks,
--Bill|||What you are wanting to do is exactly what RS is designed to do quite
easily. If a simple matter of here is a dept, list all employee's
information with page breaks between them. That would be a single
parameterized query with appropriate grouping and page breaks. If it is more
a master detail type report then subreports will do what you want.
Bruce L-C
"bill" <bli2001@.hotmail.com> wrote in message
news:2a3a3975.0408251442.232899ab@.posting.google.com...
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:<uUeQVAtiEHA.3612@.TK2MSFTNGP12.phx.gbl>...
> > What do you mean by dynamic? I don't see the report being dynamic (i.e.
that
> > you show different columns at different times or some such thing). It
seems
> > to me that you are just returning different data depending on the
parameter
> > but the format/layout etc of the report is unchanged. Everything you
> > describe here is very vanilla report generation for RS. You can easily
add
> > page breaks, you can easily have a query based on a parameter.
> >
> > Bruce L-C
> >
> > "bill" <bli2001@.hotmail.com> wrote in message
> > news:2a3a3975.0408250950.723ae518@.posting.google.com...
> > > Hi All,
> > >
> > > I would like to create report like below:
> > > For example: I have a stored procedure spDeptEmp, which has a Dept ID
> > > as input parameter, Once the Dept ID has been passed in, I will get
> > > all the employees on that dept. The question is: I would like to
> > > generate the report, which will display each employee's detail
> > > information, one person per page.
> > >
> > > Could you let me know how can I do that using reporting services?
> > >
> > > Thanks in advance!
> > >
> > > --Bill
> The dynamic means you don't know how many employees inside one dept.
> until you get the input parameter(dept ID). Different dept. will have
> different number of employees. i.e. the report will be different.
> Also, for one employee's information, it will come from different
> dataset.
> Thanks,
> --Bill

Dynamically set chart type?

How can I dynamically set the chart type based on a report parameter?
-mdbTo my knowledge, chart type is not available at run time, only design
time.
Andy Potter|||Howdy,
Being a complete newbie to reporting services I can think of 2 Q & D
ways to do this.
1. Add all your charts of various type to the report and set visibility
hidden=true on all but the one chart indicated by the parameter, or
2. Add and configure a chart with designer, then copy the XML in the
code view. Repeat for all chart types. Then paste all chart snippets
into the rdl with a case/switch statement for the parameter.
1. is probably quicker and dirtier, 2. is perhaps a little less
inelegant.
HTH,
Sean G.|||FYI,
YMMV, but I couldn't do much with suggestion 2 neither with the chart
<Type> nor the entire <Chart> element.
But no trouble at all with suggestion 1. Add all desired charts in
designer and set visibility based on your parameter.
Sean G.|||SeanGerman@.gmail.com wrote in news:1136925323.993091.53580
@.f14g2000cwb.googlegroups.com:
> But no trouble at all with suggestion 1. Add all desired charts in
> designer and set visibility based on your parameter.
Problem with that is that the data probably gets queried once for each
chart, no? In other words, the charts are being generated, you just can't
see them.
Its ok I found another way to do what I needed to do. Thanks!
-mdb

Dynamically select column

Hey all. I'm trying to create a stored proc that will update a variable column, depending on the parameter I pass it. Here's the stored proc:

CREATE PROCEDURE VoteStoredProc
(
 @.PlayerID int,
 @.VoteID int,
 @.BootNumber nvarchar(50)
)
AS
DECLARE @.SQLStatement varchar(255)
SET @.SQLStatement = 'UPDATE myTable SET '+ @.BootNumber+'='+ @.VoteID + ' WHERE (PlayerID = '+ @.PlayerID +')'
EXEC(@.SQLStatement)
GO

I get the following error:

Syntax error converting the nvarchar value 'UPDATE myTable SET Boot3=' to a column of data type int

The update statement is good, because if I use the stored proc below (hard-coded the column), it works fine.

CREATE PROCEDURE VoteStoredProc
(
 @.PlayerID int,
 @.VoteID int,
 @.BootNumber nvarchar(50)
)
AS
UPDATE
 myTable
SET
 Boot3 = @.VoteID
WHERE
 PlayerID = @.PlayerID
GO

Is there a way to dynamically choose a column/field to select from? Or is my syntax incorrect..?
Thanks!Try this:

CREATE PROCEDURE VoteStoredProc
(
 @.PlayerID int,
 @.VoteID int,
 @.BootNumber varchar(50)
)
AS
DECLARE @.SQLStatement varchar(255)
SET @.SQLStatement = 'UPDATE myTable SET '+ @.BootNumber+ ' = ' + CAST(@.VoteID as VARCHAR(10)) + ' WHERE (PlayerID = '+ CAST(@.PlayerID as VARCHAR(10)) +')'
EXEC(@.SQLStatement)
GO

Casting the integers to varchars so they can be concatenated into the larger string.

Hope this helps,
John|||John:

Brilliant! Thanks; works beautifully.

JP

Dynamically passing a Parameter Name to Custom Code

Hello,

I'm trying to create a custom code function for Reporting Services. I would like to have it user-friendly and give the user the ability to pass a report parameter name into the function (so it can be a generic function that can be used for many reports).

Is there a way to do this so inside the code I can have access to other properties of the object?

I envision something like:

Function Blah(ParameterName as String) as String

Dim MaxNum as Integer
Dim ParamValue as String

MaxNum = Reports.Parameters!ParameterName.Count - 1
ParamValue = Reports.Parameters!ParameterName.Value

etc...

Is this possible? I can't figure out how to do this. Using dynamic SQL you can do this very easily by concatenating string values together and then executing the string. Is there something similar to this in VB?

Help! Thanks

Below is an example for a custom code function that you can call in a textbox e.g. as

=Code.ShowParametersValues(Parameters!Country)

Public Function ShowParameterValues(ByVal parameter as Parameter) as String
Dim s as String
If parameter.IsMultiValue then
s = "Multivalue: "
For i as integer = 0 to parameter.Count-1
s = s + CStr(parameter.Value(i)) + " "
Next
Else
s = "Single value: " + CStr(parameter.Value)
End If
Return s
End Function

-- Robert

Dynamically number of parameters

I have 3 paramemters. How i can dynamically show/hide other two parameter based on the value as 1 or 2 input from the user from the first parameter? (for example: if the user enter 1, i will show the second parameter, if tyhe user enter 2, i will sho the thirs parameter on the report so the user can enter other value in these dynamical parameters?)Thats not possible. What is possible to still display them , but to clean them to show no value. That can be done using a query in the parameter definition.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Dynamically hide parameters

Is there a way to dynamically show/hide the parameter selection for a report? For example, when my report is viewed in Report Manager I want the parameters to be visible, but in our custom app with an embedded report I want them hidden so that the user can use our UI to enter them.
Is this possible?

The ReportViewer control has a ShowParameterPrompts property that can be set to False if you do not want to show the parameter prompts in your custom app. There is also a ShowToolBar property which you could use to hide the rest of the toolbar, so that all the user sees is the actual report.

If you need to leave some parameters visible and hide others, I am not sure, someone else will have to answer that.

Good luck,

Dave

Dynamically display/hide the parameter input

I have a handful of reports that are currently used by sales reps, and I'm trying to make them available to their regional VP's, and coporate users (executives and administrative staff that support Sales nationwide).

Currently, the reports take the UserID and resolve it to show the information that is only appropriate for that specific rep.

What I would like to do is have the parameter section at the top of the report be displayed for higher level users, so they could select an individual sales rep from a drop-down. (Ideally, the RVP's would only be able to select from reps in their region, but the corporate users would be able to select any rep.) The problem is, I don't want any of the sales reps to be able to select a rep other than themselves, for obvious reasons.

Is there a way to have the parameter section hidden/displayed dynamically, based on the UserID, so that users other than reps would have the ability to enter the desired rep name, but reps would not?

If you are using the .NET ReportView, it is just:

ReportViewer1.ShowParameterPrompts = False

|||I am using Report Manager.|||If you want any level of control, you are going to have to host the ReportViewer control in an ASP.NET page and customize the web page/report based on the user. There is not much you can do in ReportManager as far as fine control.|||

1. One way is to control this by writting a html page instead of report manager home page within which you will have a link to each of the report. Depending on who the user is you will change the path of the link (with or without parameters), meaning you will default all the parameters and pass them within the hidden link, but this wont work if you have more that 1 parameter and would want to hide only the user id parameter

2. Another option is to control this within the report. As your userid parameter list is being populated by a stored procedure, you will make the stored procedure accept a input parameter which is the userId of the person that is trying to run the report. Depending on who the user is the SP will return only the required users list.

Meaning

1. if XXX is passed and if XXX is RVP then it will return only the sales persons of XXX's region.

2. If YYY is passed and if YYY is sales person then it will return back only YYY

3. If CORP is passed and if CORP is a corporate user then the procedure will return all sales person's names

Hope this is clear and helps!!!

|||I think another way of achieving this is writting specific RDL code.|||

Thanks.

In the short term, this was the best solution. I wrote a sp that populates the drop down based on the NetLogin value.

So, the sales reps can see the drop down, but it only has their name in it.

|||You can also set the parameters visible in the URL using the &rsarameters=True/False.

i.e.

Code Snippet

http://SERVER/ReportServer/ReportPath&rs:Command=Render&rs:Parameters=False

HTH,
Jimmy

Dynamically creatng charts

Hi,
I am new using Reporting Services and I have a need to create charts
dynamically depending on information from a parameter. I can have
anywhere from 1 to about 30 graphs that will need to be dynamically
created. Can this be done using the report builder? Is there any
documentation on the Web somewhere?
Thanks!!!Hi,
Yes can be done. But the graphs are limited with some parameters. For more
info
check this link on SQL SERVER 2005 online books. at
"Working with Chart Data Regions"
I have worked with creating different graphs, let me know what type of
graphs and the data required accordingly, can explain how can be done.
Amarnath
"etienne.bourgeois@.gmail.com" wrote:
> Hi,
> I am new using Reporting Services and I have a need to create charts
> dynamically depending on information from a parameter. I can have
> anywhere from 1 to about 30 graphs that will need to be dynamically
> created. Can this be done using the report builder? Is there any
> documentation on the Web somewhere?
> Thanks!!!
>|||Etienne,
I suggest you also check out dundas charts from report services
2005...we finished up going with these as they contain many more
charting features than the 'free' reports with report services. And I
believe the 'free' ones are also dundas charts rolled into the product
still. (they were with the first release)...
We are dynamically creating charts based on parameters returning data
from stored procedures...
Peter
www.peternolan.com

dynamically changing default parameter?

I am creating SSRS reports on top of SSAS cubes. I want the default value of parameter to change dynamically based on the current year or it should select the last of the parameter values.

Can this be done?

here is how i'm setting my "from date" (datetime) param to be the first of the current month
=DateSerial(Year(Now()), Month(Now()),1)

for today, just use
=Today()

this is for a whole date, you can just pick the year part

Dynamically change report datasource based upon parameter.

I currently have one report that I would like to be able to use to report
off of stored procs on multiple servers. And am hoping someone can point me
in the right direction.
Example:
Report A will run off of storedProc1 which exists in every database.
However, the specific server and database to use will depend upon the user
currently logged in.
Currently I am trying to make use of the custom dataset extension (by Teo
Lachev) to report off of an XML string. Unfortunately, I am having fits
trying to get it to work and don't even know if this is the best way.
Any help would be appreciated.Various approaches for dynamic database connections in RS 2000 have been
discussed on this newsgroup:
* Use a custom data processing extension (as you currently do)
* Use the linked server functionality of SQL Server; please check this
thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=848bac6b-98a2-4de7-abfd-bf199a99b660&sloc=en-us
* If the databases are on the same server, use a dynamic query text (i.e.
="select * from " & Parameters!DatabaseName.Value & "..table")
* If you're just toggling between two or three databases, you can publish
the same report 3 times with 3 different names using 3 different data
sources and write a main report that shows/hides the correct subreport based
on whatever criteria you want.
Native support (expression-based connection strings) is available in RS
2005.
Hope this helps,
Robert
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tarik Peterson" <tarikp@.investigo.net> wrote in message
news:O4%23lPKA9EHA.960@.TK2MSFTNGP11.phx.gbl...
> I currently have one report that I would like to be able to use to report
> off of stored procs on multiple servers. And am hoping someone can point
me
> in the right direction.
> Example:
> Report A will run off of storedProc1 which exists in every database.
> However, the specific server and database to use will depend upon the user
> currently logged in.
> Currently I am trying to make use of the custom dataset extension (by Teo
> Lachev) to report off of an XML string. Unfortunately, I am having fits
> trying to get it to work and don't even know if this is the best way.
> Any help would be appreciated.
>

Dynamically assigning a value using the "Top N" Clause

I've developed a proc that takes an input parameter @.TopN Int. I want to
use it to dynamically pull the top n records from my DB as
such
Select TOP @.TopN UserID, Metric1, Metrics2...
Order By Metric1
I can only get this to work if I use an integer constant.
i.e. Select TOP 10 UserID
I Can't get it to work with the variable I'm passing in
Anyone know how to do this easily?
Thanks in advance
Message posted via http://www.webservertalk.comYou can use SET ROWCOUNT instead, but make sure that your ORDER BY
clause includes a key that is unique in the result set because SET
ROWCOUNT has no equivalent of the TOP WITH TIES option. Unless the
ORDER BY criteria is unique you may get unpredictable results.
David Portas
SQL Server MVP
--|||See:
http://groups.google.ca/groups?selm...FTNGP10.phx.gbl
Anith|||T Harris via webservertalk.com wrote:
> I've developed a proc that takes an input parameter @.TopN Int. I want
> to use it to dynamically pull the top n records from my DB as
> such
> Select TOP @.TopN UserID, Metric1, Metrics2...
> Order By Metric1
> I can only get this to work if I use an integer constant.
> i.e. Select TOP 10 UserID
> I Can't get it to work with the variable I'm passing in
> Anyone know how to do this easily?
> Thanks in advance
You'll need to wait for SQL 2005 for that.
David Gugick
Imceda Software
www.imceda.com|||Thanks, everyone for the responses.
Since the subset of data that I'm querying for is dynamic itself (i.e. I
don't know what the topN data set will be until after the order by is
applied) I can't directly apply the set rowcount. So the only way I can get
it to work it to query the dataset and then use the set rowcount against
the already ordered data set. This adds some overhead but it does work.
Tharrris
Message posted via http://www.webservertalk.com

Dynamically add update parameter to formview

I have a formview with name, email, and password. I bind all fields to sql except the password which is blank.

In my sqldatasource, I define parameters for name, email and id:

UpdateCommand

="UPDATE UserProfile SET Name = @.Name,Email = @.Email WHERE (ID = @.ID)">
<UpdateParameters>
<asp:ParameterName="Name"/>
<asp:ParameterName="Email"/>
<asp:ParameterName="ID"/>
</UpdateParameters>

In code I want to add a password parameter if there is value in the password field otherwise I don't want the password field updated. If I add define a password parameter like above then if a user left the password field blank then their new is blank. That's way I think adding it dynamically is the way. But I am having problems with the code to add the parameter in sqldatasource_updating event.

Protected

Sub SqlProfile_Updating(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlProfile.Updating
Dim passwordAs TextBox = FormView1.FindControl

Protected Sub SqlProfile_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlProfile.UpdatingDim passwordAs TextBox = FormView1.FindControl("tb_password1")If Not password.Text.ToString &"" =""ThenSqlProfile.UpdateParameters.Add(New Parameter("@.Password", TypeCode.String, password.Text.ToString))End IfEnd Sub

ThanksYou're close:

Protected Sub SqlProfile_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlProfile.UpdatingDim passwordAs TextBox = FormView1.FindControl("tb_password1")If Not String.IsNullOrEmpty(password.Text)Thene.Command.Parameters.Add(password.Text)End IfEnd Sub

|||

I think you should add the parameter manually, and check for a null / blank parameter in the sql statement. That way you just pass what ever you have in your form (blank password or populated password) and let the SQL statement figure it out for you. If not, then you have do add a new parameter to the updateparameters AND modify your UpdateCommand to have the additional line.

need help with the SQL?

|||

ecbruck:

You're close:

Protected Sub SqlProfile_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlProfile.UpdatingDim passwordAs TextBox = FormView1.FindControl("tb_password1")If Not String.IsNullOrEmpty(password.Text)Thene.Command.Parameters.Add(password.Text)End IfEnd Sub

if he does it that way, he will need to modify his command as well... adding "Password = @.Something"

|||

pixelsyndicate:

I think you should add the parameter manually, and check for a null / blank parameter in the sql statement.

I agree. I would personally let me Stored Procedure handle the case when the Password parameter was passed in as null.

|||

Thanks for the help.

This is what I have so far but still doesn't work.

Protected Sub SqlProfile_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlProfile.Updating Dim password As TextBox = FormView1.FindControl("tb_password1") If Not String.IsNullOrEmpty(password.Text) Then SqlProfile.UpdateParameters.Add("password", password.Text) SqlProfile.UpdateCommand ="UPDATE UserProfile SET FirstName = @.FirstName,Password=@.Password WHERE (UserName = @.UserName)" End If l_errormessage.Text = password.Text.ToString l_errormessage.Text += e.Command.CommandText.ToStringEnd Sub

|||

Thanks for the help.

This is what I have so far but still doesn't work.

Protected Sub SqlProfile_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlProfile.Updating Dim password As TextBox = FormView1.FindControl("tb_password1") If Not String.IsNullOrEmpty(password.Text) Then SqlProfile.UpdateParameters.Add("password", password.Text) SqlProfile.UpdateCommand ="UPDATE UserProfile SET FirstName = @.FirstName,Password=@.Password WHERE (UserName = @.UserName)" End If l_errormessage.Text = password.Text.ToString l_errormessage.Text += e.Command.CommandText.ToStringEnd Sub

 It updates the name field with no errors but the password doesn't get updated.

|||You need to be modifying the members of the SqlDataSourceCommandEventArgs class rather than the SqlDataSource class as I did in my previous example.|||

When I did your example:

Protected Sub SqlProfile_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlProfile.Updating Dim password As TextBox = FormView1.FindControl("tb_password1") If Not String.IsNullOrEmpty(password.Text) Then e.Command.Parameters.Add(password.Text) e.Command.CommandText ="UPDATE UserProfile SET FirstName = @.FirstName,Password=@.Password WHERE (UserName = @.UserName)" End If l_errormessage.Text = password.Text.ToString l_errormessage.Text += e.Command.CommandText.ToStringEnd Sub

I get this error:

The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.InvalidCastException: The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects.

Source Error:

Line 31: Dim password As TextBox = FormView1.FindControl("tb_password1")Line 32: If Not String.IsNullOrEmpty(password.Text) ThenLine 33: e.Command.Parameters.Add(password.Text)Line 34: e.Command.CommandText = "UPDATE UserProfile SET FirstName = @.FirstName,Password=@.Password WHERE (UserName = @.UserName)"Line 35: End If

|||

Thanks for all the help. This finally work with this code:

Protected Sub SqlProfile_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlProfile.UpdatingDim passwordAs TextBox = FormView1.FindControl("tb_password1")If Not String.IsNullOrEmpty(password.Text)Then Dim pAs SqlParameter =New SqlParameter("@.Password", SqlDbType.NVarChar) p.Value = password.Text e.Command.Parameters.Add(p) e.Command.CommandText ="UPDATE UserProfile SET FirstName = @.FirstName,Password=@.Password WHERE (UserName = @.UserName)"End If End Sub