Is it possible to output a Field Value based on another field value?

I would like the data value of a text field in a table to based on the
value of another field (also used as a parameter). However, if I try
switch or iif, I get an 'expression expected' error.
For example, if car type is 'used' then output the 'salvage value'
data field. If car type is 'new' output 'retail value' data field.
Any suggestions?You can do this two ways. You can put in an expression or you can have it
done in SQL. iif should have worked. From BOL (search on iif).
=Iif(Fields!PctComplete.Value >= .8, "Green", Iif(Fields!PctComplete.Value
>= .5, "Amber", "Red"))a.. The following expression also returns one of
three values based on the value of PctComplete, but uses the Switch function
instead, which returns the value associated with the first expression in a
series that evaluates to true:
=Switch(Fields!PctComplete.Value >= .8, "Green", Fields!PctComplete.Value >=.5, "Amber", Fields!PctComplete.Value < .5, "Red")In the example it is
setting the color but it could just be a value in the table. If it said it
expected an expression my guess is that you did not have the equal sign.--
Bruce Loehle-Conger MVP SQL Server Reporting Services"ChrisL"
<chrispycrunch@.gmail.com> wrote in message
news:9e416a3e.0504120558.5cbdc793@.posting.google.com...
> I would like the data value of a text field in a table to based on the
> value of another field (also used as a parameter). However, if I try
> switch or iif, I get an 'expression expected' error.
> For example, if car type is 'used' then output the 'salvage value'
> data field. If car type is 'new' output 'retail value' data field.
> Any suggestions?|||Sorry for the crummy formatting. I copied and pasted in from Books On-Line.
This should look better.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eehnli2PFHA.648@.TK2MSFTNGP14.phx.gbl...
You can do this two ways. You can put in an expression or you can have it
done in SQL. iif should have worked. From BOL (search on iif).
=Iif(Fields!PctComplete.Value >= .8, "Green", Iif(Fields!PctComplete.Value
= .5, "Amber", "Red"))
The following expression also returns one of three values based on the
value of PctComplete, but uses the Switch function instead, which returns
the value associated with the first expression in a series that evaluates to
true:
=Switch(Fields!PctComplete.Value >= .8, "Green", Fields!PctComplete.Value
>=> .5, "Amber", Fields!PctComplete.Value < .5, "Red")
In the example it is setting the color but it could just be a value in the
table. If it said it expected an expression my guess is that you did not
have the equal sign.
Bruce Loehle-Conger MVP SQL Server Reporting Services
>>"ChrisL" <chrispycrunch@.gmail.com> wrote in message
> news:9e416a3e.0504120558.5cbdc793@.posting.google.com...
> > I would like the data value of a text field in a table to based on the
> > value of another field (also used as a parameter). However, if I try
> > switch or iif, I get an 'expression expected' error.
> >
> > For example, if car type is 'used' then output the 'salvage value'
> > data field. If car type is 'new' output 'retail value' data field.
> >
> > Any suggestions?
>

Is it possible to have sqlcmd connect to local sqlexpress without a parameter?

(SQL Server Express 2005)

sqlcmd will connect to the local SQLExpress server just fine if I give the -S parameter:
SQLCMD -S .\SQLExpress

but is there any way to have it default to that so I can just say:
SQLCMD
?

Thanks,

dwh
You can do something like:
1) Create an Alias thorough Computer Management | SQL Server Configuration Manager | SQL Native Client Configuration
Alias Name: foo
Protocol: Named Pipes
Server: .\SQLExpress

2) Execute SQLCMD using the alias, like SQLCMD /S foo

Peter|||Umm... thanks, but that doesn't really help.

If I still need to say "sqlcmd /s foo", how is that any better than having to say "sqlcmd /s .\SQLExpress"?

Apparently on some machines here at work people are able to just say "sqlcmd" and it will default to the local SQL (Express) Server.

No?

Thanks,

dwh
|||They probably have a default instance installed. If /S is not specified, SQLCMD will try to connect to the dedfault instance. When installing Express, choose Advanced Options and then select "Default Instance". By default the Express setup installs Express and as a named instance called SQLEXPRESS.

To check whether they have a default or named istance, execute sp_helpserver against the server.

Peter

Is it possible to dynamically populate a parameter list with values based on another parameter v

Is it possible to fill a parameter list with values based on another parameter value?

Here's what I have so far (which hasn't worked)...

I'd like to generate a report listing information for a student. The report viewer would first select a school from the first drop-down menu, and then the second drop-down menu would populate with the list of students at that school.

I have a dataset that calls a sp which returns a list of schools (SchoolID and SchoolName fields from the database table).

I have another dataset that calls a sp (with SchoolID as the parameter) which returns a list of students for that school.

Both datasets return the appropriate data when tested individually, but when I set up the Report Parameters and build the report, these errors come up...

The value expression for the query parameter '@.SchoolID' refers to a non-existing report parameter 'SchoolID'.

The report parameter 'Student' has a DefaultValue or a ValidValue that depends on the report parameter "SchoolID". Forward dependencies are not valid.

...Is it possible for the reoprt to generate a list of available parameter values based on the value selected for another parameter?

Any help you can give me would be great!! Thank you
-Ethan

I ran into that problem, and it was simply due to the fact that my parameters were in the wrong order. Try this : go into the parameters dialog and make sure the list-of-schools parameter is above the list-of-students parameter.

Hope that helps.|||If you look at the sample reports included with RS, you should see one called 'Product Line Sales'. It demonstrates what you are trying to do.|||

hallo,

i have the same problem, can you give me a example.

thx

Is it possible to dynamically populate a parameter list with values based on another parameter v

Is it possible to fill a parameter list with values based on another parameter value?

Here's what I have so far (which hasn't worked)...

I'd like to generate a report listing information for a student. The report viewer would first select a school from the first drop-down menu, and then the second drop-down menu would populate with the list of students at that school.

I have a dataset that calls a sp which returns a list of schools (SchoolID and SchoolName fields from the database table).

I have another dataset that calls a sp (with SchoolID as the parameter) which returns a list of students for that school.

Both datasets return the appropriate data when tested individually, but when I set up the Report Parameters and build the report, these errors come up...

The value expression for the query parameter '@.SchoolID' refers to a non-existing report parameter 'SchoolID'.

The report parameter 'Student' has a DefaultValue or a ValidValue that depends on the report parameter "SchoolID". Forward dependencies are not valid.

...Is it possible for the reoprt to generate a list of available parameter values based on the value selected for another parameter?

Any help you can give me would be great!! Thank you
-Ethan

I ran into that problem, and it was simply due to the fact that my parameters were in the wrong order. Try this : go into the parameters dialog and make sure the list-of-schools parameter is above the list-of-students parameter.

Hope that helps.

|||If you look at the sample reports included with RS, you should see one called 'Product Line Sales'. It demonstrates what you are trying to do.|||

hallo,

i have the same problem, can you give me a example.

thx

Is it possible to display the selected label (not value) of a query based Parameter in rep

Hi,
If I have a query based parameter which has labels and values, for example,
parameter: Department, label: DepartmentName, Value: DepartmentID, I am
using the parameter value (e.g. DepartmentID) in the dataset filter, but I
want to display the parameter label in the report page footer. Is there an
easy way to do it? Right now I can use only Parameters!Department.Value
which displays the chosen DepartmentID.
Thanks.You can access the report parameter label by using the Label property:
=Parameters!Department.Label
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wendy H" <WendyH@.tequilasoftware.com> wrote in message
news:%23sh1GK$eEHA.636@.TK2MSFTNGP12.phx.gbl...
> Hi,
> If I have a query based parameter which has labels and values, for
example,
> parameter: Department, label: DepartmentName, Value: DepartmentID, I am
> using the parameter value (e.g. DepartmentID) in the dataset filter, but I
> want to display the parameter label in the report page footer. Is there an
> easy way to do it? Right now I can use only Parameters!Department.Value
> which displays the chosen DepartmentID.
> Thanks.
>
>

Is it possible to custom format the parameter dropdownlist?

I have a reporting services 2000 report that has only one string report
parameter with a prompt 'Select a Project'. The project list contains
project names that are so long that they take up about 95% of the page
width and the View report button becomes a partially visible button
with a horizontal scroll bar to scroll further to the right to view the
entire page.
My users hate to have to scroll to the right. They are requesting a
fixed width dropdownlist with fully visible 'View report' button.
We want to make our DDL pretty narrow so that it only shows the first n
characters of the project name string when the DDL is not dropped down.
When the user
drops the list down we want to resize it wide enough to show the entire
string.
We could also assign a tool tip to the DDL if we could supply a unique
tip
to each row in the DDL?
Any ideas? Does SSRS 2005 let the designer specify the parameter
control width?
Many thanks in advance.I've found that the parameter panel is not at all configurable and have
been creating a front-end web page for each report giving me total
control over presentation. Then I hide the parameter panel via a
parameter on the URL. It's my understanding RS 2005 has more control
over the parameter panel.|||Many thanks, Kent.

