TM1 Forum

Hi

I have a scenario

Region Property_Status Lease_ID Building_Sublet_Area SubLet_Area Gross_Area Calendar_Year Currency_from_CD Cost_Type Net_Amount Gross_Amount Sublet_Amount Sublet
Asia Active 123 10,000 10,000 2016 USD OPERATING 1,000. 1,000.
Asia Active 123 10,000 10,000 2016 USD ELECTRICITY 2,000. 3,000.
Asia Active 123 10,000 10,000 2016 USD RENT 3,000. 1,000. 10,000.


Out Put in Cognos Report

Building_Sublet_Area Net_Amount DW_Internal_Lease_ID Net_Amount Sublet
10,000 123 6,000 10,000

need to get consolidated - like building sublet should not sum but we need cost sum.

HI

The layout of your grid lost something in the upload. However, if the area is only related to the building and the building is a dimension then why not make that an attribute. However, to me if you were to make it a measure, I can't see why you wouldn't want the areas to sum, as then you could do a ratio like average cost per square metre.

Regards

Paul Simon

Hi Paul,

Thank you for the reply we can make area as measure as we need to calculate total area of a region with total amount.
We can't have sum of area as it duplicates .

Please let me know if there are any rules that we can make area non aggregate and amount as aggregate or load precalculated values in the cube.

Appreciate your answers

Thank you

Veera

Hi Veeras,

I think you are going to struggle to get a response as the layout of your grids are making your question very confused.

I suggest you do an examples of how your data looks in Excel now and then another example of how you want it to look.

Then screen-shot and attach to a post, include as much detail as possible and if there are any rules in the cube already make sure you show them.

Hi
Sorry for the confusion

The requirement is

Let me elaborate my requirement

"Lease Id" "Lease Name" "Area" "Cost Type" "Cost"

" 1 " "ABC" "10 sq" "C1" "100"
" 1 " "ABC" "10 sq" "C1" "200"
" 1 " "ABC" "10 sq" "C1" "300"
" 1 " "ABC" "10 sq" "C1" "400"


Lease, Area, lease Name comes from one cube A Cube
Cost type,Lease ID and cost comes from another cube B Cube

We need to combine these two cubes and when we do reporting on this cube

when we want Lease Id Lease Name Area and Cost without Cost type
The out put should be

"Lease Id" "Lease Name" "Area" "Cost"

" 1 " "ABC" "10 sq" "1000"



Consolidation should not sum


Hope this clarifies my question

Thank you

Veera

Veeras,

Obviously without cube/dimension names no one will be able to give you specific code etc.

However I believe that the issue you are describing is simply a case of you having a cube where you need consolidations to sum up some measures and not others.
Based on what you have provided such measures as "Lease Name" would not consolidate anyway as they are string (or text) so you would instead be left with a blank cell at the consolidation intersection. Other measures such as "Lease ID" you have shows as being a numeric value but based on the description I would argue that it should also probably be set as a string measure (as such also not consolidating) however if for some reason you have decided to set it as a numeric measure then you can add a simple C-Level rule to set it to 0.
The above code will keep your values at the lowest level but will set any consolidated intersections to zero. The same would work for measures such as "Area" but it looks like from your example data that it is set as string anyway since you have "10 sq" as opposed to just "10"?
If though your areas are set as numeric measures then you can either set the consolidated values to 0 using the rule above or you could look at the ConsolidatedAVG function to give you an average as opposed to a zero; such data may be useful for KPIs etc.