Conditional Column Name On Insert

Jan 21, 2006

I need to dynamic select a column in which insert a vale based on a
parameter value, I have this code, but it throws an incorrect syntax
error.

How do I dinamically select a column to insert based on a parameter?

Create PROCEDURE dbo.UpdateDetalleOT (
@eotId int,
)

insert into OT (
select Case
when @eotId = 1 THEN OTFechaBorrador
when @eotId = 2 THEN OTFechaAAsignar
end
) values ....

Best Regards
Fabio Cavassini
http://www.pldsa.com

View 9 Replies

Conditional Insert

Oct 29, 2006

Hi,

Originally had 2 tables, fullsource and ssotarget. I did the following extract on fullsource due to its irregular schema and inserted into ssotarget:

INSERT SSOTARGET (pin, address1, address2, address3, MemberNo, Tel1, Tel2, Tel3, Tel4, DOB, Email, IDNumber, Title, Initials, Firstname, Surname, STATUS)

SELECT
PIN,
MAX(CASE WHEN HEADER = 'ADDRESS DETAILS' AND PROPERTY = 'LINE 1' THEN VALUE ELSE NULL END) AS address1,
MAX(CASE WHEN HEADER = 'ADDRESS DETAILS' AND PROPERTY = 'LINE 2' THEN VALUE ELSE NULL END) AS address2,
MAX(CASE WHEN HEADER = 'ADDRESS DETAILS' AND PROPERTY = 'LINE 3' THEN VALUE ELSE NULL END) AS address3,
MAX(CASE WHEN HEADER = 'ACOMPANY' AND PROPERTY = 'Membership Number' THEN VALUE ELSE NULL END) AS MemberNo,
MAX(CASE WHEN HEADER = 'CONTACT DETAILS' AND PROPERTY = 'Preferred method of contact*' THEN VALUE ELSE NULL END) AS Tel1,
MAX(CASE WHEN HEADER = 'CONTACT DETAILS' AND PROPERTY = 'Tel number (o/h) e.g. 011 2690000' THEN VALUE ELSE NULL END) AS Tel2,
MAX(CASE WHEN HEADER = 'CONTACT DETAILS' AND PROPERTY = 'Mobile number e.g. 0821234567' THEN VALUE ELSE NULL END) AS Tel3,
MAX(CASE WHEN HEADER = 'CONTACT DETAILS' AND PROPERTY = 'Fax number e.g. 011 2691000' THEN VALUE ELSE NULL END) AS Tel4,
MAX(CASE WHEN HEADER = 'Date Of Birth' AND PROPERTY = 'eg. 04 Jan 1965' THEN VALUE ELSE NULL END) AS DOB,
MAX(CASE WHEN HEADER = 'Email address' AND PROPERTY = 'Email Address' THEN VALUE ELSE NULL END) AS Email,
MAX(CASE WHEN HEADER = 'ID Number' AND PROPERTY = 'ID Number' THEN VALUE ELSE NULL END) AS IDNumber,
MAX(CASE WHEN HEADER = 'Member Information' AND PROPERTY = 'Title' THEN VALUE ELSE NULL END) AS Title,
MAX(CASE WHEN HEADER = 'Member Information' AND PROPERTY = 'Initials' THEN VALUE ELSE NULL END) AS Initials,
MAX(CASE WHEN HEADER = 'Member Information' AND PROPERTY = 'Firstname' THEN VALUE ELSE NULL END) AS Firstname,
MAX(CASE WHEN HEADER = 'Member Information' AND PROPERTY = 'Surname' THEN VALUE ELSE NULL END) AS Surname,
MAX(CASE WHEN HEADER = 'ACOMPANY' AND PROPERTY = 'Membership Number' THEN STATUS ELSE NULL END) AS STATUS

FROM

FULLSOURCE

GROUP BY PIN

Now how can I reverse this to insert all the values back to tbl FULLSOURCE from maybe an updated SSOTARGET Tbl. FULLSOURCE looks like this:

