Conditional Split Question

Hello,

I am have an ID column that sometimes contains all numeric characters and sometimes contains all digits. I would like to the records with all digits (0-9) to continue downstream in my Data Flow. I would like the records that contain characters other than digits to be logged to a table.

This sounds like a job for the Conditional Split transformation, but I don't see a way to easily test for a numeric value. For example, I would like to use something like ISNUMERIC([MyIDField]) for testing the values in my Conditional Split, but I don't see a way to do this.

Do I have to create a Derived Column transformation prior to my conditional split that populates a "numeric" ID column for each of my records then test this Derived Column in my Conditional Split? Seems like more work than I would to see for something as simple as testing for a numeric...

TIA...

Brian

"numeric characters and sometimes contains all digits"... digits are numeric? Anyway I'd use my Regular Expression Transform http://www.sqlis.com/default.aspx?91. It will handle the test and split, and regular expressions are great for validating things like this.

|||

Brian,

You are correct, there is no ISNUMERIC() function in the expression evaluator. However, in your case, there is a fairly decent workaround I think.

If you are SURE that the string is never a mix of numeric and character data, you could use the following expression to direct alpha strings (where Col is the name of your input column:

FINDSTRING("0123456789", SUBSTRING(Col, 1, 1), 1) == 0

This expresssion will be true if the first character of Col is an alpha character.

Hope this helps.

Mark

Conditional group footer sum

HI,
I am working with a developer who has an interesting problem. They have
data field in a sql table that is of a numeric value a second field that
tells whether or not the previous value is a debit or credit for a general
ledger.
They want to show on a report a sum of the numeric field in a group footer.
We have tried writing a conditional formula(=Iif(FieldB="dr",Sum(FieldA),o))
for a hidden field on the report in the detail row of a table and then using
the Reportitems Syntax to display that fields value in the group footer, but
we get an out of scope error.
Looking for possible suggestions, code sample, or alternatives. Changing
the data in the SQL table is not a possibility.
Thanks!!I recently tried doing something similar to this but I was getting a data
type error. I resolved it by making the following change:
Orig: sum(iif(Fields!Type.Value = 'Dr', Fields!Amount.Value, 0) --Got errors
New: sum(iif(Fields!Type.Value = 'Dr', Fields!Amount.Value,
Fields!Amount.Value*0)
I don't know why mulitplying by 0 gives the correct data type, but simply
putting a 0 in does not, but that is only variation (0, 0.0, 0.00, etc...) I
could find that worked.
"Mark" wrote:
> HI,
> I am working with a developer who has an interesting problem. They have
> data field in a sql table that is of a numeric value a second field that
> tells whether or not the previous value is a debit or credit for a general
> ledger.
> They want to show on a report a sum of the numeric field in a group footer.
> We have tried writing a conditional formula(=Iif(FieldB="dr",Sum(FieldA),o))
> for a hidden field on the report in the detail row of a table and then using
> the Reportitems Syntax to display that fields value in the group footer, but
> we get an out of scope error.
> Looking for possible suggestions, code sample, or alternatives. Changing
> the data in the SQL table is not a possibility.
> Thanks!!|||The reason why multiplying with 0 works is that it preserves the original
datatype of the numeric field (which could be anything like UInt16, Decimal,
etc.).
This should work (the constant value 0.0 is a System.Double at runtime):
=sum(iif(Fields!Type.Value = 'Dr', CDbl(Fields!Amount.Value), 0.0)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
news:9EAB31A6-E7CA-4912-82BC-A678EAE9CA0C@.microsoft.com...
> I recently tried doing something similar to this but I was getting a data
> type error. I resolved it by making the following change:
> Orig: sum(iif(Fields!Type.Value = 'Dr', Fields!Amount.Value, 0) --Got
errors
> New: sum(iif(Fields!Type.Value = 'Dr', Fields!Amount.Value,
> Fields!Amount.Value*0)
> I don't know why mulitplying by 0 gives the correct data type, but simply
> putting a 0 in does not, but that is only variation (0, 0.0, 0.00, etc...)
I
> could find that worked.
> "Mark" wrote:
> > HI,
> >
> > I am working with a developer who has an interesting problem. They have
> > data field in a sql table that is of a numeric value a second field that
> > tells whether or not the previous value is a debit or credit for a
general
> > ledger.
> >
> > They want to show on a report a sum of the numeric field in a group
footer.
> > We have tried writing a conditional
formula(=Iif(FieldB="dr",Sum(FieldA),o))
> > for a hidden field on the report in the detail row of a table and then
using
> > the Reportitems Syntax to display that fields value in the group
footer, but
> > we get an out of scope error.
> >
> > Looking for possible suggestions, code sample, or alternatives.
Changing
> > the data in the SQL table is not a possibility.
> >
> > Thanks!!

Conditional formatting for missing numeric data

I created a matrix report which includes numeric data to be formatted as
currency. In cases where the data is missing, the empty cell displays "$".
If I use the iif() function for conditional formatting, it inserts the entire
expression into all of the cells. Does anyone know how I can format the
cells that do not contain any data so that nothing will be displayed?
thanks!It sounds like you need Nothing:
=iif(Fields!MyValue.Value Is Nothing,"","C0")
Put the expression in the Custom field in the Textbox Properties window.
I think you can use "". If not, choose something else.
"Anna" wrote:
> I created a matrix report which includes numeric data to be formatted as
> currency. In cases where the data is missing, the empty cell displays "$".
> If I use the iif() function for conditional formatting, it inserts the entire
> expression into all of the cells. Does anyone know how I can format the
> cells that do not contain any data so that nothing will be displayed?
> thanks!

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.

Concatenating Numeric Fields

Friends,

I am attempting to concatenate two numeric type fields together with character data and the query is adding them together. I am assuming I need to convert the ints to a string type but would appreciate some info on the best way to do this...I am sure it's something simple but am not finding much on the web about it.

SELECT vehFacID + '-' + vehID AS vehNew FROM Vehicles

Returns the sum of vehFacID & vehID. Doh!

J.H.

I think I found it...Something like this works..

SELECT *, CAST(vehFacID AS VARCHAR(4)) + '-' + CAST(vehID AS VARCHAR(10)) AS vehCombo FROM Vehicles

Is this the right way to do this?

J.H.

|||

If the vehFacId and vehId are numbers, then this is the way to go.

<stuff you can ignore if you want>

A bit nasty with the column names, I hope for your sake you don't have 3 letter abbreviations in every column (but not in your table name.) That must be hard to follow.

</stuff you can ignore if you want>

|||

Are you referring to the "veh" abbreviation? If so, why would you say it would be hard to follow? A small sample of my tables is like:

Vehicles, Departments, Facilities, Customers, etc...I use the 3 (or 4 sometimes) letter abbreviation to determine which table the field came from. I am open to hearing a better suggestion if you have one.

J.H.

|||

You know what else I am curious about is the casting. My numeric columns in this case are smallInt and can hold up to 5 digits. Is the recommendation to cast them to varchar(5) in this case?

J.H.

|||

I don't see any problem even if you cast to varchar(25), that way down the road if you happen to change the datatype from smallint to int, you don't have to worry about T-SQL code like this in various stored procs and functions.

As far as database naming conventions goes there isn't a standard. I wish Microsoft would have suggested something on MSDN.

I kind of agree with a article on aspfaq: http://www.aspfaq.com/show.asp?id=2538

|||

A little bit for the veh abbreviation. I would prefer to see vehicleId, and vehicleFaciltiyId, etc, which is easier to follow for the uninitiated (and in fact good finger exercises :)

The vehFacId was what kind of concerned me. I got this flash of:

select vehId, mak, modYr, numWhl, vehIdNum...etc.

There were a lot of these sorts of naming conventions back when names could only be 30 characters (funny how many times we hit 30, but rarely do I go over it now...) I don't like to see something that might be an issue and not say something. (hence the: <stuff you can ignore if you want> tags) Like the link to aspfaq says, it is a matter of taste, but the more clear it is, the more clear it is.

If a new person or contractor or newsgroup helper can read it and understand it, your job of naming is done right.