Profit and Loss Driven By Dept or By Site

[michaelc99]

Good Morning Everyone,

I received a request to create a P&L Report in TM1 for one of my entities. The P&L report was initially created in Excel where is continues to be managed. Each of the six P&L reports are driven by either a Site Code or Department. The challenge that I'm facing is that for specific Account + Department combinations, I need to pull all site codes. And, for specific Account + Site Code combinations, I need to pull all departments.

In my initial attempt, I created an attribute under Account Main that determines if the driver is Department or Site. Then, I created new consolidated and leaf members in the Measure dimension. In my General Ledger cube, I added rules to retrieve the balances for those conditions, but I am not seeing values in the the new members.

While I continue to tweak my rule, I wanted to step back and reach out to the community here to see if there is a better way to approach the unique P&L request. Would a separate P&L Cube be better to accomplish the goal, or can this be achieved in an already existing GL cube?

Thank you,
Michael

[lotsaram]

It's not possible to answer your question without you first providing more information about the current structure of the GL cube. What are the dimensions of the cube? Do Department and Site Code map to existing dimensions? What do you mean by the "measure" dimension? (Usually in a GL cube the Chart of Account is the measure.)

Why are you creating rules? By the sounds of it this is a mapping issue solvable in the front end report and the correct data already exists in the cube but to get the correct outcome in different use cases requires to pull consolidations for some and leaves for others. Assuming that all needed consolidations and combinations already exist it should be just a matter of some custom reports for certain use cases or building in sufficient smarts into the report that the correct combination can be dynamically selected.

[michaelc99]

It's not possible to answer your question without you first providing more information about the current structure of the GL cube. What are the dimensions of the cube? Do Department and Site Code map to existing dimensions? What do you mean by the "measure" dimension? (Usually in a GL cube the Chart of Account is the measure.)

Why are you creating rules? By the sounds of it this is a mapping issue solvable in the front end report and the correct data already exists in the cube but to get the correct outcome in different use cases requires to pull consolidations for some and leaves for others. Assuming that all needed consolidations and combinations already exist it should be just a matter of some custom reports for certain use cases or building in sufficient smarts into the report that the correct combination can be dynamically selected.

Thank you for your response, lotsaram. Let me see if I can add more context to my initial question.

The cube that I am describing above is my GL MAIN cube which has 12 dimensions ranging from: Version, Region, Entity, Currency, Department, Cost Center, Channel, Prod Center, Site, Account, Period and GL Main Measure. Yes, the department and site codes map to their respective dimensions. The GL Main Measure dimension helps me identify the source of the data set (I.e. Excel File, Local Cube, Eliminations, Adjustments, Forecast Cube, etc.)

You're correct that I will need consolidations in some cases and leaves for others. The reason that I approached this problem from a rules-standpoint is because I was attempting to summarize the data at a leaf-level then create a dynamic report in PAFE at a consolidated level, in conjuntion with ReportWORQ, to produce the 6 P&L reports.

It sounds like your approach, and maybe the best approach, would be to use Excel to create a custom report to use a leaf-level account member, determine its' driver, then use TM1 formulas to retrieve data based on the specific requirements. Depending on how it's setup, I might be able to maintain dynamic report capability. (see attached for breakout)

Thank you,
Michael

[lotsaram]

I think the easiest way to solve this (assuming the GUI is PAfE or Perspectives or Apliqo UX) would be to have a helper dimension which encodes valid reporting selections. (This dimension is artificial and just for lookups and isn't in the cube.) The helper dimension would have attributes for Account, Department, Site (and possibly other dimensions). The helper dimension would act like a master filter and one change of selection here would drive selections via the attribute mapping in the "dependent" dimensions. This can make a report quite dynamic and much easier for users as they don't need to know the details of the cube and independently select different things on different dimensions depending on the use case in order to get the correct data.

[michaelc99]

I think the easiest way to solve this (assuming the GUI is PAfE or Perspectives or Apliqo UX) would be to have a helper dimension which encodes valid reporting selections. (This dimension is artificial and just for lookups and isn't in the cube.) The helper dimension would have attributes for Account, Department, Site (and possibly other dimensions). The helper dimension would act like a master filter and one change of selection here would drive selections via the attribute mapping in the "dependent" dimensions. This can make a report quite dynamic and much easier for users as they don't need to know the details of the cube and independently select different things on different dimensions depending on the use case in order to get the correct data.

Thank you. I will look into creating a ReportingSelections dimension to serve as a dimension mapping tool. I will have to think about how to design the dimension to cover the 80~ accounts and the site v. department driver.

Thank you