SUM A Column Of Values Derived From An IIF Expression

Jan 31, 2007

I have a table in my report which shows sales values for each month by looking at the month number

so for Jan i use

=IIf(Fields!period_.Value=1,Fields!nett_.Value,0)

for Feb

=IIf(Fields!period_.Value=2,Fields!nett_.Value,0)

this is all good and I get a series of rows per customer with the correct value in the correct column

JAN FEB Mar

Customer A 250

Customer A 350

Customer A 5000

.

However I want to create a summary a line

JAN FEB Mar

Customer A 250 350 5000



I tried using SUM but this doesn't give all of the values in the summary line. It might just give the first and the rest are zeros. Or if the customer didn't have a value in Jan, but did in Feb and March, Feb's value is shown, but March is at 0 etc.

Have spent far too long on this today, so if anyone as any suggestion (come back Crystal all is forgiven ) on any approach I could take to this I'd appreciate it.

Cheers

Steven

View 4 Replies


ADVERTISEMENT

Add Values To A Column With Derived Column Expression?

Feb 25, 2008

Hi, how are you?
I'm having a problem and I don't know if it can be solved with a derived column expression. This is the problem:


We are looking data in a a sql database.

We are writting the SQL result in a flat file.

We need to transform data in one of the columns.

For example: we can have 3 digits as value in a column but that column must be 10 digit length. So we have to complete all the missing digits with a zero. So, that column will have the original 3 digits and 7 zeros. How we can do that tranformation? We must do it from de the flat file or it can be a previous step?
Thanks for any help you can give me.
Regards,

Beli

View 10 Replies View Related

Question On Derived Column Expression

Apr 30, 2007

Hi, all experts here,



Thank you very much for your kind attention.



I am having a question on derived column expression. The expression I am trying to use for the derived column is as below (column1 is a numeric data type column):

case

when column1<0 then 'yes'

else 'no'

end

But I got the error message though, would please any experts here give me any advices on the expression I used? What is wrong with the expression I used above?

Thank you very much and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,





View 9 Replies View Related

Derived Column: Expression Question

Dec 4, 2006

The following simple expression is not working for in a derived column transform. I have tried several things back and forth with lots of frustration.

DAY((DT_DATE)mydate)

In fact any of the date/time functions are not working for me and I keep getting the following error

[Derived Column [380]] Error: The "component "Derived Column" (380)"
failed because error code 0xC0049067 occurred, and the error row
disposition on "output column "Derived Column 1" (824)" specifies
failure on error. An error occurred on the specified object of the specified component.

mydate happens to be imported as string[DT_STR] column from a flat file source.

Thank you in advance.

View 1 Replies View Related

Derived Column Transformation Expression

Mar 7, 2006

Hi.

I am using the following expression to check if the first charcter of a string is not the letter "E" and if it is, strip it off by selecting the remainder of the string:

SUBSTRING([Derv.comno],1,1) == "E" ? SUBSTRING([Derv.comno],2,10) : [Derv.comno]

This is ok in 99.9% of cases, but ideally I would like to be able to check, and alter the string if the first charcter is anything but numeric

I had something like this in mind:

SUBSTRING([Derv.comno],1,1) != ("1","2","3") ? SUBSTRING([Derv.comno],2,10) : [Derv.comno] 

but the syntax is incorrect.

Could you tell me if what I am attempting is actually possible, and if so, point me in the right direction regarding the syntax!

Thanks

 

 

 

 

 

 

 

 

 

 

 

 

 

  

 

 

 

 

View 2 Replies View Related

Problem With Derived Column Expression

Feb 14, 2008

I'm trying to import from Excel, using
(Assignment == 0 ? "In Use" : Assignment == 1 ? "In Stock" : "Retired") to convert 3 values to text and I' getting the following error:
An error occurred while attempting to perform data conversion. The source type could not be converted to the destination type.

Could someone please tell me what I am doing wrong?

Thanks
Dean

ps. just fixed it myself by changing the data type of the input column

View 1 Replies View Related

Derived Column Expression Question......

May 7, 2007

Greetings,



I have an existing 2000 DTS package that uses the following case statement:


Case
When TERMS_PERCENT ='0'
then 0
else cast(TERMS_PERCENT as decimal(6,2))/100
end as TermsPct



to convert a source DT_STR(4) datatype to a DT_Numeric(5,2) destination column and would like to use an equivelent derived column expression in 2005. Being a DBA by nature and experience I'm having trouble converting this statement to a valid expression without failure, any help would be greatly appreciated.

View 4 Replies View Related

Simple Derived Column Formatting Expression

Jul 10, 2006

What is the equivalent SSIS expression for this C expression to format an unsigned integer by padding it with leading zeros?

sprintf(publ_doc_id, "%010u", id)

View 1 Replies View Related

UDF Or Trigonometric Functions In An Expression For A Derived Column?

May 24, 2006

Hi all!

I am importing data from a CSV to a DB with a SSIS package.
Among some things things it already does, it has to decide if the relation between one row and the following is acceptable.
If it is not, the 2nd row is discarded, the next one is taken and the relation value is calculated again to decide whether to keep this one or not, and so on.

To calculate this value, I need to apply a formula that includes sin(), cos() and acos() functions.
I have already written this formula as a scalar-valued function in my SQL Server.

So, my question is:
- Is there a way to call a function (a UDF) within the Expression in a Derived Column dataflow item?
and if not,
- Hoy can I use trigonometric functions within the Expression in a Derived Column dataflow item?

I hope someona can tell me something about this... I'm falling into despair! :-s

Thanks a lot!!

Cau

View 5 Replies View Related

Expression Problem In Derived Column Component

Nov 22, 2006

I am reading an excel file with a field that consists of lastname, firstname. I am using the Derived Column transformation to separate the two fields. The firstname expression works fine: SUBSTRING(F1,FINDSTRING(F1,",",1) + 1,LEN(F1) - FINDSTRING(F1,",",1))

However, the lastname field keeps giving me an error when I use SUBSTRING(F1,1,FINDSTRING(F1,",",1) - 1). I'm sure I've used this substring before in visual studio. Could this be a bug? The error is below.

[Add Columns [2886]] Error: The "component "Add Columns" (2886)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "LastName" (3100)" specifies failure on error. An error occurred on the specified object of the specified component.

View 1 Replies View Related

Is It Possible To Run A Dynamic Expression In The Derived Column Transfermation?

Nov 13, 2006

I would like to run following T-SQL statement in the Derived Column:

declare @strData varchar(1000), @strSQL varchar(3000)
select @strdata = 'LANEX ~11/13/06 10:10:08 ~112233 9th ST S. Arlington, 22204, VA ~ 093300~XYZ '
select @strSQL ='select datalength('''+replace(@strData,'~','''),datalength(''') +''')'
--print @strSQL
exec (@strSQL)

Thank you!

View 4 Replies View Related

How To Give The Null Value In The Derived Column Expression Value

Aug 25, 2006

Hi I want to pas one Derived Column Value is "Null" Default . How to Give the Expression "

For Example"

The Derived Column i gave one Column Name



Derived Column Name Expression Data Type



Price ""



Any one give me the Solution

View 1 Replies View Related

Default Date In A Derived Column Expression

Nov 20, 2006

one of my SSIS packages use this expression to put todays date in if it is NULL:

(ISNULL(datejs)) ? GETDATE() : datejs

however, what I really want to do though is put a default date in like '2007-01-01' but I get syntax error because SSIS thinks it's a string, which it is I suppose.

Is it possible to do what I want it to?

Thanks

 

 

 

 

View 5 Replies View Related

Problem With Expression Window In Derived Column

Jan 10, 2007

Hi,

i am facing problem to dervie one column value from another column using either if and case statements in expression window of dervived columns transformation.

let me give the exapmle. i get 1 column from source system name as "col a" and i want to insert 2 columns into my destination as col A and col B. based on the values of col A i want to derive the values of col B,like if col A value is 0 then col B value is Good else BAD.

Can any one asssit in this regard how to achive it?? and is it possible to use IF and CASE statement in this dervived column tranformation???





Sreenivas

View 1 Replies View Related

How To Set Empty A Space From A Derived Column Expression?

May 30, 2006

hi,

This field comes from a flat file and sometimes own zero and sometimes own a empty position (after 'EUR'):

2006053000499236000005307700108287457080200408287452006052953990000000010000EUR
2006053000499236004414989200101423426004400501423422006052953990000000010000EUR0

[Column 12] == "0" ? [Column 12] : ?????????



TIA

View 7 Replies View Related

SSIS - Derived Column Transformation Editor Expression

Aug 23, 2007

I am trying to put the following as an expression in the SSIS Derived Column Transformation Editor.

DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

It is not allowing it. This works fine in a regular SQL statement.

Does anyone know how I can get this to work?

View 14 Replies View Related

Integration Services :: SSIS Derived Column Expression

Nov 23, 2015

I am trying to figure out my expression here with derived column 

source

Unavailable/Planned

output

Planned(which is after "/".

I achieved this in sql but not finding solution in SSIS Derived column transformation.

View 3 Replies View Related

Integration Services :: Derived Column Expression - Display Only Name?

Jun 16, 2015

How can i write expression for below input?

1858;# 1234;#Chun  Yang

I want to display only name.

View 2 Replies View Related

Date Issue With Derived Column / Expression Language

Jan 10, 2006

Can someone confirm this for me?  The expression language in SSIS has the same limitations on date ranges as Sql Server?  That limitation is that valid date ranges are from Jan 1, 1753 to Dec 31, 9999.

When ever I try to do a date function (DATEPART, for example) in a Derived Column Transformation on a date less than 1/1/1753, I get an error.  I initially discovered this when bringing data over from Oracle to Sql Server.  Just as a test, I created a text file filled with various dates and tried to import it.  Whenever a date is less than 1/1/1753, it blows up. 

For example, this expression code - DATEPART("YEAR",Date) will yield this error - [Derived Column [24]] Error: The "component "Derived Column" (24)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "YEAR" (80)" specifies failure on error. An error occurred on the specified object of the specified component. 

As a workaround, I've been using a Script Component to do date checking, but this is obviously not ideal.

View 5 Replies View Related

Major Problem With REPLACE-expression In The Derived Column Transformation

Apr 26, 2006

I'm importing a csv-file delimited with semicolons. Firstly I LTRIM the columns "in place" and the data imports fine. All the numbers in right columns in the target table. Then I add another Derived Colum Transformation to replace decimal character comma (,) to a dot (.) in order to convert the string/varchar value to numeric. But here I run into trouble. Running the task ends in success but the result in the target table (same as above) is not. All the commas are now dots as expected but what is worse is that SSIS have added values in cells that should not be there. I get values in cells that shoud be empty!

Shortly: Only LTRIM([Column1]) as expression and "Derived Column" as Replace 'Column1' works OK.

But adding REPLACE-expression (i.e REPLACE(LTRIM([Column1]) , "," , ".") to this breaks things up

I'm aware that I could do this with SQL but this is not the point...

Any ideas?

BR Jompe

View 6 Replies View Related

Integration Services :: Derived Column Expression - Manipulate Data

Jun 4, 2015

I have one column CandidatePlaced (Data type Boolean).

Using data conversion i changed data type to DT_WSTR and then i used derived column to manipulate the data. Ex. 1 = "Yes" and 0 = "No"

[Candidate Placed ?] == "1" ? "Yes" : "No"

But at end of result i got all the columns as No. Some should be Yes.

View 7 Replies View Related

Newbie Case/search Question In Derived Column Expression Syntax

Feb 6, 2008



Hi everyone,

I am a newbie to SSIS and looking for answer to a simple search/replace style question.

I am building a derived column and looking for some expression syntax that can search for several strings in one column and set a value in a new column.

A bit like a case statement:

CASE ProductLine
WHEN 'R1' THEN 'Road'
WHEN 'M1' THEN 'Mountain'
WHEN 'T1' THEN 'Touring'
WHEN 'S1' THEN 'Other sale items'
ELSE 'Not for sale'
END,


The twist is that 'R1','M1','T1','S1' could feature anywhere in the string that is ProductLine.

Thanks for all your help,

regards,

Chris

View 12 Replies View Related

Newbie Search/replace Case Style Functionality In Derived Column Expression Syntax

Feb 6, 2008



Hi guys,

I am looking for some syntax to help me with a traditional search/replace style requirement. I am wanting to examine the contents of one column and populate another.

similar to this SQL case statement

CASE ProductLine
WHEN 'R1' THEN 'Road'
WHEN 'M1' THEN 'Mountain'
WHEN 'T1' THEN 'Touring'
WHEN 'S2' THEN 'Other sale items'
ELSE 'Not for sale'
END,


the twist is that R1, M1 etc. can appear anywhere in the ProductLine column.

thanks for any assistance you can provide.

regards,

Chris

View 1 Replies View Related

Altering Column Values Using Derived Column Component

Dec 21, 2007

Can anyone show how to alter the value in a column using DerivedColumn component when creating an SSIS package programatically.

View 4 Replies View Related

Integration Services :: How To Declare Multiple Derived Column In SSIS Derived Column Task

Jul 22, 2015

how to declare multiple derived columns in SSIS Derived Column Task in one attempt.as i have around 150 columns coming from Flat file. I had created the required Expression in Excel and now i want add those in derived column task but its allowing only 1 expression at a time.

View 4 Replies View Related

Reporting Services :: Expression For Parsing Out Values From XML Stored In Column

Nov 25, 2015

I'm new to SSRS (We are using SQL 2014) and have been asked to create a report from SharePoint list data. One of the columns in my list contains XML data. I need to pull three different fields from this XML. I see lots on using xml as a datasource but I'm using a SharePoint list as my datasource. Before I get too far down the wrong road I thought I'd ask here if there was a built in means of parsing XML or if I should look at using the Text Function under Common Functions in the Epression builder?

View 8 Replies View Related

Validating Date Via An Derived Filed/expression

Apr 8, 2008

How can I validate the date that is coming across in a 8 byte character field via an expression? It will either be a valid date or 0. If it is zero I want to make it a null.

View 1 Replies View Related

Is Pattern Matching Possible In Derived Columns Expression Syntax?

Feb 8, 2008



Chaps,

apologies for the drip-drip approach......

Is it possible to do pattern matching against a string using FINDSTRING or similar in a derived column expression without recourse to including 3rd party regexp style plugins?

I want to seach for a reference in a string which will have the format ANNNNNAAA ie. an alpha with a certain value followed by 5 digits followed by three alphas with specific values.

eg Z00001YYY or X00022HHH

thanks for your assistance,

regards,

Chris

View 9 Replies View Related

Integration Services :: Add New Column Based On Existing Column Using Derived Column Task?

Jul 28, 2015

I have a excel file which has a column called "Code" and their values are A,B,C,D,E,F,G,H.  I want to create a new column called "status" based on the values of "Code".

Code:

A
B
C
D
E
F
G
H

If A,C,E,G then "status" = "Active" else if  B,D,F,H then "Status" = "Inactive". I like to do it using "Derived Column".

View 4 Replies View Related

Cast/Convert Mmddyy In String To New DB_DATETIMESTAMP Column In Derived Column Transformation

Mar 5, 2007

Hi,
I have dates in "mmddyy" format coming from the sources and they are older dates of mid 80s like 082580 for instance.

When I cast it this way (DT_DBTIMESTAMP) Source_Date , It says ok but throws a runtime error.

When I hardcode a date in same format, (DT_DBTIMESTAMP) "082580" , It becomes red (an indication of syntax error) . Please note that we use double quotes in expressions in Derived Column Transformation; So an anticipation that using double quotes over single ones would be the syntax problem would be wrong.



Any help in this will sincerely be appreciated.


Thanks

View 7 Replies View Related

Derived Column Usage When Column Does Not Exist In Source (but Exists In Destination)

Sep 11, 2007





Posted - 09/10/2007 : 15:53:26



Hey all - got a problem that seems like it would be simple (and probably is : )

I'm importing a csv file into a SQL 2005 table and would like to add 2 columns that exist in the table but not in the csv file. I need these 2 columns to contain the current month and year (columns are named CM and CY respectively). How do I go about adding this data to each row during the transformation? A derived column task? Script task? None of these seem to be able to do this for me.

Here's a portion of the transformation script I was using to accomplish this when we were using SQL 2000 DTS jobs:


'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************

' Copy each source column to the destination column
Function Main()
DTSDestination("CM") = Month(Now)
DTSDestination("CY") = Year(Now)
DTSDestination("Comments") = DTSSource("Col031")
DTSDestination("Manufacturer") = DTSSource("Col030")
DTSDestination("Model") = DTSSource("Col029")
DTSDestination("Last Check-in Date") = DTSSource("Col028")
Main = DTSTransformStat_OK
End Function
***********************************************************
Hopefully this question isnt answered somewhere else, but I did a quick search and came up with nothing. I've actually tried to utilize the script component and the "Row" object, but the only properties I'm given with that are the ones from the source data.

thanks in advance!

jm

View 1 Replies View Related

Reporting Services :: Count Values In A Column Based Upon Distinct Values In Another Column In SharePoint List

Sep 7, 2015

We have SharePoint list which has, say, two columns. Column A and Column B.

Column A can have three values - red, blue & green.

Column B can have four values - pen, marker, pencil & highlighter.

A typical view of list can be:

Column A - Column B
red  - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red  - pen
blue - pencil
blue - highlighter
blue - pencil

We are looking to create a report from SharePoint List using SSRS which has following view:

                    red     blue   green
    pen            2       0      1
    marker       0       1      0
    pencil          1       3      0
    highlighter  1       1      1 

We tried Sum but not able to display in single row.

View 2 Replies View Related

Derive A Column Based On Other Derived Column In A Table .. Is It Possible ?

Mar 25, 2008


Table structure as follows



Employee

Empno empname salary
commission



I want to have an other employee table named employee_modified

Empno empname salary
commission derived_column1(salary+commission)
derived_column2(derived_column1 + xxxx) and so on derive other
columns based on the earlier derived columns)

Is that possible to do it.. or am I doing something wrong.



something like



Select empno , empname , salary , commission,

(salary + commission) as derived_colum1 ,

(derived_colum1 + xxxxx) as derived_colum2 ,

(derived_colum2 + xxxxx) as derived_colum3

into employee_modified from employee

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved