Natural search in SQL Server Reporting Services using Full-Text-Search

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:

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.

2 Comments

  1. goal

    This is a topic that is close to my heart…
    Take care! Where are your contact details though?

    Reply
  2. RS

    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 = ”)

    Reply

Leave a Comment

Your email address will not be published. Required fields are marked *