Dimwit MDX count problem

I have run into a small problem. I have a dimension that identifies certain types of bills. I also have a measure that reports how many bills have been issued. There are several types of bills. But 1 of these is an adjustment bill. This is like a correction to an existing bill. These are represented as "A" bills in the DB

I need to count the number of unique bills, so that's a count of all the bills other than "A" bills.

I've looked at calculated members but am struggling with MDX syntax.

Anyone have any pointers or good articles I should read?

It should be something like this:`

with member [Measures].[Num]

as 'sum(except([Bills].[Level Name].members, [Bills].[ A ]), [Measures].[NumBills])'

Hope this helps,

Santi

Dimension, CurrentMember and a hierarchy

Greetings -

I am writing some MDX for a RS report, and seem to have hit a bit of a wall. The business folks keep changing their minds on what hierarchy they want to report from, so I would like to have it as a parameter to the report. Problem is that I don't know if there is a way to get the CurrentMember.Properties('Caption') without explicitly declaring the hierarchy. My MDX:

With Set [MySet] as

StrToSet(@.HierarchyAndMember)

Set [DateSet] as

StrToSet(@.Date, Constrained)

Member [Measures].[Display] as

[Dimension].[Hierarchy Name].CurrentMember.Properties('Caption')

I would like to do StrToSet(@.HierarchyMember).CurrentMember.Properties('caption') here

Select { [measures].[created invoices], [measures].[display] } on 0,

{ [MySet] * [DateSet] } on 1

from [cube]

I have tried several different things, and this is the closest I have found. Placing the StrToSet in either it's own set or directly in the MDX doesn't affect the results. Is there any way to get the current member of the dimension without hardcoding the hierarchy? Does anyone have any creative solutions, or at least any directions to go?

Thanks in advance,

John Hennesey

It turns out that the RS designer is a pain in the sense it wants you to explicitly select a hierarchy & member so it knows what fields are returned from MDX. It doesn't check the validity of the items during runtime, it passes the values into MDX and lets AS take over. I have the ability to get done exactly what I am looking to do.

Thanks though,

John

sql

Dimension Properties and Dimension Attributes not found

I am writing an MDX query and I want to include dimension attributes as part of the DIMENSION PROPERTIES section of the query.

SELECT

{ Measures.SalesAmount, Measures.ShipQuantity } on columns,

{ (Items.ItemNumber.Allmembers,

Time.Month.AllMembers) }

DIMENSION PROPERTIES

Items.Description, Items.Category,

MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

From Sales

My problem is that while I can include Items.Description in the dimension properties list, I cannot include Items.Category in the list. The query parser gives an error,

The [Items].[Category] dimension attribute was not found.

I look at the definition of the dimension, and I see no difference between the two attributes in the schema.

What should I be looking at to see if there are differences between these two attributes? Or am I doing something else wrong?

Mike

I'm guessing that Items.ItemNumber is an attribute hierarchy and so Items.ItemNumber.AllMembers includes the All member plus the members of the attribute. Since the server will only return dimension properties that apply to all the members returned for that dimension, having members from different levels can unintentionnally restrict the dimension properties. Try changing the mdx to remove the all member (probably something like Items.ItemNumber.ItemNumer.AllMembers).

Another possibility is that Items.Category is a valid member property name, but not one associated with the level you are querying. Check for a similarly named member property as its easy to confuse member properties for attribute hierarchies and user defined hierarchies. In this case the name you want might be something like "ItemNumber.Category" since it probably only applies to the ItemNumber level.

|||

I'm not sure I am understanding your answer.

Items.ItemNumber is an attribute heirarchy, and so (it appears) are Series, Family, Category, and a bunch of others.

There are two user-defined hierarchies.

I don't understand when to use Items.Description and Items.Description.Description - both work in the DIMENSION properties statement - the first is the hierarchy, the second is the level. Another items attribute hierarchy I have is [Product Life Cycle], and neither Items.[Product Life Cycle] nor Items.[Product Life Cycle].[Product Life Cycle] works in the dimension properties statement, tho they appear identical in the dimension editor.

Here is the Item Dimension in the BIDS dimension editor:

|||

Now I know why Items.Description works in my DIMENSION PROPERTIES statement.

It is referring to the built-in property of the dimension, the Description property that I see in the Dimension editor property window when I select the top node in the tree, the Items dimension node itself. It also works for Items.ID and Items.Name built-in properties.

Still, now the question remains, why can't I use Items.Category or Items.Whatever in my dimension properties statement?

|||

If Items.ItemNumber.Allmembers returns an all member, then that is likely your problem. Try the MDX satement with a single specific member.

Here's a re-wording of basically what I wrote in my first reply that might help.

When including member properties in MDX queries using the DIMENSION PROPERTIES syntax, here are two common causes for confusion.

First, the server will only return member properties that apply to all members requested in a hierarchy. This means that if you request members from multiple levels, you will only get member properties which you have requested and which exist for all of the levels from which you have requested members.So if you request “[MyDim].[MyHier].Members” you will only get member properties applying to all levels.Instead, you should specifically request just members of a single level using something like “[MyDim].[MyHier].[MyLevel].Members”.Note that most attributes hierarchies contain two levels – the all level and the attribute level.This means you can get different results from [MyDim].[MyAttribute].Members and [MyDim].[MyAttribute].[MyAttribute].Members.

A second source of confusion is the fact that you can have similarly named member property for attribute hierarchy levels and user defined hierarchy levels based on the same attributes.This is true even if the attribute hierarchy is not visible.As a result, you may be unintentionally using a perfectly valid member property name that doesn’t correspond to the level of the members you are requesting.This results in no error, but you don’t get back any member properties.

Dimension Filter in MDX

Hi guys,

I need to create a report using data from our OLAP. I'm using a MDX query to do it, but I founded some limitations that I want to know if its real or just my mistakes. I have a the following dimensions

- "Projects", that have Project Name and Company name

- "Company", that have my company name too

- "Months", that have the months and years about projects

If I'm developing a sql query, its seems like this:

select * from Projects p, Months m

where p.company in ('A', 'B', 'C')

and m.Year = 2007

But when I'm using MDX queries, I need the same flexibility to filter more than one data (like the IN at the sql query above) from the dimension that I'm looking for (like filter p.company from my PRojects tables)

Here is the sample of my code. If you look inside it, I'm filtering the best I can in the mdx query and after, I'm filtering better using T-sql. Its works fine, but its expensive and I need to get better performance. I want to filter my company using the project dimension and not company dimension. I'm also want to filter the Month dimension at my mdx query, but its says that I cant because I'm using it as a row member.

SELECT

"[Measures].[codProject]" as codProj,

"[Measures].[Month]" asMonth,

isnull("[Measures].[Valor Total]",0)as vlrTotal

FROM

OPENQUERY( DW2007,''

with

member [Measures].[codProject] as'[Project].Properties("key")'

member [Measures].[Month] as'[Month].Properties("key")'

SELECT

NON EMPTY { [Measures].[Valor Total], [Measures].[codProject], [Measures].[Month] }ON COLUMNS,

non empty {[Project].[Project].Members * [Month].[Month].MEMBERS }ON ROWS

FROM [Budget]

where( [Company].['+ rtrim(@.nmCompany) +'], [Year].['+ cast(@.Year as char(4)) +'] )

'')

wherecast("[Project].[Company].[member_caption]" as varchar(10))='''+ rtrim(@.nmCompany) +'''

andcast("[Month].[Year].[MEMBER_CAPTION]" aschar(4))=' + cast(@.Year as char(4)) + ''

Can anyone help me ?

Best Regards,

Tritech - Consultoria e Desenvolvimento de Solu??es em TI

I think the problem is that the "non empty" clauses are not really doing much as the two calculated measures that get the key properties will return a non empty value for every month and project. Something like the following, where I have used the iif() function to only return a value. Possibly even excluding these measures from the MDX query and then joining back to the dimension table in T-SQL might be faster.

SELECT

"[Measures].[codProject]" as codProj,

"[Measures].[Month]" asMonth,

isnull("[Measures].[Valor Total]",0)as vlrTotal

FROM

OPENQUERY( DW2007,''

with

member [Measures].[codProject] as'iif(IsEmpty(Measures.[Valor Total]),NULL,[Project].Properties("key"))'

member [Measures].[Month] as'iif(IsEmpty(Measures.[Valor Total]),NULL,[Month].Properties("key"))'

SELECT

NON EMPTY { [Measures].[Valor Total], [Measures].[codProject], [Measures].[Month] }ON COLUMNS,

non empty {[Project].[Project].Members * [Month].[Month].MEMBERS }ON ROWS

FROM [Budget]

where( [Company].['+ rtrim(@.nmCompany) +'], [Year].['+ cast(@.Year as char(4)) +'] )

'')

wherecast("[Project].[Company].[member_caption]" as varchar(10))='''+ rtrim(@.nmCompany) +'''

andcast("[Month].[Year].[MEMBER_CAPTION]" aschar(4))=' + cast(@.Year as char(4)) + ''

|||

Hi Darren,

Tks for your reply, but I want to know if can I put my sql where clause into the mdx query ? If I can, will be faster and solve many problems for me. I look in some place, that you can select a "cube", using a select like one. as its show in the folowing query that I get from Microsoft Team Foundation Server reports. Here its use a select with another, but using parameter, anyone know if can I do this with queries and dimensions ?

SELECT
NON EMPTY {
[Measures].[Current Work Item Microsoft_VSTS_Scheduling_BaselineWork], [Measures].[Current Work Item Microsoft_VSTS_Scheduling_CompletedWork], [Measures].[Current Work Item Microsoft_VSTS_Scheduling_RemainingWork] } ON COLUMNS,
NON EMPTY {
([Assigned To].[Person].[Person].ALLMEMBERS * [Work Item].[System_Title].[System_Title].ALLMEMBERS * [Work Item].[System_State].[System_State].ALLMEMBERS )
}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM (
SELECT
( STRTOSET(@.MicrosoftVSTSSchedulingFinishDateYearMonthDate, CONSTRAINED) ) ON COLUMNS
FROM (
SELECT ( STRTOSET(@.MicrosoftVSTSSchedulingStartDateYearMonthDate, CONSTRAINED) ) ON COLUMNS
FROM (
SELECT ( STRTOSET(@.AssignedToPerson, CONSTRAINED) ) ON COLUMNS
FROM (
SELECT ( STRTOSET(@.TeamProjectTeamProject, CONSTRAINED) ) ON COLUMNS
FROM [Team System]
)
)
)
)
WHERE ( IIF( STRTOSET(@.TeamProjectTeamProject, CONSTRAINED).Count = 1,
STRTOSET(@.TeamProjectTeamProject, CONSTRAINED),
[Team Project].[Team Project].currentmember ),
IIF( STRTOSET(@.MicrosoftVSTSSchedulingStartDateYearMonthDate, CONSTRAINED).Count = 1,
STRTOSET(@.MicrosoftVSTSSchedulingStartDateYearMonthDate, CONSTRAINED), [Microsoft_VSTS_Scheduling_StartDate].[Year Month Date].currentmember ), IIF( STRTOSET(@.MicrosoftVSTSSchedulingFinishDateYearMonthDate, CONSTRAINED).Count = 1, STRTOSET(@.MicrosoftVSTSSchedulingFinishDateYearMonthDate, CONSTRAINED), [Microsoft_VSTS_Scheduling_FinishDate].[Year Month Date].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

|||

hi Darrel,

I copy the query that you changed but doesnt work, but after I make some changes, its works. This make sense ? tks

BEFORE CHANGES

with

member [Measures].[codProject] as'iif(IsEmpty(Measures.[Valor Total]),NULL,[Project].Properties("key"))'

AFTER

with

member [Measures].[ProjectCode] as '[Project].Properties("key")'

member [Measures].[codProject] as'iif(IsEmpty(Measures.[Valor Total]),NULL, Measures.[ProjectCode] )'

Tritech Informática
Brasilia
Brasilsql

Dim design / MDX help

I have two queries one runs very fast and other very slow. The difference is very little is both. I must be doing some thing wrong is dimension design but not able to solve it.

[Eq up 2sd %] - is a calculated member which I bring to Pivot table to test fast and slow running queries.

When I look into profiler fast query has some events "Query Subcube" and then end query but for slow one I get "Query Subcube" then there is pause with Notification - Flight Recorder Snapshot begin then Server state discover begin/end and same group again and again.

My Instrument Sector Dim has attributes (attributes -> <relation>)

Instrument Desc -> Issuer Desc, Issuer Desc -> Sub Ind cd, Sub Ind cd -> Ind Cd, Ind Cd -> Instrument Sector.

Instrument Desc is Key with Instrument ID as Key field

Query is not only slow it max out cpu 100% too.

Fast Query

SELECT {[Measures].[Gghm Hldng],

[Measures].[Eq dn 50p],

[Measures].[Eq up 2sd %]}

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,

NON EMPTY CROSSJOIN(HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Sector Cd 1].[All]})})), HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Issuer Desc 1].[All]})})))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS

FROM [Position Scenario P and L]

WHERE ([Instrument].[Asset Type Cd].[All], [Investment].[Investment].[Investment Cd].&[228], [Date].[Date].[Year].&[2007].[Quarter 1].[February].[02/21/2007])

Slow Query

SELECT {[Measures].[Gghm Hldng],

[Measures].[Eq dn 50p],

[Measures].[Eq dn 2sd %]}

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,

NON EMPTY CROSSJOIN(CROSSJOIN(HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Sector Cd 1].[All]})})),

HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Issuer Desc 1].[All]})}))),

HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Instrument Desc 1].[All]})})))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS

FROM [Position Scenario P and L]

WHERE ([Instrument].[Asset Type Cd].[All], [Investment].[Investment].[Investment Cd].&[228], [Date].[Date].[Year].&[2007].[Quarter 1].[February].[02/21/2007])

Thanks guys - Ashok

--

Just to add more in my question, when l look into new performance guide there is example of dim like following

Product Key (PK) -> SubCategory -> Category
-> Size -> Size Range
-> Color
-> Description

In my case I am selecting in my query Category, Product Name (Name of key field), and Size Range. This is creating CROSSJOIN(CROSSJOIN 2 of them in MDX for Category and Size Range.

In my cube I have Issuer Desc 1, Instrument Desc 1 in place of Category and Size Range

Hi Ashok,

Could you clarify the following points, to help better understand your scenario?

What's the MDX expression for [Measures].[Eq dn 2sd %] - this could be crucial?|||

Thanks Deepak.

I think the problem was I didn't set "Non-empty behavior" in calculated member.

"Eq dn 2sd %" is a calculated member. I did notice some issues in my Dim design. I am in the middle of final testing I will confirm it has fixed or not today.

Based in my experience the use of "Non-empty behavior" in calculated member should be highlighted in Performance document for OLAP. It worked like magic in query difference but again allow me to confirm that by end of today.

-Ashok

confirmed ! it's "Non-empty behavior" in calculated member

Dim design / MDX help

I have two queries one runs very fast and other very slow. The difference is very little is both. I must be doing some thing wrong is dimension design but not able to solve it.

[Eq up 2sd %] - is a calculated member which I bring to Pivot table to test fast and slow running queries.

When I look into profiler fast query has some events "Query Subcube" and then end query but for slow one I get "Query Subcube" then there is pause with Notification - Flight Recorder Snapshot begin then Server state discover begin/end and same group again and again.

My Instrument Sector Dim has attributes (attributes -> <relation>)

Instrument Desc -> Issuer Desc, Issuer Desc -> Sub Ind cd, Sub Ind cd -> Ind Cd, Ind Cd -> Instrument Sector.

Instrument Desc is Key with Instrument ID as Key field

Query is not only slow it max out cpu 100% too.

Fast Query

SELECT {[Measures].[Gghm Hldng],

[Measures].[Eq dn 50p],

[Measures].[Eq up 2sd %]}

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,

NON EMPTY CROSSJOIN(HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Sector Cd 1].[All]})})), HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Issuer Desc 1].[All]})})))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS

FROM [Position Scenario P and L]

WHERE ([Instrument].[Asset Type Cd].[All], [Investment].[Investment].[Investment Cd].&[228], [Date].[Date].[Year].&[2007].[Quarter 1].[February].[02/21/2007])

Slow Query

SELECT {[Measures].[Gghm Hldng],

[Measures].[Eq dn 50p],

[Measures].[Eq dn 2sd %]}

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,

NON EMPTY CROSSJOIN(CROSSJOIN(HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Sector Cd 1].[All]})})),

HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Issuer Desc 1].[All]})}))),

HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Instrument Desc 1].[All]})})))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS

FROM [Position Scenario P and L]

WHERE ([Instrument].[Asset Type Cd].[All], [Investment].[Investment].[Investment Cd].&[228], [Date].[Date].[Year].&[2007].[Quarter 1].[February].[02/21/2007])

Thanks guys - Ashok

--

Just to add more in my question, when l look into new performance guide there is example of dim like following

Product Key (PK) -> SubCategory -> Category
-> Size -> Size Range
-> Color
-> Description

In my case I am selecting in my query Category, Product Name (Name of key field), and Size Range. This is creating CROSSJOIN(CROSSJOIN 2 of them in MDX for Category and Size Range.

In my cube I have Issuer Desc 1, Instrument Desc 1 in place of Category and Size Range

Hi Ashok,

Could you clarify the following points, to help better understand your scenario?

What's the MDX expression for [Measures].[Eq dn 2sd %] - this could be crucial?|||

Thanks Deepak.

I think the problem was I didn't set "Non-empty behavior" in calculated member.

"Eq dn 2sd %" is a calculated member. I did notice some issues in my Dim design. I am in the middle of final testing I will confirm it has fixed or not today.

Based in my experience the use of "Non-empty behavior" in calculated member should be highlighted in Performance document for OLAP. It worked like magic in query difference but again allow me to confirm that by end of today.

-Ashok

confirmed ! it's "Non-empty behavior" in calculated member

Dim design / MDX help

I have two queries one runs very fast and other very slow. The difference is very little is both. I must be doing some thing wrong is dimension design but not able to solve it.

[Eq up 2sd %] - is a calculated member which I bring to Pivot table to test fast and slow running queries.

When I look into profiler fast query has some events "Query Subcube" and then end query but for slow one I get "Query Subcube" then there is pause with Notification - Flight Recorder Snapshot begin then Server state discover begin/end and same group again and again.

My Instrument Sector Dim has attributes (attributes -> <relation>)

Instrument Desc -> Issuer Desc, Issuer Desc -> Sub Ind cd, Sub Ind cd -> Ind Cd, Ind Cd -> Instrument Sector.

Instrument Desc is Key with Instrument ID as Key field

Query is not only slow it max out cpu 100% too.

Fast Query

SELECT {[Measures].[Gghm Hldng],

[Measures].[Eq dn 50p],

[Measures].[Eq up 2sd %]}

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,

NON EMPTY CROSSJOIN(HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Sector Cd 1].[All]})})), HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Issuer Desc 1].[All]})})))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS

FROM [Position Scenario P and L]

WHERE ([Instrument].[Asset Type Cd].[All], [Investment].[Investment].[Investment Cd].&[228], [Date].[Date].[Year].&[2007].[Quarter 1].[February].[02/21/2007])

Slow Query

SELECT {[Measures].[Gghm Hldng],

[Measures].[Eq dn 50p],

[Measures].[Eq dn 2sd %]}

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,

NON EMPTY CROSSJOIN(CROSSJOIN(HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Sector Cd 1].[All]})})),

HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Issuer Desc 1].[All]})}))),

HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Instrument Desc 1].[All]})})))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS

FROM [Position Scenario P and L]

WHERE ([Instrument].[Asset Type Cd].[All], [Investment].[Investment].[Investment Cd].&[228], [Date].[Date].[Year].&[2007].[Quarter 1].[February].[02/21/2007])

Thanks guys - Ashok

--

Just to add more in my question, when l look into new performance guide there is example of dim like following

Product Key (PK) -> SubCategory -> Category
-> Size -> Size Range
-> Color
-> Description

In my case I am selecting in my query Category, Product Name (Name of key field), and Size Range. This is creating CROSSJOIN(CROSSJOIN 2 of them in MDX for Category and Size Range.

In my cube I have Issuer Desc 1, Instrument Desc 1 in place of Category and Size Range

Hi Ashok,

Could you clarify the following points, to help better understand your scenario?

What's the MDX expression for [Measures].[Eq dn 2sd %] - this could be crucial?|||

Thanks Deepak.

I think the problem was I didn't set "Non-empty behavior" in calculated member.

"Eq dn 2sd %" is a calculated member. I did notice some issues in my Dim design. I am in the middle of final testing I will confirm it has fixed or not today.

Based in my experience the use of "Non-empty behavior" in calculated member should be highlighted in Performance document for OLAP. It worked like magic in query difference but again allow me to confirm that by end of today.

-Ashok

confirmed ! it's "Non-empty behavior" in calculated member

Different results when updating a cube

I've updated an OLAP cube and I can see the new cell value in my report.

But I was surprised when I tried to execute a MDX against the changes I made and compared it with the data of my cube in the reportin-Services.

I updated a cube cell with the value of 1

The report showed - in fact - 1; but the MDX-query got 0.99999 back.

Why is their a different?

Any idea?

Florian

If the cell you updated was not right down at the leaf level, then AS would have split up the value you were allocating across all the leaf level cells under the one that you updated. My guess is that this has most likely resulted in a small rounding error. The report is probably just set to format to a given number of decimal places an is rounding up.

Different results for MDX queries when using Attribute Hierarchies

We receive different results for the follow 2 MDX expressions. The only difference is that the second parameter in the Where clause uses a separate dimension called Asset Class in the first query, whereas in the first it uses an Attribute Hierarchy dimension on the Asset dimension.

The first provides the expected results which is the top 10 Equity assets, whereas the second returns just 3 Equity assets which belong to the top 10 assets overall.

Can anyone explain this? Using a cross join in the Topcount function works, but unfortunately ProClarity which we are using does not deal with this properly.

Query 1

SELECT NON EMPTY { [Measures].[Value Base] } ON COLUMNS ,

NON EMPTY { TOPCOUNT( { [Asset].[Asset].[All].CHILDREN }, 10, ( [Measures].[Value Base] ) ) } ON ROWS

FROM [MIQB Daily]

WHERE ( [Period].[Month].&[2005-11-01T00:00:00], [Asset Class].[Asset Class Category].&[Equity])

Query 2

SELECT NON EMPTY { [Measures].[Value Base] } ON COLUMNS ,

NON EMPTY { TOPCOUNT( { [Asset].[Asset].[All].CHILDREN }, 10, ( [Measures].[Value Base] ) ) } ON ROWS

FROM [MIQB Daily]

WHERE ( [Period].[Year Month Hierarchy].[Month].&[2005-11-01T00:00:00], [Asset].[Asset Class Hierarchy].[Asset Class Category].&[Equity )

At first sight this might be an issue with your attribute relationships. Have you looked into that?|||

Yes we believe the relations have been set up correctly and the indicator on the hierarchy has turned green.

We think it is because the hierarchy in the Where clause is in the same dimension as the hierarchy in the Topcount function in the second case - possibly something to do with the auto exists?

Interestingly if you use the browser in the Dev Studio and filter on the [Asset].[Asset Class Hierarchy] in the sepate Filter pane, then doing a top 10 query works fine, but if you put the filter on the page section of the browser, it does not produce the correct results.

|||

Can you "translate" this to an Adventure Works cube? Do you get the same results if you run the two queries in management studio?

Regards

/Thomas

|||This is a known bug that Microsoft is fixing (see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=549706&SiteID=1). Should get it by the end of August. It is a major fix that will be backported to SP1 (as it required some fixes from the SP2 branch).|||

Many thanks for your post - we were worried that it might have been a 'feature' rather than a bug

Paul

|||We are testing the fix now and the results look promising.

Different results for MDX queries when using Attribute Hierarchies

We receive different results for the follow 2 MDX expressions. The only difference is that the second parameter in the Where clause uses a separate dimension called Asset Class in the first query, whereas in the first it uses an Attribute Hierarchy dimension on the Asset dimension.

The first provides the expected results which is the top 10 Equity assets, whereas the second returns just 3 Equity assets which belong to the top 10 assets overall.

Can anyone explain this? Using a cross join in the Topcount function works, but unfortunately ProClarity which we are using does not deal with this properly.

Query 1

SELECT NON EMPTY { [Measures].[Value Base] } ON COLUMNS ,

NON EMPTY { TOPCOUNT( { [Asset].[Asset].[All].CHILDREN }, 10, ( [Measures].[Value Base] ) ) } ON ROWS

FROM [MIQB Daily]

WHERE ( [Period].[Month].&[2005-11-01T00:00:00], [Asset Class].[Asset Class Category].&[Equity])

Query 2

SELECT NON EMPTY { [Measures].[Value Base] } ON COLUMNS ,

NON EMPTY { TOPCOUNT( { [Asset].[Asset].[All].CHILDREN }, 10, ( [Measures].[Value Base] ) ) } ON ROWS

FROM [MIQB Daily]

WHERE ( [Period].[Year Month Hierarchy].[Month].&[2005-11-01T00:00:00], [Asset].[Asset Class Hierarchy].[Asset Class Category].&[Equity )

At first sight this might be an issue with your attribute relationships. Have you looked into that?|||

Yes we believe the relations have been set up correctly and the indicator on the hierarchy has turned green.

We think it is because the hierarchy in the Where clause is in the same dimension as the hierarchy in the Topcount function in the second case - possibly something to do with the auto exists?

Interestingly if you use the browser in the Dev Studio and filter on the [Asset].[Asset Class Hierarchy] in the sepate Filter pane, then doing a top 10 query works fine, but if you put the filter on the page section of the browser, it does not produce the correct results.

|||

Can you "translate" this to an Adventure Works cube? Do you get the same results if you run the two queries in management studio?

Regards

/Thomas

|||This is a known bug that Microsoft is fixing (see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=549706&SiteID=1). Should get it by the end of August. It is a major fix that will be backported to SP1 (as it required some fixes from the SP2 branch).|||

Many thanks for your post - we were worried that it might have been a 'feature' rather than a bug

Paul

|||We are testing the fix now and the results look promising.

Different reportparameters for same dimension in different cube

Hi,

I have 1 report with 2 charts, both charts have their own dataset. The two datasets are mdx queries on 2 different cubes, but some dimensions have the same name.

Now I want to have 2 differenent selectable parameters for the [dim time] dimension. One for the first query in the first cube and the second for the other query in the other cube .

So I check in the mdx query builder, both dimensions as parameter, but because both dimensions have the same name, i have only one selectable [dim time] -parameter in my report.

How can i solve this?

Thanks,

Dennis

Dear Dennis

Please help me to pass a parameter thru reportbulder to get drill down from report1 to jump into report2 . I created report1 and report 2. I want to jump into report 2 using parameter . I am getting one error when I run the report1 after giving drill throu in property page of the report in reportBulder

"Query Parameter missing " . Please help me

regards

Polachan

|||

Hi Dennis,

You can solve this by mapping a second parameter to the dataset.

Under Report > Report Parameters, Add a new parameter.

Create a new name and copy over the rest of the information from the parameter created by the MDX designer.

Within the properties of the second dataset (click the ellipses next to the DataSet name), modify the Parameters Value to point at the new parameter you just created.

That should give you two parameters, each mapped to the appropriate dataset.

HTH,

Jessica