Creating Dynamic MDX Queries within SQL Server 2005 Reporting Services

Please I need some help urgently, How can I create Dynamic MDX Queries
within SQL Server 2005 Reporting Services to use Parameters created
from SQL Queries Dataset.
I was able to do this in SQL Server 2000 Reporting Services by
replacing MDX Statement (1) with (2) below, but I notice the symbol ="
at the begining of statement (2) is not allowed.
(1)
SELECT NON EMPTY { [Measures].[Expected Gross Ult Premium],
[Measures].[Expected Ultimate Deductions Pct], [Measures].[IELR Pct],
[Measures].[Expected Composite Ratio Pct], [Measures].[Booked Premium],
[Measures].[Booked Deduction Pct], [Measures].[Booked Paid Loss Pct],
[Measures].[Booked Incurred Loss Pct], [Measures].[Booked Composite
Pct], [Measures].[Expected NPV], [Measures].[Expected NPV Pct],
[Measures].[Expected ROE], [Measures].[Allocated Capital],
[Measures].[Booked Tech Margin Inc Cat] } ON COLUMNS ,
NON EMPTY { DESCENDANTS( [Underwriting Year].[Underwriting Year].[All
Underwriting Years], [Underwriting Year].[Underwriting
Year].[Underwriting Year] ) } ON ROWS
FROM [GroupReporting]
WHERE ( [Transaction Original Currency].[Currency].&[85], [Lifetime
Contract].[Lifetime Contract].[Originating Contract
Reference].&[C12664] )
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE
(2)
="SELECT NON EMPTY { [Measures].[Expected Gross Ult Premium],
[Measures].[Expected Ultimate Deductions Pct], [Measures].[IELR Pct],
[Measures].[Expected Composite Ratio Pct], [Measures].[Booked Premium],
[Measures].[Booked Deduction Pct], [Measures].[Booked Paid Loss Pct],
[Measures].[Booked Incurred Loss Pct], [Measures].[Booked Composite
Pct], [Measures].[Expected NPV], [Measures].[Expected NPV Pct],
[Measures].[Expected ROE], [Measures].[Allocated Capital],
[Measures].[Booked Tech Margin Inc Cat] } ON COLUMNS , NON EMPTY {
DESCENDANTS( [Underwriting Year].[Underwriting Year].[All Underwriting
Years], [Underwriting Year].[Underwriting Year].[Underwriting Year] ) }
ON ROWS
FROM [GroupReporting]
WHERE ( [Transaction Original Currency].[Currency].&[" &
Parameters!CurrencyCode.Value & "], [Lifetime Contract].[Lifetime
Contract].[Originating Contract Reference].&[" &
Parameters!MISOriginatingContractID.Value & "] ) CELL PROPERTIES
VALUE, FORMATTED_VALUE, CELL_ORDINAL, FORMAT_STRING"You need to change it to be an expression.
Open the dataset properties window.
Make sure the command type is text,
Click Expression Builder (fx) next to the Query String input box, and then
Enter your expression. You won't be able to execute it anymore in the
dataset window, but it will
execute when you refresh your dataset and go to use it.
<michael.oseni@.aleagroup.com> wrote in message
news:1157464471.474318.252920@.d34g2000cwd.googlegroups.com...
> Please I need some help urgently, How can I create Dynamic MDX Queries
> within SQL Server 2005 Reporting Services to use Parameters created
> from SQL Queries Dataset.
> I was able to do this in SQL Server 2000 Reporting Services by
> replacing MDX Statement (1) with (2) below, but I notice the symbol ="
> at the begining of statement (2) is not allowed.
> (1)
> SELECT NON EMPTY { [Measures].[Expected Gross Ult Premium],
> [Measures].[Expected Ultimate Deductions Pct], [Measures].[IELR Pct],
> [Measures].[Expected Composite Ratio Pct], [Measures].[Booked Premium],
> [Measures].[Booked Deduction Pct], [Measures].[Booked Paid Loss Pct],
> [Measures].[Booked Incurred Loss Pct], [Measures].[Booked Composite
> Pct], [Measures].[Expected NPV], [Measures].[Expected NPV Pct],
> [Measures].[Expected ROE], [Measures].[Allocated Capital],
> [Measures].[Booked Tech Margin Inc Cat] } ON COLUMNS ,
> NON EMPTY { DESCENDANTS( [Underwriting Year].[Underwriting Year].[All
> Underwriting Years], [Underwriting Year].[Underwriting
> Year].[Underwriting Year] ) } ON ROWS
> FROM [GroupReporting]
> WHERE ( [Transaction Original Currency].[Currency].&[85], [Lifetime
> Contract].[Lifetime Contract].[Originating Contract
> Reference].&[C12664] )
> CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE
> (2)
> ="SELECT NON EMPTY { [Measures].[Expected Gross Ult Premium],
> [Measures].[Expected Ultimate Deductions Pct], [Measures].[IELR Pct],
> [Measures].[Expected Composite Ratio Pct], [Measures].[Booked Premium],
> [Measures].[Booked Deduction Pct], [Measures].[Booked Paid Loss Pct],
> [Measures].[Booked Incurred Loss Pct], [Measures].[Booked Composite
> Pct], [Measures].[Expected NPV], [Measures].[Expected NPV Pct],
> [Measures].[Expected ROE], [Measures].[Allocated Capital],
> [Measures].[Booked Tech Margin Inc Cat] } ON COLUMNS , NON EMPTY {
> DESCENDANTS( [Underwriting Year].[Underwriting Year].[All Underwriting
> Years], [Underwriting Year].[Underwriting Year].[Underwriting Year] ) }
> ON ROWS
> FROM [GroupReporting]
> WHERE ( [Transaction Original Currency].[Currency].&[" &
> Parameters!CurrencyCode.Value & "], [Lifetime Contract].[Lifetime
> Contract].[Originating Contract Reference].&[" &
> Parameters!MISOriginatingContractID.Value & "] ) CELL PROPERTIES
> VALUE, FORMATTED_VALUE, CELL_ORDINAL, FORMAT_STRING"
>|||Thanks Chris, your below solution works fine.
Chris wrote:
> You need to change it to be an expression.
> Open the dataset properties window.
> Make sure the command type is text,
> Click Expression Builder (fx) next to the Query String input box, and then
> Enter your expression. You won't be able to execute it anymore in the
> dataset window, but it will
> execute when you refresh your dataset and go to use it.
>
> <michael.oseni@.aleagroup.com> wrote in message
> news:1157464471.474318.252920@.d34g2000cwd.googlegroups.com...
> > Please I need some help urgently, How can I create Dynamic MDX Queries
> > within SQL Server 2005 Reporting Services to use Parameters created
> > from SQL Queries Dataset.
> >
> > I was able to do this in SQL Server 2000 Reporting Services by
> > replacing MDX Statement (1) with (2) below, but I notice the symbol ="
> > at the begining of statement (2) is not allowed.
> >
> > (1)
> > SELECT NON EMPTY { [Measures].[Expected Gross Ult Premium],
> > [Measures].[Expected Ultimate Deductions Pct], [Measures].[IELR Pct],
> > [Measures].[Expected Composite Ratio Pct], [Measures].[Booked Premium],
> > [Measures].[Booked Deduction Pct], [Measures].[Booked Paid Loss Pct],
> > [Measures].[Booked Incurred Loss Pct], [Measures].[Booked Composite
> > Pct], [Measures].[Expected NPV], [Measures].[Expected NPV Pct],
> > [Measures].[Expected ROE], [Measures].[Allocated Capital],
> > [Measures].[Booked Tech Margin Inc Cat] } ON COLUMNS ,
> >
> > NON EMPTY { DESCENDANTS( [Underwriting Year].[Underwriting Year].[All
> > Underwriting Years], [Underwriting Year].[Underwriting
> > Year].[Underwriting Year] ) } ON ROWS
> >
> > FROM [GroupReporting]
> >
> > WHERE ( [Transaction Original Currency].[Currency].&[85], [Lifetime
> > Contract].[Lifetime Contract].[Originating Contract
> > Reference].&[C12664] )
> > CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE
> >
> > (2)
> > ="SELECT NON EMPTY { [Measures].[Expected Gross Ult Premium],
> > [Measures].[Expected Ultimate Deductions Pct], [Measures].[IELR Pct],
> > [Measures].[Expected Composite Ratio Pct], [Measures].[Booked Premium],
> > [Measures].[Booked Deduction Pct], [Measures].[Booked Paid Loss Pct],
> > [Measures].[Booked Incurred Loss Pct], [Measures].[Booked Composite
> > Pct], [Measures].[Expected NPV], [Measures].[Expected NPV Pct],
> > [Measures].[Expected ROE], [Measures].[Allocated Capital],
> > [Measures].[Booked Tech Margin Inc Cat] } ON COLUMNS , NON EMPTY {
> > DESCENDANTS( [Underwriting Year].[Underwriting Year].[All Underwriting
> > Years], [Underwriting Year].[Underwriting Year].[Underwriting Year] ) }
> > ON ROWS
> > FROM [GroupReporting]
> > WHERE ( [Transaction Original Currency].[Currency].&[" &
> > Parameters!CurrencyCode.Value & "], [Lifetime Contract].[Lifetime
> > Contract].[Originating Contract Reference].&[" &
> > Parameters!MISOriginatingContractID.Value & "] ) CELL PROPERTIES
> > VALUE, FORMATTED_VALUE, CELL_ORDINAL, FORMAT_STRING"
> >