how to get the rowNumber in a matrix?

I am working with reporting services recently.

when using a matrix, I want to get the row number to dynamically set the row color.

but i can not get it by using the rownumber function,

can anyone help me with it?

thank you very much.

The row number doesn't really help you in a matrix because you are always dealing with grouped data in a matrix. You have to follow the approach discussed in the following blog article: http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx

-- Robert

how to get the real boundary of "time" dimension of a subcube dynamically?

how to get the real boundary of "time" dimension of a subcube dynamically?

e.g.

CREATE SUBCUBE [SMS2005] AS
(
SELECT
(
[time].[date].[date].&[2007-01-01T00:00:00] : [time].[date].[date].&[2007-01-10T00:00:00]
)
ON COLUMNS
FROM [SMS2005]
)
go

I want to create a global measure that show the stocks on the boundaries of the subcube'range
(in this case , they are 2007-1-1 and 2007-1-10).

Whenever a different new subcube (focused on time dimension) is built,
this measure can behave differently based on the subcube.

To this problem ,
I think the key is that
how to get the real boundary of "time" dimension of the subcube dynamically
in the definition of the globally wanted measure.

Does anyone has a inspiration?

3x.

So eagerly for the answer.|||

I wrote the following measure,but it does not work.

create calculated member
sms2005.measures.w as
([time].[date].[date].members.item(0) , [Measures].[stocks])
go

|||UP ! Is there anyone who knows how to get it?

How to get the database name dynamically?

I need an sql statement that dynamically matches a database name in a stored proc. Here is my attempt

Select Table_name

From Information_schema.Tables

Where Table_type = 'BASE TABLE' and Objectproperty (Object_id(Table_name), 'IsMsShipped') = 0

and table_name like 'Item%'

and (DATABASENAME=?)

Can someone help me out?

Thanks.

Try the DB_NAME() function.

|||

DB_NAME() works but if i have to run this query dynamically in the database as per the database parameter how do i do it?

like

declare @.dbname varchar(100)

Select Table_name

From Information_schema.Tables

Where Table_type = 'BASE TABLE' and Objectproperty (Object_id(Table_name), 'IsMsShipped') = 0

and table_name like 'Item%Master'

and DB_NAME=@.dbname

|||

You need to use dynamic SQL to execute commands in multiple databases at run-time. See example below on how to do it the safe way (without SQL injection risks):

Code Snippet

-- SQL Server 2005 version:

-- Will work with minimal changes in SQL Server 2000 too:

declare @.sp nvarchar(500), @.dbname nvarchar(128);

declare @.dbs cursor;

set @.dbs = cursor fast_forward for

select name from sys.databases

where name not in ('master', 'model', 'msdb', 'tempdb');

open @.dbs;

while(1=1)

begin

fetch @.dbs into @.dbname;

if @.@.fetch_status < 0 break;

set @.sp = quotename(@.dbname) + N'.sys.sp_executesql';

exec @.sp N'

SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = ''BASE TABLE''

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + N''.'' + QUOTENAME(TABLE_NAME)),

''IsMSShipped'') = 0

'

end;

How to get result of Expression which builds dynamically?

I am using SQL Server 2000 and in SP or Function, my problem is as follows:
I have one expression in variable of varchar. I want result of that
expression into another variable which is of numeric. For that code look
likes:
--code is as follows--
--Declaration of variables
declare @.str1 varchar (100)
declare @.int1 numeric (18, 2)
--This is assignment is for sampling purpose
select @.str1 = '2 + 2 * 8 / 3'
--Below sentence is my problem, I do not know the way of getting result of
expression in this situation
select @.int1 = cast(@.str1 as numeric)
--I need this value of variable for further use
select @.int1
--code ends here--
I will be thankful if problem get solved. Please note there are lots of code
before building expression and after getting result of this. I stuck up at
this point, as not able to convert string expression to numeric and hence
result.
Thank you in advance.Hi
You could do something like:
--code is as follows--
--Declaration of variables
DECLARE @.str1 varchar (100)
DECLARE @.int1 numeric (18, 2)
CREATE TABLE #tmp ( int1 numeric (18, 2) )
--This is assignment is for sampling purpose
SET @.str1 = 'SELECT (2 + (2 * 8)) / 3'
INSERT INTO #tmp ( int1 )
EXEC ( @.str1 )
SELECT @.int1 = int1 FROM #tmp
SELECT @.int1
DROP TABLE #tmp
But it would probably not be a very scallable solution.
John
"Kaushik Gadani" wrote:

