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'
- Open SQL Server 2005 Management Studio, and then connect to an instance of SQL Server 2005.
- In Object Explorer, expand Server Objects, expand Linked Servers, expand Providers, and then double-click MSOLAP.
- In the Provider Options dialog box, click to select the Allow inprocess check box under Provider options.
- 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:
