Can't get Dynamic Security working ...

I'm trying to get dynamic security working using a member property which contains a list of usernames that are allowed to access that level of the hierarchy. I'm using the following setup:

Dimension: DynManager

Hierarchy: DynManager

Levels (in order): SVP, DVP, RVP, ROM, DDO, Store

Attributes: SVP -> SVP Access List, DVP -> DVP Access List, RVP -> RVP Access List, ... etc

When I run the following test query, I get the set back that I expect:

SELECT FILTER([DynManager].[DDO].Members, INSTR([DynManager].CurrentMember.Properties("DDO Access List"), "JJester")) ON 0 FROM BiCube

However, when I attempt to use the same FILTER expression in the role's "Allowed Member Set" expression, I get the following error message:

An error occurred in the MDX script for the dimension attribute permission's allowed member set: Query (1, 56) The 'DynManager' dimension contains more than one hierarchy, therefore the hierarchy must be explicitly specified.

The DynManager dimension only has one hierarchy, so I don't understand where the error is coming from. However, I did what it says and changed my query to the following:

FILTER([DynManager].[DDO].Members, INSTR([DynManager].[DynManager].CurrentMember.Properties("DDO Access List"), "JJester"))

The syntax checks out but when I go to browse the cube as this dynamic role, I get the following error message:

The DynManager hierarchy is used more than once in the Crossjoin function

Can anyone please provide some insight!? Thank you!

Jeff

What is the attribute that you are setting this as AllowedSet on ? Is it DDO attribute ? If it is not DDO attribute, then the error can be because of it, but if it is, then the error doesn't look right. Are you on SP2 of AS ? I vaguely recall a fix in this area...

|||

Hi Mosha! Thanks for the quick reply. I just upgraded to SP2 this morning hoping it would clear up some issues ... but no luck.

I'm setting the "AllowedSet" on the SVP, DVP, RVP, ROM, and DDO attributes. All other attributes are set to "Select all members".

Thanks again,
Jeff

|||The expression that you provided above should only be set on DDO attribute. Please remove it from all other attributes.|||

My filter expression in the AllowedSet is different for each level of the hierarchy.

For DDO, it's:

FILTER([DynManager].[DDO].Members, INSTR([DynManager].[DynManager].CurrentMember.Properties("DDO Access List"), "JJester"))

For ROM, it's:

FILTER([DynManager].[ROM].Members, INSTR([DynManager].[DynManager].CurrentMember.Properties("ROM Access List"), "JJester"))

For RVP it's:

FILTER([DynManager].[RVP].Members, INSTR([DynManager].[DynManager].CurrentMember.Properties("RVP Access List"), "JJester"))

and on and on ....

Do you have any idea why my test query would work in SQL Server Management Studio, but then I get the "more than one hierarchy" error when I try to apply the same FILTER expression in the AllowedSet?

|||

Ok, I think I figured out the issue. The parameters in my filter expression are too specific (if that's even possible). I tried removing the Dimension name and it works. So, the new expression looks like this:

FILTER([DDO].Members, INSTR([DDO].CurrentMember.Properties("DDO Access List"), "JJester"))

Anyhow, thanks for your help Mosha!

sql

Can't get AND to work.

I have a contains string that says ' "Log In" ' which works fine and returns
results. I put in ' "Log" or "In" ' and I get results. I put in ' "Log" and
"In" ' and get "Informational: The full-text search condition contained
noise word(s)."
Any thoughts?
What I want to do is be able to have multiple terms with ands in the
expression.
TIA - Jeff.
Remove the words in from your noise word list. As you appear to be in the
States, empty the noise.enu or noiseenu.txt files and replace them with a
single space. Then rebuild your catalogs. You should be able to search on
them now.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mufasa" <jb@.nowhere.com> wrote in message
news:%23BdhgvGdHHA.4872@.TK2MSFTNGP03.phx.gbl...
>I have a contains string that says ' "Log In" ' which works fine and
>returns results. I put in ' "Log" or "In" ' and I get results. I put in '
>"Log" and "In" ' and get "Informational: The full-text search condition
>contained noise word(s)."
> Any thoughts?
> What I want to do is be able to have multiple terms with ands in the
> expression.
> TIA - Jeff.
>
|||Thanks for the help.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:O$DM0OIdHHA.208@.TK2MSFTNGP05.phx.gbl...
> Remove the words in from your noise word list. As you appear to be in the
> States, empty the noise.enu or noiseenu.txt files and replace them with a
> single space. Then rebuild your catalogs. You should be able to search on
> them now.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Mufasa" <jb@.nowhere.com> wrote in message
> news:%23BdhgvGdHHA.4872@.TK2MSFTNGP03.phx.gbl...
>
|||You're welcome.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mufasa" <jb@.nowhere.com> wrote in message
news:%23RiY9UWdHHA.4784@.TK2MSFTNGP06.phx.gbl...
> Thanks for the help.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:O$DM0OIdHHA.208@.TK2MSFTNGP05.phx.gbl...
>
|||Hilary,
That didn't seem to work. I changed both the noise.enu and for grins the
noise.eng file to be a single space. I also regenerated the full text index.
Now if I try where contains ( webpagetext, '"IN"' ) I still get
the Informational: The full-text search condition contained noise word(s).
message.
Any thoughts?
TIA - Jeff.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23w5O$1fdHHA.2268@.TK2MSFTNGP02.phx.gbl...
> You're welcome.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Mufasa" <jb@.nowhere.com> wrote in message
> news:%23RiY9UWdHHA.4784@.TK2MSFTNGP06.phx.gbl...
>
|||Did you restart the msftesql service after updating the noise list?
Best regards,
-Denis.
"Mufasa" wrote:

> Hilary,
> That didn't seem to work. I changed both the noise.enu and for grins the
> noise.eng file to be a single space. I also regenerated the full text index.
> Now if I try where contains ( webpagetext, '"IN"' ) I still get
> the Informational: The full-text search condition contained noise word(s).
> message.
> Any thoughts?
> TIA - Jeff.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23w5O$1fdHHA.2268@.TK2MSFTNGP02.phx.gbl...
>
>
|||The machine has been completely rebooted, and the text index was rebuilt.
It sounds like the file was not saved correctly. But I've checked it. I've
also done a search for noise.enu and there is only one copy on the machine -
in the Windows\System32 directory.
"denistc" <denistc@.discussions.microsoft.com> wrote in message
news:38149F6E-B714-446F-B6E0-31057D7D7B71@.microsoft.com...[vbcol=seagreen]
> Did you restart the msftesql service after updating the noise list?
> Best regards,
> -Denis.
> "Mufasa" wrote:

Can't find how to add a line feed to a textbox

Hello,
I'm creating a report with a textbox that contains a field from the dataset
and some hard text (something like =First(Fields!MyField.Value) & "
TheTextPart"). This works, but when I try to do a shift+enter (line feed) I
get an error "The value expresxsion for the textbox 'textbox7' contains an
error: [BC30648] String constants must end with a double quote".
Is there any way to add a line feed / carriage return in a text box?
Thanks!
Ricktry this:
=First(Fields!MyField.Value) & vbcrlf & "The Second Line" & vbcrlf &
"The Third Line"|||Brilliant! Thanks Sleepy!
"SleepyLab" <david.blancard@.fairmont.com> wrote in message
news:1187036726.406106.312410@.d55g2000hsg.googlegroups.com...
> try this:
> =First(Fields!MyField.Value) & vbcrlf & "The Second Line" & vbcrlf &
> "The Third Line"
>

Can't figure out how to write query

I have a table TABLE1. My company has 2 sites. This table contains employees
with the amount of hours they worked on which project at which sites.

code:

CREATE TABLE #TABLE1 (
Calldate varchar(10) NULL,
Employee varchar(10) NULL,
Project varchar(10) NULL,
Hours decimal(10,4) NULL,
Site varchar(1) NULL)
INSERT #TABLE1 (calldate, employee, project, hours, site)
VALUES ('20060217', '123', 'EAUD5', 2.5, '2')
INSERT #TABLE1 (calldate, employee, project, hours, site)
VALUES ('20060217', '246', 'EACQ5', 3, '2')
INSERT #TABLE1 (calldate, employee, project, hours, site)
VALUES ('20060217', '369', 'EACQ5', 2, '1')
INSERT #TABLE1 (calldate, employee, project, hours, site)
VALUES ('20060217', '369', 'EACQ6', 1.5, '1')
INSERT #TABLE1 (calldate, employee, project, hours, site)
VALUES ('20060217', '369', 'EACQ6', 5, '2')

I need to figure out the following:
I need the total hours of employees from both sites ONLY if they worked on a
project that ended in a 5. If employees worked on projects that did not end
in 5 I need the totals for their site only. A parameter of site will be
passed to the stored procedure.
So for example: If site parameter of 1 is passed.
I need to see the following results:
Calldate Project TotalHours
20060217 EAUD5 2.5
20060217 EACQ5 5
20060217 EACQ6 1.5
If site parameter of 2 is passed.
I need to see the following results:
Calldate Project TotalHours
20060217 EAUD5 2.5
20060217 EACQ5 5
20060217 EACQ6 5
Any help would be greatly appreciated,
Thanks,
ninel
Message posted via http://www.webservertalk.comThanks for posting DDL and sample data.
declare @.site varchar(1)
set @.site = '1'
select calldate, project,
sum(case when site = @.site or right(project,1) = '5' then hours else 0 end)
from #table1
group by calldate, project
set @.site = '2'
select calldate, project,
sum(case when site = @.site or right(project,1) = '5' then hours else 0 end)
from #table1
group by calldate, project
"ninel g via webservertalk.com" wrote:

>
I have a table TABLE1. My company has 2 sites. This table contains employe
es
>
with the amount of hours they worked on which project at which sites.
>
>

code:

>
 CREATE TABLE #TABLE1 (
>
 Calldate varchar(10) NULL,
>
 Employee varchar(10) NULL,
>
 Project varchar(10) NULL,
>
 Hours decimal(10,4) NULL,
>
 Site varchar(1) NULL)
>
>
 INSERT #TABLE1 (calldate, employee, project, hours, site)
>
 VALUES ('20060217', '123', 'EAUD5', 2.5, '2')
>
>
 INSERT #TABLE1 (calldate, employee, project, hours, site)
>
 VALUES ('20060217', '246', 'EACQ5', 3, '2')
>
>
 INSERT #TABLE1 (calldate, employee, project, hours, site)
>
 VALUES ('20060217', '369', 'EACQ5', 2, '1')
>
>
 INSERT #TABLE1 (calldate, employee, project, hours, site)
>
 VALUES ('20060217', '369', 'EACQ6', 1.5, '1')
>
>
 INSERT #TABLE1 (calldate, employee, project, hours, site)
>
 VALUES ('20060217', '369', 'EACQ6', 5, '2')
>

>
>
I need to figure out the following:
>
I need the total hours of employees from both sites ONLY if they worked on
a
>
project that ended in a 5. If employees worked on projects that did not en
d
>
in 5 I need the totals for their site only. A parameter of site will be
>
passed to the stored procedure.
>
>
So for example: If site parameter of 1 is passed.
>
I need to see the following results:
>
>
Calldate Project TotalHours
>
20060217 EAUD5 2.5
>
20060217 EACQ5 5
>
20060217 EACQ6 1.5
>
>
If site parameter of 2 is passed.
>
I need to see the following results:
>
>
Calldate Project TotalHours
>
20060217 EAUD5 2.5
>
20060217 EACQ5 5
>
20060217 EACQ6 5
>
>
Any help would be greatly appreciated,
>
>
Thanks,
>
ninel
>
>
--
>
Message posted via http://www.webservertalk.com
>
|||Thnak you so much for teh quick response.
Mark Williams wrote:
>Thanks for posting DDL and sample data.
>declare @.site varchar(1)
>set @.site = '1'
>select calldate, project,
>sum(case when site = @.site or right(project,1) = '5' then hours else 0 end)
>from #table1
>group by calldate, project
>set @.site = '2'
>select calldate, project,
>sum(case when site = @.site or right(project,1) = '5' then hours else 0 end)
>from #table1
>group by calldate, project
>
>[quoted text clipped - 49 lines]
Message posted via http://www.webservertalk.comsql