reporting services - Unpivot columns of cube for SSRS report -
i need modify report has ton of fields pivot columns. uses ssas cube. don't know mdx , learning task isn't feasible.
example: existing
date amount salesperson1 salesperson2 manager 1 product nbr 4/1/15 100 jsmtih jdoe tprice 99 new results
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 i rewrite report using sql , unpivot (i did write query), reconstruct report ton of work (it has around 10 cascading parameters, sections collapse/expand, etc.)
is modifying mdx unpivot columns easy? couldn't find unpivot mdx , guessing inherent in mdx.
mdx 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 of deal input].[client status 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 ( select ( strtoset(@dealdocumenttype, constrained) ) on columns ( select ( strtoset(@dealdealtype, constrained) ) on columns ( select ( strtoset(@productproductdescription, constrained) ) on columns ( select ( strtoset(@dealnonprimaryofficer, constrained) ) on columns ( select ( strtoset(@dealofficerone, constrained) ) on columns ( select ( strtoset(@dealclientstatusasofdealinput, constrained) ) on columns ( select ( strtoset(@servicetransactioncostcenter, constrained) ) on columns ( select ( strtoset(@dealgroup, constrained) ) on columns ( select ( strtoset(@closingdatecalendar, constrained) ) on columns ( select ( strtoset(@dealstage, constrained) ) on columns [pipeline] ) ) ) ) ) ) ) ) ) )
you use union set people in same column.
but maybe not produce same results want. getting "totals" manager1 , manager2. give try.
with set mypeople union( [deal].[manager1].[manager1].allmembers, [deal].[manager2].[manager2].allmembers, [deal].[agent].[agent].allmembers )
Comments
Post a Comment