Is it Possible To Create an Image from Parameter Data?

Thanks in advance.On Feb 27, 1:09 pm, "Daedius" <richard.An...@.gmail.com> wrote:
> Thanks in advance.
Please clarify.
Enrique Martinez
Sr. SQL Server Developer

Is it possible to create a custom SQL session function/variable

To avoid having to pass this user specific ID via a parameter in the stored
procedure, is it possible to create your own function like (like
suser_sid()) or variable (like @.@.SPID) ?
This value would be passed from a Web Service (asp.net 2.0) in the
connection string or something and could be referred to anywhere in the SQL
code (SQL 2005).
Has anyone got a better solution than a SP parameter ?Take a look at CONTEXT_INFO in the Books Online. This allows you to store
up to 128 bytes of binary info that can be used anywhere in the session.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"GMG" <gmgsoftware@.nospam.net.au> wrote in message
news:uI04I9duHHA.4440@.TK2MSFTNGP06.phx.gbl...
> To avoid having to pass this user specific ID via a parameter in the
> stored
> procedure, is it possible to create your own function like (like
> suser_sid()) or variable (like @.@.SPID) ?
> This value would be passed from a Web Service (asp.net 2.0) in the
> connection string or something and could be referred to anywhere in the
> SQL
> code (SQL 2005).
> Has anyone got a better solution than a SP parameter ?
>

Is it possible to create a custom SQL session function/variable

To avoid having to pass this user specific ID via a parameter in the stored
procedure, is it possible to create your own function like (like
suser_sid()) or variable (like @.@.SPID) ?
This value would be passed from a Web Service (asp.net 2.0) in the
connection string or something and could be referred to anywhere in the SQL
code (SQL 2005).
Has anyone got a better solution than a SP parameter ?Take a look at CONTEXT_INFO in the Books Online. This allows you to store
up to 128 bytes of binary info that can be used anywhere in the session.
Hope this helps.
Dan Guzman
SQL Server MVP
"GMG" <gmgsoftware@.nospam.net.au> wrote in message
news:uI04I9duHHA.4440@.TK2MSFTNGP06.phx.gbl...
> To avoid having to pass this user specific ID via a parameter in the
> stored
> procedure, is it possible to create your own function like (like
> suser_sid()) or variable (like @.@.SPID) ?
> This value would be passed from a Web Service (asp.net 2.0) in the
> connection string or something and could be referred to anywhere in the
> SQL
> code (SQL 2005).
> Has anyone got a better solution than a SP parameter ?
>sql

Is it possible to create a custom SQL session function/variable

To avoid having to pass this user specific ID via a parameter in the stored
procedure, is it possible to create your own function like (like
suser_sid()) or variable (like @.@.SPID) ?
This value would be passed from a Web Service (asp.net 2.0) in the
connection string or something and could be referred to anywhere in the SQL
code (SQL 2005).
Has anyone got a better solution than a SP parameter ?Asked and answered in .programming. Please refrain from multiposting.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"GMG" <gmgsoftware@.nospam.net.au> wrote in message
news:%23cNc2ceuHHA.3376@.TK2MSFTNGP04.phx.gbl...
> To avoid having to pass this user specific ID via a parameter in the
> stored
> procedure, is it possible to create your own function like (like
> suser_sid()) or variable (like @.@.SPID) ?
> This value would be passed from a Web Service (asp.net 2.0) in the
> connection string or something and could be referred to anywhere in the
> SQL
> code (SQL 2005).
> Has anyone got a better solution than a SP parameter ?
>|||Take a look at SET CONTEXT_INFO in the Books Online. That allows you to set
a value for the connection that is accessible anywhere in the session with
the CONTEXT_INFO function. Since the value is binary, you'll need to
convert as needed.
Hope this helps.
Dan Guzman
SQL Server MVP
"GMG" <gmgsoftware@.nospam.net.au> wrote in message
news:%23cNc2ceuHHA.3376@.TK2MSFTNGP04.phx.gbl...
> To avoid having to pass this user specific ID via a parameter in the
> stored
> procedure, is it possible to create your own function like (like
> suser_sid()) or variable (like @.@.SPID) ?
> This value would be passed from a Web Service (asp.net 2.0) in the
> connection string or something and could be referred to anywhere in the
> SQL
> code (SQL 2005).
> Has anyone got a better solution than a SP parameter ?
>

