none
calculated members from outer scope RRS feed

  • Question

  •  

    Dear,

    We want to calculate values based on existing values. For example:

    Table A

                Line 1

                Line 2

    Table B

                Line 3 = Line 1 + Line 2

    When working with custom rollup on the line attribute it works, but only when Line 1 & Line 2 & Line 3 are in the same or no hierarchy. ex.

    Line 1
    Line 2
    Line 3

    When filtering on Table or adding "Table" in the report like the first example, Line 3 becomes empty.

    The final result should be

    Table B
                Line 3

    The goal is that new tables/lines can be created based on existing dimension values, it should be flexible so the calculation should happen in the cube and the formula's stored in the database.

    Any suggestions,

    Thanks in advance,

    Bram




    • Edited by BramDe Thursday, March 1, 2012 9:34 AM
    Friday, February 17, 2012 2:07 PM

All replies

  • Hi Bram,

    Is your question with SQL Server Analysis Service or Reporting Service? Please make the question more clear.

    Regards,
    Jerry

    Monday, February 20, 2012 10:11 AM
    Moderator
  •  

    Hi Jerry,

    I want to calculate the calculation in the cube > SSAS. As a reporting tool, we use SSRS but excel, for power users, as well.

    The goal is that the end-user (via an interface of course) can create their own calculations without interference of IT, so the calculation definition should be stored in the database and be calculated in the cube.

    We have 3 dimensions "Table";"Line";"Column" that are related to the fact via m-n relations.

    What we want to achieve is a new calculation for example:
    Cell (Table: T_Custom A; Line: L_Custom 1; Column: C_x)
      = (Table D; Line 1; Column a) + (Table D; Line 2; Column a)

    We want to avoid calculating these new formulas in the ETL, as doing the calculation for millions of records, and as the base-values already exist, it isn't very pragmatic.

    Thanks in advance,

    Bram

    Monday, February 20, 2012 11:02 AM