Archive

Posts Tagged ‘Search report’

SSRS – Searching report

December 10, 2011 Leave a comment

Recently I have involved as a team member to develop a search report besides some interesting reporting concepts.
Requirement: The end-user needs to search feature in the report. The logic behind is how we could accommodate the search in MDX source query.
I am simulating the scenario using the AdventureWorks database.
 
Step 1: MDX Source query for the report, using the filter function we could filter the dataset as

SELECT 
  {
    [Measures].[Internet Average Sales Amount]
   ,[Measures].[Internet Unit Price]
  } ON COLUMNS
 ,
NON EMPTY(

IIF ( (Len("Road")>0 ) ,  -- Replace with ParameterName
Filter
([Product].[Product].Children, 
 instr([Product].[Product].currentmember.Name, "Road") )  
, [Product].[Product].Children
)
)
ON ROWS
FROM [Adventure Works];

 
Step 2: The report source query and the parameter are setup as

 
Step 3: Report parameter setup and it will mapped to the query parameter as

 
Step 4: In search reports, It could be useful to have the searching information so added expression and it will display the searching product and by default it will give “ALL” while getting all the products info as

 
Step 5: Develop the report (basic implementation for demo) and preview, here we are not searching for any product so default we need to get all the products into the report as

 
Step 6: Now search for any product name, here we are searching for the product name like “AWC” and we will get all the products names like “AWC”

 
Points to consider:
1. Here discussed only the direct searching functionality using MDX but it could also similar to TSQL source query using LIKE operator.
2. I have presented only basic search apart from advanced search implementation approaches.
3. I am planning to use this report for additional implementation using SSRS 2008 features.

Categories: MDX, SSRS Tags: