One of the major features of Ontolica Search Intelligence (OSI) is its support for creating customized reports. Creating reports for OSI is as simple as creating any other SQL Server reports. OSI exposes various data fields which you can access to create and customize reports that suit your requirements. This article will demonstrate how easy it is to create a custom report using OSI data fields.
Let’s assume we have a requirement where we need to see the users search activity and should be filterable by department.
To create a report for Ontolica Search Intelligence, you must first create a report server project where you will create a report definition (.rdl) file which you need to deploy into SharePoint and display using ReportViewerWebPart. This article will only describe creating report definition file, deploying this .rdl file into SharePoint will be discussed in the next article.
Requirements:
- Ontolica Search Intelligence installation.
- Business Intelligence Development Studio 2005/2008.
- You are a Database Administrator for the Ontolica Search Intelligence database instance.
Creating Report:
A report server project is used to create reports that run on a report server. When you run the report, actual data is retrieved and combined with the layout, and then rendered on your screen, from where you can export, print or save it.
Follow these steps to create a report
1. Create a report server project.
1.1 Open Business Intelligence Development studio.
1.2. Create new Report Server Project (Business Intelligence Projects Project type).
1.3. Name the project to OSIReports and click OK.
2. Setting Shared Data Sources.
2.1 In the Solution Explorer, right click on the Shared Data Sources and select Add New Data Source.
2.2 In the Shared Data Source Properties window enter/select the following properties
Name: REDW
Type: Microsoft SQL Server Analysis Services
Connection String: Enter the connection string or click Edit and set the server name (Database instance) where OSI database is installed and set the database name to Surfray Reporting.
Then click OK
2.3 Right click on Reports under Solution Explorer and select Add New Report, follow the instructions on the report wizard.
2.3.1 Welcome Screen: Click Next.
2.3.2 Select the Data Source: Select Shared data source and select REDW as shared data source, click Next
2.3.3 Design the Query: Click Query Builder
In the Query Builder under Metadata you will see the OSI Cube displaying available Measures, calculated values and dimensions. In the right top section you can define the dimensions which you want to set as selectable/filterable parameter. Since we want to filter the data based on the Departments, let’s set Departments as a parameter. Select Departments by clicking Dimension cell, click Hierarchy cell and select Departments set the operator to be Equal. For filter expression you can either set it to all or to a particular department, this is going to be the default value. Check the check box under Parameters.
Now drag and drop the fields which you need into the design area. For this example we will use Display name from the Users dimension and Ontolica Search measures fields. Drag and drop the Ontolica Search Count, Result Count and Clicked Result Count fields. You will see the empty fields if there is no data in the database, otherwise the available data will be shown. Click Ok and proceed to next window.
2.3.4 Select the report type: choose Tabular.
2.3.5 Design the table: Add all of the available fields into Details under displayed fields.
Chose the Table styles: select the desired table styles from the list.
Completing the wizard: name the report to User Search Details and check the preview report check box and click Finish.
A Preview of the report will be displayed.
The User Search Details report is ready for deployment.