Conditionally referring to fields

Hi,
I am using RS 2000. In a report, I have database field whose name keeps changing everytime based on some condition. Say, a stored proc returns a field Aug2005. The name of this field becomes Oct2005 on some other condition. How can I use this field in the layout (to drag n drop). By what name/alias could I refer to this field. I read that in RS 2005 there is an option like Fields.Items(index).Value to access the field conditionally but I tried it in RS 2000 to no avail. Please suggest a solution.
Thanks,
Biju.

When you use the Fields.Items syntax, what you are varying is the field name, not the underlying database query column name (called DataField in RDL). All columns returned by the query must be known and mapped in the RDL.

If you have a query that returns different columns, you need to add them both to the query and then conditionally switch between them.

Conditionally referring to fields

Hi,
I am using RS 2000. In a report, I have database field whose name keeps changing everytime based on some condition. Say, a stored proc returns a field Aug2005. The name of this field becomes Oct2005 on some other condition. How can I use this field in the layout (to drag n drop). By what name/alias could I refer to this field. I read that in RS 2005 there is an option like Fields.Items(index).Value to access the field conditionally but I tried it in RS 2000 to no avail. Please suggest a solution.
Thanks,
Biju.

When you use the Fields.Items syntax, what you are varying is the field name, not the underlying database query column name (called DataField in RDL). All columns returned by the query must be known and mapped in the RDL.

If you have a query that returns different columns, you need to add them both to the query and then conditionally switch between them.

sqlsql

Conditionally Formating Subtotal Output

I created a subtotal for my column group by right-clicking on the group and selecting subtotal. I would like to add the condition to the subtotal, that if the value is less than 0 to print zero and not the negative subtotal amount. Is there a way to do this? I haven't been able to find a way to add an expression to the greyed out subtotal field. Thanks.

I would get rid of the autogenerated subtotal, create another matrix that sums the field and conditionally edit the expression of the Sum matrix.

I have also found that the autogenerated subtotal feature has much to be desired.

Conditionally CREATE a VIEW in a script

Hi,

I would like to create a view depending on a condition check first. However, I do not seem to the able to put a 'CREATE VIEW' within an IF statement. The following example demonstates what I am trying to achieve (please excuse the triviality of the example):

IF NOT col_length('authors','city') IS NULL

BEGIN
CREATE VIEW TestView
AS
SELECT (au_fname + ' ' + au_lname) as fullName, (address + ', ' + city) as fullAddress
FROM authors
END
ELSE
BEGIN
CREATE VIEW TestView
AS
SELECT (au_fname + ' ' + au_lname) as fullName, (address) as fullAddress
FROM authors
END

When I try to parse/run this I get the following syntax error:

"Incorrect syntax near the keyword 'VIEW'."

Any help would be much appreciated.

Thanks.

Try the code below.

Chris

Code Snippet

DECLARE @.sqlstring NVARCHAR(4000)

IF NOT col_length('authors', 'city') IS NULL
BEGIN
SET @.sqlstring = '
CREATE VIEW TestView
AS
SELECT (au_fname + '' '' + au_lname) as fullName, (address + '', '' + city) as fullAddress
FROM authors'
EXEC (@.sqlstring)
END
ELSE
BEGIN
SET @.sqlstring = '
CREATE VIEW TestView
AS
SELECT (au_fname + '' '' + au_lname) as fullName, (address) as fullAddress
FROM authors'
EXEC (@.sqlstring)
END

|||

I think this looks misguided. Rather than changing the view that is is created dynamically, I think you need to change the view permanently so that both views can be represented by a singular view that uses CASE construct. Hang on and if I don't get you an example, I imagine someone else will.

Maybe something like this:

create view testView
as

select au_fname + ' ' + au_lname
as fullName,
address
+ case when len(rtrim(city)) = 0
then ''
else ', ' + city
end
as address
from authors

go

select * from testView

/*
fullName address
--
Johnson White 10932 Bigge Rd., Menlo Park
Marjorie Green 309 63rd St. #411, Oakland
Cheryl Carson 589 Darwin Ln., Berkeley
*/

|||

Hi Chris,

I had thought about doing that but the real view is quite large and I was trying to avoid dealing with string manipulation but I suppose its just two single quotes for ant existing single quotes.

Thanks.

Smoc

|||

Hi Kent,

Thanks for the response but that will not work if the column does not exist in the table which is the reason I want to conditionally create 1 of 2 possible views. In the simplistic example, I want to handle the situation when the column 'city' may not be in the authors table.

I realise that i could use the col_length function instead to achieve the result you have proposed. I was just wondering why I could have two 'clean' view definitions in a script contained within an IF statement.

Regards,

Smoc

|||

Just thinking out loud really, but could you programatically add the City column to the authors table if the column doesn't exist? That way, going forward, you'd only have one version of the View to maintain.

Chris

|||

Hi Chris,

