Creating Your First Tabular SSRS Report With And Without Wizard: SSRS Part One

This article explains how you can create your first Tabular SSRS report with and without wizard. In this article all the steps required to create Tabular SSRS report using both ways has been mentioned one by one in step-by-step format.

To create the report you will require Visual Studio with Business Intelligence template Installed and SQL Server. To create your first report please follow the steps mentioned below:

Step 1: Creating SSRS project

Open Visual Studio 2015 (I am using Visual Studio 2015 but you can use any version of Visual Studio e.g. VS 2015, VS 2013, VS 2012, VS 2010…)

Click on New Project. It will open a “New Project” window. Click on “Installed template”, select “Business Intelligence”, “Reporting Services”, then click “Report Server Project”. You can also refer the below image for details.

Step 2: Create a Shared Data Source.

Now go to the solution explorer and select the project “SSRSTutorial”, then right click on “Shared Data Source” and click on “Add New Data Source”,

It will open a new “Shared Data Source Properties” window.

Provide “DS_SSRSTutorial” as dataset name. Select “Microsoft SQL Server” from "Type:" dropdownlist. Write the connection string in the box provided for connection string.

If you do not want to type connection string in the box then you can set it up using wizard also. To set using wizard click on Edit button on the right side of Connection String. It will open a new “Connection Properties” window.

After setting connection details click on the OK button to close “Test results”, then click on OK to close “Connection Properties” window and then again click on OK to close “Shared Data Source Properties” window.

So you have created “Shared Data Source” successfully. We can also create Shared Datasets but we will not create a shared dataset. We will create separate Datasets for each report.

Step 3: Adding an SSRS report file (*.rdl) in solution

Right click on “Reports” folder and select Add New Report,

It will open the report wizard window. Click on “Next”.

Select the Shared data source from the dropdown list and click on Next Button.

It will open a new window. Click on “Query Builder”.

It will open the Query designer window. Click on “Edit as Text”.

It will open this window in editable mode. Now write the following queries in editable window:

SELECT [Id]
,[StateName]
,[Population]
,[CensusYear]
FROM [Test].[dbo].[IndiaPopulation] orderby [Population] desc

Select command type as Text and execute by clicking on Run button.

Click on OK to close the “Query Designer” Window. Click on Next in the “Design The query” window in wizard. Select the report type from Tabular and Matrix radio button. Select Tabular right now and I will explain about Matrix later. Then click on Next.

Tabular

Select the 3 columns StateName, Population and CensusYear from "Available fields" section and send it to details section and click on Next Button.

Available fields section

Select the Table style from the wizard. I have selected the table style bold and click on Next button.

Write the name of Report “CensusReport” in text box and click on Finish. You can also see the Report Summary in the wizard.

Click on the "Preview" tab to view the report.

CensusReport

Finally, first SSRS report has been created successfully. You may be wondering what happened in the background as I have created report using wizard. But you don’t need to worry that it’s not complex, in fact it is very easy and we can create an SSRS Report without using wizard in a simpler way. Let’s create another SSRS Report without using the wizard.

Creating SSRS Report Without using Wizard

Use “Ctrl+Shift+A” to open “Add New Item” window or you can also open it by right clicking on “Reports” Folder and select Add, then click New Item,

Select Report Project on the left pane and then select on “Report” and give the report name as CensusReport2.rdl and then click on Add button.

Report

Select CensusReport2.rdl in Solution Explorer window and press “Ctrl+Alt+D” to open “Report Data” window. You can also open it from view menu.

Right click on “Data Sources” folder of “Report Data” window and select “Add Data Source". It will open “Data Source Properties” window. Now provide DataSource Name and select the shared data source which we have created earlier and click on OK.

Now right click on the “DataSets” folder in Report Data Window click on “Add DataSet”.

Click on the radio button “Use a dataset embedded in my report.” Select Data Source from dropdown list. Click on “Query Designer” button and it will open a new “Query Designer” window. Click on Edit as Text Button.

Write the following query:

SELECT [Id]
,[StateName]
,[Population]
,[CensusYear]
FROM [Test].[dbo].[IndiaPopulation] order by [Population] desc

Execute it by clicking on Run button. Make sure that you have selected Command Type as Text. Click on OK to close the “Query Designer” window. Again click OK to close “Dataset Properties” window.

Now go to “Report Data” window and expand “Data Sources” and Datasets.

Now click on Toolbox and select a Table control and put it in right side design window as displayed in the following image.

select a Table control

Now click on Report Data window and drag 3 fields StateName, Population and CensusYear in the table as displayed in the following image. While dragging fields from “Report Data” window you will notice that it will automatically create both header and data rows.

Now click on Preview tab to see the result,

Preview

So, in this article you have seen how you can create your first tabular SSRS report using both options i.e. using wizard and without wizard. In the next Tutorial I will explain about creating SSRS matrix report with and without wizard and then we will look into the expressions and formatting of the reports.