Incorrect Subtotal in Matrix

Hi,

I am using the matrix element. in the data part i m using the aggregate function CountDistinct. and when i m taking the subtotal for this value across rows the total is coming wrong. while in the same matrix i have other data values also which uses functions SUM, for these the Subtotal is coming correct.

Can somebody tell me why this is happening with CountDistinct function.

Thanks in advance.

Count will count the number of occurrences of this record in a row or column. It will not add them together. Even if your value is more than 1, it will only count 1 time and add 1 to the total.
SUM adds the values in the rows together. That's why the subtotal will work correctly but CountDistinct not.

Hope this can help,
|||

Thanks for a quick response eduard,

what should i do to get the sum of all the rows where the countdistinct function is used?

|||Any work around on this?|||

the 'sum distinct' workaround might help

look for the whitepaper by 'fang wang' which has this sample in

Incorrect Subtotal in Matrix

Hi,

I am using the matrix element. in the data part i m using the aggregate function CountDistinct. and when i m taking the subtotal for this value across rows the total is coming wrong. while in the same matrix i have other data values also which uses functions SUM, for these the Subtotal is coming correct.

Can somebody tell me why this is happening with CountDistinct function.

Thanks in advance.

Count will count the number of occurrences of this record in a row or column. It will not add them together. Even if your value is more than 1, it will only count 1 time and add 1 to the total.
SUM adds the values in the rows together. That's why the subtotal will work correctly but CountDistinct not.

Hope this can help,
|||

Thanks for a quick response eduard,

what should i do to get the sum of all the rows where the countdistinct function is used?

|||Any work around on this?|||

the 'sum distinct' workaround might help

look for the whitepaper by 'fang wang' which has this sample in

sql

Incorrect parent child aggregate values in SSAS browser

I've created an "Account" dimension using parent child relationships and added a unary operator to the dimension e.g. "+" and "-" to aggregate things like gross margin = sales - cost of sales.

The dimension hierarchy shows correct operators and levels (it is a ragged dimension) in the dimension browser, however when I go to the cube browser and place the hirearchy in the rows some of the members which have children cannot be "expanded" to see the children while others can. Some members when expanded show children which do not belong to them (i.e. different than what is shown in the dimension browser) and some of the values in the grid return #VALUE.

Also as diferent members are expanded it seems to effect the value of the fact data in the grid for other members i.e. expanding members lower in the dimension list will suddenly populate values in the grid which were #VALUE for members that were higher up in the dimesion list.

The odd thing is that while I can't tell for sure (since I can't see down to the leaf level on many of the members) the aggregates do seem to be applying the unary operator correctly.

It seems that the problem has taken care of itself. In each of the dimensions I had an attribute with the same name as the dimension. By renaming and in some instances deleting the atributes with the same name as the dimension it now appears that the hierarchy works properly in the SSAS browser and returns the results I want.|||

Just to let you know that I am using the Account Dimension with success with 115m source records. I am using the unary operator + and ~ and custom members in certain cases.

Attribute names and dimension names caught me out earlier on as well.

Let me know if you need any info.

|||

Thank you very much for your comment. I'm really glad to see that it is working with such a large data set.

May I ask how deep do the levels go in your account dimension and what storage mechanisim do you use? ROLAP, HOLAP, MOLAP

I am finding that I when I do an MDX query in SQL Management Studio that I'm only able to return leaf level values not aggregates. I'm using the default (I believe MOLAP) storage mechanisim. Is this the way it is supposed to work? I've worked with other OLAP tools for almost 20 years where Excel is used as the front end and have never had to worry about differenciating between leaf or non leaf data nor worry about preaggregating data in the cube. I'm a bit new to the MSAS world though so forgive me if I'm asking novice questions.

Thank you again for your insight.

|||MOLAP is used as the storage mechanism. I have not optimized the aggregates and get reasonable performance on a quad processor with 8gb. The cube is 6.16gb. I have never tried other forms of storage as MOLAP is efficient. The db is 120gb. With aggregation, the cube size will increase.
Your account dimension needs to be set-up correctly. It can have a ragged hierarchy. Ensure that the sort order is correct. eg.
Key ParentKey Account Descr
1000 Null Gross Sales
2000 Null Advertising
2100 2000 TV Advertising
2200 2100 Magazine Adv.
etc.
When u drag the account hierarchy on Mgt Studio, you should see the top levels, 1000 and 2000. Then u can drill down into children of 2000.
Just to let u know there is a bug in backing up a cube db with one of the db files > 4gb. I have to partition the cube and try again. I have logged it on the forum here.

|||

Again, thanks a million for the info. You were right, I was able to drag the account hierarchy on to Mgt Studio and drill down. I was also able to do the MDX query and return the fact data for the non leaf level (parent) members of the dimension.

I really appreciate your input.

Inconsistency in HTML display

Hi all i am currently using a custom aggregate function in the Stored Proc to Concatenate certain fields and group the others. The concatenation is done be leaving an empty line (Line Break) inbetween. The problem is that the New Line Break is getting displayed in the PDF format of the Reports generated using Reporting Service 2005 but in the HTML (using IE) format the Line break does not occur, can anyone help me out with this. Doesnt IE support New Line?

You can change the width of this line with specific property (lineheigt) on that TABLEROW object. You must complete the "write" expression on that property and the results are fine.

For example you can use the function posted below to get the number of occurence of char(13) character.

TABLEROW1.LineHeight= n*(1-occurs(@.yourstring,char(13))

Create function occurs

(

@.iStr varchar(4000),

@.fStr varchar(100)

)

Returns smallint As

Begin

Declare @.rStr varchar(100)

Select @.rStr = case when charindex(left(@.fStr,1),@.fStr,2)>0 then stuff(@.fStr,charindex(left(@.fStr,1),@.fStr,2),0,'~') else Replicate('~',datalength(@.fStr)+1) end

Return(datalength(Replace(Replace(@.iStr,@.fStr,@.rStr),@.fStr,@.rStr)) - datalength(@.iStr))

End