Microsoft SQL Reporting Services platform (SSRS) and Report Builder

Note: The process of developing reports will depend on your Microsoft SQL Reporting Services and Report Builder platform (SSRS) version. For more information, refer to Microsoft SQL Server Reporting Services Help Documentation.

Let’s create a basic report from scratch:

This user guide is based on SQL Server 2014 (Report Builder)

Try it!

You should have SSRS installed and open the Reporting Services web portal (URL).
Select Report Builder

Next, select New Report and then Table or Matrix Wizard.

Specify data connection.

A data connection contains the information to connect to an external data source such as a SQL Server database. Usually, you get the connection information and the type of credentials to use from the data source owner. To specify a data connection, you can use a shared data source from the report server or create an embedded data source used only in this report.

Create an embedded data source for the data connection:

Start by choosing your dataset. Ensure the Create a dataset check box is selected.

Select Next.
On the Choose a connection to a data source page, select New.

On the Data Source Properties dialog, set the following properties on the General tab:
The connection to the data source must be set up. Select New

Set the Name property of the data source to, for example, Product_Sales.
For the Select a connection type property, confirm Microsoft SQL Server is selected.
Select Build to build up your data sources.

Enter your SQL Server name in the Server name field.
Ensure to select Use SQL Server Authentication.
Enter the SQL Server username and password.
Under Connect to a database, you will choose a database to connect to.

It’s a good idea to test the connection then. Select Test Connection.
Select OK.
A data source will now be available.

Select OK.

To continue in the wizard, select Next.

Create a query.

The Design a query screen will open where you can create your queries.
There are two ways how to do this:

o Drag tables into the Selected Fields section to start creating the query.

OR

o Select the Edit as Text option. (Most people prefer this option)

For this user guide, use the text-based query designer.

Paste or add your query in the field.
Our example query looks at the number of pallets in a bin area called ‘cross docs’.

If you do not want to add the query as text, you can change the Command Type to a Stored Procedure and add the Stored Procedure name here.

Select the run icon to get results back and test whether the report runs successful.

To continue in the wizard, select Next.

Organize data into groups.

When you select data fields to form groups, you design a table with rows and columns displaying detailed and aggregated data. In the following process, we will only organize the values.

The Arrange fields screen will open, where you will set up the look and feel of the report.
Drag the available fields into the required group box OR drag all the fields into the Values box to get a flat data grid.

Select Next to check the table in the Preview pane.

Once you are happy with your report, select Next.
Select a specific style for the Report.
Select Finish.
Report Builder adds your table to the design surface. This blank report template will be used for the report (and query) you want to run.

Select Run from your selected data source to view the report (per the query).

Format report columns.

When running a report for the first time, you might need to format the columns a little as the text might not wrap correctly, or numeric data might have too many zeros, etc.

Report Builder displays table data values in the report using default formats, such as numbers, dates, times, etc. You can use the formatting features on the Home tab to change how table data is displayed.

Change column width

To reduce the vertical space for each row, you can expand the column width to accommodate the expected contents of the text boxes in the column on a single line.

Hold and drag the handles to increase the table width.
Rerun the report, to see whether the column width is correct.

Customize report title.

A report title appears at the top of the report. You can place the report title in a report header.

On the design surface, select the Click to add title text box.
In the Click to add title text box, enter the name of your report, and then select outside the text box.

Save the report.

You can save the report to the report server or on your computer. Before saving your report, always confirm the Credentials.

The Credentials are important to keep the data source secure. Select the Use the username and password option.

Select File / Save As
Select or enter the name of the report server where you have permission to save reports.
The message "Connecting to report server" appears. When the connection completes, the default report folder opens. The report server administrator specifies the default folder location.
For the report Name, replace the default value Untitled with your report name.
Select Save.
The report is saved to the report server. The status bar at the bottom of the window shows the name of the report server that you're connected to.
The report will now be visible on the Report Builder Home page in your selected folder.

DATASCOPE WMS User Guides

Create a basic report