Extract Data From A Column

Hi ,
I have to extract City,State and Zip Code from the below
column and insert it separately in 3 columns. how can i
write my select statements so i can get
City=North Bergen
State=NJ
Zip =07057
Site
--
NORTH BERGEN, NJ, 07057
Springfield, IL, 62704
MANASQUAN, NJ, 08736
BLOOMINGTON, MN, 55425As long as you have a uniform delimiter, you can use the following proc to
parse the tokens and load them appopriately.
You tweak the stored proc to handle the tokens as you wnat.
--
HTH
Satish Balusa
Corillian Corp.
Create Procedure sp_ParseArrayAndLoadTable ( @.Array varchar(1000),
@.Separator char(1) ,
@.LoadTableName sysname OUT
)
AS
BEGIN
SET NOCOUNT ON
-- @.Array is the array we wish to parse
-- @.Separator is the separator charactor such as a comma
DECLARE @.separator_position int -- This is used to locate each separator
character
DECLARE @.array_value varchar(1000) -- this holds each array value as it is
returned
-- For my loop to work I need an extra separator at the end. I always look
to the
-- left of the separator character for each array value
SET @.array = @.array + @.separator
-- Loop through the string searching for separtor characters
WHILE PATINDEX('%' + @.separator + '%' , @.array) <> 0
BEGIN
-- patindex matches the a pattern against a string
SELECT @.separator_position = PATINDEX('%' + @.separator + '%' , @.array)
SELECT @.array_value = LEFT(@.array, @.separator_position - 1)
-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @.array_value holds the value of this element of the array
-- Do the job whatever you wanted to do
SELECT Array_Value = @.array_value
-- This replaces what we just processed with and empty string
SELECT @.array = STUFF(@.array, 1, @.separator_position, '')
END
SET NOCOUNT OFF
END
GO
"Mohamadi.Slatewala@.wellsfargo.com" <anonymous@.discussions.microsoft.com>
wrote in message news:22f301c3e12f$9d1dbc30$a301280a@.phx.gbl...
> Hi ,
> I have to extract City,State and Zip Code from the below
> column and insert it separately in 3 columns. how can i
> write my select statements so i can get
> City=North Bergen
> State=NJ
> Zip =07057
> Site
> --
> NORTH BERGEN, NJ, 07057
> Springfield, IL, 62704
> MANASQUAN, NJ, 08736
> BLOOMINGTON, MN, 55425
>

Extract Data From A Column

Hi ,
I have to extract City,State and Zip Code from the below
column and insert it separately in 3 columns. how can i
write my select statements so i can get
City=North Bergen
State=NJ
Zip =07057
Site
--
NORTH BERGEN, NJ, 07057
Springfield, IL, 62704
MANASQUAN, NJ, 08736
BLOOMINGTON, MN, 55425As long as you have a uniform delimiter, you can use the following proc to
parse the tokens and load them appopriately.
You tweak the stored proc to handle the tokens as you wnat.
--
HTH
Satish Balusa
Corillian Corp.
Create Procedure sp_ParseArrayAndLoadTable ( @.Array varchar(1000),
@.Separator char(1) ,
@.LoadTableName sysname OUT
)
AS
BEGIN
SET NOCOUNT ON
-- @.Array is the array we wish to parse
-- @.Separator is the separator charactor such as a comma
DECLARE @.separator_position int -- This is used to locate each separator
character
DECLARE @.array_value varchar(1000) -- this holds each array value as it is
returned
-- For my loop to work I need an extra separator at the end. I always look
to the
-- left of the separator character for each array value
SET @.array = @.array + @.separator
-- Loop through the string searching for separtor characters
WHILE PATINDEX('%' + @.separator + '%' , @.array) <> 0
BEGIN
-- patindex matches the a pattern against a string
SELECT @.separator_position = PATINDEX('%' + @.separator + '%' , @.array)
SELECT @.array_value = LEFT(@.array, @.separator_position - 1)
-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @.array_value holds the value of this element of the array
-- Do the job whatever you wanted to do
SELECT Array_Value = @.array_value
-- This replaces what we just processed with and empty string
SELECT @.array = STUFF(@.array, 1, @.separator_position, '')
END
SET NOCOUNT OFF
END
GO
"Mohamadi.Slatewala@.wellsfargo.com" <anonymous@.discussions.microsoft.com>
wrote in message news:22f301c3e12f$9d1dbc30$a301280a@.phx.gbl...

quote:

---

> Hi ,
> I have to extract City,State and Zip Code from the below
> column and insert it separately in 3 columns. how can i
> write my select statements so i can get
> City=North Bergen
> State=NJ
> Zip =07057
> Site
> --
> NORTH BERGEN, NJ, 07057
> Springfield, IL, 62704
> MANASQUAN, NJ, 08736
> BLOOMINGTON, MN, 55425
>

---

Extra column in matrix

Hello,

I'm trying to make a report with the following layout:

Car Sales 2004 2005 Var %
Total 10 20 50%
Green 7 14 50%
Red 3 6 50%

I'm using a matrix and the data is coming from a cube. One dimension called 'Years' is used to fill the matrix columns and the data corresponding to the car sales is filled by a measure called 'Sales'.
I've built part of the example but I can't add the final column ( the VAR% column).
If I try to add a static column in the end it apears only one year.
The behavior that I would like to have is simillar to the situation when we add the Subtotal column. Although, instead the sum() made by subtotal I would like to calculate the variation percentage between years.
Is there any way to add a final column into a matrix avoiding the problem that I'm having or maybe change the behavior of Subtotal column?

Thanks and best regards.

vjn

I have a very similar need. I have two levels of columns and two row groups. I need to calculate a percentage difference between the 2nd level column values:

Month

Quarter

Year

Mar-06

Mar-07

Q1 2006

Q1 2007

2006

2007

Company

Product

$1,000

$2,000

$3,000

$6,000

$12,000

$24,000

Change above to this:

Month

Quarter

Year

Mar-06

Mar-07

% Change

Q1 2006

Q1 2007

% Change

2006

2007

% Change

Company

Product

$1,000

$2,000

$3,000

$6,000

$12,000

$24,000

I need to calculate a % Change column. I can't seem to find a way to do this. I can add a subtotal easily enough but how can I do a custom calculation? This is based on a sql query not an analysis cube. I had to use a matrix to "cross tab" the data. The data is not in cross tab format at the source otherwise I would just calculate the % in the query.

Can anyone help?

Thanks!

|||Right click in the cell where you need to add the extra column to its right and select Add Column from the popup menu. HTH.|||

Thanks for the reply however that does not get me to what I need. Adding a column simply changes it to this:

Month

Quarter

Year

Mar-06

Mar-07

Q1 2006

Q1 2007

2006

2007

Company

Product

1000

2000

3000

6000

12000

24000

I need the % change from Mar-06 to Mar-07, Q1 2006 to Q1 2007, and 2006 to 2007.

|||

I also need to be able to add an extra column to my matrix - I've searched high and low on the net and I cant seem to find the answer anywhere.

I have grouped data which displays as it should and I can get the SubTotal part for my Dailys to run - I need to add a total for MTD and YTD (which is a part of the SQL Data) after the subtotal - is there a straight forward way of doing this?

This is an example of how I want my report to look (I can already achieve the bits highlighted bold)

Daily Data

Commission (GroupData cell 1)

Interest (GroupData CELL 2)

GroupData etc …

SUBTOTAL (DTD)

MTD TOTAL

YTD TOTAL

Book

Currency

=sum(Fields!DTD.VALUE)

........

=sum(Fields!MTD.VALUE)

=sum(Fields!YTD.VALUE)

and this is an example of the data being returned by my Sproc

Book

CCY

GroupedData

DTD

MTD

YTD

ABC

GBP

Commission

￡0.01

￡0.09

-￡0.10

ABC

GBP

Interest

￡0.02

￡0.29

￡0.11

ABC

GBP

Brokerage

￡0.12

￡0.06

￡0.20

Like the person above - if i use the "Add Column" functionality - I get a repeat of the MTD and YTD under each of the groupData cells - where as i only need it as a summary after the subtotal.

Daily Data

Grouped Data CELL 1

Grouped Data CELL 2

Book Name

Currency

DTD TOTAL

MTD TOTAL

YTD TOTAL

DTD TOTAL

MTD TOTAL

YTD TOTAL

=sum(Fields!DTD.VALUE)

Does anyone out there know how to do it?

|||

Hi Everyone,

