Conditional suppression formula PLEASE HELP

I have Crystal reports 8.5 and Im trying to generate a report that will give all of my sales orders with the part numbers price and the address they shipped to but I keep getting alot of duplicate fields so I used a suppresion forumla below

{InvoiceDetail.PartNumber} = previous({InvoiceDetail.PartNumber})

That did a great job of eliminated the duplicates but I found out that I have some orders that are in succesion with identical part numbers and they were being suppressed so I tried this formula

{InvoiceDetail.PartNumber} and {InvoiceHeader.SONumber} = previous({InvoiceDetail.PartNumber}) and {InvoiceHeader.SONumber}

thinking that will suppress any field that has the same part number and so number but it errors our and tells me I need a boolean.

I've tried just about everything and Im sure im missing something very simple but I dont know what it is.

PLEASE HELP{InvoiceDetail.PartNumber} and {InvoiceHeader.SONumber} = previous({InvoiceDetail.PartNumber}) and {InvoiceHeader.SONumber}

The very first thing is try to find out why you are getting duplicate data. Something must be wrong with you linking.

To suppress try this:

{InvoiceDetail.PartNumber} = previous({InvoiceDetail.PartNumber}) AND {InvoiceHeader.SONumber} = previous({InvoiceHeader.SONumber})

Conditional Formatting - text to text

Hello,
My query returns numbers for months. Can I use conditional formatting to
convert a 1 into Jan, 2 into Feb, ect?
I know that there is a way to do this within the query itself, but am
curious as to the conditional formatting way. Thanksabunch.You could use the MonthName function to achieve this effect in the report:
=MonthName(CInt(Fields!Month.Value))
See also:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctMonthName.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Drew" <Drew@.discussions.microsoft.com> wrote in message
news:D4481668-F461-41CF-972B-162E88F716B8@.microsoft.com...
> Hello,
> My query returns numbers for months. Can I use conditional formatting to
> convert a 1 into Jan, 2 into Feb, ect?
> I know that there is a way to do this within the query itself, but am
> curious as to the conditional formatting way. Thanksabunch.

concatenating strings like summing numbers

I'd like to join string within a group, preferable with a separator, the same
way I'd sum values.
So, I'd like to write somethign like
=Join( Fields!Email.Value, ";" )
to obtain a string of semicolon separated substrings, the same way I'd say
=Sum( Fields!Number.Value) to get the sum of numeric field.
Does anyone know a way to do this?Having exactly the same problem. Thought of writing custom code, but not sure
what arguments to pass. The code had an array of strings as an argument, but
when i called the function the same way I would call an aggregate, got an
error message saying that aggregates can take only numeric values as
arguments..
"Max" wrote:
> I'd like to join string within a group, preferable with a separator, the same
> way I'd sum values.
> So, I'd like to write somethign like
> =Join( Fields!Email.Value, ";" )
> to obtain a string of semicolon separated substrings, the same way I'd say
> =Sum( Fields!Number.Value) to get the sum of numeric field.
> Does anyone know a way to do this?
>
>

Concatenating Numbers/Converting Dates to Integers

My ERP software stores all dates as integers. So originally, I wrote a T-SQL function to convert these integer dates to normal people dates in the query I use as the recordset for my report. Well...that worked fine on 1,000 rows, but NOT for 100,000. So I've figured out that if I convert my normal person date parameter to an integer date, then SQL only has to convert my 1 parameter instead of having to convert 100,000 fields, (actually, 300,000 because I have 3 date columns).

So my question is, what is the best way to do this? This is what I have so far:

SET @.Macola = Cast(Datepart(yy,@.MacolaDate) as varchar) + Cast(Datepart(mm,@.MacolaDate) as varchar) + Cast(Datepart(dd,@.MacolaDate) as varchar)

However, I want the leading zeros for the month and day. For example if I enter '1/1/2004' into this function, it returns 200411, but I need it to return 20040101.

Any suggestions would be greatly apprectiated. Thank you.just to be sure
what is the type of your variable @.Macola

doeas it HAVE to be integer ?|||Well, I am working with a database design that I cannot modify and it stores the dates in an int column with the format 20040101 and any conversions to the 100,000 values in the table takes too long, so I want to convert my date to an integer.|||i'm not sure if this is what you want

but create this user define function

CREATE function dbo.Date(@.Date DateTime)
returns Varchar(10) as
begin
return(
Cast(Datepart(yy,@.Date) as varchar) +
Replicate('0',2-len(Cast(Datepart(mm,@.Date) as varchar)))+Cast(Datepart(mm,@.Date) as varchar) +
Replicate('0',2-len(Cast(Datepart(dd,@.Date) as varchar)))+Cast(Datepart(dd,@.Date) as varchar)
)
end

then you'll be able to get easily

set @.Macola=dbo.date('1/1/2004')
set @.Macola=dbo.date(@.MacolaDate)

the Replicate+Len functions adds the 0 if necessary|||Thank you!|||is it what you needed ('cause i'm going offline)|||Yes it is, I appreciate your help.|||Cross post?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=32771

Anyway, what does your base data look like

Since you say you want it to be 20040101, are you assuming that that's the int value?

Where do you get 1/1/2004 from?

Some ddl might help

I wrote a T-SQL function to convert these integer dates to normal people dates in the query I use as the recordset for my report. Well...that worked fine on 1,000 rows, but NOT for 100,000. So I've figured out that if I convert my normal person date parameter to an integer date, then SQL only has to convert my 1 parameter instead of having to convert 100,000 fields,

That part confused me...|||Sorry for any confusion. Let me try this again.

I need to pass StartDate and EndDate parameters to the query I use as the record-source for several reports.

My database stores dates as integers in the format 20040101, but I don't want my end users to have to enter dates in that integer format, also, I figured it would be easier to convert the integer dates to datetimes in the query so that I wouldn't have to make this conversion on every report, it would already be done for me.

I wrote a T-SQL scalar function that converts from the integer format to a datetime format, and I just used that 'fnIntToDate(doc_date)' as the column returned in the header for the 3 date columns I have. This function looks like this, and works well:

Convert(Datetime,Left(Right(@.MacolaDate,4),2) + '/' + Right(@.MacolaDate,2) + '/' + Left(@.MacolaDate,4))

I do my development on a database with 1,000 rows, but our production database has over 100,000, and this date conversion (along with a few other things I have since fixed) caused the query to time-out. And according the the query analyzer, I can save 3-4 seconds PER COLUMN if I just leave the integers as integers and convert my date parameters to integers instead.

So I'm asking for help on a function that does the opposite of what my function does. I want to pass it a datetime value and have it return a date in the format 20040101. I had gotten all of it except the leading 0s, but I think the previous poster answered that for me, so i'm goign to go try that now.

Thank you.|||And what he told me worked great :). I just converted it to an integer and I'm good to go.