SELECT [PIN]
,[SYSTEM]
,[Header]
,[Property]
,[Value]
,[Status]
,[ID_Header]
,[ID_Property]
FROM [fullsource]

View 1 Replies View Related

Conditional Insert

Oct 22, 2004

I need to do a conditional insert. This is what I have tried, it does not work.
What am I doing incorrectly?

IF (SELECT COUNT(*) FROM TBL1 INNER JOIN
TBL2 ON TBL1.MODEL_ID = TBL2.MODEL_ID INNER JOIN
TBL3 ON TBL1.PRO_TYPE = TBL3.TYPE INNER JOIN
TBL4 ON TBL1.PRO_SITE = TBL4.WHDESC) > 0

INSERT INTO TBL5
SELECT TBL1.PRO_SITE, TBL1.MODEL_ID,
TBL1.NUM_SLOTS, TBL1.TARGET_DAYS, GETDATE() AS Expr1,
TBL3.TYPE_ID, NULL AS Expr2, TBL1.NUM_SLOTS AS Expr3, NULL AS Expr4, NULL
AS Expr5, 0 AS RAMP
FROM TBL1 INNER JOIN
TBL2 ON TBL1.MODEL_ID = TBL2.MODEL_ID INNER JOIN
TBL3 ON TBL1.PRO_TYPE = TBL3.TYPE INNER JOIN
TBL4 ON TBL1.PRO_SITE = TBL4.WHDESC

UPDATE TBL5
SET TEAMS=0
GO
UPDATE TBL5
SET TEAMS = (SELECT NUM_SLOTS
FROM TBL6 R1
WHERE (R1.TEAM_ID = TBL5.TEAM_ID)
)
WHERE (TEAM_ID =
(SELECT TEAM_ID
FROM TBL6 R3
WHERE (R3.TEAM_ID = TBL5.TEAM_ID)))
GO
UPDATE TBL5
SET TOTALTEAMS = TEAMS + RAMP
GO

I need to do a conditional because sometimes the select that returns data contains no records.

Thanks...

View 1 Replies View Related

Conditional INSERT

Aug 8, 2006

Hello all. I am inserting data into a table from a linked server (.XLS file). One of the columns deals with dates. In the .XLS file some ofthe dates are invalid (either wrong format or wrond data). What i am looking for is how to insert only the rows where the dates are correct. My basic structure is:

INSERT INTO PENSIONS(EMPLOY_REF, CONTRIB_CHANGE)
SELECT CAST(EMPLOY_REF AS VARCHAR(10)),
CAST(CONTRIB_CHANGE AS DATETIME)
FROM mockdownload_dd...[Pensions$]

thanks people

View 8 Replies View Related

INSERT Based On Conditional

Jun 4, 2008

Good Afternoon,

I am new to MSSQL and am trying to write a complicated SQL statement that I'm having trouble with. Any help that anyone can offer is much appreciated!

Here is the problem I am tackling:

I have a list of about 5,000 members of our organization stored in the MemberList MSSQL table. I have a separate MSSQL table (CityList) that has approximately 500,000 resident of a city.

I am trying to find matches between MemberList and CityList for the purposes of figuring out which of our members are registered voters.

The tricky part of this problem, is that there is no unique ID (such as a social security number) that is present in each list. Accordingly, I have decided to created several types of matches:

1. NameDOBMatch: Where the FirstName, LastName & DOB fields in MemberList table match the same fields in CityList table.

2. NameAddressMatch: Where the LastName, FirstName & Address fields in the MemberList table match the same fields in the CityList table.

3. DoubleMatch: A combination of the first two matches (i.e. where the LastName, FirstName, DOB, & Address fields in the MemberList table match the same fields in the CityList table).