I solved a similar issue by adding a new column to the matrix and using this expression to find the percentage of change from month to month.

=(Sum(Fields!TotalSales.Value)-(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value)))/(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value))

NOTE: matrix1_FiscalMonth is my row group name

To find the percentage of change take the “after” amount, minus the “before” amount and divide that result by the before amount. i.e.(60-50)/50 = 20%

Regard,

AA

"Small things done consistently in strategic places create major impact"

sql

Extra column in matrix

Hello,

I'm trying to make a report with the following layout:

Car Sales 2004 2005 Var %
Total 10 20 50%
Green 7 14 50%
Red 3 6 50%

I'm using a matrix and the data is coming from a cube. One dimension called 'Years' is used to fill the matrix columns and the data corresponding to the car sales is filled by a measure called 'Sales'.
I've built part of the example but I can't add the final column ( the VAR% column).
If I try to add a static column in the end it apears only one year.
The behavior that I would like to have is simillar to the situation when we add the Subtotal column. Although, instead the sum() made by subtotal I would like to calculate the variation percentage between years.
Is there any way to add a final column into a matrix avoiding the problem that I'm having or maybe change the behavior of Subtotal column?

Thanks and best regards.

vjn

I have a very similar need. I have two levels of columns and two row groups. I need to calculate a percentage difference between the 2nd level column values:

Month Quarter Year Mar-06 Mar-07 Q1 2006 Q1 2007 2006 2007 Company Product $1,000 $2,000 $3,000 $6,000 $12,000 $24,000

Change above to this:

Month Quarter Year Mar-06 Mar-07 % Change Q1 2006 Q1 2007 % Change 2006 2007 % Change Company Product $1,000 $2,000 $3,000 $6,000 $12,000 $24,000

I need to calculate a % Change column. I can't seem to find a way to do this. I can add a subtotal easily enough but how can I do a custom calculation? This is based on a sql query not an analysis cube. I had to use a matrix to "cross tab" the data. The data is not in cross tab format at the source otherwise I would just calculate the % in the query.

Can anyone help?

Thanks!

|||Right click in the cell where you need to add the extra column to its right and select Add Column from the popup menu. HTH.|||

Thanks for the reply however that does not get me to what I need. Adding a column simply changes it to this:

Month Quarter Year Mar-06 Mar-07 Q1 2006 Q1 2007 2006 2007 Company Product 1000 2000 3000 6000 12000 24000

I need the % change from Mar-06 to Mar-07, Q1 2006 to Q1 2007, and 2006 to 2007.

|||

I also need to be able to add an extra column to my matrix - I've searched high and low on the net and I cant seem to find the answer anywhere.

I have grouped data which displays as it should and I can get the SubTotal part for my Dailys to run - I need to add a total for MTD and YTD (which is a part of the SQL Data) after the subtotal - is there a straight forward way of doing this?

This is an example of how I want my report to look (I can already achieve the bits highlighted bold)

Daily Data Commission (GroupData cell 1) Interest (GroupData CELL 2) GroupData etc … SUBTOTAL (DTD) MTD TOTAL YTD TOTAL Book Currency =sum(Fields!DTD.VALUE) ........ =sum(Fields!MTD.VALUE) =sum(Fields!YTD.VALUE)

and this is an example of the data being returned by my Sproc

Book CCY GroupedData DTD MTD YTD ABC GBP Commission ￡0.01 ￡0.09 -￡0.10 ABC GBP Interest ￡0.02 ￡0.29 ￡0.11 ABC GBP Brokerage ￡0.12 ￡0.06 ￡0.20

Like the person above - if i use the "Add Column" functionality - I get a repeat of the MTD and YTD under each of the groupData cells - where as i only need it as a summary after the subtotal.

Daily Data Grouped Data CELL 1 Grouped Data CELL 2 Book Name Currency DTD TOTAL MTD TOTAL YTD TOTAL DTD TOTAL MTD TOTAL YTD TOTAL =sum(Fields!DTD.VALUE)

Does anyone out there know how to do it?

|||

Hi Everyone,

I solved a similar issue by adding a new column to the matrix and using this expression to find the percentage of change from month to month.

=(Sum(Fields!TotalSales.Value)-(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value)))/(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value))

NOTE: matrix1_FiscalMonth is my row group name

To find the percentage of change take the “after” amount, minus the “before” amount and divide that result by the before amount. i.e.(60-50)/50 = 20%

Regard,

AA

"Small things done consistently in strategic places create major impact"

Extra column in matrix

Hello,

I'm trying to make a report with the following layout:

Car Sales 2004 2005 Var %
Total 10 20 50%
Green 7 14 50%
Red 3 6 50%

I'm using a matrix and the data is coming from a cube. One dimension called 'Years' is used to fill the matrix columns and the data corresponding to the car sales is filled by a measure called 'Sales'.
I've built part of the example but I can't add the final column ( the VAR% column).
If I try to add a static column in the end it apears only one year.
The behavior that I would like to have is simillar to the situation when we add the Subtotal column. Although, instead the sum() made by subtotal I would like to calculate the variation percentage between years.
Is there any way to add a final column into a matrix avoiding the problem that I'm having or maybe change the behavior of Subtotal column?

Thanks and best regards.

vjn

I have a very similar need. I have two levels of columns and two row groups. I need to calculate a percentage difference between the 2nd level column values:

Month

Quarter

Year

Mar-06

Mar-07

Q1 2006

Q1 2007

2006

2007

Company

Product

$1,000

$2,000

$3,000

$6,000

$12,000

$24,000

Change above to this:

Month

Quarter

Year

Mar-06

Mar-07

% Change

Q1 2006

Q1 2007

% Change

2006

2007

% Change

Company

Product

$1,000

$2,000

$3,000

$6,000

$12,000

$24,000

I need to calculate a % Change column. I can't seem to find a way to do this. I can add a subtotal easily enough but how can I do a custom calculation? This is based on a sql query not an analysis cube. I had to use a matrix to "cross tab" the data. The data is not in cross tab format at the source otherwise I would just calculate the % in the query.

Can anyone help?

Thanks!

|||Right click in the cell where you need to add the extra column to its right and select Add Column from the popup menu. HTH.|||

Thanks for the reply however that does not get me to what I need. Adding a column simply changes it to this:

Month

Quarter

Year

Mar-06

Mar-07

Q1 2006

Q1 2007

2006

2007

Company

Product

1000

2000

3000

6000

12000

24000

I need the % change from Mar-06 to Mar-07, Q1 2006 to Q1 2007, and 2006 to 2007.

|||

I also need to be able to add an extra column to my matrix - I've searched high and low on the net and I cant seem to find the answer anywhere.

I have grouped data which displays as it should and I can get the SubTotal part for my Dailys to run - I need to add a total for MTD and YTD (which is a part of the SQL Data) after the subtotal - is there a straight forward way of doing this?

This is an example of how I want my report to look (I can already achieve the bits highlighted bold)

Daily Data

Commission (GroupData cell 1)

Interest (GroupData CELL 2)

GroupData etc …

SUBTOTAL (DTD)

MTD TOTAL

YTD TOTAL

Book

Currency

=sum(Fields!DTD.VALUE)

........

=sum(Fields!MTD.VALUE)

=sum(Fields!YTD.VALUE)

and this is an example of the data being returned by my Sproc

Book

CCY

GroupedData

DTD

MTD

YTD

ABC

GBP

Commission

￡0.01

￡0.09

-￡0.10

ABC

GBP

Interest

￡0.02

￡0.29

￡0.11

ABC

GBP

Brokerage

￡0.12

￡0.06

￡0.20

Like the person above - if i use the "Add Column" functionality - I get a repeat of the MTD and YTD under each of the groupData cells - where as i only need it as a summary after the subtotal.

Daily Data

Grouped Data CELL 1

Grouped Data CELL 2

Book Name

Currency

DTD TOTAL

MTD TOTAL

YTD TOTAL

DTD TOTAL

MTD TOTAL

YTD TOTAL

=sum(Fields!DTD.VALUE)

Does anyone out there know how to do it?

|||

Hi Everyone,

I solved a similar issue by adding a new column to the matrix and using this expression to find the percentage of change from month to month.

=(Sum(Fields!TotalSales.Value)-(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value)))/(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value))

NOTE: matrix1_FiscalMonth is my row group name

To find the percentage of change take the “after” amount, minus the “before” amount and divide that result by the before amount. i.e.(60-50)/50 = 20%

Regard,

AA

"Small things done consistently in strategic places create major impact"