We have an application that is using a database that we have no control over and no authority to change. We have discovered some differences between schemas of different clients who have this database. The differences are not critical and we hope to handle it at the view level. Other than that we would do as you suggested.

I'm just supprised that I can do a DROP command but not a Create View command in an IF statement.

Smoc

|||

You can't create view/procedure/function/trigger inside or mid of your batch.

These create scripts should be the first line of the batch.

In IF batch you can put only the Drop view/procedure/function/trigger.

The only possible way is using dynmaic sql.

|||

Thanks for the clarification.

I will probably use the dynamic sql that you have suggested and as was also suggested in an earlier thread.

Thanks.

|||There is a neat trick to achieve just what you want . Check out this example:

-- If column doesn't exists, does not create the view that use it
IF col_length('authors','city') IS NULL set noexec on
go
CREATE VIEW dbo.TestView
AS
SELECT (au_fname + ' ' + au_lname) as fullName, (address + ', ' + city) as fullAddress
FROM authors
go
-- Return execute mode to default
set noexec off
go
-- If column exists, does not create the view without it
IF col_length('authors','city') IS not NULL set noexec on
go
CREATE VIEW dbo.TestView
AS
SELECT (au_fname + ' ' + au_lname) as fullName, (address) as fullAddress
FROM authors
go
-- Return execute mode to default
set noexec off

You only need to carefully choose your conditions because they have to be "reversed", in a way. Still, it is a proven and reliable approach.

conditional syntax in functions

I can't seem to get the nesting correct for an IF THEN condition inside a
function. My intent is to return the results (a table) of one of two
dfferent complex select statements. And, really, I am porting this SELECT
over from a working stored procedure. I wanted the convenience of being able
to use it in another select statement to further limit it down without
filters.
I keep getting "Incorrect syntax near 'BEGIN'"
To summarize:
CREATE FUNCTION dbo.fnGetProducts
( @.category int = 1,
@.subcategory int = 1,
@.classification int = 0)
RETURNS table
AS
BEGIN
If @.category=@.subcategory
RETURN (
SELECT ...... WHERE products.FK_category = @.category
)
ELSE
RETURN (
SELECT ...... WHERE products.FK_category = @.category
AND products.FK_subcategory = @.subcategory
)
END
GO
Am I doing this correctly?
Thanks
JulianYou are mixing inline table-valued functions (which are basically views that
accept parameters) and multi-statement table-valued functions (which allow
control-flow statement like IF..ELSE
Try the following to have an inline table-valued function:
CREATE FUNCTION dbo.fnGetProducts
( @.category int = 1,
@.subcategory int = 1,
@.classification int = 0)
RETURNS table
AS
RETURN (
SELECT ...... WHERE products.FK_category = @.category
AND products.FK_subcategory = CASE WHEN
@.category=@.subcategory
THEN products.FK_subcategory ELSE @.subcategory
END
)
END
GO
Jacco Schalkwijk
SQL Server MVP
"stjulian" <anonymous@.discussions.microsoft.com> wrote in message
news:eUH9CyHYFHA.3712@.TK2MSFTNGP09.phx.gbl...
>I can't seem to get the nesting correct for an IF THEN condition inside a
>function. My intent is to return the results (a table) of one of two
>dfferent complex select statements. And, really, I am porting this SELECT
>over from a working stored procedure. I wanted the convenience of being
>able to use it in another select statement to further limit it down without
>filters.
> I keep getting "Incorrect syntax near 'BEGIN'"
> To summarize:
> CREATE FUNCTION dbo.fnGetProducts
> ( @.category int = 1,
> @.subcategory int = 1,
> @.classification int = 0)
> RETURNS table
> AS
> BEGIN
> If @.category=@.subcategory
> RETURN (
> SELECT ...... WHERE products.FK_category = @.category
> )
> ELSE
> RETURN (
> SELECT ...... WHERE products.FK_category = @.category
> AND products.FK_subcategory = @.subcategory
> )
> END
> GO
>
>
> Am I doing this correctly?
>
> Thanks
> Julian
>|||If the in-line function would not work for you (because the two select
statements are completely different), you may want to use an
multi-statement function, i.e. something like this:
CREATE FUNCTION dbo.fnGetProducts
( @.category int = 1,
@.subcategory int = 1,
@.classification int = 0)
RETURNS @.result TABLE (
column1 int,
column2 varchar(50),
..
)
AS
BEGIN
IF @.category=@.subcategory BEGIN
INSERT INTO @.result (column1, column2, ...)
SELECT .... WHERE products.FK_category = @.category
END
ELSE BEGIN
INSERT INTO @.result (column1, column2, ...)
SELECT .... WHERE products.FK_category = @.category
AND products.FK_subcategory = @.subcategory
END
RETURN
END
GO
Of course, if the two SELECT statements are similar, it's easier (and
usually better) to write an in-line function, like Jacco suggested.
Razvan|||If you just have 2 select statement in your function, you can always write
it as an inline function. The two select statements must always return the
same columns when you have a multi-statement function, so you can always put
them in an inline function with a UNION. Inline functions have less overhead
and in general leas to better query plans.
Jacco Schalkwijk
SQL Server MVP
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1116961197.756965.191640@.g49g2000cwa.googlegroups.com...
> If the in-line function would not work for you (because the two select
> statements are completely different), you may want to use an
> multi-statement function, i.e. something like this:
> CREATE FUNCTION dbo.fnGetProducts
> ( @.category int = 1,
> @.subcategory int = 1,
> @.classification int = 0)
> RETURNS @.result TABLE (
> column1 int,
> column2 varchar(50),
> ...
> )
> AS
> BEGIN
> IF @.category=@.subcategory BEGIN
> INSERT INTO @.result (column1, column2, ...)
> SELECT .... WHERE products.FK_category = @.category
> END
> ELSE BEGIN
> INSERT INTO @.result (column1, column2, ...)
> SELECT .... WHERE products.FK_category = @.category
> AND products.FK_subcategory = @.subcategory
> END
> RETURN
> END
> GO
> Of course, if the two SELECT statements are similar, it's easier (and
> usually better) to write an in-line function, like Jacco suggested.
> Razvan
>

Conditional Statement in a Group

Hi,
This probably has a simple answer.
This is what I am trying to do on a report , I am trying to get a count of a
condition where IsUnderInvestigation = "yes" where the report is grouped by
"TEAM"
Dataset:
ID Name Team IsUnderInvestigation
1 John X Yes
2 Michael Y No
3 Peter X Yes
Then the report is grouped by TEAM to produce and then a column of count of
if the field "IsUnderInvestigation" is yes, then it increments the counter
to produce teh following report.
Team How Many Investigations for Team
X 2
Y 0
So as you can see, I need to group the data by TEAM and then have a count of
how many people within each team are under investigation.
Does anyone know how to put this conditional count statement in a cell of
the report?
Thanks in advance for any help.
Kind Regards
WarrenTry this:
=Sum(iif(Fields!UnderInvestigation = "Yes", 1, 0))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Warren Patterson" <des@.newsgroups.nospam> wrote in message
news:%23OKXMCeYFHA.4032@.tk2msftngp13.phx.gbl...
> Hi,
> This probably has a simple answer.
> This is what I am trying to do on a report , I am trying to get a count of
> a
> condition where IsUnderInvestigation = "yes" where the report is grouped
> by
> "TEAM"
> Dataset:
> ID Name Team IsUnderInvestigation
> 1 John X Yes
> 2 Michael Y No
> 3 Peter X Yes
> Then the report is grouped by TEAM to produce and then a column of count
> of
> if the field "IsUnderInvestigation" is yes, then it increments the counter
> to produce teh following report.
> Team How Many Investigations for Team
> X 2
> Y 0
>
> So as you can see, I need to group the data by TEAM and then have a count
> of
> how many people within each team are under investigation.
> Does anyone know how to put this conditional count statement in a cell of
> the report?
> Thanks in advance for any help.
> Kind Regards
> Warren
>
>
>
>
>|||Thank you thank you thank you thank you!
When I saw your solution, I thought....aish, I now look stoopid.
Kind Regards
Warren
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:%23nK5qviYFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Try this:
> =Sum(iif(Fields!UnderInvestigation = "Yes", 1, 0))
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Warren Patterson" <des@.newsgroups.nospam> wrote in message
> news:%23OKXMCeYFHA.4032@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > This probably has a simple answer.
> >
> > This is what I am trying to do on a report , I am trying to get a count
of
> > a
> > condition where IsUnderInvestigation = "yes" where the report is grouped
> > by
> > "TEAM"
> >
> > Dataset:
> > ID Name Team IsUnderInvestigation
> > 1 John X Yes
> > 2 Michael Y No
> > 3 Peter X Yes
> >
> > Then the report is grouped by TEAM to produce and then a column of count
> > of
> > if the field "IsUnderInvestigation" is yes, then it increments the
counter
> > to produce teh following report.
> >
> > Team How Many Investigations for Team
> > X 2
> > Y 0
> >
> >
> > So as you can see, I need to group the data by TEAM and then have a
count
> > of
> > how many people within each team are under investigation.
> >
> > Does anyone know how to put this conditional count statement in a cell
of
> > the report?
> >
> > Thanks in advance for any help.
> >
> > Kind Regards
> > Warren
> >
> >
> >
> >
> >
> >
> >
> >
> >
>

Conditional Split - DatTime Condition

Hi,

How do I make a condition for a DateTime field?

The SQL that I use for it is:

select..

from..

where...anddatePart(hh, myDateTimefield)> 10

Thank you!!

If you look in BOL for Expressions in SSIS you'll see it is much the same.|||BOL?|||BOL = Books on Line. SQL's help file.

For a Conditional split, in the condition you would put your condition. Example: Col1 == 1

This would cause any column with a value of 1 to go to that output. Anything not matching any one of the conditions would go to the default output.

Options are:
== (Double =)
<=
>=
!=

See BOL for Conditional Split help.

Conditional Split - DateTime Condition

Hi,

I'm trying to check if a row was created yesterday?

This does not seem to work?

(MyId == "10") && DATEPART("dd",GETDATE()) == (DATEPART("dd",MyDateTimeColumn) - 1)

Does anybody know how I can accomplish this?

Many thanks.

Two things...

First, you should surround your DATEPART equality checks in parenthesis.

(MyID == "10") && ( datepart stuff here )

Second, you probably shouldn't subtract one from the result of datepart("dd",MyDateTimeColumn), instead, you should use datepart("dd",dateadd("dd",-1,MyDateColumn))|||

Hi Mr. Hat,

I would try something similar to

(MyId == "10") && MyDateTimeColumn == DATEADD("dd", -1, GetDate())

Hope this helps,

Andy

|||

Andy Leonard wrote:

Hi Mr. Hat,

I would try something similar to

(MyId == "10") && MyDateTimeColumn == DATEADD("dd", -1, GetDate())

Hope this helps,

Andy

HAHAHAHA! (Sorry, but I blindly overlooked the part where the user was comparing day of months.) Yeah, do what Andy suggests. There's no need to compare "days" to each other. Just compare the dates. However, cast them to dt_dbdate first, to eliminate time.

(MyId == "10) && ((DT_DBDATE)[MyDateColumn] == (DT_DBDATE)DATEADD("dd",-1",GetDate()))|||

Brilliant! It worked.

Thank you so much!!

Conditional Split - Assign to value

Hello,

When you′re comparing values in the Condition of the Conditional split, can you assign a value to a variable?

If so, how can you accomplish this?

Thank you.

Assign what value?

No, you cannot. Not without using a script component.

Conditional Split

I am using a conditional split to evaluate the condition below. It should only send records to my SQL Server database if the PatientZip matches one of the eight below and the PatientCity is not Wichita Falls (you wouldn't believe how bad this is mispelled sometimes). I checked the output table and it has all records for the zipcodes below both matching and non-matching the cityname of Wichita Falls. The table should not have entries for records with the cityname of Wichita Falls. Do I have the code correct or could I have missed something?

LTRIM(PatientCity) != "Wichita Falls" && (PatientZip == "76301" || PatientZip == "76302" || PatientZip == "76305" || PatientZip == "76306" || PatientZip == "76307" || PatientZip == "76308" || PatientZip == "76309" || PatientZip == "76310")

One thing to look at is if you're sending the correct output from the Conditional Split to your destination.

Another thing is that you may want to RTRIM to catch trailing spaces instead of just LTRIMming to catch leading spaces.

|||

Thanks for the replay Matthew. I added the RTRIM as you suggested. My output name for my condition is "Bad City Name" and the default output name is "Correct City Name". I connected each output to different SQL Server tables that are exactly the same except for the table names. The Bad City Name output table is still being populated with data that is actually correct (city = "Wichita Falls" and is in the zipcodes listed above). The Correct City Name output table is being populated with any and all entries except (city = "Wichita Falls" and is in the zipcodes listed above).

As a check; I just ran the following query against the source database after replacing the logical operators with their SQL equivalents and the double quotes (") with single quotes (') and the query returned exactly what I am attempting to achieve with Integration Services.

select PatientName, PatientCity, PatientState, PatientZip

from ampfm.rpt_PatientDemographics

where LTRIM(RTRIM(PatientCity)) != 'Wichita Falls'

and (PatientZip = '76301' or PatientZip = '76302'

or PatientZip = '76305' or PatientZip = '76306'

or PatientZip = '76307' or PatientZip = '76308'

or PatientZip = '76309' or PatientZip = '76310')

I am at a loss as to why the Integration Services routine is not returning the correct row data. I must have something designed incorrectly. This is the first of several similar packages I am creating as the cornerstone to our audit process, but I need the correct data in the output (reporting) tables first. Please advise anything you feel may be in error that I can check.

Thanks!

|||

Have you tried putting a data viewer on the path going into and out of the conditional split? It might help to see what data you are getting in, and what data is on which path going out... (perhaps you have your tables flipped on your destinations, etc)

|||

I appreciate your post. Yes, I had earlier added data viewers and they showed the same data that querying the output tables were showing. I have everything set correctly as far as I can tell, it just isn't working as expected.

I finally deleted the conditional split and went with a Lookup object using the query below and it is pulling the correct information and putting it in the correct output tables. I guess I'll try to tackle conditional split issues at another time.

select PatientName, PatientCity, PatientState, PatientZip

from ampfm.rpt_PatientDemographics3

where LTRIM(RTRIM(PatientCity)) <> 'Wichita Falls'

and LTRIM(RTRIM(PatientZip)) IN ('76301','76302',

'76305','76306','76307','76308','76309','76310')

Thanks to all who have responded.

conditional query

Just wondering if someone could provide a brief example of how to do this. I
have a stored procedure and I need a condition where statement, for example
inputs are
@.name varchar(25)
@.color varchar(25)
if color is not 'none ' I want
select * from table1
where table1.name = @.name
and table1.color = @.color
if color is 'none' I want
select * from table1 where table1.name=@.name.
Thanks.
--
Paul G
Software engineer.One way
If @.color <> 'none'
select * from table1
where table1.name = @.name
Else
select * from table1
where table1.name = @.name
and table1.color = @.color
But there are lots of ways to to this type of processing. See
http://www.sommarskog.se/dyn-search.html
for a good discussion of ways to do this.
Tom
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:34367D41-4D61-4C01-ABC5-C5DD9DB969BD@.microsoft.com...
> Just wondering if someone could provide a brief example of how to do this.
> I
> have a stored procedure and I need a condition where statement, for
> example
> inputs are
> @.name varchar(25)
> @.color varchar(25)
> if color is not 'none ' I want
> select * from table1
> where table1.name = @.name
> and table1.color = @.color
> if color is 'none' I want
> select * from table1 where table1.name=@.name.
> Thanks.
> --
> Paul G
> Software engineer.|||create proc myProc
@.name varchar(25),
@.color varchar(25)
as
if @.color != 'none'
select * from table1
where table1.name = @.name and table1.color = @.color
if @.color = 'none'
select * from table1
where table1.name=@.name
Linchi
"Paul" wrote:
> Just wondering if someone could provide a brief example of how to do this. I
> have a stored procedure and I need a condition where statement, for example
> inputs are
> @.name varchar(25)
> @.color varchar(25)
> if color is not 'none ' I want
> select * from table1
> where table1.name = @.name
> and table1.color = @.color
> if color is 'none' I want
> select * from table1 where table1.name=@.name.
> Thanks.
> --
> Paul G
> Software engineer.|||This does it all in one query.
SELECT *
FROM table1
WHERE table1.name = @.name
AND (@.color = 'none '
OR table1.color = @.color)
Note that it MIGHT not perform as well as the alternatives using two
individual queries.
Roy Harvey
Beacon Falls, CT
On Tue, 29 Jan 2008 11:17:02 -0800, Paul
<Paul@.discussions.microsoft.com> wrote:
>Just wondering if someone could provide a brief example of how to do this. I
>have a stored procedure and I need a condition where statement, for example
>inputs are
>@.name varchar(25)
>@.color varchar(25)
>if color is not 'none ' I want
>select * from table1
>where table1.name = @.name
> and table1.color = @.color
>if color is 'none' I want
>select * from table1 where table1.name=@.name.
>Thanks.|||thanks for the responses. I simplified the example as I actually have
several items in the select statement as well as several in the where clause
as well as joins. I may have to use two seperate queries but may try to do it
with a sing query using OR if possible.
--
Paul G
Software engineer.
"Roy Harvey (SQL Server MVP)" wrote:
> This does it all in one query.
> SELECT *
> FROM table1
> WHERE table1.name = @.name
> AND (@.color = 'none '
> OR table1.color = @.color)
> Note that it MIGHT not perform as well as the alternatives using two
> individual queries.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 29 Jan 2008 11:17:02 -0800, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >Just wondering if someone could provide a brief example of how to do this. I
> >have a stored procedure and I need a condition where statement, for example
> >
> >inputs are
> >@.name varchar(25)
> >@.color varchar(25)
> >
> >if color is not 'none ' I want
> >select * from table1
> >where table1.name = @.name
> > and table1.color = @.color
> >
> >if color is 'none' I want
> >select * from table1 where table1.name=@.name.
> >Thanks.
>|||On Tue, 29 Jan 2008 12:34:23 -0800, Paul
<Paul@.discussions.microsoft.com> wrote:
>thanks for the responses. I simplified the example as I actually have
>several items in the select statement as well as several in the where clause
>as well as joins. I may have to use two seperate queries but may try to do it
>with a sing query using OR if possible.
Be aware that the warning about possible performance problems of the
all-in-one version becomes more apt as the query becomes more complex.
You may not have any problem, only trying it will determine that, but
be aware of the possibility.
Roy Harvey
Beacon Falls, CT|||ok thanks for the additional information. I have it running in the live
database, using 3 separate queries based on the condition of two input
parameters and the longest query is about 2 seconds. Fortunately the
database is relatively small and some indexes have been put into place to
enhance performance.
--
Paul G
Software engineer.
"Roy Harvey (SQL Server MVP)" wrote:
> On Tue, 29 Jan 2008 12:34:23 -0800, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >thanks for the responses. I simplified the example as I actually have
> >several items in the select statement as well as several in the where clause
> >as well as joins. I may have to use two seperate queries but may try to do it
> >with a sing query using OR if possible.
> Be aware that the warning about possible performance problems of the
> all-in-one version becomes more apt as the query becomes more complex.
> You may not have any problem, only trying it will determine that, but
> be aware of the possibility.
> Roy Harvey
> Beacon Falls, CT
>

Conditional Page Header

Will Crystal Reports have a page header that will hide if a condition is not met or viceaversa? If so how is this done?
Example: If Company_Name = 'xyz' then unhide page header A
thanks in advance
cgInstead of hard coding the header,write a formula like :

if companyname = 'xyz' then

'Header name'

else

' '

and drag the formula in page header!

or

right click on Page header section,select Section Expert,select Common Tab,
click formula button on right of suppress button ,and write

companyname <> 'xyx'|||how exactly do i do this in Crystal? I will have to do the same for the footer too.

thanks
cg|||you mean writing the formula ?

I think you should go for 2nd option for both header and footer i.e suppress the section based on your condition.|||i tried the conditional supressing the header and it is not working.
i have tried with and with out checking the supress box with the formula in there.

this is what i have in the formula, the first 2 are the same. i was testing if it was case sensitive or not. this is a parameter field instead of a db field.

{?CustomerName} <> "Typenex Medical, LLC"
or
{?CustomerName} <> "TYPENEX MEDICAL, LLC"
or
{?CustomerName} <> "TYPENEX MEDICAL LLC"
or
{?CustomerName} <> "TYPENEX MEDICAL L.L.C."
or
{?CustomerName} <> "TYPENEX"
or
{?CustomerName} <> "TYPENEX LLC"

not sure if this makes a difference but i have version 8.5

thanks
cg|||i tried the conditional supressing the header and it is not working.
i have tried with and with out checking the supress box with the formula in there.

this is what i have in the formula, the first 2 are the same. i was testing if it was case sensitive or not. this is a parameter field instead of a db field.

{?CustomerName} <> "Typenex Medical, LLC"
or
{?CustomerName} <> "TYPENEX MEDICAL, LLC"
or
{?CustomerName} <> "TYPENEX MEDICAL LLC"
or
{?CustomerName} <> "TYPENEX MEDICAL L.L.C."
or
{?CustomerName} <> "TYPENEX"
or
{?CustomerName} <> "TYPENEX LLC"

not sure if this makes a difference but i have version 8.5

thanks
cg

You have written the expression wrong.
Whatever you have written will be always true in any condition. Just check it properly. Use

NOT (condition1 AND condition2 AND so on)|||Make it simple :

- Right click on Formula field in field explorer

- click New

-name your formula

Type this

if {?CustomerName} <> 'ABC' and {?CustomerName} <> 'XYZ' then

' '
else

'HEADER'

Drag the formula field in report where you want to print the header.

conditional aggregate?

Is there any way to add some sort of condition, or where clause to an
aggregate function? I have a report with three nested lists - the detail is
in the inner list, list3. In the second (middle) list, I want to count the
number of rows for column x where column y is not equal (<>) "This".
I have tried adding a textbox in the 3rd list to tally rows where the
condition satisfies a count or a 'tic', and then attempted to add another
expression in list 2 that would sum the new textbox values in list 3, but am
ripping my hair out with error messages - what is the one about 'aggregates
can only be used on report items in the header and footer'? As far as I
understand, you can't really use Fields in the header or footer anyway - so
how one would use an aggregate function in that manner is beyond me...
Anyway, I know Crystal offers a variety of ways to accomplish this type of
thing - is there anyway to do a sum or a count for field X in a detail group
where field Y in the same detail group meets a certain criteria?
Thanks,Myles:
You can do a count in a group based on the condition or expression.
You can do soemthing like this:
=RunningValue(IIf(y.value<>"This",X,Nothing),Count,"grpName")
Or you can have this expression for you count:
CountDisctinct(IIf(y.value<>"This",X,Nothing),"grpName")
Hope this answers your question. For more information, search for
RunningValue in reporting services on msdn.
"Myles" wrote:
> Is there any way to add some sort of condition, or where clause to an
> aggregate function? I have a report with three nested lists - the detail is
> in the inner list, list3. In the second (middle) list, I want to count the
> number of rows for column x where column y is not equal (<>) "This".
> I have tried adding a textbox in the 3rd list to tally rows where the
> condition satisfies a count or a 'tic', and then attempted to add another
> expression in list 2 that would sum the new textbox values in list 3, but am
> ripping my hair out with error messages - what is the one about 'aggregates
> can only be used on report items in the header and footer'? As far as I
> understand, you can't really use Fields in the header or footer anyway - so
> how one would use an aggregate function in that manner is beyond me...
> Anyway, I know Crystal offers a variety of ways to accomplish this type of
> thing - is there anyway to do a sum or a count for field X in a detail group
> where field Y in the same detail group meets a certain criteria?
>
> Thanks,|||thank you sam, I will give that a try!
"sam" wrote:
> Myles:
> You can do a count in a group based on the condition or expression.
> You can do soemthing like this:
> =RunningValue(IIf(y.value<>"This",X,Nothing),Count,"grpName")
> Or you can have this expression for you count:
> CountDisctinct(IIf(y.value<>"This",X,Nothing),"grpName")
> Hope this answers your question. For more information, search for
> RunningValue in reporting services on msdn.
> "Myles" wrote:
> > Is there any way to add some sort of condition, or where clause to an
> > aggregate function? I have a report with three nested lists - the detail is
> > in the inner list, list3. In the second (middle) list, I want to count the
> > number of rows for column x where column y is not equal (<>) "This".
> >
> > I have tried adding a textbox in the 3rd list to tally rows where the
> > condition satisfies a count or a 'tic', and then attempted to add another
> > expression in list 2 that would sum the new textbox values in list 3, but am
> > ripping my hair out with error messages - what is the one about 'aggregates
> > can only be used on report items in the header and footer'? As far as I
> > understand, you can't really use Fields in the header or footer anyway - so
> > how one would use an aggregate function in that manner is beyond me...
> >
> > Anyway, I know Crystal offers a variety of ways to accomplish this type of
> > thing - is there anyway to do a sum or a count for field X in a detail group
> > where field Y in the same detail group meets a certain criteria?
> >
> >
> > Thanks,

condition with group by

Data:

PROJ PLAN TOTTIME UNIT
A P1 10 DAY
A P2 10 HOUR
A P3 1 MONTH

WHEN I'M DOING GROUP BY ON PROJ

AND CALCULATING TOTTIME IT CONSIDER ONE OF THE UNIT I.E. DAY, HOUR, MONTH

I WANT TO SUMUP ALL WITH HAVING UNIT CALCULATION ALSO.

10 DAY=10 DAYS
10 HOUR=1.25 DAYS
1 MONTH=20 DAYS

THE RESULT SHOULD BE LIKE THIS:

PROJ PLAN TOTTIME (IN DAYS)
----------
A ALL 31.25
----------

THANKS IN ADV.

T.S.NEGI
tilak.negi@.mind-infotech.comtilak.negi@.mind-infotech.com (T.S.Negi) wrote in message news:<a1930058.0401052155.53e0e35a@.posting.google.com>...
> Data:
> PROJ PLAN TOTTIME UNIT
> A P1 10 DAY
> A P2 10 HOUR
> A P3 1 MONTH
>
> WHEN I'M DOING GROUP BY ON PROJ
> AND CALCULATING TOTTIME IT CONSIDER ONE OF THE UNIT I.E. DAY, HOUR, MONTH
> I WANT TO SUMUP ALL WITH HAVING UNIT CALCULATION ALSO.
>
> 10 DAY=10 DAYS
> 10 HOUR=1.25 DAYS
> 1 MONTH=20 DAYS
> THE RESULT SHOULD BE LIKE THIS:
> PROJ PLAN TOTTIME (IN DAYS)
> ----------
> A ALL 31.25
> ----------
>
>
> THANKS IN ADV.
> T.S.NEGI
> tilak.negi@.mind-infotech.com

Here is one way to do the summing using a CASE statement

SELECT
SUM(CASE UNIT
WHEN 'DAY' THEN TOTTIME
WHEN 'HOUR' THEN TOTTIME * 0.125
WHEN 'MONTH' THEN TOTTIME * 20
END)
FROM
PLANSAMPLE
WHERE
<CONDITION>
GROUP BY
<Grouping>

Condition validation on Crystal report Fields

Hi Folks,

I am CR XI..I have 2 numeric Fields in Report.I want to update the Field Data based on Below Condition.

Let us assume report Fields Like A, B

Condition: if A>10 and B=10 then B='Good'(String)
else B=B(earlier data)

Please help me out How do I apply this logic

Urgent...

ThnaksIf the field was numeric in the DB, and you want to change the value of the field in some records to an alphanumeric ('Good') you gotta problem. Create another field.|||Hi Folks,

I am CR XI..I have 2 numeric Fields in Report.I want to update the Field Data based on Below Condition.

Let us assume report Fields Like A, B

Condition: if A>10 and B=10 then B='Good'(String)
else B=B(earlier data)

Please help me out How do I apply this logic

Urgent...

Thnaks

you can't update field of your data base and which are used in
crystal report.

you would have create a formula to it.

COndition Spli - Error date condition

Dear friends,

I'm having a problem... maybe it's very simple, but with soo many work, right now I can't think well...

I need to filter rows in a dataflow...

I created a condition spli to that... maybe there is a better solution...

And the condition is: Datex != NULL(DT_DATE)

(Some DATE != NULL)

[Eliminar Datex NULL [17090]] Error: The expression "Datex != NULL(DT_DATE)" on "output "Case 1" (17123)" evaluated to NULL, but the "component "Eliminar Datex NULL" (17090)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error.

What is wrong?

Regards,

Pedro

Hi Pedro,

What if you use IsNull(Datex) to define your condition? You could then use the default output for all rows where Datex is not Null.

Hope this helps,

Andy

|||

oooohhhh Men... soo simple... last week is being too hard for me... lot of work.... jesus!!!

Thanks!!

|||

Hi Pedro,

You're welcome! Don't be so hard on yourself. SSIS isn't intuitive and I've been working with it a while. We're all still learning!

Andy

Condition on a field date in a query data set

I need to extraxt from a table of my db the records that satisfy a condition
on a field date.
I use this query string for the data set:
="SELECT Cliente, NrDocumento, DataDocumento, NrRegistrazione,
DataRegistrazione, TipoDocumento, Descrizione, DataScadenza, Importo,
ImportoIncassato FROM Partitario WHERE (Azienda = " & "'" &
Parameters!Azienda.Value & "') AND (Cliente = " & "'" &
Parameters!Cliente.Value & "') AND (Importo - ImportoIncassato > 0) AND
(DataScadenza<=" & Format(Globals!ExecutionTime,"d") & ")"
In my table Partitario exists a record with datascadenza < actual date
calculated by Globals!ExecutionTime: but this query doesn't extract nothing!
Is the syntax right?
How can I solve this issue?
Many thanksUse a datediff instead of a <= comparison.
Craig
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:E6FB900D-CE25-4688-A88F-9384592FBA3A@.microsoft.com...
>I need to extraxt from a table of my db the records that satisfy a
>condition
> on a field date.
> I use this query string for the data set:
> ="SELECT Cliente, NrDocumento, DataDocumento, NrRegistrazione,
> DataRegistrazione, TipoDocumento, Descrizione, DataScadenza, Importo,
> ImportoIncassato FROM Partitario WHERE (Azienda = " & "'" &
> Parameters!Azienda.Value & "') AND (Cliente = " & "'" &
> Parameters!Cliente.Value & "') AND (Importo - ImportoIncassato > 0) AND
> (DataScadenza<=" & Format(Globals!ExecutionTime,"d") & ")"
> In my table Partitario exists a record with datascadenza < actual date
> calculated by Globals!ExecutionTime: but this query doesn't extract
> nothing!
> Is the syntax right?
> How can I solve this issue?
> Many thanks|||Or use GETDATE() in SQL query string.
Many thanks
"Craig" wrote:
> Use a datediff instead of a <= comparison.
> Craig
> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
> news:E6FB900D-CE25-4688-A88F-9384592FBA3A@.microsoft.com...
> >I need to extraxt from a table of my db the records that satisfy a
> >condition
> > on a field date.
> > I use this query string for the data set:
> > ="SELECT Cliente, NrDocumento, DataDocumento, NrRegistrazione,
> > DataRegistrazione, TipoDocumento, Descrizione, DataScadenza, Importo,
> > ImportoIncassato FROM Partitario WHERE (Azienda = " & "'" &
> > Parameters!Azienda.Value & "') AND (Cliente = " & "'" &
> > Parameters!Cliente.Value & "') AND (Importo - ImportoIncassato > 0) AND
> > (DataScadenza<=" & Format(Globals!ExecutionTime,"d") & ")"
> >
> > In my table Partitario exists a record with datascadenza < actual date
> > calculated by Globals!ExecutionTime: but this query doesn't extract
> > nothing!
> > Is the syntax right?
> >
> > How can I solve this issue?
> >
> > Many thanks
>
>

Condition in Subtotal?

Hi
I have the following matrix
CA AZ

No surplus 11 5

---

Surplus 12 10

---

Zotal 100 50

---

Totlal 123 65

---

I want that only (No surplus and Surplus) sum include in total Can I apply This condition on Subtotal.

thanks in advance

Hi Yaseen,

Take a look to this post. This will help you out.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1315844&SiteID=1

Bernard Ong

Condition in Subtotal?

Hi
I have the following matrix
CA AZ

No surplus 11 5

---

Surplus 12 10

---

Zotal 100 50

---

Totlal 123 65

---

I want that only (No surplus and Surplus) sum include in total Can I apply This condition on Subtotal.

thanks in advance

Hi Yaseen,

Take a look to this post. This will help you out.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1315844&SiteID=1

Bernard Ong

condition in script

hi

I need to alter a procedure depend on some information .

if A is true then

alter procedure .... < code 1>

else

alter procedure .... <code 2>

is it possible?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1557658&SiteID=1

Check out my post in there.