Finding a Member

Hi,
I try to evaluate a functionality to find a date-Member. Wath we need
is, we search the first existing Date in our Time-Dimension, which is,
say 11 days before the actual-date. Since such a Member maybe not
exits, the function should return the smallest existing Date bigger then the
one we are searching for.

MEMBER [MEASURES].[date1] AS
DATEADD("d", -11, CDate([Zeit].[Zeit].CURRENTMEMBER.MEMBER_CAPTION))
MEMBER [MEASURES].[mydate] AS
FILTER([Zeit].[Zeit].[Datum].members,
cdate([Zeit].[Zeit].[Datum].value) >= [MEASURES].[date1]).Item(0)
It doesn’t work in that way.
Can anybody give me some Help.
Thank you very much.

Bahram.

Looks like the problem here with the use of .Value function, which will retrieve cell value. If you are using AS2005, you can rewrite the Filter to be

FILTER([Zeit].[Zeit].[Datum].members, [Zeit].[Zeit].CurrentMember.MemberValue >= [MEASURES].[date1]).Item(0))

HTH

Mosha (http://www.mosha.com/msolap)

|||

Hi,

I tried your Formula. It doesn't work, i.e. it returns all Dates (([Zeit].[Zeit].[Datum].members). I think, it's becouse of the calculation of [MEASURES].[date1]. It uses the Currentmember and wenn the filter itterates through the members, it calculates the [MEASURES].[date1] again and again.

I don't know, if its correct or not!

Thanke you

|||Sorry - I didn't notice that date1 was going against CurrentMember in Zeit dimension. It needs to be fixed on the particular date that you are interested in for this Filter to work correctly.sql

Find out if current user is member of a role

I need a stored procedure to find out if the current user is a member of a certain role.

I want to pass the role name and return a bit to tell whether he is a member or not.

I have this code to list the groups the current user is a member of but I don't know how to search or do a "SELECT FROM" on the results.

DECLARE @.usr varchar(32)

SET @.usr = USER

EXEC sp_helpuser @.usr


But if the current user is a member of more than one role it returns multiple rows. So if I could do something like:

DECLARE @.grpName varchar(32)

SELECT * FROM (EXEC sp_helpuser @.usr) WHERE GroupName=@.grpName
IF rowcount > 0 THEN
RETURN 1
ELSE
RETURN 0
END IF

I know that doesn't work so how can I do this?I'm sure that someone out there can do better than this, but you might try:

ALTER PROC spCheckGroup

@.UserName varchar(255), @.GroupName varchar(255)

AS

DECLARE @.Count int

SELECT @.Count = Count(*)
FROM (
select
s1.name as username,
s2.name as groupname
from
dbo.sysusers s1 left join dbo.sysmembers sm on
s1.uid = sm.memberuid
left outer join dbo.sysusers s2 on
sm.groupuid = s2.uid
where
s1.uid < 16383
) t1
WHERE
t1.userName = @.UserName and
t1.GroupName = @.GroupName

If @.Count > 0
Return 1
ELSE
Return 0

Test it with this code:

[/code]
DECLARE @.return_status int
EXEC @.return_status = spCheckGroup 'OAJO-SQLAdmin', 'db_owner'
SELECT 'Return Status' = @.return_status
[/code]|||check: BOL

IS_MEMBER ( { 'group' | 'role' } )|||I'll just crawl back under the rock where I came from...|||LOL.

Thanks for trying.|||I need a stored procedure to work with .NET so here's what I have:

CREATE PROCEDURE IsGroupMember
(
@.groupName nvarchar(32),
@.retVal bit OUTPUT
)
AS
SET @.retVal = IS_MEMBER(@.groupName)
GO

and in Query Analyzer I run this:

DECLARE @.bt bit

EXEC IsGroupMember 'db_owner', @.bt

IF @.bt = 1 print 'member'
ELSE IF @.bt = 0 print 'non-member'
ELSE print 'undefined'

but I keep getting undefined. What's wrong?|||EXEC IsGroupMember 'db_owner', @.bt output

filtering with set?

i have two dimensions named dimLocation and dimTargetSet. What is needed is to count the members of dimLocation that is also a member of dimTargetSet. I am thinking of using a filter but have no idea what to do.

Count(FILTER(Descendants([dimLocation].currentmember,9,Leaves), ?))

Any ideas?... thank you so much....

Are you saying that both dimensions have the same key values? From your example code, I am guessing that you may be using AS2k (as there are only dimension and no hierarchy references) so I have coded my suggestion below to work on AS2k.

count(filter(descendants([dimLocation].currentmember,,leaves), linkMember([dimLocation].currentmember,[dimTargetSet]) <> NULL))

LinkMember will look for a member with the same key in the other hierarchy.

|||

thanks for your reply... i tried it but its not what i need. uhhmmm... what i need to do is to count the members of the first dimension.

count(Descendants([dimLocation].&[2583],9,LEAVES))

But the problem is when we select a member of the second dimension ([dimTargetset])... the second dimension is about location grouping... so if we select a group for west it should only count the members of [dimLocation] that belongs to that group....

Thank you....

|||

If targetset is a group of locations, why haven't you just modelled it as an attribute (or level in AS2000) of the location hierarch? Unless there is a many-to-many relationship between targetsets and locations, this would probably be the best way to go.

If you model two items as separated dimensions, then they are, by definition, treated as independent entities.

The only other way to do this would be to count a non-empty measure value

count(filter(descendants(dimLocation.Currentmember,,LEAVES),NOT IsEmpty(Measures.[....])))

If you are using AS2k5 you could use the NonEmpty() function which would be faster in most cases than using filtering.

|||

thank you so much for your reply....

filtering with set?

i have two dimensions named dimLocation and dimTargetSet. What is needed is to count the members of dimLocation that is also a member of dimTargetSet. I am thinking of using a filter but have no idea what to do.

Count(FILTER(Descendants([dimLocation].currentmember,9,Leaves), ?))

Any ideas?... thank you so much....

Are you saying that both dimensions have the same key values? From your example code, I am guessing that you may be using AS2k (as there are only dimension and no hierarchy references) so I have coded my suggestion below to work on AS2k.

count(filter(descendants([dimLocation].currentmember,,leaves), linkMember([dimLocation].currentmember,[dimTargetSet]) <> NULL))

LinkMember will look for a member with the same key in the other hierarchy.

|||

thanks for your reply... i tried it but its not what i need. uhhmmm... what i need to do is to count the members of the first dimension.

count(Descendants([dimLocation].&[2583],9,LEAVES))

But the problem is when we select a member of the second dimension ([dimTargetset])... the second dimension is about location grouping... so if we select a group for west it should only count the members of [dimLocation] that belongs to that group....

Thank you....

|||

If targetset is a group of locations, why haven't you just modelled it as an attribute (or level in AS2000) of the location hierarch? Unless there is a many-to-many relationship between targetsets and locations, this would probably be the best way to go.

If you model two items as separated dimensions, then they are, by definition, treated as independent entities.

The only other way to do this would be to count a non-empty measure value

count(filter(descendants(dimLocation.Currentmember,,LEAVES),NOT IsEmpty(Measures.[....])))

If you are using AS2k5 you could use the NonEmpty() function which would be faster in most cases than using filtering.

|||

thank you so much for your reply....

filtering with set?

i have two dimensions named dimLocation and dimTargetSet. What is needed is to count the members of dimLocation that is also a member of dimTargetSet. I am thinking of using a filter but have no idea what to do.

Count(FILTER(Descendants([dimLocation].currentmember,9,Leaves), ?))

Any ideas?... thank you so much....

Are you saying that both dimensions have the same key values? From your example code, I am guessing that you may be using AS2k (as there are only dimension and no hierarchy references) so I have coded my suggestion below to work on AS2k.

count(filter(descendants([dimLocation].currentmember,,leaves), linkMember([dimLocation].currentmember,[dimTargetSet]) <> NULL))

LinkMember will look for a member with the same key in the other hierarchy.

|||

thanks for your reply... i tried it but its not what i need. uhhmmm... what i need to do is to count the members of the first dimension.

count(Descendants([dimLocation].&[2583],9,LEAVES))

But the problem is when we select a member of the second dimension ([dimTargetset])... the second dimension is about location grouping... so if we select a group for west it should only count the members of [dimLocation] that belongs to that group....

Thank you....

|||

If targetset is a group of locations, why haven't you just modelled it as an attribute (or level in AS2000) of the location hierarch? Unless there is a many-to-many relationship between targetsets and locations, this would probably be the best way to go.

If you model two items as separated dimensions, then they are, by definition, treated as independent entities.

The only other way to do this would be to count a non-empty measure value

count(filter(descendants(dimLocation.Currentmember,,LEAVES),NOT IsEmpty(Measures.[....])))

If you are using AS2k5 you could use the NonEmpty() function which would be faster in most cases than using filtering.

|||

thank you so much for your reply....