Unpivot columns of cube for SSRS report
<p>I need to modify a report that has a ton of fields to pivot some columns. It  uses a SSAS cube.  I don't know MDX and learning it for this task isn't really feasible.  </p>

<p>Example: Existing </p>

<pre><code>Date    Amount    SalesPerson1  Salesperson2 Manager 1  Product nbr
4/1/15  100       JSMtih        JDoe        TPrice     99
</code></pre>

<p>New Results</p>

<pre><code>Participant  Participant Role  Date    Amount Product Nbr
JSmith       SalesPerson1      4/1/15    100  99
JDOE         Salesperson2      4/1/15    100  99
TPRice       Manager1          4/1/15    100  99
</code></pre>

<p>I could rewrite the report using SQL and do an UNPIVOT (I did write the query), but to reconstruct the report would be a ton of work (it has around 10 cascading parameters, sections that collapse/expand, etc.) </p>

<p>Is modifying the MDX to unpivot some columns easy?  I couldn't find an unpivot for MDX and I am guessing it is just inherent in MDX. </p>

<p>MDX Code:</p>

<pre><code> SELECT 
    NONEMPTY({ 
        [Measures].[Outstanding Balance], 
        [Measures].[TM Fee], 
        [Measures].[Upfront Fee], 
        [Measures].[Non Recurring Fee], 
        [Measures].[Recurring Fee], 
        [Measures].[Syndication Fee], 
        [Measures].[Recap Fee], 
        [Measures].[International Fee], 
        [Measures].[Total Deposits Expected], 
        [Measures].[ACE], 
        [Measures].[Commitments], 
        [Measures].[Swap Fee], 
        [Measures].[Global Commitment], 
        [Measures].[Loan Fundings Expected One], 
        [Measures].[Loan Fundings Expected Two], 
        [Measures].[Change Amount], 
        [Measures].[Total Fees], 
        [Measures].[Private Balance] 
        }) ON COLUMNS, 
    NONEMPTY ({(
        [Account].[Account Number].[Account Number].ALLMEMBERS * 
        [Account].[Non Accrual Flag].[Non Accrual Flag].ALLMEMBERS * 
        [Deal].[Deal ID].[Deal ID].ALLMEMBERS * 
        [Deal].[Hierarchy Type].[Hierarchy Type].ALLMEMBERS * 
        [Deal].[Officer One].[Officer One].ALLMEMBERS * 
        [Deal].[Officer Two].[Officer Two].ALLMEMBERS * 
        [Deal].[Other Competing Banks].[Other Competing Banks].ALLMEMBERS * 
        [Deal].[Winning Bank].[Winning Bank].ALLMEMBERS * 
        [Deal].[Officer Three].[Officer Three].ALLMEMBERS * 
        [Deal].[Group].[Group].ALLMEMBERS * 
        [Deal].[Competitor One].[Competitor One].ALLMEMBERS * 
        [Deal].[Incumbentbank].[Incumbentbank].ALLMEMBERS * 
        [Deal].[Competitor Two].[Competitor Two].ALLMEMBERS * 
        [Deal].[New Target Market].[New Target Market].ALLMEMBERS * 
        [Deal].[Approval Description].[Approval Description].ALLMEMBERS * 
        [Deal].[Loan Disposition].[Loan Disposition].ALLMEMBERS * 
        [Deal].[Process Center].[Process Center].ALLMEMBERS * 
        [Deal].[Process Job].[Process Job].ALLMEMBERS * 
        [Deal].[Deal Type].[Deal Type].ALLMEMBERS * 
        [Deal].[Screener].[Screener].ALLMEMBERS * 
        [Deal].[Part Or Synd].[Part Or Synd].ALLMEMBERS * 
        [Deal].[Department].[Department].ALLMEMBERS * 
[Deal].[Party Package Id].[Party Package Id].ALLMEMBERS *
        [Deal].[Stage].[Stage].ALLMEMBERS * 
        [Deal].[Stage Date].[Stage Date].ALLMEMBERS * 
        [Deal].[Referral].[Referral].ALLMEMBERS * 
        [Deal].[Deal NAICS Code].[Deal NAICS Code].ALLMEMBERS * 
        [Deal].[Document Type].[Document Type].ALLMEMBERS * 
        [Deal].[Internal Referral].[Internal Referral].ALLMEMBERS * 
        [Deal].[External Referral].[External Referral].ALLMEMBERS * 
        [Deal].[Referral Category].[Referral Category].ALLMEMBERS * 
        [Deal].[Underwriter].[Underwriter].ALLMEMBERS * 
        [Deal].[Product Partner1].[Product Partner1].ALLMEMBERS * 
        [Deal].[Product Partner2].[Product Partner2].ALLMEMBERS * 
        [Deal].[Manager1].[Manager1].ALLMEMBERS * 
        [Deal].[Manager2].[Manager2].ALLMEMBERS * 
        [Deal].[Other Participant].[Other Participant].ALLMEMBERS * 
        [Deal].[Agent].[Agent].ALLMEMBERS * 
        [Deal].[Deal Source].[Deal Source].ALLMEMBERS * 
        [Deal].[Target Market Client].[Target Market Client].ALLMEMBERS * 
        [Deal].[Reason].[Reason].ALLMEMBERS * 
        [Deal].[Client Status As Of Deal Input].[Client Status As Of Deal Input].ALLMEMBERS * 
        [Deal].[Source System].[Source System].ALLMEMBERS * 
        [Deal].[Stage ID].[Stage ID].ALLMEMBERS * 
        [Deal].[ABLBDO].[ABLBDO]. ALLMEMBERS *
        [Party].[Customer Number].[Customer Number].ALLMEMBERS * 
        [Party].[Party NAICS Code].[Party NAICS Code].ALLMEMBERS * 
        [Party].[Party Name].[Party Name].ALLMEMBERS * 
        [Party].[Name].[Name].ALLMEMBERS * 
        [Product].[Product Type Number].[Product Type Number].ALLMEMBERS * 
        [Product].[CB Description].[CB Description].ALLMEMBERS * 
        [Service Transaction].[Service Transaction Key].[Service Transaction Key].ALLMEMBERS *      
        [Service Transaction].[Expected Funding Date Two].[Expected Funding Date Two].ALLMEMBERS * 
        [Service Transaction].[Expected Funding Date One].[Expected Funding Date One].ALLMEMBERS * 
        [Service Transaction].[Gross Revenue Date].[Gross Revenue Date].ALLMEMBERS * 
        [Service Transaction].[Loan Action].[Loan Action].ALLMEMBERS * 
        [Service Transaction].[Queue Creation Date].[Queue Creation Date].ALLMEMBERS * 
        [Service Transaction].[Loan Open Date].[Loan Open Date].ALLMEMBERS * 
        [Service Transaction].[Comments].[Comments].ALLMEMBERS * 
        [Service Transaction].[Next Maturity Date].[Next Maturity Date].ALLMEMBERS * 
        [Service Transaction].[Creation Date].[Creation Date].ALLMEMBERS * 
        [Service Transaction].[Total Loan Fundings Expected].[Total Loan Fundings Expected].ALLMEMBERS * 
        [Service Transaction].[Risk Rating].[Risk Rating].ALLMEMBERS * 
        [Service Transaction].[Last Modified On].[Last Modified On].ALLMEMBERS * 
        [Service Transaction].[Revenue].[Revenue].ALLMEMBERS * 
        [Service Transaction].[Note Rate].[Note Rate].ALLMEMBERS * 
        [Service Transaction].[RLOC Stock UDF].[RLOC Stock UDF].ALLMEMBERS * 
        [Service Transaction].[Basis Points].[Basis Points].ALLMEMBERS * 
        [Service Transaction].[Pricing Index].[Pricing Index].ALLMEMBERS * 
        [Service Transaction].[Law Firm].[Law Firm].ALLMEMBERS * 
        [Service Transaction].[Cost Center].[Cost Center].ALLMEMBERS * 
        [Service Transaction].[Expected Funding Date].[Expected Funding Date].ALLMEMBERS * 
        [Service Transaction].[Year].[Year].ALLMEMBERS * 
        [Service Transaction].[Estimated Closing Date].[Estimated Closing Date].ALLMEMBERS * 
        [Service Transaction].[Expected Funding Month].[Expected Funding Month].ALLMEMBERS * 
        [Service Transaction].[Expected Deposit Funding Date].[Expected Deposit Funding Date].ALLMEMBERS * 
        [Pipeline Dates].[Approved Date].[Approved Date].ALLMEMBERS * 
        [Pipeline Dates].[Closed Date].[Closed Date].ALLMEMBERS * 
        [Pipeline Dates].[Declined Date].[Declined Date].ALLMEMBERS * 
        [Pipeline Dates].[Discussion Date].[Discussion Date].ALLMEMBERS * 
        [Pipeline Dates].[Documentation Date].[Documentation Date].ALLMEMBERS * 
        [Pipeline Dates].[Lost Date].[Lost Date].ALLMEMBERS * 
        [Pipeline Dates].[PAR Date].[PAR Date].ALLMEMBERS * 
        [Pipeline Dates].[Proposal Date].[Proposal Date].ALLMEMBERS * 
        [Pipeline Dates].[Suspended Date].[Suspended Date].ALLMEMBERS * 
        [Pipeline Dates].[Term Sheet Date].[Term Sheet Date].ALLMEMBERS * 
        [Pipeline Dates].[Underwriting Date].[Underwriting Date].ALLMEMBERS * 
        [Pipeline Dates].[Withdrawn Date].[Withdrawn Date].ALLMEMBERS 
        )}) DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
