Add static and dynamic columns to a matrix

I

have received a request for a report that has a variable number of

columns that can be generated with a matrix, and then two columns that

are fixed, i.e. one per group. Here is what I mean:

Sect 1 Sect 2 Sect 3 Score Time
-- -
Person 1 X X 50% 8 Days
Person 2 X 12% 3 Days

Another

page-level group could have 2 Sections or 8 Sections. There should only

be one Score and Elapsed Time column. How can I add these fixed columns

to a matrix?

Thanks for any help.
Having dynamic and static columns side-by-side is not currently supported in a matrix. We are working on adding this for a future release. For now, as a workaround, you can add a table that contains two columns (for score and time) to the right side of the matrix, and add a table group on person so that the matrix and the table are grouped the same way on the row.

Add perent element

I want to add perent element to existing element in xml variable (SQL 2005).
I solve that problem with:
DECLARE @.a xml
SET @.a =
N'<EVENT_INSTANCE><a>CREATE_TYPE</a><b1>2005-10-14T01:58:28.070</b1><c>51</c
></EVENT_INSTANCE>'
SELECT @.a
SET @.a.modify('
insert (<AddElement></AddElement> )
after (/EVENT_INSTANCE/a)[1]')
select @.a
SET @.a.modify('
insert /EVENT_INSTANCE/b1
as first
into (/EVENT_INSTANCE/AddElement)[1]')
select @.a
SET @.a.modify('
delete /EVENT_INSTANCE/b1')
SELECT @.a
Is there any simplier solution.
ThanksDear Ana
There are two ways at the moment to insert a new intermediate layer. The one
you show below, the other (which may be worse), is to decompose the tree and
use the FOR XML PATH mode to recompose the tree with the new level in
between.
The three updates below - unlike the FOR XML approach - will only update the
subtrees involved in the updates and not the whole XML document.
We are looking into extending the update language to make this a bit better,
but in general, inserting new levels into an XML tree will always be a
somewhat costly operation since your three steps would have to be executed
in any case (due to the node-id implementation that encodes the position in
the tree).
Best regards
Michael
"Ana Mihalj" <AnaMihalj@.discussions.microsoft.com> wrote in message
news:48FC0BE4-EEB6-408F-A119-994F0C1A0DEE@.microsoft.com...
>I want to add perent element to existing element in xml variable (SQL
>2005).
> I solve that problem with:
> DECLARE @.a xml
> SET @.a =
> N'<EVENT_INSTANCE><a>CREATE_TYPE</a><b1>2005-10-14T01:58:28.070</b1><c>51<
/c></EVENT_INSTANCE>'
> SELECT @.a
> SET @.a.modify('
> insert (<AddElement></AddElement> )
> after (/EVENT_INSTANCE/a)[1]')
> select @.a
>
> SET @.a.modify('
> insert /EVENT_INSTANCE/b1
> as first
> into (/EVENT_INSTANCE/AddElement)[1]')
> select @.a
> SET @.a.modify('
> delete /EVENT_INSTANCE/b1')
> SELECT @.a
> Is there any simplier solution.
> Thanks|||Thanks Michael.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OmzYQl06FHA.2716@.TK2MSFTNGP11.phx.gbl...
> Dear Ana
> There are two ways at the moment to insert a new intermediate layer. The
> one you show below, the other (which may be worse), is to decompose the
> tree and use the FOR XML PATH mode to recompose the tree with the new
> level in between.
> The three updates below - unlike the FOR XML approach - will only update
> the subtrees involved in the updates and not the whole XML document.
> We are looking into extending the update language to make this a bit
> better, but in general, inserting new levels into an XML tree will always
> be a somewhat costly operation since your three steps would have to be
> executed in any case (due to the node-id implementation that encodes the
> position in the tree).
> Best regards
> Michael
> "Ana Mihalj" <AnaMihalj@.discussions.microsoft.com> wrote in message
> news:48FC0BE4-EEB6-408F-A119-994F0C1A0DEE@.microsoft.com...
>|||One way is xquery, optionally combined with update query.
select @.a.query('
for $a in /* return (
element EVENT_INSTANCE {$a/a, element AddElement {$a/b1}, $a/c}
)
')
Pohwan Han. Seoul. Have a nice day.
"Ana Mihalj" <amihalj@.hotmail.com.false> wrote in message
news:%23Q2O2326FHA.2524@.TK2MSFTNGP10.phx.gbl...
> Thanks Michael.
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OmzYQl06FHA.2716@.TK2MSFTNGP11.phx.gbl...
>|||Thanks Han.
"Han" <hp4444@.kornet.net.korea> wrote in message
news:ORUxMN#6FHA.268@.TK2MSFTNGP10.phx.gbl...
> One way is xquery, optionally combined with update query.
> select @.a.query('
> for $a in /* return (
> element EVENT_INSTANCE {$a/a, element AddElement {$a/b1}, $a/c}
> )
> ')
> --
> Pohwan Han. Seoul. Have a nice day.
> "Ana Mihalj" <amihalj@.hotmail.com.false> wrote in message
> news:%23Q2O2326FHA.2524@.TK2MSFTNGP10.phx.gbl...
The
update
always
the
N'<EVENT_INSTANCE><a>CREATE_TYPE</a><b1>2005-10-14T01:58:28.070</b1><c>51</c
></EVENT_INSTANCE>'
>

Add perent element

I want to add perent element to existing element in xml variable (SQL 2005).
I solve that problem with:
DECLARE @.a xml
SET @.a =
N'<EVENT_INSTANCE><a>CREATE_TYPE</a><b1>2005-10-14T01:58:28.070</b1><c>51</c></EVENT_INSTANCE>'
SELECT @.a
SET @.a.modify('
insert (<AddElement></AddElement>)
after (/EVENT_INSTANCE/a)[1]')
select @.a
SET @.a.modify('
insert /EVENT_INSTANCE/b1
as first
into (/EVENT_INSTANCE/AddElement)[1]')
select @.a
SET @.a.modify('
delete /EVENT_INSTANCE/b1')
SELECT @.a
Is there any simplier solution.
Thanks
Dear Ana
There are two ways at the moment to insert a new intermediate layer. The one
you show below, the other (which may be worse), is to decompose the tree and
use the FOR XML PATH mode to recompose the tree with the new level in
between.
The three updates below - unlike the FOR XML approach - will only update the
subtrees involved in the updates and not the whole XML document.
We are looking into extending the update language to make this a bit better,
but in general, inserting new levels into an XML tree will always be a
somewhat costly operation since your three steps would have to be executed
in any case (due to the node-id implementation that encodes the position in
the tree).
Best regards
Michael
"Ana Mihalj" <AnaMihalj@.discussions.microsoft.com> wrote in message
news:48FC0BE4-EEB6-408F-A119-994F0C1A0DEE@.microsoft.com...
>I want to add perent element to existing element in xml variable (SQL
>2005).
> I solve that problem with:
> DECLARE @.a xml
> SET @.a =
> N'<EVENT_INSTANCE><a>CREATE_TYPE</a><b1>2005-10-14T01:58:28.070</b1><c>51</c></EVENT_INSTANCE>'
> SELECT @.a
> SET @.a.modify('
> insert (<AddElement></AddElement>)
> after (/EVENT_INSTANCE/a)[1]')
> select @.a
>
> SET @.a.modify('
> insert /EVENT_INSTANCE/b1
> as first
> into (/EVENT_INSTANCE/AddElement)[1]')
> select @.a
> SET @.a.modify('
> delete /EVENT_INSTANCE/b1')
> SELECT @.a
> Is there any simplier solution.
> Thanks
|||Thanks Michael.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OmzYQl06FHA.2716@.TK2MSFTNGP11.phx.gbl...
> Dear Ana
> There are two ways at the moment to insert a new intermediate layer. The
> one you show below, the other (which may be worse), is to decompose the
> tree and use the FOR XML PATH mode to recompose the tree with the new
> level in between.
> The three updates below - unlike the FOR XML approach - will only update
> the subtrees involved in the updates and not the whole XML document.
> We are looking into extending the update language to make this a bit
> better, but in general, inserting new levels into an XML tree will always
> be a somewhat costly operation since your three steps would have to be
> executed in any case (due to the node-id implementation that encodes the
> position in the tree).
> Best regards
> Michael
> "Ana Mihalj" <AnaMihalj@.discussions.microsoft.com> wrote in message
> news:48FC0BE4-EEB6-408F-A119-994F0C1A0DEE@.microsoft.com...
>
|||One way is xquery, optionally combined with update query.
select @.a.query('
for $a in /* return (
element EVENT_INSTANCE {$a/a, element AddElement {$a/b1}, $a/c}
)
')
Pohwan Han. Seoul. Have a nice day.
"Ana Mihalj" <amihalj@.hotmail.com.false> wrote in message
news:%23Q2O2326FHA.2524@.TK2MSFTNGP10.phx.gbl...
> Thanks Michael.
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OmzYQl06FHA.2716@.TK2MSFTNGP11.phx.gbl...
>
|||Thanks Han.
"Han" <hp4444@.kornet.net.korea> wrote in message
news:ORUxMN#6FHA.268@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> One way is xquery, optionally combined with update query.
> select @.a.query('
> for $a in /* return (
> element EVENT_INSTANCE {$a/a, element AddElement {$a/b1}, $a/c}
> )
> ')
> --
> Pohwan Han. Seoul. Have a nice day.
> "Ana Mihalj" <amihalj@.hotmail.com.false> wrote in message
> news:%23Q2O2326FHA.2524@.TK2MSFTNGP10.phx.gbl...
The[vbcol=seagreen]
update[vbcol=seagreen]
always[vbcol=seagreen]
the[vbcol=seagreen]
N'<EVENT_INSTANCE><a>CREATE_TYPE</a><b1>2005-10-14T01:58:28.070</b1><c>51</c
></EVENT_INSTANCE>'
>

Add constant variable to report project

I'd like to add a constant string variable to my report project.
Something like stCOMPANY_NAME. I'd like the reports to be portable to some
of our other plants so it would be nice so be able to change the company
name on the header of all the reports by just changing one constant
somewhere in the project. I know this could be done at the report level
(putting a constant in the code block for the report), but can it be done at
the project, public level ?I'm sure there must be an OLEDB/ODBC driver for reading in text files.
Stick in a text file and read it in as a dataset.
If you use the OLEDB provider for ODBC drivers, you can read from Excel.
Hope that helps.
Chris
D Witherspoon wrote:
> I'd like to add a constant string variable to my report project.
> Something like stCOMPANY_NAME. I'd like the reports to be portable
> to some of our other plants so it would be nice so be able to change
> the company name on the header of all the reports by just changing
> one constant somewhere in the project. I know this could be done at
> the report level (putting a constant in the code block for the
> report), but can it be done at the project, public level ?|||You can make Company name as parameter.
Kiran
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:%23$PMtNocFHA.3040@.TK2MSFTNGP14.phx.gbl...
> I'm sure there must be an OLEDB/ODBC driver for reading in text files.
> Stick in a text file and read it in as a dataset.
> If you use the OLEDB provider for ODBC drivers, you can read from Excel.
> Hope that helps.
> Chris
> D Witherspoon wrote:
>> I'd like to add a constant string variable to my report project.
>> Something like stCOMPANY_NAME. I'd like the reports to be portable
>> to some of our other plants so it would be nice so be able to change
>> the company name on the header of all the reports by just changing
>> one constant somewhere in the project. I know this could be done at
>> the report level (putting a constant in the code block for the
>> report), but can it be done at the project, public level ?
>

Add column with variable name

HI!

Im trying to use an stored procedure to add a new column.
The problem is that the column name is stored in a variable, and I get syntax error.

Here is my code:

CREATE PROCEDURE test AS
declare @.colname as varchar(10)

set @.colname = 'new_col'

alter table prueba add @.colname int

And here is the error I get:
Error 170: Line 6 Incorrect syntax near '@.colname'

Any idea?Originally posted by soy_lore_lore
HI!

Im trying to use an stored procedure to add a new column.
The problem is that the column name is stored in a variable, and I get syntax error.

Here is my code:

CREATE PROCEDURE test AS
declare @.colname as varchar(10)

set @.colname = 'new_col'

alter table prueba add @.colname int

And here is the error I get:
Error 170: Line 6 Incorrect syntax near '@.colname'

Any idea?

Try this:

exec('alter table prueba add '+@.colname+' int')|||Originally posted by snail
Try this:

exec('alter table prueba add '+@.colname+' int')

THANKS!

Add a variable from custom component

Hi

I am writing a custom transformation component that utilises a user variable.

Before using the variable at run time I am checking that the variable exists, but it would be nice to be able to add it if it does not. I cannot find any documentation on the subject, though I can see that the Variables class is derived from a ReadOnlyCollectionBase.

Is there a way to add a user variable with package scope from a custom component, either at run time or design time?

Thanks . . . Ed

I do not think it is possible currently.

Thanks.

Add a line feed to a string

How do you add a line feed to string variable?
="Activate Item " + Fields!item_id.Value + \n + Fields!
item_desc.Value
Thanks
BobRS uses .Net
"foo" + System.Environment.NewLine

ADD A COLUMN AND POPULATE IT WITH A SINGLE VALUE FROM A USER VARIABLE

Hi !

I have one Excel source with ... say 2 columns, I have an SQL Server destination with 3 columns.

I want the third column to be populated with a single value stored in a User::Variable.

Can someone help !

Jacques...

Just add a Derived Column transformation and assign your User::Variable to the derived column.

That's it.

|||

Thanks Sbastien !

Jacques