none
question on MDX Query RRS feed

  • Question


  • I am trying to add an additional parameter to the ssrs report  i.e ('Dimension - Tied Non-Tied') to an  existing query however it’s not working it is keep saying the syntax error.

    Query:
    EVALUATE
      CALCULATETABLE(
        ADDCOLUMNS(
     KEEPFILTERS(
    GENERATE(
     KEEPFILTERS(
    GENERATE(
     KEEPFILTERS(VALUES('Dimension - Account'[Segment Business Group])),
     VALUES('Dimension - Account Team'[SR Name]))),
    FILTER(
    KEEPFILTERS(VALUES('Dimension - Fiscal Calendar'[Fiscal Week])),
    OR(OR(OR(OR(
     NOT(ISBLANK('Measures - Units'[Units System Client])),
     NOT(ISBLANK('Measures - Units'[Units EUC SnP]))),
     NOT(ISBLANK('Measures - Revenue'[Revenue System Client]))),
     NOT(ISBLANK('Measures - Revenue'[Revenue EUC SnP]))),
     NOT(ISBLANK('Measures - Margin'[Margin EUC SnP]))
    )))
          ),
          "Units_System_Client", 'Measures - Units'[Units System Client],
          "Units_System_Client_OP", CALCULATE('Measures - Units'[Units System Client],'Dimension - Product'[Product LOB] = "Optiplex Desktops"),
          "Units_System_Client_LT", CALCULATE('Measures - Units'[Units System Client],'Dimension - Product'[Product LOB] = "Latitude"),
          "Units_System_Client_FW", CALCULATE('Measures - Units'[Units System Client],'Dimension - Product'[Product LOB] = "Fixed Workstations"),
          "Units_System_Client_MW", CALCULATE('Measures - Units'[Units System Client],'Dimension - Product'[Product LOB] = "Mobile Workstations"),
          "Revenue_System_Client", 'Measures - Revenue'[Revenue System Client],
          "Revenue_System_Client_OP", CALCULATE('Measures - Revenue'[Revenue System Client],'Dimension - Product'[Product LOB] = "Optiplex Desktops"),
          "Revenue_System_Client_LT", CALCULATE('Measures - Revenue'[Revenue System Client],'Dimension - Product'[Product LOB] = "Latitude"),
          "Revenue_System_Client_FW", CALCULATE('Measures - Revenue'[Revenue System Client],'Dimension - Product'[Product LOB] = "Fixed Workstations"),
          "Revenue_System_Client_MW", CALCULATE('Measures - Revenue'[Revenue System Client],'Dimension - Product'[Product LOB] = "Mobile Workstations"),
          "Margin_System_Client", 'Measures - Margin'[Margin System Client],
          "Revenue_EUC_SnP_CP_DB", CALCULATE('Measures - Revenue'[Revenue EUC SnP CP],'Custom - Product SnP'[SnP LOB Level 4] = "Dell Branded",'Dimension - Base Upsell'[Base Upsell Type]="Upsell"),
          "Revenue_EUC_SnP_CP_TP", CALCULATE('Measures - Revenue'[Revenue EUC SnP CP],'Custom - Product SnP'[SnP LOB Level 4] = "Third Party",'Dimension - Base Upsell'[Base Upsell 
    Type]="Upsell"),
          "Margin_EUC_SnP_CP_TP", CALCULATE('Measures - Margin'[Margin EUC SnP CP],'Custom - Product SnP'[SnP LOB Level 4] = "Third Party",'Dimension - Base Upsell'[Base Upsell Type]="Upsell"),
          "Units_EUC_SnP_CP_DB", CALCULATE('Measures - Units'[Units EUC SnP CP],'Custom - Product SnP'[SnP LOB Level 4] = "Dell Branded",'Dimension - Base Upsell'[Base Upsell Type]="Upsell"),
          "Units_EUC_SnP_CP_TP", CALCULATE('Measures - Units'[Units EUC SnP CP],'Custom - Product SnP'[SnP LOB Level 4] = "Third Party",'Dimension - Base Upsell'[Base Upsell Type]="Upsell"),
          "Revenue_EUC_SnP_Displays_DB", CALCULATE('Measures - Revenue'[Revenue EUC SnP Displays],'Custom - Product SnP'[SnP LOB Level 4] = "Dell Branded",'Dimension - Base Upsell'[Base Upsell Type]="Upsell"),
          "Revenue_EUC_SnP_Displays_TP", CALCULATE('Measures - Revenue'[Revenue EUC SnP Displays],'Custom - Product SnP'[SnP LOB Level 4] = "Third Party",'Dimension - Base Upsell'[Base Upsell Type]="Upsell"),
          "Margin_EUC_SnP_Displays_DB", CALCULATE('Measures - Margin'[Margin EUC SnP Displays],'Custom - Product SnP'[SnP LOB Level 4] = "Dell Branded",'Dimension - Base Upsell'[Base Upsell 
    Type]="Upsell"),
          "Units_EUC_SnP_Displays_TP", CALCULATE('Measures - Units'[Units EUC SnP Displays],'Custom - Product SnP'[SnP LOB Level 4] = "Third Party",'Dimension - Base Upsell'[Base Upsell Type]="Upsell"),
          "Revenue_EUC_SnP_Software_DB", CALCULATE('Measures - Revenue'[Revenue EUC SnP Software],'Custom - Product SnP'[SnP LOB Level 4] = "Dell Branded",'Dimension - Base Upsell'[Base Upsell Type]="Upsell"),
          "Revenue_EUC_SnP_Software_TP", CALCULATE('Measures - Revenue'[Revenue EUC SnP Software],'Custom - Product SnP'[SnP LOB Level 4] = "Third Party",'Dimension - Base Upsell'[Base Upsell Type]="Upsell"),
          "Margin_EUC_SnP_Software_DB", CALCULATE('Measures - Margin'[Margin EUC SnP Software],'Custom - Product SnP'[SnP LOB Level 4] = "Dell Branded",'Dimension - Base Upsell'[Base Upsell Type]="Upsell"),
          "Margin_EUC_SnP_Software_TP", CALCULATE('Measures - Margin'[Margin EUC SnP Software],'Custom - Product SnP'[SnP LOB Level 4] = "Third Party",'Dimension - Base Upsell'[Base Upsell Type]="Upsell"),
          "Units_EUC_SnP_Software_DB", CALCULATE('Measures - Units'[Units EUC SnP Software],'Custom - Product SnP'[SnP LOB Level 4] = "Dell Branded",'Dimension - Base Upsell'[Base Upsell Type]="Upsell"),
          "Units_EUC_SnP_Software_TP", CALCULATE('Measures - Units'[Units EUC SnP Software],'Custom - Product SnP'[SnP LOB Level 4] = "Third Party",'Dimension - Base Upsell'[Base Upsell Type]="Upsell")
        ),
        KEEPFILTERS(
          FILTER(
            KEEPFILTERS(VALUES('Dimension - Route to Market'[Route To Market])),
            AND(
              'Dimension - Route To Market'[Route To Market] = "Direct",
              ISBLANK('Dimension - Route To Market'[Route To Market]) = ISBLANK("Direct")
            )
          )
        ),
        KEEPFILTERS(
          FILTER(
            KEEPFILTERS(VALUES('Dimension - Fiscal Calendar'[Fiscal Quarter])),
            AND(
              'Dimension - Fiscal Calendar'[Fiscal Quarter] = @prm_FiscalQuarter,
              ISBLANK('Dimension - Fiscal Calendar'[Fiscal Quarter]) = ISBLANK(@prm_FiscalQuarter)
            )
          )
        ),
        KEEPFILTERS(
          FILTER(
            KEEPFILTERS(VALUES('Dimension - Geography'[Region])),
            AND(
              'Dimension - Geography'[Region] = @prm_Region,
              ISBLANK('Dimension - Geography'[Region]) = ISBLANK(@prm_Region)
            )
          )
        ),
        KEEPFILTERS(
          FILTER(
            KEEPFILTERS(VALUES('Dimension - Account Team'[SR Mgr Name])),
            AND(
              'Dimension - Account Team'[SR Mgr Name] = @prm_ISMName,
              ISBLANK('Dimension - Account Team'[SR Mgr Name]) = ISBLANK(@prm_ISMName)
            )
          )
        ),
      KEEPFILTERS(
          FILTER(
            KEEPFILTERS(VALUES('Dimension - Tied Non-Tied'[Tied Non-Tied Type])),
    AND('Dimension - Tied Non-Tied'[Tied Non-Tied Type] = @prm_Tied_NonTiedType,
    ISBLANK('Dimension - Tied Non-Tied'[Tied Non-Tied Type]) = ISBLANK(@prm_Tied_NonTiedType)
          )
        )
      )


    ORDER BY
      'Dimension - Fiscal Calendar'[Fiscal Week],
      'Dimension - Account'[Segment Business Group],
      'Dimension - Account Team'[SR Name]


    • Moved by Just KarlModerator Tuesday, February 9, 2016 3:07 PM Looking for the correct forum.
    Tuesday, February 9, 2016 7:55 AM

Answers

All replies