Is it possible to capture an OUT type parameter from a PL/SQL stored procedure?

When a stored PL/SQL procedure in my Oracle database is called from ASP.NET, is it possible to retrieve the OUT parameter from the PL/SQL procedure? For example, if I have a simple procedure as below to insert a row into the database. Ideally I would like it to return back the parameter namedNewId to my ASP.NET server. I'd like to capture this in the VB.NET code.

1createorreplace procedure WriteName(FirstNamein varchar2, LastNamein varchar2,NewIdout pls_integer)is
2
3NameId pls_integer;
4
5begin
6
7 select name_seq.nextvalinto NameIdfrom dual;
8
9insert into all_names(id, first_name, last_name)
10values(NameId, FirstName, LastName);
11
12NewId := NameId;
13
14end WriteName;

1<asp:SqlDataSource
2 ID="SqlDataSaveName"
3 runat="server"
4 ConnectionString="<%$ ConnectionStrings:ConnectionString%>"
5 ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName%>"
6 SelectCommand="WRITENAME"
7 SelectCommandType="StoredProcedure">
8 <SelectParameters>
9 <asp:ControlParameter ControlID="TextBoxFirstName" Name="FIRSTNAME" PropertyName="Text" Type="String" />
10 <asp:ControlParameter ControlID="TextBoxLastName" Name="LASTNAME" PropertyName="text" Type="String" />
11 </SelectParameters>
12</asp:SqlDataSource>

This is then called in the VB.NET code as below. It is in this section that I would like to capture the PL/SQL OUT parameterNewId returned from Oracle. 

1 SqlDataSaveName.Select(DataSourceSelectArguments.Empty)

If anybody can help me with the code I need to add to the VB.NET section to capture and then use the returned OUT parameter then I'd be very grateful.

This Select looks an awfully lot like an Insert, doesn't it? :)

My most honest suggestion is that you skip the SqlDataSource altogether for this kind of task. The SqlDataSource has some merit when it comes to binding databound controls to a datasource, but in this case it's a just simple insert from two textboxes.

In other words, use a simple OracleCommand, with a defined output parameter.

If you really want to use the SqlDataSource, then you should first use its Insert method, and corresponding InsertCommand etc. Next, define

<asp:Parameter Name="NewId" Direction="ReturnValue" />

and subscribe to the Inserted event of the SqlDataSource, where you'll be able to retrieve the value from the SqlDataSourceStatusEventArgs.

|||

Hi, thanks for replying. Yes, the example is overly simplified and an INSERT would have been better - I just used it to highlight what I was trying to achieve and that was to accept a returned value from the PL/SQL proc back in ASP.NET. However I think that you have answered my questions to thank you for showing me the Direction="ReturnValue" which is the bit I was missing.

Is it possible pass to procedure open cursor from other procedure

No. A procedure can not have a input parameter of type cursor. You can call
a
second procedure that has an output parameter of type cursor.
Example:
use northwind
go
create procedure proc1
@.c cursor varying output
as
set nocount on
set @.c = cursor local fast_forward for select orderid from dbo.orders
open @.c
go
create procedure proc2
as
set nocount on
declare @.c cursor
declare @.i int
exec proc1 @.c output
if cursor_status('variable', '@.c') = 1
begin
while 1 = 1
begin
fetch next from @.c into @.i
if @.@.error != 0 or @.@.fetch_status != 0 break
print @.i
end
close @.c
deallocate @.c
end
go
exec proc2
go
drop procedure proc2, proc1
go
Try to find a set-based solution first, leave cursors as the last tool in
your pocket.
AMB
"JB via webservertalk.com" wrote:

> Is it possible pass to procedure open cursor from other procedure ?
> Any suggestions will be appreciated
> --
> Message posted via http://www.webservertalk.com
>thank you.
What divantages of using cursor rather then temp table?
Message posted via http://www.webservertalk.com