members of db_owner can't mangage roles?

I have two people assigned to the db_owner role in a database and they can do
everything except manage users and roles. For example when they try to look
at the properties of a role in EM they get the following error message:
Error229: EXECUTE permission denied on object ‘sp_helprolemember’, database
‘master’, owner dbo
(The same thing happens in QA)
In master, the public role has permission to execute sp_helprolemember and
both of these users are members of the public role in master.
I also tried adding them to the db_accessadmin and db_securityadmin roles in
the database as well as making them members of the securityadmin server role.
None of this worked either.
Any suggestions?
Did you check for user, roles denied execute on the stored
procedure?
use master
go
sp_helprotect 'sp_helprolemember'
-Sue
On Wed, 27 Apr 2005 14:22:07 -0700, "AlexB_05"
<AlexB_05@.discussions.microsoft.com> wrote:

>I have two people assigned to the db_owner role in a database and they can do
>everything except manage users and roles. For example when they try to look
>at the properties of a role in EM they get the following error message:
>Error229: EXECUTE permission denied on object sp_helprolemember, database
>master, owner dbo
>(The same thing happens in QA)
>In master, the public role has permission to execute sp_helprolemember and
>both of these users are members of the public role in master.
>I also tried adding them to the db_accessadmin and db_securityadmin roles in
>the database as well as making them members of the securityadmin server role.
> None of this worked either.
>Any suggestions?
|||That was it. Somehow the public role did not have permission to execute
sp_helprolemember. Once I granted those permissions everything worked.
"Sue Hoegemeier" wrote:

> Did you check for user, roles denied execute on the stored
> procedure?
> use master
> go
> sp_helprotect 'sp_helprolemember'
> -Sue
> On Wed, 27 Apr 2005 14:22:07 -0700, "AlexB_05"
> <AlexB_05@.discussions.microsoft.com> wrote:
>
>

members of db_owner can't mangage roles?

I have two people assigned to the db_owner role in a database and they can do
everything except manage users and roles. For example when they try to look
at the properties of a role in EM they get the following error message:
Error229: EXECUTE permission denied on object â'sp_helprolememberâ', database
â'masterâ', owner dbo
(The same thing happens in QA)
In master, the public role has permission to execute sp_helprolemember and
both of these users are members of the public role in master.
I also tried adding them to the db_accessadmin and db_securityadmin roles in
the database as well as making them members of the securityadmin server role.
None of this worked either.
Any suggestions?Did you check for user, roles denied execute on the stored
procedure?
use master
go
sp_helprotect 'sp_helprolemember'
-Sue
On Wed, 27 Apr 2005 14:22:07 -0700, "AlexB_05"
<AlexB_05@.discussions.microsoft.com> wrote:
>I have two people assigned to the db_owner role in a database and they can do
>everything except manage users and roles. For example when they try to look
>at the properties of a role in EM they get the following error message:
>Error229: EXECUTE permission denied on object ?sp_helprolemember?, database
>?master?, owner dbo
>(The same thing happens in QA)
>In master, the public role has permission to execute sp_helprolemember and
>both of these users are members of the public role in master.
>I also tried adding them to the db_accessadmin and db_securityadmin roles in
>the database as well as making them members of the securityadmin server role.
> None of this worked either.
>Any suggestions?|||That was it. Somehow the public role did not have permission to execute
sp_helprolemember. Once I granted those permissions everything worked.
"Sue Hoegemeier" wrote:
> Did you check for user, roles denied execute on the stored
> procedure?
> use master
> go
> sp_helprotect 'sp_helprolemember'
> -Sue
> On Wed, 27 Apr 2005 14:22:07 -0700, "AlexB_05"
> <AlexB_05@.discussions.microsoft.com> wrote:
> >I have two people assigned to the db_owner role in a database and they can do
> >everything except manage users and roles. For example when they try to look
> >at the properties of a role in EM they get the following error message:
> >
> >Error229: EXECUTE permission denied on object â'sp_helprolememberâ', database
> >â'masterâ', owner dbo
> >
> >(The same thing happens in QA)
> >
> >In master, the public role has permission to execute sp_helprolemember and
> >both of these users are members of the public role in master.
> >
> >I also tried adding them to the db_accessadmin and db_securityadmin roles in
> >the database as well as making them members of the securityadmin server role.
> > None of this worked either.
> >
> >Any suggestions?
>

members of db_owner can't mangage roles?

I have two people assigned to the db_owner role in a database and they can d
o
everything except manage users and roles. For example when they try to look
at the properties of a role in EM they get the following error message:
Error229: EXECUTE permission denied on object ‘sp_helprolemember’, data
base
‘master’, owner dbo
(The same thing happens in QA)
In master, the public role has permission to execute sp_helprolemember and
both of these users are members of the public role in master.
I also tried adding them to the db_accessadmin and db_securityadmin roles in
the database as well as making them members of the securityadmin server role
.
None of this worked either.
Any suggestions?Did you check for user, roles denied execute on the stored
procedure?
use master
go
sp_helprotect 'sp_helprolemember'
-Sue
On Wed, 27 Apr 2005 14:22:07 -0700, "AlexB_05"
<AlexB_05@.discussions.microsoft.com> wrote:

>I have two people assigned to the db_owner role in a database and they can
do
>everything except manage users and roles. For example when they try to loo
k
>at the properties of a role in EM they get the following error message:
>Error229: EXECUTE permission denied on object sp_helprolemember, databas
e
>master, owner dbo
>(The same thing happens in QA)
>In master, the public role has permission to execute sp_helprolemember and
>both of these users are members of the public role in master.
>I also tried adding them to the db_accessadmin and db_securityadmin roles i
n
>the database as well as making them members of the securityadmin server rol
e.
> None of this worked either.
>Any suggestions?|||That was it. Somehow the public role did not have permission to execute
sp_helprolemember. Once I granted those permissions everything worked.
"Sue Hoegemeier" wrote:

> Did you check for user, roles denied execute on the stored
> procedure?
> use master
> go
> sp_helprotect 'sp_helprolemember'
> -Sue
> On Wed, 27 Apr 2005 14:22:07 -0700, "AlexB_05"
> <AlexB_05@.discussions.microsoft.com> wrote:
>
>sql

MDX-search LIKE argument

is there a way to search for LIKE in MDX?
for example I have people entering 5 digits zipcode
but there are zipcodes with dash 4 at the end that I also want
to capture as well...how do I do that?
thanks aheadhttp://groups-beta.google.com/group...p&q=like&qt_g=1
Ohjoo Kwon
"bc" <bc@.discussions.microsoft.com> wrote in message
news:FBCF8B1E-0AE0-4003-BD50-6DFEE3C25123@.microsoft.com...
> is there a way to search for LIKE in MDX?
> for example I have people entering 5 digits zipcode
> but there are zipcodes with dash 4 at the end that I also want
> to capture as well...how do I do that?
> thanks ahead
>|||outstanding !!!
did I tell you guys that you guys are a great service to the lost-soul dbas
out there?
great job
thank you
"Ohjoo Kwon" wrote:

> http://groups-beta.google.com/group...p&q=like&qt_g=1
>
> Ohjoo Kwon
> "bc" <bc@.discussions.microsoft.com> wrote in message
> news:FBCF8B1E-0AE0-4003-BD50-6DFEE3C25123@.microsoft.com...
>
>

MDX-Drillthrough

Hi,
I am trying to display the results of for example:
DRILLTHROUGH SELECT FROM [Request] WHERE [Day].[Day_Id].[5501]
inside a table of a report. I tried to follow
http://www.databasejournal.com/features/mssql/article.php/10894_3557476_10
but I didn't succeed:
I created a new dataset and entered the above statement into the Design mode
of the query builder. When I execute the statement, an error shows up:
"Query preparation failed. Additional information: Failed to parse the
query to detect if it is MDX or DMX. (MDXQueryGenerator)."
(In the Management Studio the query doesn't fail and shows the expected
results.)
What am I doing wrong?Use OLEDB instead of "Microsoft Analysis Services" as Datasource-Typ and it
works!
"Ralph Watermann" <We.Want@.NoSpam.de> schrieb im Newsbeitrag
news:eRMfBtQ8FHA.1276@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am trying to display the results of for example:
> DRILLTHROUGH SELECT FROM [Request] WHERE [Day].[Day_Id].[5501]
> inside a table of a report. I tried to follow
> http://www.databasejournal.com/features/mssql/article.php/10894_3557476_10
> but I didn't succeed:
> I created a new dataset and entered the above statement into the Design
> mode of the query builder. When I execute the statement, an error shows
> up:
> "Query preparation failed. Additional information: Failed to parse the
> query to detect if it is MDX or DMX. (MDXQueryGenerator)."
> (In the Management Studio the query doesn't fail and shows the expected
> results.)
> What am I doing wrong?
>

MDX year,month function

Is there a equivalent function from mdx to convert date to year or month like datepart from sql? ytd for example seems only working on a specified date member. But I want something like :

WITH

MEMBER [Measures].[ParameterCaption] AS '[aaaa].[bbbb].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[ParameterValue] AS '[aaaa].[bbbb].CURRENTMEMBER.UNIQUENAME'

MEMBER [Measures].[ParameterLevel] AS '[aaaa].[bbbb].CURRENTMEMBER.LEVEL.ORDINAL'

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,

[aaaa].[bbbb].AllMembers ON ROWS

FROM [XXXX]

...

but return all available years with the dates in the cube.

Thanks.

Regards

Alu

What I did was to amend my datasource view on which my cube is based, for date with a month and year field.

Then I did not need to worry about this in the MDX.|||MDX supports VBA function VBA!DatePart, but it performs worse then other MDX functions.|||

Hi thanks for the reply. However I have a data source view time dimension table and the mdx query which i have provided does not come from it. rather it comes from just a field with normal dates.

Thanks.

Regards

Alu

MDX to aggregate measure over specific dimensions

Hi,

I'm trying to write a calculated member in SSAS 2005 that will only aggregate across certain dimensions. For example, say I have five dimensions: D1 - 5. I only want the member to aggregate across three of these dimensions. So in the cube browser, when I drag these three dimensions in, I get the correct aggregated value. But when I then drag dimensions four and five in, I want this value to stay the same. (The measure is currently in a measure group that uses all five dimensions).

I was thinking that the solution would be to have an MDX expression of the form

([Measures].[Measure],
[D1].CurrentMember,
[D2].CurrentMember,
[D3].CurrentMember,
[D4].[(All)],
[D5].[(All)])

but I would prefer not to have to list all dimensions and their hierarchies, and have to remember to add to this list if I add a dimension in the future. In SSAS 2000 I had a lookup cube that only contained the dimensions I wanted to slice by.

My other solution was to create a named query on the fact table that this measure is currently in, create a measure group on that, and then set up the dimension usage so that only the dimensions I want to slice by are referenced. However, I'm thinking that there must be a better solution, probably using some MDX that I don't know about!

Thanks in advance.
James

Take a look at the MDX Root function. Here's an example of something that might work for you using Adventure Works:

with member measures.rootdemo as (
root([Date].[Calendar Year].currentmember),
root([Product].[Category].currentmember),
root([Customer]),
[Measures].[Internet Sales Amount]
)
select
[Date].[Calendar Year].[Calendar Year].members
*
[Date].[Calendar Semester of Year].[Calendar Semester of Year].members
*
[Customer].[Country].[Country].members
on 0,
[Product].[Category].[Category].members
on 1
from [Adventure Works]
where(measures.rootdemo)

You still need to list all the dimensions but at least you don't need to list the hierarchies. Watch out for this 'feature' of the function, though, which occurs when more than one member from a hierarchy is in scope:

with member measures.rootdemo as (
root([Date].[Calendar Year].currentmember),
root([Product].[Category].currentmember),
root([Customer]),
[Measures].[Internet Sales Amount]
)
select
[Date].[Calendar Year].[Calendar Year].members
*
[Date].[Calendar Semester of Year].[Calendar Semester of Year].members
on 0,
[Product].[Category].[Category].members
on 1
from [Adventure Works]
where(measures.rootdemo,{[Customer].[Country].&[Australia],[Customer].[Country].&[United Kingdom]})

HTH,

Chris

|||
Hi Chris, many thanks for your reply.

This indeed worked. Going back to my previous example, I created a calculated member as:

(Root([D4]),
Root([D5]),
[Measures].[Measure])

and the measure is only sliced by dimensions D1, D2 and D3.

Thanks for your help!

James
|||

Another approach to this problem is to put these measures into dedicated measure group which excludes dimensions D4 and D5 - then you will get the aggregates you need without calculated members. Of course, if you sometimes do need detailed information over them, then the approach with calculated member is the right one.

One more note - you don't have to use Root() function if all the attributes in your dimensions are aggregatable. You will get better performance if you simply use

([D4].[All], [D5].[All], [Measures].[Measure])

|||
Hi, yes I thought those were my two options.

I don't want to create another measure group as I'll have duplicate measures and the table with this measure in is large (it's a requirement in our system to keep processing time to a minimum). I was hoping that there would be a solution where I didn't have to list every dimension I wanted to remove from the slice (and remember to add to the list if I add dimensions in the future), but at least I have a solution!

Many thanks for your help.
James

MDX Ranking Question

Hi

We are trying to create a Ranking of a dimension based on multiple different measures. An example would be to rank the Sales Consultant by Num Sales, Total Sales and Num Customers.

In SQL, I would create three different temp tables, with the Sales Consultant, Measure and Ranking for each different measure. Then you could join these results together and generate the final ranking based on this result set.

Now the problem we are having is doing this in MDX. You can only order by one of the measures in any result set. Any way I look at this, it looks to be a multi step process to create this data. The only way I can think that this might work is to create a stored proc, but then this will limit the users ability to slice data by all the dimensions.

Are there any ideas on how we can do this that I have missed?

Thanks

Michael

Hi Michael,

Here's a sample Adventure Works ranking query - could you give more details, with examples, of your scenario?

With

Set [OrderedProducts] as

Order([Product].[Category].[Category]

* {[Measures].[Order Quantity]},

[Measures].[Order Quantity], BDESC)

+ Order([Product].[Category].[Category]

* {[Measures].[Sales Amount]},

[Measures].[Sales Amount], BDESC)

+ Order([Product].[Category].[Category]

* {[Measures].[Total Product Cost]},

[Measures].[Total Product Cost], BDESC)

Member [Measures].[QuantityRank] as

Rank(([Product].[Category].CurrentMember,

[Measures].[Order Quantity]),

Exists([OrderedProducts],

{[Measures].[Order Quantity]}))

Member [Measures].[SalesRank] as

Rank(([Product].[Category].CurrentMember,

[Measures].[Sales Amount]),

Exists([OrderedProducts],

{[Measures].[Sales Amount]}))

Member [Measures].[CostRank] as

Rank(([Product].[Category].CurrentMember,

[Measures].[Total Product Cost]),

Exists([OrderedProducts],

{[Measures].[Total Product Cost]}))

select

{[Measures].[Order Quantity],

[Measures].[QuantityRank],

[Measures].[Sales Amount],

[Measures].[SalesRank],

[Measures].[Total Product Cost],

[Measures].[CostRank]} on 0,

Extract(Exists([OrderedProducts],

{[Measures].[Order Quantity]}),

[Product].[Category]) on 1

from [Adventure Works]

--

Order Quantity QuantityRank Sales Amount SalesRank Total Product Cost CostRank
Bikes 90,220 1 $94,620,526.21 1 $84,105,429.60 1
Clothing 73,598 2 $2,117,613.45 3 $1,748,777.45 3
Accessories 61,931 3 $1,272,057.89 4 $637,590.73 4
Components 49,027 4 $11,799,076.66 2 $10,766,110.18 2

|||

Hi

Thanks very much!!!! This just shows how far I have to go to understand MDX...

I took your query and expanded it to give what we were looking for:

With

Set [OrderedProducts] as

Order([Product].[Category].[Category]

* {[Measures].[Order Quantity]},

[Measures].[Order Quantity], BDESC)

+ Order([Product].[Category].[Category]

* {[Measures].[Sales Amount]},

[Measures].[Sales Amount], BDESC)

+ Order([Product].[Category].[Category]

* {[Measures].[Total Product Cost]},

[Measures].[Total Product Cost], BDESC)

Member [Measures].[QuantityRank] as

Rank(([Product].[Category].CurrentMember,

[Measures].[Order Quantity]),

Exists([OrderedProducts],

{[Measures].[Order Quantity]}))

Member [Measures].[SalesRank] as

Rank(([Product].[Category].CurrentMember,

[Measures].[Sales Amount]),

Exists([OrderedProducts],

{[Measures].[Sales Amount]}))

Member [Measures].[CostRank] as

Rank(([Product].[Category].CurrentMember,

[Measures].[Total Product Cost]),

Exists([OrderedProducts],

{[Measures].[Total Product Cost]}))

Member [Measures].[OverallActivity] as

1/(

(1/[Measures].[QuantityRank])^2 +

(1/[Measures].[SalesRank])^2 +

(1/[Measures].[CostRank])^2

)

Set [OrderedActivity] as

Order([Product].[Category].[Category]

* {[Measures].[OverallActivity]},

[Measures].[OverallActivity], BASC)

Member [Measures].[ActivityRank] as

Rank(([Product].[Category].CurrentMember,

[Measures].[OverallActivity]),

Exists([OrderedActivity],

{[Measures].[OverallActivity]}))

select

{[Measures].[Order Quantity],

[Measures].[QuantityRank],

[Measures].[Sales Amount],

[Measures].[SalesRank],

[Measures].[Total Product Cost],

[Measures].[CostRank],

[Measures].[OverallActivity],

[Measures].[ActivityRank]} on 0,

Extract(Exists([OrderedProducts],

{[Measures].[Order Quantity]}),

[Product].[Category]) on 1

from [Adventure Works]

Which gives us the Overall ranking of the Product by these three rankings:

Order Quantity QuantityRank Sales Amount SalesRank Total Product Cost CostRank OverallActivity ActivityRank
Bikes 90,220 1 $94,620,526.21 1 $84,105,429.60 1 0.333333333333333 1
Clothing 73,598 2 $2,117,613.45 3 $1,748,777.45 3 2.11764705882353 3
Accessories 61,931 3 $1,272,057.89 4 $637,590.73 4 4.23529411764706 4
Components 49,027 4 $11,799,076.66 2 $10,766,110.18 2 1.77777777777778 2

Once again, Thanks for the help!!!!

|||

Hi

A further question to this. We are also wanting to make this ranking Time aware. Currently, you can only run the quesry for a specific time period (eg [dimDate].[Year].[2006]). If you try to create a crossjoin in the report to have all the above measures by 2005 and 2006, then the ranking does not work anymore. As far as I understand it, this will be the case as the Order Set does not know about the time dimension, and hasn't ordered the Products by Time.

So, how I see this is that I will need to add the time Dim to the order Set, but at what level. We would like to make this dynamic within the cube and would like the ranking to work in a year on year, quarter on quarter, month on month ...etc level. I would think that ordering by the lowest level of time (day in my case) might work, but would create a set that is extremely large.

Would this work, or is there a better way to do this?

Michael

|||

Hi Michael,

This is a common requirement, but it's beyond the ordered SQL temp table approach. You could use Rank() dynamically, as shown in the query below. But there might be a performance penalty - see this 2005 thread from the OLAP newsgroup:

With

Member [Measures].[QuantityRank] as

Rank([Product].[Category].CurrentMember,

[Product].[Category].[Category],

[Measures].[Order Quantity])

Member [Measures].[SalesRank] as

Rank([Product].[Category].CurrentMember,

[Product].[Category].[Category],

[Measures].[Sales Amount])

Member [Measures].[CostRank] as

Rank([Product].[Category].CurrentMember,

[Product].[Category].[Category],

[Measures].[Total Product Cost])

Member [Measures].[OverallActivity] as

1/(

(1/[Measures].[QuantityRank])^2 +

(1/[Measures].[SalesRank])^2 +

(1/[Measures].[CostRank])^2

)

Member [Measures].[ActivityRank] as

Rank([Product].[Category].CurrentMember,

[Product].[Category].[Category],

-[Measures].[OverallActivity])

select

{[Measures].[Order Quantity],

[Measures].[QuantityRank],

[Measures].[Sales Amount],

[Measures].[SalesRank],

[Measures].[Total Product Cost],

[Measures].[CostRank],

[Measures].[OverallActivity],

[Measures].[ActivityRank]} on 0,

[Date].[Calendar Year].[Calendar Year]

* [Product].[Category].[Category] on 1

from [Adventure Works]

|||Thanks for all the help! That is working fine, and is not too slow. It is not a commonly used calc so shouldn't pose a problem.

MDX Question

Can I do an MDX query with a start/stop filter specified as dates, but aggregate results by month? For example, could I query data between Jan 15, 2005 and April 14, 2005 and get results aggregated by month? I would expect four resulting rows or cells:

January (only contains data from the fifteenth to the thirty first)
February (data for entire month)
March (data for entire month)
April (only contains data from the first to the fourteenth)

I know how to filter by month and aggregate by month:

SELECT [Date].[Month].[2005-01]:[Date].[Month].[2005-04] ON Columns FROM [My Cube];

I also know how to filter by day and aggregate by day:

SELECT [Date].[Day].[2005-01-15]:[Date].[Day].[2005-04-14] ON Columns FROM [My Cube];

But I don't know how to filter by day and aggregate by month. Is this at all possible using MDX?

SELECT Date.Month.Month.MEMBERS ON 0 FROM

(SELECT [Date].[Day].[2005-01-15]:[Date].[Day].[2005-04-14] ON Columns FROM [My Cube])

|||

Mosha Pasumansky wrote:

SELECT Date.Month.Month.MEMBERS ON 0 FROM

(SELECT [Date].[Day].[2005-01-15]:[Date].[Day].[2005-04-14] ON Columns FROM [My Cube])

Brilliant! That worked. Thanks!

MDX query on a dimension member

Is it possible to query an analysis services dimension using MDX select syntax, if so

can someone list a example?

For example there is an employee dimension with Employee Name as the member name

and Employee ID as the member property. I want to write an MDX query to return the member name

if I have the member property.

Thanks

Umesh

Yes. Something like:

select { ... } on 0
from [$Your Dimension Name]

You can attach SQL profiler to the server and browse a dimension from BI Studio or SQL Server Management Studio. The profiler will trace the queries we send.

MDX multiple where condition

Is there any way in MDX to give multiple where clause:
Example in SQL:
SELECT * FROM Price
WHERE Type in("Cost1", "Cost2", "Cost3")
I have total 10 Cost types. But I need this query to return only 3 listed
above. It is simple with SQL.
My question is How to do this in MDX?
I have tried using filter but I am not getting correct values. Is there any
way in WHERE clause? Is there any other solution?
My Development environment is:
Reporting Services using Visual Studio.Net
OLAP: MDX Queries (Base Database is MS SQL Server)
Thanks,
SamThis question would more likely be answered in the OLAP newsgroup.
microsoft.public.sqlserver.olap
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:7F350E56-F781-4DDA-8C92-0045F1B0FEB5@.microsoft.com...
> Is there any way in MDX to give multiple where clause:
> Example in SQL:
> SELECT * FROM Price
> WHERE Type in("Cost1", "Cost2", "Cost3")
> I have total 10 Cost types. But I need this query to return only 3 listed
> above. It is simple with SQL.
> My question is How to do this in MDX?
> I have tried using filter but I am not getting correct values. Is there
> any
> way in WHERE clause? Is there any other solution?
> My Development environment is:
> Reporting Services using Visual Studio.Net
> OLAP: MDX Queries (Base Database is MS SQL Server)
> Thanks,
> Sam|||Use named set.
with
set [Type Cost Set] as '{ [Type].[Cost1] : [Type].[Cost3] }'
select
{ [measures] } on columns,
{ [Type Cost Set] } on rows
from cube
"Jeff A. Stucker" wrote:
> This question would more likely be answered in the OLAP newsgroup.
> microsoft.public.sqlserver.olap
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Sam" <Sam@.discussions.microsoft.com> wrote in message
> news:7F350E56-F781-4DDA-8C92-0045F1B0FEB5@.microsoft.com...
> > Is there any way in MDX to give multiple where clause:
> >
> > Example in SQL:
> > SELECT * FROM Price
> > WHERE Type in("Cost1", "Cost2", "Cost3")
> >
> > I have total 10 Cost types. But I need this query to return only 3 listed
> > above. It is simple with SQL.
> > My question is How to do this in MDX?
> >
> > I have tried using filter but I am not getting correct values. Is there
> > any
> > way in WHERE clause? Is there any other solution?
> >
> > My Development environment is:
> > Reporting Services using Visual Studio.Net
> > OLAP: MDX Queries (Base Database is MS SQL Server)
> >
> > Thanks,
> > Sam
>
>|||Cost types are not sequential. It will not work with range. Is there anything
in MDX where condition:
Example: [Type].[Type1 or Type2 or Type3]
Sam
"mike" wrote:
> Use named set.
> with
> set [Type Cost Set] as '{ [Type].[Cost1] : [Type].[Cost3] }'
> select
> { [measures] } on columns,
> { [Type Cost Set] } on rows
> from cube
> "Jeff A. Stucker" wrote:
> > This question would more likely be answered in the OLAP newsgroup.
> > microsoft.public.sqlserver.olap
> >
> > --
> > Cheers,
> >
> > '(' Jeff A. Stucker
> > \
> >
> > Business Intelligence
> > www.criadvantage.com
> > ---
> > "Sam" <Sam@.discussions.microsoft.com> wrote in message
> > news:7F350E56-F781-4DDA-8C92-0045F1B0FEB5@.microsoft.com...
> > > Is there any way in MDX to give multiple where clause:
> > >
> > > Example in SQL:
> > > SELECT * FROM Price
> > > WHERE Type in("Cost1", "Cost2", "Cost3")
> > >
> > > I have total 10 Cost types. But I need this query to return only 3 listed
> > > above. It is simple with SQL.
> > > My question is How to do this in MDX?
> > >
> > > I have tried using filter but I am not getting correct values. Is there
> > > any
> > > way in WHERE clause? Is there any other solution?
> > >
> > > My Development environment is:
> > > Reporting Services using Visual Studio.Net
> > > OLAP: MDX Queries (Base Database is MS SQL Server)
> > >
> > > Thanks,
> > > Sam
> >
> >
> >|||with
set [Type Cost Set] as '{ [Type].[Cost1], [Type].[Cost2], [Type].[Cost3] }'
or if you want you could use the filter function, but know that using filter
the whole set is return then the results filtered so you get overhead. See
BOL.
"Sam" wrote:
> Cost types are not sequential. It will not work with range. Is there anything
> in MDX where condition:
> Example: [Type].[Type1 or Type2 or Type3]
> Sam
>
> "mike" wrote:
> > Use named set.
> >
> > with
> > set [Type Cost Set] as '{ [Type].[Cost1] : [Type].[Cost3] }'
> >
> > select
> > { [measures] } on columns,
> > { [Type Cost Set] } on rows
> > from cube
> >
> > "Jeff A. Stucker" wrote:
> >
> > > This question would more likely be answered in the OLAP newsgroup.
> > > microsoft.public.sqlserver.olap
> > >
> > > --
> > > Cheers,
> > >
> > > '(' Jeff A. Stucker
> > > \
> > >
> > > Business Intelligence
> > > www.criadvantage.com
> > > ---
> > > "Sam" <Sam@.discussions.microsoft.com> wrote in message
> > > news:7F350E56-F781-4DDA-8C92-0045F1B0FEB5@.microsoft.com...
> > > > Is there any way in MDX to give multiple where clause:
> > > >
> > > > Example in SQL:
> > > > SELECT * FROM Price
> > > > WHERE Type in("Cost1", "Cost2", "Cost3")
> > > >
> > > > I have total 10 Cost types. But I need this query to return only 3 listed
> > > > above. It is simple with SQL.
> > > > My question is How to do this in MDX?
> > > >
> > > > I have tried using filter but I am not getting correct values. Is there
> > > > any
> > > > way in WHERE clause? Is there any other solution?
> > > >
> > > > My Development environment is:
> > > > Reporting Services using Visual Studio.Net
> > > > OLAP: MDX Queries (Base Database is MS SQL Server)
> > > >
> > > > Thanks,
> > > > Sam
> > >
> > >
> > >|||I found a work around, instead getting row wise I am getting required data in
column axis.
Thanks for your help.
Sam
"mike" wrote:
> with
> set [Type Cost Set] as '{ [Type].[Cost1], [Type].[Cost2], [Type].[Cost3] }'
> or if you want you could use the filter function, but know that using filter
> the whole set is return then the results filtered so you get overhead. See
> BOL.
> "Sam" wrote:
> > Cost types are not sequential. It will not work with range. Is there anything
> > in MDX where condition:
> > Example: [Type].[Type1 or Type2 or Type3]
> > Sam
> >
> >
> > "mike" wrote:
> >
> > > Use named set.
> > >
> > > with
> > > set [Type Cost Set] as '{ [Type].[Cost1] : [Type].[Cost3] }'
> > >
> > > select
> > > { [measures] } on columns,
> > > { [Type Cost Set] } on rows
> > > from cube
> > >
> > > "Jeff A. Stucker" wrote:
> > >
> > > > This question would more likely be answered in the OLAP newsgroup.
> > > > microsoft.public.sqlserver.olap
> > > >
> > > > --
> > > > Cheers,
> > > >
> > > > '(' Jeff A. Stucker
> > > > \
> > > >
> > > > Business Intelligence
> > > > www.criadvantage.com
> > > > ---
> > > > "Sam" <Sam@.discussions.microsoft.com> wrote in message
> > > > news:7F350E56-F781-4DDA-8C92-0045F1B0FEB5@.microsoft.com...
> > > > > Is there any way in MDX to give multiple where clause:
> > > > >
> > > > > Example in SQL:
> > > > > SELECT * FROM Price
> > > > > WHERE Type in("Cost1", "Cost2", "Cost3")
> > > > >
> > > > > I have total 10 Cost types. But I need this query to return only 3 listed
> > > > > above. It is simple with SQL.
> > > > > My question is How to do this in MDX?
> > > > >
> > > > > I have tried using filter but I am not getting correct values. Is there
> > > > > any
> > > > > way in WHERE clause? Is there any other solution?
> > > > >
> > > > > My Development environment is:
> > > > > Reporting Services using Visual Studio.Net
> > > > > OLAP: MDX Queries (Base Database is MS SQL Server)
> > > > >
> > > > > Thanks,
> > > > > Sam
> > > >
> > > >
> > > >

MDX Lag and Font use

Hi all,
I have a cube with a Year Dimension and Month dimension (wanted
intersections).
Does anyone have a calc-cell example that would perform something akin to
the following.
If (Current month sales - Last Month Sales)/Current month sales >15% then
cell font color bold and green, else if (Current month sales - Last Month
Sales)/Current month sales <20% then cell font color bold and red, else
default.
Thanks in advance.Here's an example for Foodmart Sales cube:
[vbcol=seagreen]
With
Member [Measures].[SalesTrend]
as '([Measures].[Unit Sales]
- ([Measures].[Unit Sales], [Time].PrevMember))
/[Measures].[Unit Sales]',
FORMAT_STRING = 'Percent'
Cell Calculation [SalesFormat]
for '({[Measures].[Unit Sales]}, {[Time].[Month].M
embers})'
as 'CalculationPassValue([Measures].[Unit Sales], -1, RELATIVE)',
FORE_COLOR = 'iif([Measures].[SalesTrend] > 0.15,
rgb(0,128,0), iif([Measures].[SalesTrend] < -0.20,
rgb(255,0,0), rgb(0,0,0)))',
FONT_FLAGS = 'iif([Measures].[SalesTrend] > 0.15
Or [Measures].[SalesTrend] < -0.20, 1, 0)'
Select {[Measures].[Unit Sales], [Measures].[SalesTrend
]} on columns,
Descendants([Time].[1997], [Time].[Month]) on rows
from Sales[vbcol=seagreen]
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!