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 insert a blank line in my report

Hello,

I my dataset is like following

sql:

select quarter, sum(amount) as amount from table1 group by quarter

then I get the dataset

field: quarter,amount

date: quarter1,100

quarter2,500

Is it possible to get my report from the dataset above.

my report:

quarter,amount

quarter1,100

quarter2,500

quarter3,0

quarter4,0

thanks!

This is possible if you have a time (or quarter) table in your database:

SELECT

q.quarter,

ISNULL(t1.amount,0) as amount

FROM quarters q

LEFT JOIN table1 t1

ON q.quarter = t1.quarter

|||

thank you for reply

I can't get the tabe quarters ...

|||

Use this SQL:

SELECT

q.quarter,

ISNULL(t1.amount,0) as amount

FROM quarters q LEFT JOIN

(SELECT 'quarter1' AS quarter UNION SELECT 'quarter2' AS quarter UNION SELECT 'quarter3' AS quarter UNION SELECT 'quarter4' AS quarter) AS qrt

ON quarters.quarter = qrt.quarter

This will work.

Please mark the post as answer.

Shyam

|||

In order to more easily manage date groupings (weeks, months, quarters, years, etc.) it is a VERY good idea to have a Calendar table in your database. Having a Calendar table makes tasks such as this one very simple.

Here is additional information about creating and using a Calendar table.

Datetime -Calendar Table
http://www.aspfaq.com/show.asp?id=2519

Is it possible to hide a field or fields based on User Groups?

RS 2000

I have a report that has a few sensitive fields and would only like to show them to a certain AD group.

Is this possible?
Hi,

sure this is possible through custom .NET code. Implement your own Active Driectory checking class and use that within reporting Services. Based on this class you can make the textbox Visible or Hidden.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Is it possible to get the max length of a TEXT field?

I have a text field and want to know if any of the text exceeds 10,000 characters
I can do a select max(len(rtrim(convert(varchar(8000)))) on the field but I'm not able to do for more than 8000 and you can't manipulate TEXT datay type.
Any ideas?
Thanks!SELECT DATALENGTH(Col1) FROM myTable99

Is it possible to display field B if field A is null and vice versa.

Hi all,
I'm new with Crystal Report. Just to ask is it possible for me to display field B if field A is null? And to display field A if field B is null. This is because, in one record, either field A or field B will have the value. Any idea?
Thanx in advance..;)Yes u can

write a formula

if isnull(fieldA) then
fieldB
else if isnull(FieldB) then
fieldA

hope it helps|||or

IIF(Isnull(fieldA),fieldB,fieldA)

Is it possible to chart values contained within a single field?

Just picking up SRS for the first time, so please excuse me if my question turns out to be dumb. If I have a Field that contains a csv (comma seperated variable) list of integers, is there any way to chart those values within a report?

I.e:

SELECT id, value FROM DataTable WHERE id = @.ValueId

id value

-- --

1 1,2,3,4,5,6,7,8,9,10,11

(1 row(s) affected)

I can do something like =split(Fields!Value.Value, ",") to get the individual data points out, but I'm not sure how to create a data set out of them so that I can point the chart to it. Changing the DB schema to accomidate the report is kinda out of the question at this point.

Thanks!

You could rewrite your query to split "Value" into numbers and group it by "ID", something like this:

1, ‘1,2,3,4’

2, ‘1,1,2’

1, 1

1,2

1,3

1,4

2,1

2,1

2,2

You can use split() but I don't think you can use it in the chart. It won't acceppt string[] as a value.

Is it better work with date/time in same field or separated?

