Inspiration, MSSQL, SQL, SSRS, TIPS, Uncategorized
In this post I will show you an interesting option of combining the SSRS report with the SQL Server Full Text Search mechanism. We will make a report about the product sales based on Adventure Works 2012 DW. We need to do just the two things, first: enable the full text search for the table that we want to search in and second: prepare a report. At the end we will able to ask for our data in the report just like in the picture below:
Let’s go.
To use full text search we need a Clustered Index that doesn’t allow NULL values first and then we have to define the full text search index. Go to your table and check if you have properly index.
Click on your table and select “Full Text index” and then “Define Full Text index”.
Read and click “Next”
Select the table clustered index
Select the attributes that you will want to search in and decide if you want to use the statistical semantic mechanicsm
Decide how to handle with the data changes.
Select or create new Full-Text catalog
Optionally define the population schedules.
Read and accept the summary and click “Finish”
Wait and close the window.
Go to the Visual Studio and create new Report Server project.
Add new shared data source where you have prepared table.
Change name of the data source and select type
Prepare a connection string and accept
Add new item
Add new blank report
Add new data source to your report
Select already created shared data source
Add new parameter
Choose name and prompt message and accept
Prepare the SQL query:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
select dpc.EnglishProductCategoryName, dpsc.EnglishProductSubcategoryName, dp.EnglishProductName, fis.TotalProductCost, fis.SalesAmount from [dbo].[FactInternetSales] fis left join [dbo].[DimProduct] dp on fis.ProductKey = dp.ProductKey left join dbo.DimProductSubcategory dpsc on dp.ProductSubcategoryKey = dpsc.ProductSubcategoryKey left join dbo.DimProductCategory dpc on dpsc.ProductCategoryKey = dpc.ProductCategoryKey where contains(EnglishProductName, 'parameter') |
Create new dataset
Select the data source and paste the query inserting the parameter into the contains clousere
Point the report parameter to the parameter in the query
Add new table to the report
Design as you want, for example like below
Go to the “Preview” and put the “gloves” as the parameter. As you can see we have filtered table
You can do something more. Like “gloves or set”
Or like “”glov*””
Or like “road and tire”
Or like “road and tire and not tube”
As you can see it is really powerful and I think this can be a useful feature of the report, especially if you have many values in the attributes and it is not comfortable to select the values from the list.
Latest posts by sdrzymala (see all)
- WordPress blog analysis in Power BI - May 1, 2016
- Power BI Desktop. An error while changing the name in Power Query - April 30, 2016
- Microsoft SQL Server Data Types – why this is so important? - April 30, 2016
This is a topic that is close to my heart…
Take care! Where are your contact details though?
Hi!
It does not work for me. Is it because I have more than one inputparameter??
My Query looks like this:
SELECT *
FROM MyTable
WHERE
(Contains(complaint_text, @complaint_text)
AND
(
(ac_reg IN (@ac_reg))
OR
(@ac_reg IS NULL)
)
AND
(
(REPLACE(ac_serial_no,’ ‘,”)) LIKE ‘%’ + @ac_serial_no + ‘%’ OR @ac_serial_no IS NULL OR @ac_serial_no = ”)