Dynamic Security to Cube

I have the requiremrnt that when a User loggs in He would be able to see only his and his Ancestores Details(means he ll be able to see the users deatils those are reporting him)

The MDX Query i wrote in Roles--> Dimension Data-->Advanced-->Allowed Memberset is.

FILTER( [DIMUSERS].[DIMUSERS].Members,

'POLARIS\'+ [DIMUSERS].[DIMUSERS].Currentmember.Name=username)

When After execuing the above MDX Its doing hardcode as the login of the Current User

which is not affecting for other Users.

So that Who ever user login to the system everybody getting all the User deatils

This is Urgent plz help me in this regards

Thank U

Check that your users are not setup in the Administrators group, or are not part of a windows group that is in the admin role. This is definitely true for the server admins and possibly also for database admins, but I have not double checked that.|||

Thanks Daren

what you gav solution I did the same still its comming the same

|||

I'm not entirely sure I understand your issue. Are you possibly saying that the members are restricted at the lower level, but the parent levels still have the total for all members? If this is your problem, go into the advanced tab and turn on the "Visual Totals" option. This will make the value for all the parent members only show a total which is the sum of the values to which the user has access.

If this is not your issue maybe an example of what you are seeing and what you would like to see would help.

|||

I want the cube data to be filtered on the basis of the current domain user. Is there any way to get that filter implemented on analysis cube or through MDX query as said above.....please its urgent...

|||You can either do what the first poster was doing and setup a "users" dimension and setup the allowed set in your role to filter using the username function. or you can setup a series of different roles with different allowed sets and allocate users to the different roles - it really depends on your specific security needs as to which is the better approach.

Dynamic Security to Cube

I have the requiremrnt that when a User loggs in He would be able to see only his and his Ancestores Details(means he ll be able to see the users deatils those are reporting him)

The MDX Query i wrote in Roles--> Dimension Data-->Advanced-->Allowed Memberset is.

FILTER( [DIMUSERS].[DIMUSERS].Members,

'POLARIS\'+ [DIMUSERS].[DIMUSERS].Currentmember.Name=username)

When After execuing the above MDX Its doing hardcode as the login of the Current User

which is not affecting for other Users.

So that Who ever user login to the system everybody getting all the User deatils

This is Urgent plz help me in this regards

Thank U

Check that your users are not setup in the Administrators group, or are not part of a windows group that is in the admin role. This is definitely true for the server admins and possibly also for database admins, but I have not double checked that.|||

Thanks Daren

what you gav solution I did the same still its comming the same

|||

I'm not entirely sure I understand your issue. Are you possibly saying that the members are restricted at the lower level, but the parent levels still have the total for all members? If this is your problem, go into the advanced tab and turn on the "Visual Totals" option. This will make the value for all the parent members only show a total which is the sum of the values to which the user has access.

If this is not your issue maybe an example of what you are seeing and what you would like to see would help.

|||

I want the cube data to be filtered on the basis of the current domain user. Is there any way to get that filter implemented on analysis cube or through MDX query as said above.....please its urgent...

|||You can either do what the first poster was doing and setup a "users" dimension and setup the allowed set in your role to filter using the username function. or you can setup a series of different roles with different allowed sets and allocate users to the different roles - it really depends on your specific security needs as to which is the better approach.

Dynamic Security in SSAS 2005

I am trying to create dynamic security and was hoping to use a named query to link the fact table to a dimension that contains Active Directory users and companies - a user can have access to multiple companies. The problem is that I cannot find out how to get the username of the current user.

Anyone have any ideas?

thanks in advance.

Does the standard MDX keyword: UserName return what you want, like:

>>

With member [Measures].[CurrentUser] as UserName

select {[Measures].[CurrentUser]} on 0

from [Adventure Works]

>>

|||

thanks for the feedback - however, I am relatively new to Analysis Services (and 2005) - and cannot see where this would be created. Are you saying that this is a calculated Dimension (or measure?)

Many thanks

|||

The MDX keyword: UserName will return the name of the current user in an MDX expression, which could be used in many different contexts:

http://msdn2.microsoft.com/en-us/library/ms144884.aspx#expressions_statements_scripts

>>

SQL Server 2005 Books Online

Key Concepts in MDX (MDX)

In MDX, an expression is a combination of identifiers, values, functions, and operators that Analysis Services can evaluate to retrieve an object, such as a set or a member, or a scalar value like a string or a number.

>>

http://msdn2.microsoft.com/en-us/library/ms146016.aspx

>>

SQL Server 2005 Books Online

UserName (MDX)

Returns the domain name and user name of the current connection.

>>

|||Thanks for the feedback.

Dynamic Search SP

Hi,

I'm new to SQL 2005.

I need to create sp that perform search on Users table.

It gets few Parameters.

@.UserName

@.UserStatus

@.UserRole

@.OrderByColumn

All Parameters are optional, and i need to build sql statement that include only the parameters that the sp got on the specific.

I cand do that by concatanating a string and execute it using the sp_executesql().

Is it the best way in sql 2005 ?

Can you please show me an example using Case When or what ever ?

Thanks A lot.

Tok

A very good article on dynamic search that contains more than examples at:

http://www.sommarskog.se/dyn-search.html.

Please check it out.

Dynamic Operator Help

Hi,

I'm trying to build a form that will allow users to choose their own parameters for the Select statement in the SqlDataSource. These results would then be displayed back to a GridView control. The only problem I am having is figuring out how to allow them to choose the operator (=, <, >, <>, etc) from a dropdown list. Does anyone have any suggestions on how to do this with a SqlDataSource control? It is probably something simple and any help would be much appreciated.

Thanks

You would just build the statement dynamically and set the SqlDataSource.SelectCommand propertyhttp://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.selectcommand.aspx

But, be advise that using dynamic SQL is frought with danger due to sql injection attacks. You will really need to scrub your input. Seehttp://en.wikipedia.org/wiki/SQL_Injection or just google "sql injection" for many, many articles on this topic

|||

Dbland thanks for the reply. I do understand that the select command will be built dynamically and this is what I have done. Here is the Data Source.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:UsersConnectionString %>"
ProviderName="<%$ ConnectionStrings:UsersConnectionString.ProviderName %>" SelectCommand="SELECT Username, Email, Active, BuildingNum, UserRole, UserID FROM UserInfo WHERE (BuildingNum = @.BuildingNum)">
<SelectParameters>
<asp:ControlParameter ControlID="BuildingDropDown" Name="BuildingNum" PropertyName="Text" />
</SelectParameters>

I would like to parameterize the operator to a dropdown control which would have the =, >, < values options. That way people can select any building less than, greater than or, equal to a building number.

So my question is how do I allow this part of the select statement, "=", to be determined and populated from a users form input. (BuildingNum = @.BuildingNum)">

Is this possible?

|||

In your code you will have to take the value of the drop down and build your statement, eg

string sql = .................

SqlDataSource.SelectCommand = sql;

I'm not exactly sure in what event you would do this but I'm sure there is one just before the SelectCommand gets executed

Is this what you were after?

|||

Can you give me or point me to an example of how to do this?

|||

Let's say you have a Submit button. When they click that you build up your sql statement and set the sqldatasource as follows:

SqlDataSource.SelectCommand = sql;

Or you can put it in one of the data datasource events, like the Selecting event which occurs just before the select is done (http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.selecting.aspx). I'm not sure which event gives you the flexibility you want, you'll have to play read about them and play around with it.

|||

Thanks for all your help dbland. I took your advise and set the select command upon the button click event. It seems to work out well. Thanks again.

dynamic matrix reports

Hello,
Is it possible to create a report which allows users to select the
fields
for the matrix in a report. To explain in detail, can we allow the
users to
select the X and Y axis for a matrix in a report?
For ex., there is a report containing a matrix which shows the total
sales(
data cell) by month (X axis) and by Rep( Y axis). Can we have some
option
so that if the users select Year as the X axis and Company as the Y
axis
then they can view the same report but by company and Year instead of
Month and Rep?
Regards
Jaideepcertainly.
Just use a switch statement or iif statement for the values for the row
and column groups:
switch(parameters!group.value = 'x', fields!month.value,
parameters!group.value = 'y', fields!year.value)
or
iif(parameters!group.value = 'x', fields!month.value,
fields!year.value)
jai wrote:
> Hello,
> Is it possible to create a report which allows users to select the
> fields
> for the matrix in a report. To explain in detail, can we allow the
> users to
> select the X and Y axis for a matrix in a report?
>
> For ex., there is a report containing a matrix which shows the total
> sales(
> data cell) by month (X axis) and by Rep( Y axis). Can we have some
> option
> so that if the users select Year as the X axis and Company as the Y
> axis
> then they can view the same report but by company and Year instead of
> Month and Rep?
>
> Regards
> Jaideep

Dynamic Grouping with Report Designer

I've got this working -- I allow users to have 2 groups, and choose what they want to group by. I'd like to add one extra bit of functionality -- for the inner grouping, I would like my users to have the option "None" -- i.e. don't have an inner group.

I've tried setting the group expression of the second (inner) group to "" when the user chooses the "None" option but the report errors out. Any suggestions as to how to dynamically get rid of the inner group?

Thx

Helen

Helen:

I had a similar requirement and here's how I solved it. For the "None" entry at each grouping level you'll need to include a value for it in the combobox that's the following functions will recognize. Include the following in the "Code" and call the appropriate function. You're probably most interested in the "GetSubGrouping" function but I'm including "GetGrouping" so you can also use it at the main grouping level. Include the call to GetSubGrouping in the "Group on"; you'll also find that "GetField" is useful instead of coding a lot of iif's for controlling the visibility of the groupings.

Hope this helps

Glenn L

SharedFunction GetGrouping(ByVal Parameters AsObject, ByVal Fields AsObject) AsObject

Return GetField(Fields, Parameters("GroupBy").Value, "no_grouping")

EndFunction

SharedFunction GetSubGrouping(ByVal Parameters AsObject, ByVal Fields AsObject) AsObject

Return IIf(Parameters("GroupBy").Value = "no_grouping" _

Or Parameters("GroupBy").Value = Parameters("SubGroupBy").Value, Nothing, GetField(Fields, Parameters("SubGroupBy").Value, "no_sub_grouping"))

EndFunction

SharedFunction GetField(ByVal Fields AsObject, ByVal FieldName AsString, ByVal NoGroupingValue AsString) AsObject

If Trim$(FieldName) = Trim$(NoGroupingValue) Then

ReturnNothing

ElseIf IsDate(Fields(Trim$(FieldName)).Value) Then

Return FormatDateTime(Fields(Trim$(FieldName)).Value, 2)

Else

Return Fields(Trim$(FieldName)).Value

EndIf

EndFunction

|||

Thank you Glenn! That did the trick!

Helen

Dynamic Grouping with Report Designer

I've got this working -- I allow users to have 2 groups, and choose what they want to group by. I'd like to add one extra bit of functionality -- for the inner grouping, I would like my users to have the option "None" -- i.e. don't have an inner group.

I've tried setting the group expression of the second (inner) group to "" when the user chooses the "None" option but the report errors out. Any suggestions as to how to dynamically get rid of the inner group?

Thx

Helen

Helen:

I had a similar requirement and here's how I solved it. For the "None" entry at each grouping level you'll need to include a value for it in the combobox that's the following functions will recognize. Include the following in the "Code" and call the appropriate function. You're probably most interested in the "GetSubGrouping" function but I'm including "GetGrouping" so you can also use it at the main grouping level. Include the call to GetSubGrouping in the "Group on"; you'll also find that "GetField" is useful instead of coding a lot of iif's for controlling the visibility of the groupings.

Hope this helps

Glenn L

SharedFunction GetGrouping(ByVal Parameters AsObject, ByVal Fields AsObject) AsObject

Return GetField(Fields, Parameters("GroupBy").Value, "no_grouping")

EndFunction

SharedFunction GetSubGrouping(ByVal Parameters AsObject, ByVal Fields AsObject) AsObject

Return IIf(Parameters("GroupBy").Value = "no_grouping" _

Or Parameters("GroupBy").Value = Parameters("SubGroupBy").Value, Nothing, GetField(Fields, Parameters("SubGroupBy").Value, "no_sub_grouping"))

EndFunction

SharedFunction GetField(ByVal Fields AsObject, ByVal FieldName AsString, ByVal NoGroupingValue AsString) AsObject

If Trim$(FieldName) = Trim$(NoGroupingValue) Then

ReturnNothing

ElseIf IsDate(Fields(Trim$(FieldName)).Value) Then

Return FormatDateTime(Fields(Trim$(FieldName)).Value, 2)

Else

Return Fields(Trim$(FieldName)).Value

EndIf

EndFunction

|||

Thank you Glenn! That did the trick!

Helen

Dynamic Filter

Hi,
I have a Reporting Services report that I would like to add a dynmaic filter
to. I want to allow the users to filter based on two seperate fields (or
view all data). Example: if the user selects "Field1" from the filter
drop-down, I only want to display records in the dataset that have a value in
Field1 (i.e. ignore those with null values). The same would hold true if the
user would select "Field2". If the user does not select a value for this
parameter (i.e. it is blank), I don't want to limit the data.
Is this possible? I would prefer to accomplish this without using dynamic
sql.
Let me know if you have any questions...
Thanks for the help!
-David.are you using embedded sql code, or a stored procedure call?
"David" wrote:
> Hi,
> I have a Reporting Services report that I would like to add a dynmaic filter
> to. I want to allow the users to filter based on two seperate fields (or
> view all data). Example: if the user selects "Field1" from the filter
> drop-down, I only want to display records in the dataset that have a value in
> Field1 (i.e. ignore those with null values). The same would hold true if the
> user would select "Field2". If the user does not select a value for this
> parameter (i.e. it is blank), I don't want to limit the data.
> Is this possible? I would prefer to accomplish this without using dynamic
> sql.
> Let me know if you have any questions...
> Thanks for the help!
> -David.|||Embedded sql...
"Carl Henthorn" wrote:
> are you using embedded sql code, or a stored procedure call?
> "David" wrote:
> > Hi,
> >
> > I have a Reporting Services report that I would like to add a dynmaic filter
> > to. I want to allow the users to filter based on two seperate fields (or
> > view all data). Example: if the user selects "Field1" from the filter
> > drop-down, I only want to display records in the dataset that have a value in
> > Field1 (i.e. ignore those with null values). The same would hold true if the
> > user would select "Field2". If the user does not select a value for this
> > parameter (i.e. it is blank), I don't want to limit the data.
> >
> > Is this possible? I would prefer to accomplish this without using dynamic
> > sql.
> >
> > Let me know if you have any questions...
> >
> > Thanks for the help!
> >
> > -David.|||An example of your code would be helpful. when you say "Filter", are talkign
about reducing the size of your result set by using the defined parameters in
a where clause. it looks like you want to return different columns from a
table depending on which parameters are picked. Which is it? both?
if you are talking columns, use the visibility expression to hide the
unwanted columns based on the parameter value. if you are talking where
clause, then you can get around using dynamic sql by using a case stmt in the
stmt. I.e. where field1=case when @.param1<>'' then @.Param1 else field1 end
hth!
"David" wrote:
> Embedded sql...
> "Carl Henthorn" wrote:
> > are you using embedded sql code, or a stored procedure call?
> >
> > "David" wrote:
> >
> > > Hi,
> > >
> > > I have a Reporting Services report that I would like to add a dynmaic filter
> > > to. I want to allow the users to filter based on two seperate fields (or
> > > view all data). Example: if the user selects "Field1" from the filter
> > > drop-down, I only want to display records in the dataset that have a value in
> > > Field1 (i.e. ignore those with null values). The same would hold true if the
> > > user would select "Field2". If the user does not select a value for this
> > > parameter (i.e. it is blank), I don't want to limit the data.
> > >
> > > Is this possible? I would prefer to accomplish this without using dynamic
> > > sql.
> > >
> > > Let me know if you have any questions...
> > >
> > > Thanks for the help!
> > >
> > > -David.|||If a user selects one of the filter criteria, I want to only show those
records that have a value in that field. I do not want to change the columns
that are returned. In effect, it would reduce the size of the dataset.
However, I just want to filter the data not exclude it from the dataset (to
reduce round-trips to the database). Also, the report parameter will contain
the field name for the users to select from (not the value).
Example: if the user selects "Field1" from the filter
drop-down, I only want to display records in the dataset that have a value in
Field1 (i.e. not show those with null values). The same would hold true if
the
user would select "Field2". If the user does not select a value for this
parameter (i.e. it is blank/null), I want to show all records.
"Carl Henthorn" wrote:
> An example of your code would be helpful. when you say "Filter", are talkign
> about reducing the size of your result set by using the defined parameters in
> a where clause. it looks like you want to return different columns from a
> table depending on which parameters are picked. Which is it? both?
> if you are talking columns, use the visibility expression to hide the
> unwanted columns based on the parameter value. if you are talking where
> clause, then you can get around using dynamic sql by using a case stmt in the
> stmt. I.e. where field1=case when @.param1<>'' then @.Param1 else field1 end
> hth!
> "David" wrote:
> > Embedded sql...
> >
> > "Carl Henthorn" wrote:
> >
> > > are you using embedded sql code, or a stored procedure call?
> > >
> > > "David" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have a Reporting Services report that I would like to add a dynmaic filter
> > > > to. I want to allow the users to filter based on two seperate fields (or
> > > > view all data). Example: if the user selects "Field1" from the filter
> > > > drop-down, I only want to display records in the dataset that have a value in
> > > > Field1 (i.e. ignore those with null values). The same would hold true if the
> > > > user would select "Field2". If the user does not select a value for this
> > > > parameter (i.e. it is blank), I don't want to limit the data.
> > > >
> > > > Is this possible? I would prefer to accomplish this without using dynamic
> > > > sql.
> > > >
> > > > Let me know if you have any questions...
> > > >
> > > > Thanks for the help!
> > > >
> > > > -David.

dynamic column population?

Hello I'm new to reporting services (2000)
How do I dynamically populate a matrix column (or table) using the
parameters entered by users?
Example: A user enters a date range to filter the dataset. Parameters are
Start Date and End Date.
Then I want to be able to display in the report all the days between start
date and end date in the columns so that the
Table headers contain all the days/dates between start date and end date.
Start: 1/1/2006
End: 1/7/2006
--Col1: 1/1--Col2: 1/2--Col3: 1/3--Col4:
1/4--Col5:1/5--Col6: 1/6--Col7: 1/7
Row1
Row2
Row3
..how is this implemented in reporting services? is this possible?hey man,
right click on the 1st column header and select [expression] then you will
have to type something like:
=Parameters!startdate.Value
for the other columns you have to add 1 or 2 or 3 to the [days] part of the
parameter... then for the last column right click and select [expression]
then type:
=Parameters!enddate.Value
peace out...
"Abi" wrote:
> Hello I'm new to reporting services (2000)
> How do I dynamically populate a matrix column (or table) using the
> parameters entered by users?
> Example: A user enters a date range to filter the dataset. Parameters are
> Start Date and End Date.
> Then I want to be able to display in the report all the days between start
> date and end date in the columns so that the
> Table headers contain all the days/dates between start date and end date.
> Start: 1/1/2006
> End: 1/7/2006
>
> --Col1: 1/1--Col2: 1/2--Col3: 1/3--Col4:
> 1/4--Col5:1/5--Col6: 1/6--Col7: 1/7
> Row1
> Row2
> Row3
> ..how is this implemented in reporting services? is this possible?
>
>

Dynamic background color when using Jump To

Is it possible to dynamicly change the background color of the row(s) in the
destination report when using Jump To. I have a report that users click a
value and it jumps to another report. Whatever value the user clicks on in
the source report I would like to highlight in the destination report so they
can easily view all of the detail records.Define another parameter in the inside report. Then use the parameter to set
the row background color...
--
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
"Shoeman" <Shoeman@.discussions.microsoft.com> wrote in message
news:7B5B3CCE-1519-48B9-BBE9-932509D431A8@.microsoft.com...
> Is it possible to dynamicly change the background color of the row(s) in
> the
> destination report when using Jump To. I have a report that users click a
> value and it jumps to another report. Whatever value the user clicks on
> in
> the source report I would like to highlight in the destination report so
> they
> can easily view all of the detail records.|||I actually have 1 report with 5 tables. How would I assign the value of the
item clicked to the parameter I created. I have created an expression for
the BackroundColor that I think will work if I could find a way to pass it
the value of the parameter.
=iif(Fields!MENU_NAME.Value = Parameters!Parameter2.Value, "Transparent",
"Blue")
"Wayne Snyder" wrote:
> Define another parameter in the inside report. Then use the parameter to set
> the row background color...
> --
> 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
> "Shoeman" <Shoeman@.discussions.microsoft.com> wrote in message
> news:7B5B3CCE-1519-48B9-BBE9-932509D431A8@.microsoft.com...
> > Is it possible to dynamicly change the background color of the row(s) in
> > the
> > destination report when using Jump To. I have a report that users click a
> > value and it jumps to another report. Whatever value the user clicks on
> > in
> > the source report I would like to highlight in the destination report so
> > they
> > can easily view all of the detail records.
>
>