FROM ( 
    SELECT ( STRTOSET(@DealDocumentType, CONSTRAINED) ) ON COLUMNS FROM ( 

      SELECT ( STRTOSET(@DealDealType, CONSTRAINED) ) ON COLUMNS FROM ( 

        SELECT ( STRTOSET(@ProductProductDescription, CONSTRAINED) ) ON COLUMNS FROM ( 
         SELECT ( STRTOSET(@DealNonPrimaryOfficer, CONSTRAINED) ) ON COLUMNS FROM ( 
          SELECT ( STRTOSET(@DealOfficerOne, CONSTRAINED) ) ON COLUMNS FROM ( 
           SELECT ( STRTOSET(@DealClientStatusAsOfDealInput, CONSTRAINED) ) ON COLUMNS FROM ( 
            SELECT ( STRTOSET(@ServiceTransactionCostCenter, CONSTRAINED) ) ON COLUMNS FROM ( 
             SELECT ( STRTOSET(@DealGroup, CONSTRAINED) ) ON COLUMNS FROM ( 
              SELECT ( STRTOSET(@ClosingDateCalendar, CONSTRAINED) ) ON COLUMNS FROM ( 
               SELECT ( STRTOSET(@DealStage, CONSTRAINED) ) ON COLUMNS FROM [Pipeline] 
                )
               )
              )
             )
            )
           )
          )
         )
        )
       )
</code></pre>