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 join the fields value together?

I am new in crystal report. I have a dataset which like this:

FieldA
---
name1
name2
name3

Now, i want to join them together like this : "name1, name2, name3". I try to use the function join(list, delimiter) but fail. It seem that i should get all the data in place them in a array.
How can I do this? Thanks!!!!!check if this is possible

{database.name1}+{database.name2}+{database.name3}|||Thanks for your reply

However, in my case, all the data which is inside the same field.
For example:

Table1.field1
------
Record 1) A
Record 2) B
Record 3) C

I want to join them to "A, B, C" and display in the report
Anyone know how to do? Thanks!!!|||What groups these 3 records together, so that you know they are related? e.g. so that "A, B, C" comes out on a separate line to "D, E, F".
Group on this, and create 3 formulas:

@.init (in the suppressed group header)
whileprintingrecords;
stringvar group_list := "";

@.add (in the suppressed details)
whileprintingrecords;
stringvar group_list;
if group_list <> "" then group_list := group_list + ", ";
group_list := group_list + {table.field};

@.display (in the group footer)
whileprintingrecords;
stringvar group_list

Is it possible to have SQL send an email out based on a value in a table?

Hi all,

I'm working on a trouble ticket application and would like to add one more bit of functionality to the system:

When a new trouble ticket is created via the application front end (ASP.NET web application) the system sends an email to an appropiate party indicating that a new ticekt has been issued and needs to be responded to. The user must then log into the system to see the new ticket. The ticket is of course stored in the database (SQL Sever 2000). My question is, whether it is possible to tell SQL server to automatically send emails on a set time interval as long as their are tickets in the database that have a status of new?

ThanksThere is a SQL Server extended stored procedure, xp_Sendmail, that will allow your SQL code to send mail (presuming the SQL Mail agent is configured and running).|||You can also use CDONTS from a stored procedure if you don't have SQL Mail installed - http://support.microsoft.com/view/tn.asp?kb=312839|||Thanks all. I have a procedure that uses CDONTS and it works fine. now I gotta figure out hot to schedule it to run based on a field in a database. But hopefully that should be easy.

Wendell|||Hi

This KB is not anymore available online. Can you provide the details for this?

Cesar|||Here'sanother article on the same topic.

Regards,

Xander

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 Concatenate a Text Column of a Table

Hi All,
I have a table in which one of the columns is Text.
I need to concatenate a hard coded text with the value of the text column
for each and every row of the table. Is this possible.
Sevugan.CI don't understand why you need to concatenate anything, *especially* the
same value for *every row.*
Can't the consuming application do that?
If you aren't storing > 8000 characters, then you could say:
DECLARE @.constant VARCHAR(12);
SET @.constant = 'some prefix';
SELECT @.constant + CONVERT(VARCHAR(7988), textColumn) FROM table;
"Sevugan" <Sevugan@.discussions.microsoft.com> wrote in message
news:A1A3FE64-4D11-4217-97AC-A8A6A380F355@.microsoft.com...
> Hi All,
> I have a table in which one of the columns is Text.
> I need to concatenate a hard coded text with the value of the text column
> for each and every row of the table. Is this possible.
>
> --
> Sevugan.C|||Hi
Thanks for your reply.
I am storing more than 8000 chars in the text column. Then, how this could
be resolved.
Sevugan.C
"Aaron Bertrand [SQL Server MVP]" wrote:

> I don't understand why you need to concatenate anything, *especially* the
> same value for *every row.*
> Can't the consuming application do that?
> If you aren't storing > 8000 characters, then you could say:
> DECLARE @.constant VARCHAR(12);
> SET @.constant = 'some prefix';
> SELECT @.constant + CONVERT(VARCHAR(7988), textColumn) FROM table;
>
>
> "Sevugan" <Sevugan@.discussions.microsoft.com> wrote in message
> news:A1A3FE64-4D11-4217-97AC-A8A6A380F355@.microsoft.com...
>
>|||> I am storing more than 8000 chars in the text column. Then, how this could
> be resolved.
Some options:
(a) have the consuming application perform the concatenation
(b) move to SQL Server 2005, where you can use VARCHAR(MAX)
(c) store your redundant prefix with the data (you can use UPDATETEXT for
that)|||Hi
I am using the third option suggested by You. But, I am getting the
following error.
What could be the reason. How it should be resolved.
Server: Msg 7123, Level 16, State 1, Procedure sp_SendStockRebalanceMails,
Line 100
Invalid text, ntext, or image pointer value
0x0100010000000000331C2F0C00000000.
The statement has been terminated.
--
Sevugan.C
"Aaron Bertrand [SQL Server MVP]" wrote:

> Some options:
> (a) have the consuming application perform the concatenation
> (b) move to SQL Server 2005, where you can use VARCHAR(MAX)
> (c) store your redundant prefix with the data (you can use UPDATETEXT for
> that)
>
>|||> I am using the third option suggested by You. But, I am getting the
> following error.
> What could be the reason. How it should be resolved.
Who knows? You forgot to include DDL, sample data, and the code in your
procedure.
http://www.aspfaq.com/5006|||> I am using the third option suggested by You. But, I am getting the
> following error.
> What could be the reason. How it should be resolved.
Who knows? You forgot to include DDL, sample data, and the code in your
procedure.
http://www.aspfaq.com/5006