CODEWITHSUNDEEP
parametersmdxssrs-2012
SHAMIM
SHAMIM

Reputation: 37

Using MDX How to create Dynamic Report with parameters

The following is my MDX and It's being executed perfectly in SQL Server Environment. Now, I want to create a dynamic report using SSRS which will give me the different years result by selecting from the parameters. I've created a parameter Named 'FY' I want to pass yearly value through this parameter, That's why I am passing this MDX as an expression and getting the error like "Query(1,385) Parser: The Syntax for 'MEMBER' is incorrect". I don't know where is the wrong i am doing? Thanks in advance

 "WITH                                                                              "+
 "MEMBER [Total] AS                                                                 "+
 "SUM({[Measures].[July],[Measures].[August],[Measures].[September],                "+
 "[Measures].[October], [Measures].[November],                                      "+
"[Measures].[December],[Measures].[January],[Measures].[February],                  "+
"[Measures].[March],[Measures].[April],                                             "+
"[Measures].[May],[Measures].[June]}, [Dim Account].[Account Type].currentmember),  "+
"MEMBER [Percent] AS                                                                "+
"Total/SUM({[Measures].[July],[Measures].[August],[Measures].[September],           "+
"[Measures].[October], [Measures].[November],                                       "+
"[Measures].[December],[Measures].[January],[Measures].[February],                  "+
"[Measures].[March],[Measures].[April],                                             "+
"[Measures].[May],[Measures].[June]}, [Dim Account].[Account Type].[Income]),       "+
"MEMBER [Dim Account].[Account Type].[Gross Profit] AS                              "+
"  [Dim Account].[Account Type].[Income] -                                          "+
"[Dim Account].[Account Type].[Cost of Sales],                                      "+
"MEMBER [Dim Account].[Account Type].[Operating Profit] AS                          "+
"  [Dim Account].[Account Type].[Gross Profit] -                                    "+
"[Dim Account].[Account Type].[Expenses],                                           "+
"MEMBER [Dim Account].[Account Type].[Net Profit] AS                                "+
"  [Dim Account].[Account Type].[Operating Profit] +                                "+
"[Dim Account].[Account Type].[Other Income] -                                      "+
"[Dim Account].[Account Type].[Other Expense],                                      "+
"MEMBER [Dim Account].[Account Type].[EBITDA] AS                                    "+
"  [Dim Account].[Account Type].[Net Profit] +                                      "+
"[Dim Account].[HierarchyAccount].[Account Activity].                               "+
"&[Profit and Loss]&[Expenses]&[Interest] +                                         "+
"[Dim Account].[HierarchyAccount].[Account Activity].                               "+
"&[Profit and Loss]&[Cost of Sales]&[Depreciation]+                                 "+
"[Dim Account].[HierarchyAccount].[Account Activity].                               "+
"&[Profit and Loss]&[Expenses]&[Depreciation]+                                      "+
"[Dim Account].[HierarchyAccount].[Account Activity].                               "+
"&[Profit and Loss]&[Expenses]&[Amortisation],                                      "+
"SELECT {[Measures].[July],[Measures].[August],[Measures].[September],              "+
"[Measures].[October], [Measures].[November],                                       "+
"[Measures].[December],[Measures].[January],[Measures].[February],                  "+
"[Measures].[March],[Measures].[April],                                             "+
"[Measures].[May],[Measures].[June], Total, [Percent]}  ON COLUMNS,                 "+
" {                                                                                 "+
"   [Dim Account].[Account Type].&[Profit and Loss]&[Income],                       "+
"   [Dim Account].[Account Type].&[Profit and Loss]&[Cost of Sales],                "+
"[Dim Account].[Account Type].[Gross Profit],                                       "+
"   [Dim Account].[Account Type].&[Profit and Loss]&[Expenses],                     "+
"[Dim Account].[Account Type].[Operating Profit],                                   "+
"   [Dim Account].[Account Type].&[Profit and Loss]&[Other Income],                 "+
"   [Dim Account].[Account Type].&[Profit and Loss]&[Other Expense],                "+
"[Dim Account].[Account Type].[Net Profit],                                         "+
"[Dim Account].[Account Type].[EBITDA]} ON ROWS                                     "+
"FROM [DSV_IncomeStmt]                                                              "+
"where [Dim Yearto Date].[Fiscal Year].&["+Parameters!FY.Value+"]   "

Upvotes: 0

Views: 732

Answers (3)

whytheq
whytheq

Reputation: 35557

Just as an aside - adding your space at the start of each line - currently you're making a very very big string with a lot of white space.

It is a better approach to create a single space at the start of each row:

" WITH"+
" MEMBER [Total] AS"+                                                                 
" SUM({[Measures].[July],[Measures].[August],[Measures].[September],"+                 
" [Measures].[October], [Measures].[November],"+                                       
" [Measures].[December],[Measures].[January],[Measures].[February],"+                  
" [Measures].[March],[Measures].[April],"+                                             
" [Measures].[May],[Measures].[June]}, [Dim Account].[Account Type].currentmember)"+ 
" MEMBER [Percent] AS"+ 
...
...

Upvotes: 0

SHAMIM
SHAMIM

Reputation: 37

The Expression is quite perfect except the "," at the end of every member. For Example, look at the end of MEMBER statement a , is present that must be eliminated from every MEMBER calculation

Other than that everything is perfect.

"WITH                                                                               "+
 "MEMBER [Total] AS                                                                 "+
 "SUM({[Measures].[July],[Measures].[August],[Measures].[September],                "+
 "[Measures].[October], [Measures].[November],                                      "+
"[Measures].[December],[Measures].[January],[Measures].[February],                  "+
"[Measures].[March],[Measures].[April],                                             "+
"[Measures].[May],[Measures].[June]}, [Dim Account].[Account Type].currentmember)   "+

Upvotes: 1

sen77
sen77

Reputation: 151

Use the formula instead of the query script:

    ="SELECT [Measures].[July]  ON COLUMNS, 
 [Dim Account].[Account Type].&[Profit and Loss]&[Income] ON ROWS
FROM [DSV_IncomeStmt]
where [Dim Yearto Date].[Fiscal Year].&["+param+"]"

Upvotes: 0

Related Questions