> I am using SQL Server 2000 and in SP or Function, my problem is as follows
:
> I have one expression in variable of varchar. I want result of that
> expression into another variable which is of numeric. For that code look
> likes:
> --code is as follows--
> --Declaration of variables
> declare @.str1 varchar (100)
> declare @.int1 numeric (18, 2)
> --This is assignment is for sampling purpose
> select @.str1 = '2 + 2 * 8 / 3'
> --Below sentence is my problem, I do not know the way of getting result of
> expression in this situation
> select @.int1 = cast(@.str1 as numeric)
> --I need this value of variable for further use
> select @.int1
> --code ends here--
> I will be thankful if problem get solved. Please note there are lots of co
de
> before building expression and after getting result of this. I stuck up at
> this point, as not able to convert string expression to numeric and hence
> result.
> Thank you in advance.

How to get result of Expression which builds dynamically?

I am using SQL Server 2000 and in SP or Function, my problem is as follows:
I have one expression in variable of varchar. I want result of that
expression into another variable which is of numeric. For that code look
likes:
--code is as follows--
--Declaration of variables
declare @.str1 varchar (100)
declare @.int1 numeric (18, 2)
--This is assignment is for sampling purpose
select @.str1 = '2 + 2 * 8 / 3'
--Below sentence is my problem, I do not know the way of getting result of
expression in this situation
select @.int1 = cast(@.str1 as numeric)
--I need this value of variable for further use
select @.int1
--code ends here--
I will be thankful if problem get solved. Please note there are lots of code
before building expression and after getting result of this. I stuck up at
this point, as not able to convert string expression to numeric and hence
result.
Thank you in advance.Hi
You could do something like:
--code is as follows--
--Declaration of variables
DECLARE @.str1 varchar (100)
DECLARE @.int1 numeric (18, 2)
CREATE TABLE #tmp ( int1 numeric (18, 2) )
--This is assignment is for sampling purpose
SET @.str1 = 'SELECT (2 + (2 * 8)) / 3'
INSERT INTO #tmp ( int1 )
EXEC ( @.str1 )
SELECT @.int1 = int1 FROM #tmp
SELECT @.int1
DROP TABLE #tmp
But it would probably not be a very scallable solution.
John
"Kaushik Gadani" wrote:
> I am using SQL Server 2000 and in SP or Function, my problem is as follows:
> I have one expression in variable of varchar. I want result of that
> expression into another variable which is of numeric. For that code look
> likes:
> --code is as follows--
> --Declaration of variables
> declare @.str1 varchar (100)
> declare @.int1 numeric (18, 2)
> --This is assignment is for sampling purpose
> select @.str1 = '2 + 2 * 8 / 3'
> --Below sentence is my problem, I do not know the way of getting result of
> expression in this situation
> select @.int1 = cast(@.str1 as numeric)
> --I need this value of variable for further use
> select @.int1
> --code ends here--
> I will be thankful if problem get solved. Please note there are lots of code
> before building expression and after getting result of this. I stuck up at
> this point, as not able to convert string expression to numeric and hence
> result.
> Thank you in advance.

how to get result of a dynamic query in a proc

Say the query is 'select count(*) cnt from xyz' where 'xyz' need to be
dynamically determined. What are natural ways to receive the value of
cnt and continue to process accordingly within a proc?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Charles,
See if this helps..
INF: Using Output Parameters with sp_executesql
http://support.microsoft.com/?id=262499
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Charles Yu" <anonymous@.devdex.com> wrote in message
news:uEDA8V%23SDHA.1572@.TK2MSFTNGP12.phx.gbl...
> Say the query is 'select count(*) cnt from xyz' where 'xyz' need to be
> dynamically determined. What are natural ways to receive the value of
> cnt and continue to process accordingly within a proc?
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!