My goal is to "loop" through the MemberList and CityList tables and to add a new row to a third MSSQL table (MemberMatch) each time one of the aforementioned matches is found. The MemberMatch table has the following fields:
1. MatchID (key)
2. MemberID (Unique ID of member from MemberList table)
3. ResidentID (Unqiue ID of matching member from CityList table).
4. MatchType (value of NameDOBMatch, NameAddressMatch or DoubleMatch, depending on match type).

If anyone could help me create an SQL statement that would accomplish that, I would very much appreciate it!

Thanks,
Bryan

View 9 Replies View Related

Conditional Insert Trigger

Mar 14, 2006

Is there any way to create a "Conditional Insert Trigger"
My Scenario is this;
When a user adds an email address to the database, I want to look to see if the email address is like '%@acme-holdings%' and if it is then to change the value to 'Not allowed', otherwise to leave it alone and go ahead with inserting the original email address

TIA

ICW

View 9 Replies View Related

Conditional Insert Without Using Exec

Dec 27, 2007

I've got a case where I am bringing older data into a brand new DB. Unfortunately, there are several versions of the old DB and I won't know ahead of time which one I will be running against. The data's basicaly the same, the table names are the same, but the column schema is NOT.
Basiclly, I want to use a conditional on the Insert.

Code Block

CREATE TABLE TestTable
(Col1 int NULL,
Col2 varchar(50) NULL)
GO

IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG= 'TestDB'
AND TABLE_NAME ='TestTable'
AND COLUMN_NAME= 'Fred')

INSERT TestTable
(Col1, Col2, Fred)
VALUES
(1,'test', 'fredvalue')

This results in an "Unknown column" error.
What am I missing here and how can I accomplish it.
I know that I COULD build a SQL string and Exec it, but that is such a PITA and not very elegant.

Any help would be appreciated

View 3 Replies View Related

Conditional Bulk Insert

Sep 19, 2007

Hi

I am doing bulk insert as follows. The @lastUpdate, @filePath, @formatFile comes as a parametes to stored proc

INSERT INTO Categories
SELECT CategoryId, @LastUpdate FROM OPENROWSET
(

BULK @filePath ,
FORMATFILE = @formatFile,
FIRSTROW =2
)
AS a
This works fine for me.

But my new requirement is that i shouldn't insert the CategoryId if it exists

How can we have conditional bulk insert? i am using Bulk insert as the file might have millions of category Ids.

Please provide your inputs that executes much faster

Best Regards,
~Mohan Babu

View 1 Replies View Related

Conditional Insert In SQL Server 2000?

Oct 17, 2005

I need to do a conditional insert, and I am afraid that if I do it in 2 steps it will create bugs...I have a table called customers and a table called lockscustomers has 3 fields CustomerID, FirstName, and LastNamelocks has fields LockID, CustomerID, UserID, SessionID, and TimeStampI need to check if there are any records in Locks for a CustomerID and if there are none, I need to insert One.This is going to tell my application that this Customer record is locked so no one else can edit it, until the Lock record is deleted.I just don't want to make 2 trips, One to check if the Lock exists, then One to insert the Lock, since this could allow 2 locks to be created if the timing is correct.Any ideas or comments?

View 1 Replies View Related

Best Practice For Conditional Insert Else Select?

Aug 7, 2007

I have several places where I need to get the id (primary key) of a resource, inserting a row if the resource does not exist (i.e. an artificial key to be used as an FK for another table). I should probably change this varchar key lookup to use a hash index, but that is beside the point.

So the table is essentially like:
CREATE TABLE MyLookup(id int identity primary key nonclustered, mykey varchar(256));
CREATE CLUSTERED INDEX mylookup_cidx_mykey ON MyLookup(mykey);

I see two main approaches for how I can do my get-id-with-insert-if-needed.

(Approach 1)
DECLARE @id INT;
SELECT @id = id FROM MyLookup WHERE mykey = 'some key value';
IF (@id is null)
BEGIN

INSERT MyLookup ('some key value');
SET @id = SCOPE_IDENTITY();
END

(Approach 2)
DECLARE @id INT;
INSERT MyLookup SELECT 'some key value' WHERE NOT EXISTS (SELECT id FROM MyLookup WHERE mykey = 'some key value');
IF (@@ROWCOUNT = 0)

SELECT @id = id FROM MyLookup WHERE mykey = 'some key value';
ELSE

SET @id = SCOPE_IDENTITY();

From some quick tests in profiler, approach 2 seems to be a bit faster and have lower resource utilization. But I'm not sure if it maybe takes some more aggressive locks even in the unnecessary case where the mykey row value of 'some key value' already exists. Approach 2 also looks cleaner to me, but I don't mind a bit of extra code if it gives me better scalability through less lock contention.

Any tip on what is considered the best practice for a conditional insert like this, or a tip on how to get detailed lock info for a query? The lock info for profiler was all greek to me, it just had a hex value with each lock acquired/released, so I have no idea what it was telling me. Is my only solution to just run exhaustive tests and look at the perf numbers from the black box?

View 1 Replies View Related

Bulk Insert + Conditional Split

Mar 12, 2008

Hello all,

I just wanted to know whether is it possible to use Bulk Insert and Conditional Split together for one transformation.

Regards,
Kapadia Shalin P.

View 3 Replies View Related

Conditional Insert Based On MAX Number Of Field

Dec 15, 2005

Hi!

I have a table called DB1 that contains this:

MID
IIN
NUM_EVENTS
DATE

MID, IIN and NUM_EVENTS are composite keys. and only NUM_EVENTS get incremented. All records start with NUM_EVENTS = 1.How can I create a query that only displays those records that only NUM_EVENTS = 1 meaning their still on the first stage of processing?

View 6 Replies View Related

T-SQL (SS2K8) :: Selection With Conditional Insert Union

Apr 4, 2014

In this selection when there is particular segment found in the selection then need to add 2 records per segment otherwise just selected results

--Drop table #list
CREATE TABLE #LIST ( email varchar(20),Segment varchar(20), Addr1 varchar(20), City varchar(20),ST varchar(20), Code int)
--Drop table #subject
CREATE TABLE #Subject (Segment varchar(20), Fname varchar(20), LName varchar(20))

[Code] ....

---Selection results
SELECT L.Segment , l.email, l.Addr1 , l.City ,l.ST, l.code, s.Fname , s.LName into #expectedresult FROM #LIST L JOIN #Subject S ON l.Segment =s.Segment

--Now here what i am trying to get, union the below two record to the above "selection Results" for only segment ='VEC_BAL' or segment = 'PPC_BAL'

--2 records
SELECT l.segment , l.email , Addr1, City, ST, 999 as Code,'' fname, ''lname from #LIST l where email ='mmm@m.com'
SELECT l.segment , l.email , Addr1, City, ST, 888 as Code, '' fname, ''lname from #LIST l where email ='lrk@m.com'

--- expected results please execute below block ( per each segment 2 records will insert if there is segment ='VEC_BAL' or 'PPC_BAL' ). If there is no Vec_Bal or PPC BAL then no additional inserts, if there is only VEC_BAL no PPC_BAL then only 2 records insert
SELECT L.Segment , l.email, l.Addr1 , l.City ,l.ST, l.code, s.Fname , s.LName into #expctresults1 FROM #LIST L JOIN #Subject S ON l.Segment =s.Segment
union

[Code] .....

View 3 Replies View Related

Conditional Insert Based On MAX Number Of Field

Dec 15, 2005

View 3 Replies View Related

Conditional On A Derived Column

Jan 27, 2006

Hi everyone,

I have a quizzing application where users log in, answer questions, and are ranked relative to each other. For this final ranking, I calculate their score using this formula -

score = (correct Qs answered) * 150 / (total Qs answered) + (total Qs answered)

The SQL query that i use to get this info is -

Code:

SELECT TOP 50 username, (sum(correct) * 150 / count(1) + count(1)) AS score, count(1) as totalq
FROM questionsstats
GROUP BY username
ORDER BY score DESC

This works just fine.

However, on top of this I need to put an additional restriction that only users who have at least answered 20 questions be counted in. How can I do this? Adding a simple 'WHERE totalq > 20' does not work. I get the error "Invalid column name 'totalq'.".

Surely there must be a simple way to do this?

Thanks.

View 2 Replies View Related

Conditional Display Of A Column

Apr 28, 2008

Would like to know if it is possiblefor any of you to suggest how to conditionally diaplay a Column based on the values of the other columns.
Here's my query
SELECT
[ProductionDay]
,DATEPART(wk,StartTime) AS WeekCount
,(AVG([RNTDuration])) AS AvgRNT
,MIN([RNTDuration]) As LeastRNT
,MAX([RNTDuration]) As WorstRNT
,dbo.fn_MinimumRNT_sp([ProgramNo],[Machine],[ProductionDay]) AS BestRNT
,dbo.fn_MinimumRNT(SubAssemblyNo,[Machine]) AS BestRNT_txt
,SUM([LoadingDuration]) AS LoadingTime
,[ProgramNo] AS ProgramNo
,COUNT(RNTID) AS PartsCount
,[SubAssemblyNo]
,[Sheetsize]
FROM [RPMS].[dbo].[List_MachineShopRNT]
where Machine = @iMachine and dateadd(month, datediff(month, 0, [StartTime]),0) = @dtMonthStartDate
Group by DATEPART(wk,StartTime), [ProductionDay],[Machine],[ProgramNo],[SubAssemblyNo],[Sheetsize]

Here I would like to Run the function dbo.fn_MinimumRNT_sp or dbo.fn_MinimumRNT if the ProgramNo is 100

Thank you

View 3 Replies View Related

Conditional Column Selection

Sep 13, 2007

I have one table with 6 columns (Entrance1,Exit1,Entrance2,Exit2,Entrance3,Exit3).
This columns can take null values.
I want to select just the last one of this columns that is not null.

View 11 Replies View Related

New Column With Conditional Values

Jan 29, 2008

Does anyone know how to combine to columns into one using sql?

For example

Say I want to create a column3:

column1 _____ column2
------------------------------
primary _____ secondary
primary _____ [Non Applicable]
tertiary _____ quad..something

and IF column1 = primary AND column2 = secondary THEN column3 = newvalue

This is easy is access (and excel) but it doesn't seem to work in the same way in sql.

View 4 Replies View Related

Conditional Column Formatting

Jul 19, 2007

Hi Everyone,

I am trying to hide a column in a matrix table. I have no trouble using conditional formatting to control the visibility of the column, but when I hide the column the main row in the matrix does not shrink. Therefore, I have a big gap�� My thought was to use conditional formatting to control the column width of the main matrix row. Listed below is the expression that I used. Can anyone help me with the error message or recommend a better solution?

=IIF(Fields!FiscalYear.Value = IIF(Month(TODAY()) = 10-12,Year(TODAY()) ,Year(Today())-1), 0.625in, 0.25in) cannot be parsed as a unit because it does not contain numeric values. Examples of valid unit strings are "1pt" and ".5in".

Regards,

A.Akin

View 1 Replies View Related

Conditional Column Select

Aug 9, 2006

Is there a statement to change the column in a select clause?

For example:

select Groups, sum ((if group like '%total%' then select TotalHours else if group like '%Direct%' then select DirectHours endif endif)) as Hours, count(*) from tblGroups. group by Groups.

View 3 Replies View Related

Conditional Column Mapping

May 25, 2007

Hey all! I have a bunch of questions, but let's start with this one:

Incoming from my flat file, I have two columns:

employee_id
dept_id

These indicate who did the work, and for which department (people can work for more than one department). In my destination table, I have the following two columns:

employee_id_sales
employee_id_wrhs

I want to map the employee id either to employee_id_sales or employee_id_wrhs, depending on the dept_id from the flat file.

How do I specify conditional column mapping?

I'm really new to SSIS, so I might be missing something obvious.

Thanks!

-- Jim

View 3 Replies View Related

Joining And Conditional Column Data Help

Dec 21, 2006

I have 2 table that I want to join and output a row on a condition that one of the records have a null in the field. Heres what I have.employee table (empid, name)tasks table (taskid, empid, taskname, resolution)If the resolution is null than I want it to be accounted for in each employee record. Heres my query so far that joins the 2 tables and accounts for each employee and counts each task they have. I need another column that counts the tasks.resolution's null values for each employee but cant figure it out. Thanks for any help!SELECT e.empid, e.name, COUNT(t.ID) as 'tcount'FROM tasks t RIGHT JOIN employee e ON c.empid = t.empidGROUP BY e.empid, e.nameorder by 'tcount' desc

View 3 Replies View Related

Inner Join Based On Conditional Column

Mar 5, 2014

I Have Table Called 'Sales' and 'Voucher',I Need To Show Each Customer ""Dueamount"" Details Based Upon Customer Paid in 'Voucher' Table But One thing I have Not Maintained Transaction History For Customer in 'Sales' Table Means I Have Column named "CreditAmount" in 'Sales' and Column Named "VoucherAmount" in 'Voucher' ,For every transaction I am updating Column named "CreditAmount" in 'Sales', So finally 'Dueamount' Must be calculated according to "VoucherAmount" of customer in 'Voucher' Table....

Sales Table:

BillMasterId BillDate CustomerId NetAmount CreditAmount

26 03/03/2014 101 1000 1000

My Query:
SELECT CONVERT(varchar,BillDate,103) as BillDate,isnull(NetAmount,0) as BillAmount, case when VoucherAmount != 0 then sum(VoucherAmount)else 0 end as'AmountReceived',case when CreditAmount !=0 then CreditAmount else 0 end as 'DueAmount' from Voucher INNER join Sales on CustomerId=CustomerID and BillMasterID=BillMasterID WHERE CONVERT(varchar,BillDate,103)='03/03/2014' AND CustomerId=101

My Output:

BillDate BillAmount AmountReceived DueAmount

03/03/2014 1000 0 0
03/03/2014 1000 500 0
03/03/2014 1000 300 0
03/03/2014 1000 200 0

Exact Output:

BillDate BillAmount AmountReceived DueAmount

03/03/2014 1000 0 1000
03/03/2014 1000 500 500
03/03/2014 1000 300 200
03/03/2014 1000 200 0

View 7 Replies View Related

Conditional IF In A Derived Column Transform

Apr 12, 2006

HI, I was wondering if there is a possibility to use a confitional if like this:

IF(ISNULL(mycolumn value, "new value if null", mycolumnvalue)

into a derived column transform to infer a value to a null column value. I do know I can do it using a script component by it would be simpler to do by using an expression.

Thank you,

Ccote

View 3 Replies View Related

SSIS Conditional Column Mapping

Aug 3, 2007

I have a condition where if column5 is equal to 1 then put column6 into the destination column "dest6", if it is not equal to 1 then put column6 in destination column "dest7"

What is the best way to do this in SSIS?

If I have to use the conditional split then do I have to copy my complete mappings, exact change this one column?

Thank for the help this mapping will take me a long time!

View 5 Replies View Related

Conditional Color In Matrix Subtotal Column

Jun 6, 2006

I am using matrix in my report with calculated fields, for example I have 3 columns : Actual amount, Budget and Variance. Variance should be in Red if it is negative. I can set up an expression to change the color on the row level, but not in Subtotal ot Total row. I cannot use sum of Fields! values, because Budget amount is also calculated field based on the "Category" value which is columns group on the matrix.

So anyway, if I am trying to reference ReportItems!Variance.Value in Subtotal Level, it gives me an error about group scope.

How can I access the cells values in subtotal and total group levels? If anybody knows any tricks for this fairly simple task?

Thanks for your help.

Olga

View 15 Replies View Related

Conditional Split For Insert Or Update Cause Dead Lock On Database Level

Aug 28, 2007

Hi

I am using conditional split Checking to see if a record exists and if so update else insert. But this cause database dead lock any one has suggestion?

Thanks

View 7 Replies View Related

Integration Services :: Conditional Concatenation In Derived Column?

Aug 3, 2015

I have 10 columns i.e from Segment1 to Segment10. I need to concatenate it with ".". All 10 segments can be null. If any of the segment is null i do not want to show ".". This is the expression I am using

(DT_STR,100,1252)((ISNULL(SEGMENT1) ? "" : "." + SEGMENT1) + (ISNULL(SEGMENT2) ? "" : "." + SEGMENT2) + (ISNULL(SEGMENT3) ? "" : "." + SEGMENT3) + (ISNULL(SEGMENT4)
? "" : "." + SEGMENT4) + (ISNULL(SEGMENT5) ? "" : "." + SEGMENT5) + (ISNULL(SEGMENT6) ? "" : "." + SEGMENT6) + (ISNULL(SEGMENT7) ? "" : "." + SEGMENT7)
+ (ISNULL(SEGMENT8) ? "" : "." + SEGMENT8) + (ISNULL(SEGMENT9) ? "" : "." + SEGMENT9) + (ISNULL(SEGMENT10) ? "" : SEGMENT10))

But if any of the column contains Null or Blank I get ...... I do not want to show these .... if any segment is null.

What mistake am I doing in the above expression?

View 4 Replies View Related

Integration Services :: Conditional Split Column Less Than Today

Oct 29, 2015

I'm trying to write a conditional split where I want to bring in only records where the date is less than today, but my problem is that I can't simply do this Column < GetDate() because if something comes in today, it takes the time into account and it will bring that record for today. You can do this in SQL, but I'm not sure how to do that in SSIS

SELECTCAST(CONVERT(char(8),GetDate(),112)ASdatetime)

View 2 Replies View Related

Conditional Number Format On Specific Matrix Column Group

Feb 6, 2008

I'll try to make this simple. I'm on SSRS 2005 and I have a report with a matrix object that has one row group and one column group. I need to switch the number format only for values where the column group has a specific value.

For example, here are the records in the table:
Customer, Type, Amount
Customer1, Revenue, -100
Customer2, Cost, 60
Customer1, Revenue, -200
Customer2, Cost, 125

By default the matrix object shows the following (the total comes from the standard subtotal on the column group):
Revenue Cost Total
Customer1 -100 60 -40
Customer2 -200 125 -75

But the users need the report to look like this, with all positives (why, oh why?! ):
Revenue Cost Total
Customer1 100 60 40
Customer2 200 125 75

I was able to use the inscope function to switch the signs of the Total numbers. But now I need to switch the signs of the Revenue column from negative to positive (and vice versa), without affecting the signs of the Cost column. It's strange to me because I CAN switch the signs for a specific row group (changing Customer1's number format, without affecting Customer2's format) using something like this:

=iif(Fields!Customer.Value = "Customer1", "($#,###.#0); $#,###.#0", "$#,###.#0; ($#,###.#0)")

But a similar expression specifying a column group value does not work, because the report seemingly doesn't recognize the value of the column group at all no matter what I do:

=iif(Fields!Type.Value = "Revenue", "($#,###.#0); $#,###.#0", "$#,###.#0; ($#,###.#0)")

The other reason why this is strange is that I've done drill-through reports off of matrix objects where specific column group values (the ones clicked on) can be passed into the drill-through report parameters. So it recognizes the column group values upon drill-through, but not for formatting?

How else can I do this? I must be missing something here. Thanks.

View 6 Replies View Related

Derived Column Transformations - DT_STR Operand Cannot Be Used With The Conditional Operation

Sep 16, 2006

Hi all I am trying to convert the string "(null)" in the [PASSWORD] column of my table to an actual NULL value. I have tried to use two different forms of a conditional operator to achieve this end. However I am getting the below errors both can be summed up with the following statement.

DT_STR operand cannot be used with the conditional operation. The expression directly below however is using a type DT_I4 in the conditional clause as this is what FINDSTRING returns. Hence the equivalencey test to the literal integer 0. So I must say I am somewhat confused by this. Does anyone know why neither of the below statements are working?

Also is there an easy way to accomplish what I am trying to do - convert the string "(null)" in the [PASSWORD] column of my table to an actual NULL value?

FINDSTRING([PASSWORD], "(null)", 1) == 0 ? [PASSWORD] : NULL(DT_STR, 255, 1252)

Error at Administrator Data Flow Task [Derived Column [1985]]: For operands of the conditional operator, the data type DT_STR is supported only for input columns and cast operations. The expression "FINDSTRING(PASSWORD,"(null)",1) == 0 ? PASSWORD : NULL(DT_STR,255,1252)" has a DT_STR operand that is not an input column or the result of a cast, and cannot be used with the conditional operation. To perform this operation, the operand needs to be explicitly cast with a cast operator.

LOWER( TRIM( [PASSWORD] ) ) != "(null)" ? [PASSWORD] : NULL(DT_STR, 255, 1252)

Error at Administrator Data Flow Task [Derived Column [1985]]: For operands of the conditional operator, the data type DT_STR is supported only for input columns and cast operations. The expression "LOWER(TRIM(PASSWORD)) != "(null)" ? PASSWORD : NULL(DT_STR,255,1252)" has a DT_STR operand that is not an input column or the result of a cast, and cannot be used with the conditional operation. To perform this operation, the operand needs to be explicitly cast with a cast operator.

View 4 Replies View Related

Matrix Report:Adding Average Column/Sorting Based On Last Month/Conditional Formating

Aug 2, 2007

I have a matrix report with 2 column SaleAmount and ProfitAmounts by Month like

Sale Profit
Dealer 5/1/2007 6/1/2007 7/1/2007 5/1/2007 6/1/2007 7/1/2007
A 100 200 300 20 25 15
B 200 250 50 30 45 19

how can i do following 3 things

1)Add Total column for Sale and Average column for Profit
2)Sort report by lastMonth of Sale (here 7/1/2007) High to low
3)if last month of sale(here 7/1/2007) is less than second last month here (6/1/2007) whole row should be red

thanks

View 1 Replies View Related

OPENROWSET (INSERT) Insert Error: Column Name Or Number Of Supplied Values Does Not Match Table Definition.

Mar 24, 2008

Is there a way to avoid entering column names in the excel template for me to create an excel file froma dynamic excel using openrowset.
I have teh following code but it works fien when column names are given ahead of time.
If I remove the column names from the template and just to Select * from the table and Select * from sheet1 then it tells me that column names donot match.
Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition.
here is my code...
SET @sql1='select * from table1'SET @sql2='select * from table2'
IF @File_Name = ''    Select @fn = 'C:Test1.xls'    ELSE    Select @fn = 'C:' + @File_Name + '.xls'        -- FileCopy command string formation    SELECT @Cmd = 'Copy C:TestTemplate1.xls ' + @fn
-- FielCopy command execution through Shell Command    EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT        -- Mentioning the OLEDB Rpovider and excel destination filename    set @provider = 'Microsoft.Jet.OLEDB.4.0'    set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn
exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT *    FROM [Sheet1$]'')     '+ @sql1 + '')        exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT *    FROM [Sheet2$]'')     '+ @sql2 + ' ')

View 4 Replies View Related