Sum of column instead of row
-
Hi,
I have got problem described with OLAP on Analysis services on following picture:
OLAP output shown as A shows SUM of Inventory Value = 1083. I spent a lot of time to get this SUM into every row that is shown on B but with no result 🙁 Anyone help me ??? Its urgent!!
Thx
-
A picture's worth a thousand words, but setup code is worth more. If you can provide the tables structure via CREATE TABLE statements, and some sample data via INSERT statements, and what the expected results of the sample data should be like, I bet several people would jump on this to help you out. So, help us help you. You might want to read the first link in my signature for how to do this.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
Agree with Wayne, needs some additional detail. Having said that, this often happens if you've made changes to the cube, in particular, that measure (group) has had it's relationship removed from the dimension. To confirm/check the Dimension Usage map and ensure that the measure group is related to the specific dim.
Steve.
-
Seems to me that a simple UPDATE with a join would do the trick and that's covered extensively in Books Online.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I am sorry guys. I forgot to write that its OLAP definition problem, not SQL! So I need dynamic, way how to sum results in column. Thx
-
Understood. Did you check the dimension usage tab for the cube? Typically, you get a single, consistent figure returned like this when there is no relationship between the measure (ie measure group that it belongs to) and the dimension you're using in the pivottable.
Steve.
-
Actualy, in looking closer, it looks like the Inventory Value is likely to be a calculated measure. There's something wrong/different between the way in which you created this between the two reports.
Just out of interest, you may want to consider utilizing the non-empty behaviour property on the calc member, tie it to the days on inventory measure. Also, you could look at adding an Iif statement to it, so you can return Nothing/NULL (ie empty) when there is no Days on Inventory. This would have the affect (on your 'A' report) of *not* returning all of those rows with ',00' as the value.
Steve.
-
Hi.
Thx, but I am new in AS and I dont understand what youre trying to say.
What I need is some calculation as calculated member like:
Create member [Inventory Value Column Sum]
AS
SUM ([Dim Time].currentmember.column, [Measure].[Inventory Value])
In this formula is written .column which obviously doesnt work, but that is something what I need. So it must be related to dim time during exploring cube in MS Excel.
-
stevefromOZ (4/23/2010)
Actualy, in looking closer, it looks like the Inventory Value is likely to be a calculated measure. There's something wrong/different between the way in which you created this between the two reports.Just out of interest, you may want to consider utilizing the non-empty behaviour property on the calc member, tie it to the days on inventory measure. Also, you could look at adding an Iif statement to it, so you can return Nothing/NULL (ie empty) when there is no Days on Inventory. This would have the affect (on your 'A' report) of *not* returning all of those rows with ',00' as the value.
Hi...thank for hint about null records.
A is what I am getting now using calculated member.
B is what I need to get.
-
Maybe I"m interpreting this incorrectly, since it's entirely different from everyone else's, but I think what you're looking for is the total of the Parent (perhaps ALL) member for the Inventory Value, right? If so, is it always for the ALL, or is there the possibility that you want it for something higher in a hierarchy?
Cheers,
Rick Todd -
Hi,
yes. Parent sum can help, but if i write something like [Dim Time].currentmember.parent, it doesnt give me that sum 🙁
-
It looks like the calculation is being broken down by the dimension you have on the rows in your diagram.
Without knowing that dimension's name, I can't give an exact answer, but try the following:
Create member [Inventory Value Column Sum]
AS
SUM
(
{
[Dim Time].currentmember.column * [YourDimension].[YourDimensionMember].[All]
}
,
[Measure].[Inventory Value]
)
The idea here is to get it to give the total answer for each row, so let me know how that works out.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply