Adding Time to Rank

Using FoodMart, how can i add time to this:
WITH MEMBER [Measures].[Rank] AS 'Rank ( Product.CurrentMember,Order( {Product.CurrentMember.Parent.Children} ,[Profit], DESC) ) '
SELECT
{[Profit] , [Rank]} ON COLUMNS,
Drink.Children ON ROWS
FROM SalesWhat platform is this?|||Brett,

This is an MDX statement for OLAP - Hard as nails to understand ..

msenoelo - are you wanting to set this up as a calculated measure or do you just want to see if the MDX is valid

If you want check the MDX then you can open up the sample mdx application from

Programs > SQL Server > Anaysis Services > Sample MDX Application

I think??

You then get a kind IDE that can use to parse MDX.

Or you can goto your cube

New calculated meause > Insert your code

Not totally sure as I don't have anaylsis services installed on my computer and MDX is total bloody mystery to me. Where did you get the MDX from ?

Here is a like to a more relevant forum:

http://www.sql-server-performance.com/forum/forum.asp?FORUM_ID=18

Cheerssql

Adding role to roles?

Hello,
Is it possible to add a dbrole as a member or another dbrole? When I try
and add members to an existing role all I see are dbusers, but I've looked a
t
some other sample dbs and noticed what appeared to be dbroles as members of
other dbroles.
Any help would be greatly appreciated!
Thanks in advance.A couple of the limitations are that you can't add a fixed
database role to other roles - which is probably what you
are seeing. And the other would be that you can't create
circular roles - you can't add role1 to role2 if role2 is
already a member of role1.
You can create your own user defined roles and can add these
to fixed database roles. You can also add user defined roles
to other user defined roles.
-Sue
On Thu, 16 Feb 2006 08:17:08 -0800, "Mark"
<Mark@.discussions.microsoft.com> wrote:

>Hello,
>Is it possible to add a dbrole as a member or another dbrole? When I try
>and add members to an existing role all I see are dbusers, but I've looked
at
>some other sample dbs and noticed what appeared to be dbroles as members of
>other dbroles.
>Any help would be greatly appreciated!
>Thanks in advance.
>

Adding member on a Role

Hi all,

I'm wondering if I can add a member on my role inside an SP.

something like this.

-- EXEC sp_addrolemember 'RoleName', 'UserName'

but i can't use the above statemnt inside a stored proc. I'm getting this error msg

Msg 15002, Level 16, State 1, Procedure sp_addrolemember, Line 19

The procedure 'sp_addrolemember' cannot be executed within a transaction.

Is there any other alternative way to add a member to a role inside an SP?

Thank you so much and have a nice day to all

My bad it is not inside a [stored proc] it is inside a [TRIGGER after INSERT]|||

You can commit the transaction inside the trigger before you execute sp_addrolemember, just put a COMMIT statement before the call to sp_addrolemember. This could have undesirable effects if there are other triggers, so be careful and test it thoroughly! You should probably make this trigger the last trigger with sp_settriggerorder, which will at least make sure that it never commits before other triggers fire.

The other possibly better option is to create a stored procedure that performs the insert and then calls sp_addrolemember, and make sure that you always use that sproc to insert into the table. Using sprocs exclusively for all data modifications is a good principle anyway.

Adding different columns from different table

I have three tables.

Member(name, address, ID)

Loan(ID, startdate, amount)

Deposite(ID, startdate, amount)

I wanna create a report which look like this.

ID MembersName startdate address etc

Member can be either borrower or a depositor.

I'm thinking of using inner join. Can anyone help me to write the query?

Thanks

*If* ID links all three tables then the query would look like this:

This query is based on a member being *Either* a borrower OR a depositor.

Code Snippet

Select

m.ID,

M.Name,

Coalesce(L.StartDate, D.StartDate) as StartDate,

m.Address

From Member m (nolock)

left outer join Loan L (nolock)

on m.ID = L.ID

left outer join Deposit D (nolock)

on m.ID = D.ID

If a member can be a borrower AND/OR a depositor, the query would look like this:

Code Snippet

Select

m.ID,

S.AccountType,

m.Name,

S.StartDate,

m.Address

From

Member m (nolock),

inner join

(Select

ID,

StartDate,

'Loan' as AccountType

From

Loan l (nolock)

union

Select

ID,

StartDate,

'Deposit' as AccountType

From

Deposit d (nolock)

) S

on

m.ID = S.ID

Order by ID

You really cant use an inner join on all 3 tables since it would look for only members that had Deposit and Loan accounts.

HtH

BobP

|||

Thanks I will try with this..Also I wanna add another column now. There i want to show whether the member is a depositor or a borrower. we can get to know that from depositor or borrower table.

As a example,

If the start date of one perticular member is in borrower table , then he is a borrower.

Can you please help me to write this query too?I really appriciate

Thanks

|||

Actually, the 2nd query I have above will show that. I included a column for member type.

BobP

Adding different columns from different table

I have three tables.

Member(name, address, ID)

Loan(ID, startdate, amount)

Deposite(ID, startdate, amount)

I wanna create a report which look like this.

ID MembersName startdate address etc

Member can be either borrower or a depositor.

I'm thinking of using inner join. Can anyone help me to write the query?

Thanks

*If* ID links all three tables then the query would look like this:

This query is based on a member being *Either* a borrower OR a depositor.

Code Snippet

Select

m.ID,

M.Name,

Coalesce(L.StartDate, D.StartDate) as StartDate,

m.Address

From Member m (nolock)

left outer join Loan L (nolock)

on m.ID = L.ID

left outer join Deposit D (nolock)

on m.ID = D.ID

If a member can be a borrower AND/OR a depositor, the query would look like this:

Code Snippet

Select

m.ID,

S.AccountType,

m.Name,

S.StartDate,

m.Address

From

Member m (nolock),

inner join

(Select

ID,

StartDate,

'Loan' as AccountType

From

Loan l (nolock)

union

Select

ID,

StartDate,

'Deposit' as AccountType

From

Deposit d (nolock)

) S

on

m.ID = S.ID

Order by ID

You really cant use an inner join on all 3 tables since it would look for only members that had Deposit and Loan accounts.

HtH

BobP

|||

Thanks I will try with this..Also I wanna add another column now. There i want to show whether the member is a depositor or a borrower. we can get to know that from depositor or borrower table.

As a example,

If the start date of one perticular member is in borrower table , then he is a borrower.

Can you please help me to write this query too?I really appriciate

Thanks

|||

Actually, the 2nd query I have above will show that. I included a column for member type.

BobP