Hi,
Is it better work with date/time in same field or separated as below:
varX = '12/12/2003' + '12:45' »» in same field in the table
OR
varX = '12/12/2003' + '12:45' »» in separated fields in the table
varY = '12:45'
Thanks,
VILMAR
BRAZIL
PRAIA GRANDE/SPUse a DATETIME column. This will make the most out of indexing, date range
queries, date comparisons, built in functions like datepart(), dateadd() and
datediff(), and easier legibility.
Since SQL Server doesn't have separate DATE and TIME datatypes, moving them
to separate columns would either mean (a) having useless date information in
the "time" column and useless time information in the "date" column, or (b)
converting the columns to a different datatype. For more information, see
http://www.aspfaq.com/2206
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Vilmar Brazão de Oliveira" <suporte@.hitecnet.com.br> wrote in message
news:#60t$RZxDHA.1704@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is it better work with date/time in same field or separated as below:
> varX = '12/12/2003' + '12:45' »» in same field in the table
> OR
> varX = '12/12/2003' + '12:45' »» in separated fields in the table
> varY = '12:45'
> Thanks,
> VILMAR
> BRAZIL
> PRAIA GRANDE/SP
>

Is it a SQL's bug?

There is not a field named as 'CRSID' in the table TMatch but the SQL Server
would return all the records in table tcrsmgr:
SELECT *
FROM dbo.TCRSMGR
WHERE (CRSID IN
(SELECT CRSID
FROM TMatch
WHERE tDATE = dbo.fDateOf('2005-7-20')))This is correct behavior because the TCRSMGR.CRSID is used in the subquery.
To avoid ambiguity, qualify column names in the subquery with the desired
table name or alias like the example below. In this case, you'll get an
error because the TMatch.CRSID column doesn't exist.
SELECT *
FROM dbo.TCRSMGR
WHERE (CRSID IN
(SELECT TMatch.CRSID
FROM TMatch
WHERE TMatch.tDATE = dbo.fDateOf('2005-7-20')))
Hope this helps.
Dan Guzman
SQL Server MVP
"Half Nitto" <mails2me@.invalidemail.com> wrote in message
news:ud6vP6XlFHA.2852@.TK2MSFTNGP14.phx.gbl...
> There is not a field named as 'CRSID' in the table TMatch but the SQL
> Server would return all the records in table tcrsmgr:
> SELECT *
> FROM dbo.TCRSMGR
> WHERE (CRSID IN
> (SELECT CRSID
> FROM TMatch
> WHERE tDATE = dbo.fDateOf('2005-7-20')))
>|||This is expected behavior. The inner reference to CRSID does
not include a table alias or table name. As a result, it is resolved
as TMatch.CRSID if that column exists, and if not, to
TCRSMGR.CRSID, if that column exists (which it does -
if it did not, you would get an error).
You now have a correlated subquery, and for each row of
TCRSMGR, that correlated subquery is
SELECT TCRSMGR.CRSID FROM TMatch
WHERE tDATE = dbo.fDateOf('2005-7-20')
So long as CRSID has at least one row for which
tDATE = dbo.fDateOf('2005-7-20'), then the WHERE
clause of the entire query is true, and so all rows of
TCRSMGR will be returned.
Outer references must always be valid in subqueries,
or it would be impossible to write a correlated subquery.
For example, no one thinks it's a bug that this works (to
select the biggest order for each employee)
select OrderID, OrderDate, OrderTotal
from Orders as O1
where OrderTotal = (
select max(OrderTotal)
from Orders as O2
where O2.EmployeeID = O1.EmployeeID
)
The reference to O1.EmployeeID is perfectly valid.
Here, the O1 alias is required to avoid ambiguity, but
aliases can be omitted when there is no chance of
ambiguity, and unfortunately in your case, omitting the
alias caused a programming error to go unnoticed.
Here's another example that might not seem so surprising
if not useful:
select * from T
where thisColumn = (
select T.thisColumn
)
You would expect this to return all rows of T with
non-null thisColumn values. Though there is not
even a table mentioned in the subquery, the reference
to T.thisColumn is valid and correlates with the rows
of the outer query. Since thisColumn would not be
ambiguous here, the same query can be written as
select * from T
where thisColumn = (
select thisColumn
)
or, if table X has at least one row,
select * from T
where thisColumn = (
select thisColumn from X
)
The moral of the story? In queries that refer to more
than one table, if not always, qualify columns with the
table you think they come from.
Had you done this here, and written
SELECT *
FROM dbo.TCRSMGR
WHERE (dbo.TCRSMGR.CRSID IN
(SELECT TMatch.CRSID
FROM TMatch
WHERE TMatch.tDATE = dbo.fDateOf('2005-7-20')))
you would have caught the programming error. Most all programming
languages are like this, in allowing inner declarations to override outer
ones, while allowing all outer declarations to be visible within sub-blocks,
if there is no shadowing inner declaration.
int i, j;
...
{
int i, k;
// you can refer to i, j and k here. j refers to the variables declared
// in the outer block, and i and k refers to the variable declared in
// the inner block.
Steve Kass
Drew University
Half Nitto wrote:

>There is not a field named as 'CRSID' in the table TMatch but the SQL Serve
r
>would return all the records in table tcrsmgr:
>SELECT *
>FROM dbo.TCRSMGR
>WHERE (CRSID IN
> (SELECT CRSID
> FROM TMatch
> WHERE tDATE = dbo.fDateOf('2005-7-20')))
>
>|||Have a look at
http://toponewithties.blogspot.com/...es_archive.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Half Nitto" <mails2me@.invalidemail.com> wrote in message
news:ud6vP6XlFHA.2852@.TK2MSFTNGP14.phx.gbl...
> There is not a field named as 'CRSID' in the table TMatch but the SQL
> Server would return all the records in table tcrsmgr:
> SELECT *
> FROM dbo.TCRSMGR
> WHERE (CRSID IN
> (SELECT CRSID
> FROM TMatch
> WHERE tDATE = dbo.fDateOf('2005-7-20')))
>

Is Count(*) faster than Count(Field)?

So I know not to do a Select * unless I need all the fields, but what about
Count(*)? Is that somehow (internally) slower than Count(FieldName)?First, is the column in question allow NULLs, then the two options doesn't e
ven do this same thing.
COUNT(*) counts all rows, where COUNT(colname) count rows where value for th
e column isn't NULL.
Assuming the column is defined as NOT NULL, then there is a possibly perform
ance advantage of using
COUNT(*), as SQL server can use a non-clustered index on *any* column in the
table to count the
rows. Hopefully, SQL Server will pick a non-clustered index on the smallest
column, where you fit
most rows on a data page, meaning the least amount of I/O. If you specify a
certain column, then SQL
Server *might* be smart enough to realize in simple cases that using any ind
ex is the same as using
an index over that particular column, but I wouldn't trust it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"D. Patrick" <replywithinthegroup@.thenotreal.com> wrote in message
news:5_1ag.2874$9W5.1637@.tornado.socal.rr.com...
> So I know not to do a Select * unless I need all the fields, but what abou
t Count(*)? Is that
> somehow (internally) slower than Count(FieldName)?
>|||I saw someone suggested using count(1) and said that is the fastest to count
rows. Just like gurus to confirm.
James
"D. Patrick" wrote:

> So I know not to do a Select * unless I need all the fields, but what abou
t
> Count(*)? Is that somehow (internally) slower than Count(FieldName)?
>
>|||You can theoretically find a *slightly* faster initial parse time for that q
uery compared COUNT(*)
(at least this holds for EXISTS, I take it the same is for COUNT()). But the
execution plan should
be the same, and we are talking about a few clock-cycles here, nothing you w
orry about in an RDBMS.
It is unlikely that you will be able to even measure such a difference with
any significance (unless
you have some 1000 columns in the table, and I still don't think you'll see
a significant
difference). And, remember this is parse time for the query, not something t
hat happens for every
row, or even every execution (assuming your plans are re-used).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"James Ma" <JamesMa@.discussions.microsoft.com> wrote in message
news:0850C483-D5D2-4614-8C74-6770B5162836@.microsoft.com...[vbcol=seagreen]
>I saw someone suggested using count(1) and said that is the fastest to coun
t
> rows. Just like gurus to confirm.
> James
> "D. Patrick" wrote:
>|||It has been said about Oracle that this makes a difference. I don't know
about Oracle, but on SQL Server it makes zero difference. "SELECT
COUNT(1) FROM ..." is no faster than "SELECT COUNT(*) FROM ..." (it is
equally fast).
Gert-Jan
James Ma wrote:[vbcol=seagreen]
> I saw someone suggested using count(1) and said that is the fastest to cou
nt
> rows. Just like gurus to confirm.
> James
> "D. Patrick" wrote:
>|||why dont you use the report on the query to see the time expend in such
query?
Bruno Alexandre
(a Portuguese in Denmark)
"D. Patrick" <replywithinthegroup@.thenotreal.com> escreveu na mensagem
news:5_1ag.2874$9W5.1637@.tornado.socal.rr.com...
> So I know not to do a Select * unless I need all the fields, but what
> about Count(*)? Is that somehow (internally) slower than
> Count(FieldName)?
>

