Showing posts with label second. Show all posts
Showing posts with label second. Show all posts

Wednesday, March 7, 2012

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.

Saturday, February 25, 2012

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.

Friday, February 24, 2012

Different Header on Second Page

I have a report that requires a different header starting on the second page. I tried creating the header for page one in the body of the report, then creating the header for page two in the actual report header and setting PrintOnFirstPage to false. Unfortunately, this leaves a large space at the top of the first page, which is not acceptable to my users.

If there was a way to create a list box header that repeats at the top of each page, that would solve this problem as well, but I can't seem to find a way to do that either.

Any suggestions would be GREATLY appreciated.

You can use an iif statement in the testboxes of your header to change the contents depending on the global variable for page. If you need totally different header then use the hidden property to hide one header and display the other according to which page it is on.|||

Thank you. I actually tried something similar with a textbox in the body of the report and got the following error:

The Hidden expression for the textbox ‘textbox31’ refers to the global variable PageNumber or TotalPages. These global variables can be used only in the page header and page footer.

It didn't occur to me to try putting the textbox in the report header, but that works!

Thanks again!

Tuesday, February 14, 2012

difference??

Hi,
why the first statement returns nothing, but the second one returns the
result I want?
select soldto.soldtonumber from soldto where soldto.soldtonumber not in
(Select soldto from fsosoldto)
SELECT dbo.SoldTo.SoldToNumber, dbo.FSOsoldto.Soldto
FROM dbo.SoldTo Left JOIN
dbo.FSOsoldto ON dbo.SoldTo.SoldToNumber =
dbo.FSOsoldto.Soldto where FSOsoldto.Soldto is null
Tks
Edforget about this question since soldto contains null value that's why the
first statement always returns null
"Ed" wrote:

> Hi,
> why the first statement returns nothing, but the second one returns the
> result I want?
> select soldto.soldtonumber from soldto where soldto.soldtonumber not in
> (Select soldto from fsosoldto)
>
> SELECT dbo.SoldTo.SoldToNumber, dbo.FSOsoldto.Soldto
> FROM dbo.SoldTo Left JOIN
> dbo.FSOsoldto ON dbo.SoldTo.SoldToNumber =
> dbo.FSOsoldto.Soldto where FSOsoldto.Soldto is null
> Tks
> Ed