There are two ways to fetch data in SSRS report.
From SSAS Cube
From Sql server database.
For the second option either you have to write a Sql query in the data tab or you have to write the store procedure.
For the 1st option you can refer cube directly the report but the report are not customizable.
We have to create first cube from bids. For example I have a cube which calcute average claim paid, incurred_amt and days taken to complete a claim life cycle.Refer the MDX query below.
Example Code:
SELECT NON EMPTY {[LOCATION DIM].[REGION].[REGION].members } ON ROWS ,
NON EMPTY {[Measures].[MTH DAYS TAKEN CLOSED CLAIMS],[Measures].[QTR DAYS TAKEN CLOSED CLAIMS],[Measures].[YR DAYS TAKEN CLOSED CLAIMS],[Measures].[m Closed],[Measures].[q Closed],[Measures].[Closed],[Measures].[MTH INCURRED AMT],[Measures].[q TR INCURRED AMT],[Measures].[YR INCURRED AMT],[Measures].[MTH_ACP],[Measures].[QTR_ACP],[Measures].[YR_ACP]}ON COLUMNS
FROM [Claim_Knowbots]
Run the mdx query from SSMS.
Create a linked server from server object of object explorer.
We can create linked server either manually by right clicking on server object or writing code.
FIG1: Linked Server from server object
Then try the query in sql query analyzer with open query. Please refer below query.
select * from openquery(KNOWBOTNew,'SELECT NON EMPTY {[LOCATION DIM].[REGION].[REGION].members } ON ROWS ,
NON EMPTY {[Measures].[MTH DAYS TAKEN CLOSED CLAIMS],[Measures].[QTR DAYS TAKEN CLOSED CLAIMS],[Measures].[YR DAYS TAKEN CLOSED CLAIMS],[Measures].[m Closed],[Measures].[q Closed],[Measures].[Closed],[Measures].[MTH INCURRED AMT],[Measures].[q TR INCURRED AMT],[Measures].[YR INCURRED AMT],[Measures].[MTH_ACP],[Measures].[QTR_ACP],[Measures].[YR_ACP]}ON COLUMNS
FROM [Claim_Knowbots] ')
Requirement complexity
In my case the requirement was to show monthly, quarterly, yearly measures from cube through ssrs report but it should be dynamic. User can choose runtime month, quarter and year and according to that choice data should be fetched. Also we have fetch the data region wise.
Secondly the challenge was to parameter data, which was passed as as a string, or row in comma separated manner. We do have to convert it in column first then have to pass it to the mdx query. Below code snippet can give more light to the current scenario.
From SSMS write click on stored procedure from object explorer.
Create new store procedure
To covert the comma-separated string in to mdx format follow the code snippet.
CREATE TABLE #prm_Region(
LIST_ITEM VARCHAR (2000) NOT NULL)
SELECT @textXML = CAST('' + REPLACE(@prm_Region, @delimiter, '') + '' AS XML);
INSERT #prm_Region
SELECT '[LOCATION DIM].[REGION].&['+T.split.value('.', 'nvarchar(max)')+']'
FROM @textXML.nodes('/d') T (split)
SELECT @RegionList = COALESCE(@RegionList + ', ', '') +
CAST(LIST_ITEM AS varchar(2000))
FROM #prm_Region
select @pos=CHARINDEX(',', @RegionList)
if @pos <>0
SET @RegionList='union('+@RegionList+')'
Then we have to create a temp table to pass the result set of mdx query.
CREATE TABLE #ACP (
[REGION] [varchar](50) NULL,
[MTH_DAYS_TAKEN_CLOSED_CLAIMS] [numeric](18, 2) NULL,
[QTR_DAYS_TAKEN_CLOSED_CLAIMS] [numeric](18, 2) NULL,
[YR_DAYS_TAKEN_CLOSED_CLAIMS] [numeric](18, 2) NULL)
Then according to user’s selecttion of Year. Quarter and month we have to convert the parameter value and pass to mdx query.
Example:
IF @prm_Period='Year'
Begin
CREATE TABLE #prm_PeriodValue_YEAR1(
LIST_ITEM VARCHAR (2000) NOT NULL)
SELECT @textXML = CAST('' + REPLACE(@prm_PeriodValue, @delimiter, '') + '' AS XML);
INSERT #prm_PeriodValue_YEAR1
SELECT T.split.value('.', 'nvarchar(max)')
FROM @textXML.nodes('/d') T (split)
CREATE TABLE #prm_PeriodValue_YEAR2(
LIST_ITEM1 VARCHAR (2000) NOT NULL)
INSERT #prm_PeriodValue_YEAR2 select distinct '[TIME DIM].[YEAR].&['+LIST_ITEM+']' from #prm_PeriodValue_YEAR1
SELECT @YearList = COALESCE(@YearList + ', ', '') +
CAST(LIST_ITEM1 AS varchar(2000))
FROM #prm_PeriodValue_YEAR2
select @pos=CHARINDEX(',', @yearlist)
if @pos <>0
SET @yearlist='union('+@yearlist+')'
SET @mdx = 'SELECT NON EMPTY { ('+@RegionList+','+@LobList+' ) } ON ROWS ,
NON EMPTY {[Measures].[MTH DAYS TAKEN CLOSED CLAIMS],[Measures].[QTR DAYS TAKEN CLOSED CLAIMS],[Measures].[YR DAYS TAKEN CLOSED CLAIMS],[Measures].[m Closed],[Measures].[q Closed],[Measures].[Closed],[Measures].[MTH INCURRED AMT],[Measures].[q TR INCURRED AMT],[Measures].[YR INCURRED AMT],[Measures].[MTH_ACP],[Measures].[QTR_ACP],[Measures].[YR_ACP]}ON COLUMNS
FROM [Claim_Knowbots] where ('+@yearlist+','+@UserList+')'
end
Lastly We have to insert result set in temp table and use the result set in the report.
Example:
Set @mdx='SELECT NON EMPTY { ('+@RegionList+' ) } ON ROWS ,
NON EMPTY {[Measures].[MTH DAYS TAKEN CLOSED CLAIMS],[Measures].[QTR DAYS TAKEN CLOSED CLAIMS],[Measures].[YR DAYS TAKEN CLOSED CLAIMS],[Measures].[m Closed],[Measures].[q Closed],[Measures].[Closed],[Measures].[MTH INCURRED AMT],[Measures].[q TR INCURRED AMT],[Measures].[YR INCURRED AMT],[Measures].[MTH_ACP],[Measures].[QTR_ACP],[Measures].[YR_ACP]}ON COLUMNS
FROM [Claim_Knowbots]’
set @sql = 'Insert #ACP([REGION],
[MTH_DAYS_TAKEN_CLOSED_CLAIMS],
[QTR_DAYS_TAKEN_CLOSED_CLAIMS],
[YR_DAYS_TAKEN_CLOSED_CLAIMS],
[m_Closed],
[q_Closed],
[Closed],
[MTH_INCURRED_AMT],
[qTR_INCURRED_AMT],
[YR_INCURRED_AMT],
[MTH_ACP],
[QTR_ACP],
[YR_ACP]) select '+ @columns +' from openquery(KNOWBOTNew,''' + @mdx + ''')'
After creating Storeprocedure in your database we can use this store procedure in SSRS.
Advantages:
It will store the data aggregated format in your cube.
So the data retrieval will be faster.
In ssrs when you refer cube you can not modify the mdx query.This limitation can be minimised using this way.
You can write your own mdx and pass it through stored procedure.
