Parameterize OLAP Report using SP

  • Article

A. Set a linked server (analysis server) in your SQL Server

 

In the SQL Server Surface Area Configuration enable 'Ad Hoc Remote Queries'

1. Open SQL Surface Area configuration

2. Go to Database enginer --> Ad Hoc Remote Queries --> Set Enable OPENROWSER andn OPENDATASOURCE support

 

In SQL Mgmt Studio in Server Objects to the MSOLAP Linked Server provider to 'Allow inprocess'

  1. Open SQL Server 2005 Management Studio, and then connect to an instance of SQL Server 2005.
  2. In Object Explorer, expand Server Objects, expand Linked Servers, expand Providers, and then double-click MSOLAP.
  3. In the Provider Options dialog box, click to select the Allow inprocess check box under Provider options.
  4. Click OK to close the Provider Options dialog box.

B. Write Stored procedure :

1. Create a parameterized stored procedure to return a flat table from the Analysis Services cube. I will explain the process of setting this in the demo session.

CREATE

PROCEDURE [dbo].[usp_DataSet]

(

@ProductCategoryID nvarchar(10), @MeasureValue float)

AS

SET NOCOUNT ON;

DECLARE @sql_data nvarchar(max)

--define the temporary table for the data

CREATE TABLE #DataSet (SalesReason varchar(255) NULL,

SalesTerritoryGroup

varchar(255) NULL,

SalesAmount

decimal(18,2) NULL,

OrderQuantity

decimal(18,2) NULL,

TotalProductCost

decimal(18,2) NULL)

--statement

SET @sql_data = 'INSERT INTO #DataSet (SalesReason, SalesTerritoryGroup, SalesAmount,

OrderQuantity)

SELECT * FROM OPENQUERY(ADVWORKS_SSAS,

''SELECT NON EMPTY { [Measures].[Internet Sales Amount], [Measures].[Internet Order Quantity]}

ON COLUMNS,

NON EMPTY { ([Sales Reason].[Sales Reason].[Sales Reason].ALLMEMBERS *

[Sales Territory].[Sales Territory Group].[Sales Territory Group].ALLMEMBERS ) }

having [Measures].[Internet Sales Amount] '

+ convert(varchar,@MeasureValue) + ' ON ROWS

FROM [Adventure Works]

WHERE ([Product].[Category].&['

+ @ProductCategoryID + '])'' )'

--populate the temp table, return nothing if there is an error in MDX

--select @sql_data

begin try

EXEC sp_executesql @sql_data

end try

begin catch

end catch

--return the dataset

SELECT SalesReason,

SalesTerritoryGroup

,

SalesAmount

,

OrderQuantity

FROM #DataSet

DROP TABLE #DataSet

C. Report setttings

 

2. Use this SP as the Data Source in your server report

3. Create the required parameters in the report

4. Set the report parameters in the winform application based on the value provided by the end user. I have not used operator parameter as of now. However, after the demo you will find it easy to implement.

    rp[0].Name = "ProductCategoryID";

          rp[1].Name = "MeasureValue";

    rp[0].Values.Add(txtCategoryId.Text.Trim());

    rp[1].Values.Add(txtMeasureValue.Text.Trim());

5. Run the winForm app. Pass the parameters in the textboxes and show the report

Final screen: