Parameterize OLAP Report using SP


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:

Comments (0)

Skip to main content