Filter & *

Hi I am wandering if it is possible to achieve the following:

I am using Reporting Services and Reporting services I got a report with a text parameter.

I would like the user to introduce AA and find the product AA and when he introduces AA* it finds AA, AAD, AAC...

I am doing the following:

Filter([DIM Product].[Product].ALLMEMBERS,

,IIF(INSTR(@.Product, "*") > 0

,[DIM Product].[Product].currentmember.name = Replace(@.Product, "%", "")

,INSTR([DIM Product].[Product].currentmember.name, @.Product)

)

)

The problem ist that the function Replace does not work!

Does someone know how to do it?

I don't think the problem is with the replace, it looks like you have the logic back-to-front, something like the following should get you closer.

Code Snippet

Filter([DIM Product].[Product].ALLMEMBERS,

,IIF(INSTR(@.Product, "*") > 0

,INSTR([DIM Product].[Product].currentmember.name, Replace(@.Product, "*", "")

,[DIM Product].[Product].currentmember.name = @.Product

)

)

However there is an issue that you may or may not be concerned with - If someone types in "A*B" the expression will strip out the "*" and return anything containing "AB".

Another alternative, if you want more sophisticated behaviour might be to look at using a stored procedure. There is a string match example which I wrote in the Analysis Services Stored Procedure project at www.codeplex.com/ASStoredProcedures . I have one in there that reproduces the equivalent of the t-sql LIKE operator.

File Group Changes

How do i place table and index objects which resides in same Primary Group to seperate User File Groups?
How can i achieve this task through T-SQL statements instead of Enterprise Manager?
sentil
If your table has a clustered index sp you can re-create a clustered index
to specify a filegroup. The data will be followed by clustered index.
For more details please refer to the BOL
"senthil" <senthil@.discussions.microsoft.com> wrote in message
news:A429215F-C3EF-4EED-A208-B215002FF45E@.microsoft.com...
> How do i place table and index objects which resides in same Primary Group
to seperate User File Groups?
> How can i achieve this task through T-SQL statements instead of
Enterprise Manager?
|||As Uri says, when you move the clustered index, the data moves as well...
The generic syntax you would use would be
Create index .....
on myfilegroup
with Drop_Existing
Read about drop_existing in BOL under "Create Index"
You would do this for each index you wish to move...
Be aware that the t-log can grow tremendously during this process... So
either back it up during, or go to simple recovery mode (kicking everyone
else off ) do the builds, and move back to full recovery.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"senthil" <senthil@.discussions.microsoft.com> wrote in message
news:A429215F-C3EF-4EED-A208-B215002FF45E@.microsoft.com...
> How do i place table and index objects which resides in same Primary Group
to seperate User File Groups?
> How can i achieve this task through T-SQL statements instead of
Enterprise Manager?
|||Hi ,
FYI, If you need to move the non clustered index as well to the new file
group you have to execute the below command for each of the non clustered
indexes.
For Non-clustered index
create index <index_name> on table_name(clumn1,column2...) with
drop_existing on <file_group>
For CLustered index and data
create clustered index <index_name> on table_name(clumn1,column2...) with
drop_existing on <file_group>
Command to list the table name with file group names
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.indid < 2
and i.groupid = s.groupid
Thanks
Hari
MCDBA
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:u2$dw4sUEHA.1604@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> As Uri says, when you move the clustered index, the data moves as well...
> The generic syntax you would use would be
> Create index .....
> on myfilegroup
> with Drop_Existing
>
> Read about drop_existing in BOL under "Create Index"
> You would do this for each index you wish to move...
> Be aware that the t-log can grow tremendously during this process... So
> either back it up during, or go to simple recovery mode (kicking everyone
> else off ) do the builds, and move back to full recovery.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "senthil" <senthil@.discussions.microsoft.com> wrote in message
> news:A429215F-C3EF-4EED-A208-B215002FF45E@.microsoft.com...
Group
> to seperate User File Groups?
> Enterprise Manager?
>

File Group Changes

How do i place table and index objects which resides in same Primary Group t
o seperate User File Groups?
How can i achieve this task through T-SQL statements instead of Enterprise M
anager?sentil
If your table has a clustered index sp you can re-create a clustered index
to specify a filegroup. The data will be followed by clustered index.
For more details please refer to the BOL
"senthil" <senthil@.discussions.microsoft.com> wrote in message
news:A429215F-C3EF-4EED-A208-B215002FF45E@.microsoft.com...
> How do i place table and index objects which resides in same Primary Group
to seperate User File Groups?
> How can i achieve this task through T-SQL statements instead of
Enterprise Manager?|||As Uri says, when you move the clustered index, the data moves as well...
The generic syntax you would use would be
Create index .....
on myfilegroup
with Drop_Existing
Read about drop_existing in BOL under "Create Index"
You would do this for each index you wish to move...
Be aware that the t-log can grow tremendously during this process... So
either back it up during, or go to simple recovery mode (kicking everyone
else off ) do the builds, and move back to full recovery.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"senthil" <senthil@.discussions.microsoft.com> wrote in message
news:A429215F-C3EF-4EED-A208-B215002FF45E@.microsoft.com...
> How do i place table and index objects which resides in same Primary Group
to seperate User File Groups?
> How can i achieve this task through T-SQL statements instead of
Enterprise Manager?|||Hi ,
FYI, If you need to move the non clustered index as well to the new file
group you have to execute the below command for each of the non clustered
indexes.
For Non-clustered index
--
create index <index_name> on table_name(clumn1,column2...) with
drop_existing on <file_group>
For CLustered index and data
--
create clustered index <index_name> on table_name(clumn1,column2...) with
drop_existing on <file_group>
Command to list the table name with file group names
----
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.indid < 2
and i.groupid = s.groupid
Thanks
Hari
MCDBA
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:u2$dw4sUEHA.1604@.TK2MSFTNGP12.phx.gbl...
> As Uri says, when you move the clustered index, the data moves as well...
> The generic syntax you would use would be
> Create index .....
> on myfilegroup
> with Drop_Existing
>
> Read about drop_existing in BOL under "Create Index"
> You would do this for each index you wish to move...
> Be aware that the t-log can grow tremendously during this process... So
> either back it up during, or go to simple recovery mode (kicking everyone
> else off ) do the builds, and move back to full recovery.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "senthil" <senthil@.discussions.microsoft.com> wrote in message
> news:A429215F-C3EF-4EED-A208-B215002FF45E@.microsoft.com...
Group[vbcol=seagreen]
> to seperate User File Groups?
> Enterprise Manager?
>

File Group Changes

How do i place table and index objects which resides in same Primary Group to seperate User File Groups?
How can i achieve this task through T-SQL statements instead of Enterprise Manager?sentil
If your table has a clustered index sp you can re-create a clustered index
to specify a filegroup. The data will be followed by clustered index.
For more details please refer to the BOL
"senthil" <senthil@.discussions.microsoft.com> wrote in message
news:A429215F-C3EF-4EED-A208-B215002FF45E@.microsoft.com...
> How do i place table and index objects which resides in same Primary Group
to seperate User File Groups?
> How can i achieve this task through T-SQL statements instead of
Enterprise Manager?|||As Uri says, when you move the clustered index, the data moves as well...
The generic syntax you would use would be
Create index .....
on myfilegroup
with Drop_Existing
Read about drop_existing in BOL under "Create Index"
You would do this for each index you wish to move...
Be aware that the t-log can grow tremendously during this process... So
either back it up during, or go to simple recovery mode (kicking everyone
else off ) do the builds, and move back to full recovery.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"senthil" <senthil@.discussions.microsoft.com> wrote in message
news:A429215F-C3EF-4EED-A208-B215002FF45E@.microsoft.com...
> How do i place table and index objects which resides in same Primary Group
to seperate User File Groups?
> How can i achieve this task through T-SQL statements instead of
Enterprise Manager?|||Hi ,
FYI, If you need to move the non clustered index as well to the new file
group you have to execute the below command for each of the non clustered
indexes.
For Non-clustered index
--
create index <index_name> on table_name(clumn1,column2...) with
drop_existing on <file_group>
For CLustered index and data
--
create clustered index <index_name> on table_name(clumn1,column2...) with
drop_existing on <file_group>
Command to list the table name with file group names
----
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.indid < 2
and i.groupid = s.groupid
Thanks
Hari
MCDBA
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:u2$dw4sUEHA.1604@.TK2MSFTNGP12.phx.gbl...
> As Uri says, when you move the clustered index, the data moves as well...
> The generic syntax you would use would be
> Create index .....
> on myfilegroup
> with Drop_Existing
>
> Read about drop_existing in BOL under "Create Index"
> You would do this for each index you wish to move...
> Be aware that the t-log can grow tremendously during this process... So
> either back it up during, or go to simple recovery mode (kicking everyone
> else off ) do the builds, and move back to full recovery.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "senthil" <senthil@.discussions.microsoft.com> wrote in message
> news:A429215F-C3EF-4EED-A208-B215002FF45E@.microsoft.com...
> >
> > How do i place table and index objects which resides in same Primary
Group
> to seperate User File Groups?
> >
> > How can i achieve this task through T-SQL statements instead of
> Enterprise Manager?
>