Is Count(*) faster than Count(Field)?

So I know not to do a Select * unless I need all the fields, but what about
Count(*)? Is that somehow (internally) slower than Count(FieldName)?First, is the column in question allow NULLs, then the two options doesn't even do this same thing.
COUNT(*) counts all rows, where COUNT(colname) count rows where value for the column isn't NULL.
Assuming the column is defined as NOT NULL, then there is a possibly performance advantage of using
COUNT(*), as SQL server can use a non-clustered index on *any* column in the table to count the
rows. Hopefully, SQL Server will pick a non-clustered index on the smallest column, where you fit
most rows on a data page, meaning the least amount of I/O. If you specify a certain column, then SQL
Server *might* be smart enough to realize in simple cases that using any index is the same as using
an index over that particular column, but I wouldn't trust it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"D. Patrick" <replywithinthegroup@.thenotreal.com> wrote in message
news:5_1ag.2874$9W5.1637@.tornado.socal.rr.com...
> So I know not to do a Select * unless I need all the fields, but what about Count(*)? Is that
> somehow (internally) slower than Count(FieldName)?
>|||I saw someone suggested using count(1) and said that is the fastest to count
rows. Just like gurus to confirm.
James
"D. Patrick" wrote:
> So I know not to do a Select * unless I need all the fields, but what about
> Count(*)? Is that somehow (internally) slower than Count(FieldName)?
>
>|||You can theoretically find a *slightly* faster initial parse time for that query compared COUNT(*)
(at least this holds for EXISTS, I take it the same is for COUNT()). But the execution plan should
be the same, and we are talking about a few clock-cycles here, nothing you worry about in an RDBMS.
It is unlikely that you will be able to even measure such a difference with any significance (unless
you have some 1000 columns in the table, and I still don't think you'll see a significant
difference). And, remember this is parse time for the query, not something that happens for every
row, or even every execution (assuming your plans are re-used).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"James Ma" <JamesMa@.discussions.microsoft.com> wrote in message
news:0850C483-D5D2-4614-8C74-6770B5162836@.microsoft.com...
>I saw someone suggested using count(1) and said that is the fastest to count
> rows. Just like gurus to confirm.
> James
> "D. Patrick" wrote:
>> So I know not to do a Select * unless I need all the fields, but what about
>> Count(*)? Is that somehow (internally) slower than Count(FieldName)?
>>|||It has been said about Oracle that this makes a difference. I don't know
about Oracle, but on SQL Server it makes zero difference. "SELECT
COUNT(1) FROM ..." is no faster than "SELECT COUNT(*) FROM ..." (it is
equally fast).
Gert-Jan
James Ma wrote:
> I saw someone suggested using count(1) and said that is the fastest to count
> rows. Just like gurus to confirm.
> James
> "D. Patrick" wrote:
> > So I know not to do a Select * unless I need all the fields, but what about
> > Count(*)? Is that somehow (internally) slower than Count(FieldName)?
> >
> >
> >|||why dont you use the report on the query to see the time expend in such
query?
--
Bruno Alexandre
(a Portuguese in Denmark)
"D. Patrick" <replywithinthegroup@.thenotreal.com> escreveu na mensagem
news:5_1ag.2874$9W5.1637@.tornado.socal.rr.com...
> So I know not to do a Select * unless I need all the fields, but what
> about Count(*)? Is that somehow (internally) slower than
> Count(FieldName)?
>