ssrs create simple parameter

This tutorial explains how to create a simple parameter in SSRS report and use it to filter your data. In this tutorial I will SSRS 2008 R2 and it should be the same in SSRS 2008 and SSRS 2012.
Below you can see the table which shows twelve months average temperature in Majorca. Let's say there is a requirement to show only one month at a time using parameter.

In my design view you can see I already have a dataset and a report table. In order to add a paramater to the report I open "Report Data" pane, right click parameters folder and click add parameter.


Now we need to setup Name (used internally), Prompt (which is what users will see), and Data Type. In this case Name is Month the same for Prompt and data type is Text. Click ok.
NOTE: In this tutorial we won't be discussing importance of data types and other option available but we will write new blog post to cover them.


Now that we have created a parameter we need to use it to filter the datase.
NOTE: There are multiple ways to filter data. We use the most common one and add filter to the SQL statement. Very often you might use stored procedures and pass parameters as a stored procedure argument.
To add the filter to our SQL Select we just add the following where clause:
WHERE Month = @Month
@Month is our parameter and will be replaced with whatever we type in the parameter text box when we run the report.



Let's preview our report.
We can notice Month parameter with empty text box. I will type January. and click view report.
As you can see the report was filtered and I get only one row for January.
NOTE: Very often you will use default values, calendars or drop down boxes as your parameters and I will write some blog posts about it soon.


I hope this SSRS tutorial will help you to create a simple parameter.

SSRS create reports

This tutorial shows you how to create a simple report in SSRS.

First we need to create new SSRS Project. Open Business Intelligence Development Studio (BIDS) from start // All programs.
Select File Menu // New // Project…


New project window should appear. Ensure you have Business Intelligence Projects selected on the left side (Project types). Select Report Server Project from templates window.
Type name, select location, Solution Name usually is the same as project name and should be updated when you type project name (name text box). Click ok to create new SSRS project.



Now that you have new SSRS Project created you should be able to view its content in solution explorer pane which is usually located on the right side (If you displayed than click view on the main menu and click solution explorer).
Next step will show you how to setup Shared Data Source.
Data source contains a connection string which is used to connect to your source of data which in SSRS is usually a SQL Server database. "Shared" just means you can re-use it for different reports.
To set up shared data source in Solution Explorer pane right click "shared data soruce" and click "Add new data source".

We should see Shared Data Source Properties window, change Name and click Edit button which will display "Connection Properties" window. In here you need to specify server name ( in this case is localhost which is default installaton name and instead of typing localhost we use shortcut which simply a dot "." without quotes).
We will use UserWindowsAuthentication (you could use SQL authentication if you have SQL (not windows) username/password) next go to Select or enter a database name. If previous options are set correctly and you have permissions and connectivity this drop down box should show you available databases.
Once you select database in our case tutorial we can double check connectivity by clicking TestConnection button and if is ok then select OK button.

We can see that the connection string is auto generated using our input from previous window. Click ok to finish creating the shared data source.

In Solution Explorer you should see your new Data Source which in my case is called “DevelopingASimpleReport”.



Now we can Add New Report. In solution explorer right click reports folder and select Add, and than New Item.
NOTE: "Add New Report" (with *) also allows you to create a report but using an SSRS wizard which we won't be using in this tutorial. My best practice recommendation is to create report templates and then copy/paste into new report. You will save yourself a lot of repetitive work.


In "Add New Item" window we have two options to create Report we can just select Report or use Report Wizard.
In this case I will use Report item (without wizard)
At the bottom we need to type report name.

Now we can see that the report was created and opened in design view.
NOTE: We use SSRS 2008 R2 which is similar to 2008 and 2012 but is different to 2005 version.

Now we need to create Data Set. A dataset contains Query String (in our case SQL query) which is executed at run-time and results are stored in the dataset and used by the report.

To create new data set open "Report Data" pane if it is not there use view menu. Right click datasets folder and click Add Dataset

In the Dataset Properties fill in dataset name and click New button next to "data source" drop down box.


Data Source properties window is displaying and now we can use our shared data source that we created at the beginning by select it in "Use shared data sourced reference" drop down box. Click ok.


Once that is ready select Query Designer to setup query string.

Into Query Designer put your sql and click  ok. (SQL Editor can help you generate your SQL query)


Make sure if you have Name typed and you selected right Data source which we added in previous step and you can see your SQL query.
Then click OK.


Finally we can see that your Dataset is visible in Report Data pane which the fields we selected.


Your Data Set is ready. Now we can create simple report table. To do this please open Toolbox (use view menu or icon on toolbar).
and drag and drop text box and table item into design area.


To place our fields from data set we can drag and drop the field or click on empty cell and in the right top corner a small rectangle schould appear; click one of the fields and it will appear in the report table.


You can format the table by selecting a cell or entire row or column. Right clicking and going to properties. To view all avialable properties select a cell (row or column) and press F4 which will display properties pane.

Finaly to run the report click preview tab
See below the end result.


I hope that helped you in desinging your first simple report using SSRS. I hope to add more tutorials soon.

Getting Started with SSRS - Designing a report using Report Designer

Introduction
This article would cover how to design a report using the Report Designer. In our previous article, we saw the option on designing a report using the Report Wizard with just the configurations used to design and develop the report with some simple steps. However, in this article we will design every component on our own with the help of the Report Designer available with the SQL Server Business Intelligence Studio templates. Let us see the step-by-step process on how to design the report with the Report Designer.
Steps
The first step towards our task is to open Business Intelligence Studio. To open it, we would need to navigate through Programs >> Microsoft SQL Server Denali / Microsoft SQL Server 2008 as per the installed version and select Business Intelligence Development Studio as shown in the screen below.

This will open Visual Studio. It is the Business Intelligence Development Studio for SQL Server as shown in the screen below.

Now select File >> New >> Project to create a new project. We can directly use the shortcut Control + Shift + N to open a new project as shown in the screen below.

Once we have selected the New Project, a list of templates will be available as shown in the screen below.

Select Report Server Project template from the list of templates available and provide a valid name for the project as shown in the screen below.

Clicking on OK will create a project and open the project in the SQL Server Business Intelligence Studio as shown in the screen below.

Initially, there are no files created and only two folders are available. The next step is manually adding a report to the project. To add the report, click on the Reports folder in the solution explorer and select ADD >> New Item as shown in the screen below. Selecting Add New Report will go directly to the Report Wizard that we have already covered in the previous article.

Now, a window will be displayed with a list of available items that can be added to the project as shown in the screen below. Select Report from the list, provide a valid name and click on Add button to add a report to the project.

This will add the report to the project and open the Report Designer to allow designing the report as per the requirement as shown in the screen below.

Next step is to go to the Tool Box. Then drag and drop a table to design the report as shown in the screen below. Now we need to configure the data set first in order to design our report table.

To do the configuration we would first need to create a data source. This can be achieved by clicking on the New button. It will open a window where we would need to supply the details of the connection string as shown in the screen below. Configure it based on the database from which the data for the report needs to be collected.

Clicking on OK will go back to the old window with the data source available for pulling the data for the report. Now click on the Query Designer button to build the query required to pull the data as shown in the screen below.

Clicking on Query Designer will open a new window as shown in screen below. Write the query required to fetch the data. In this example, we are going to use the Authors table from the Pubs database. Once we write our query, click on the Execute button to get the complete details of the data for our reference to finalize the query. We can perform as many executes as required to get a final data as per the business requirement as shown in the screen below. Once we are done, click on OK to close this window and navigate back to the wizard.

Clicking on OK button will go back to the previous window and we can see the selected query available in the list as shown in the screen below.

Click on the OK button to complete the process. With this, we can see the table created in the designer view as shown in the screen below. We can customize the table as per our requirement.

Now we need to customize the report by dragging the outline of the table to the required length and width as shown in the screen below.

Next, we need to go to the data source we have created and select which columns should appear in which part of the report. To do this, first select the cell and click at the right side corner of the cell to get the list of columns as shown in the screen below:

Select the columns based on the requirement and edit the headers to the required customized format as shown in the screen below.

Click on the Preview tab to view the report’s preview as shown in the screen below.

Now we can see the report output in the preview mode. We can build and execute the project if we are satisfied with the report output. Else, we can go ahead and change the report design based on the business requirement. Since we have not used any inbuilt wizard, we need to manually do all the customization (Selecting Styles formats etc.). Now we have made some customization as shown in the screen below.

Press F5 or hit the Play button from the tool bar to build and execute the project to get the report output. However, we will get an error message that the start-up report project is not specified as shown in the screen below.

Go to the properties of the project and select the report as shown in the screen below. Since we have not used any wizard to build the report generation, these steps needs to be performed manually.

Once again, press F5 or Play button from the tool bar to build and execute the project. This will present the result as shown in the screen below.

Summary
Thus, in this article we have seen how to design and generate report from scratch without using the Report Wizard with the help of SQL Server Business Intelligence Studio.

Getting started with SQL Server Reporting Services

Introduction

Microsoft has come up with its own reporting service, in conjuction with SQL server database to introduce the Microsoft SQL Server Reporting services[SSRS]. It provides projects of type Business Intelligence Project thus enabling not only large companies but also medium-sized and small companies also to earn from its benefits. This aids in better business decisions too.
SSRS provides several extensions towards the data rendering, delivery and security of reports thereby allowing it to have a higher programmable ability. This innovative approach enables reports to be created with lesser development effort[compared to other reporting services], along with customized security options.
SSRS is a comprehensive reporting platform whereby reports are stored on a centralized web server (or set of servers). Because reports are centralized, users run reports from one place. Having centralized reports also means that report deployment is quite simplified.

Architecture

After using SSRS, the architecture is just like a small operating system. The Report Manager is the central person who acts as a manager to decide when the reports will be scheduled to run along with maintaining the user profiles on the report server. Also, you can ask the report manager to view or search certain reports. He also helps in site property configuration and folder management in the report server. 
There is something known as a Report Server, at which all the reports reside. All other activities pertaining to SSRS is done at report server. I believe it acts like a workstation for the reporting tool.
Report Designer is basically a graphical tool that are hosted within the Microsoft Visual Studio IDE. Report Designer provides a tabbed windows for Data, Layout, and Preview that allow you to design a report interactively. You can add datasets to accommodate a new report design idea, or adjust report layout based on preview results. Also, he provides query builders, an Expression editor, and wizards to step you through the process of creating a simple report.
There is also a Scheduling and Delivery processor who pushes the reports to email inboxes or ftp locations. Like an operating system the adds processes to the queue, the Scheduling and Delivery processor adds processed[executed by the report processor] reports to the queue. 
The Report Processor, as the name suggests, executes the reports present in the server. Now that we know the basics of SSRS, lets get starting with our first report. 

Getting Started

After installing SQL server reporting services on your system, start the Visual studio IDE.
Go to File -> New Project, and you will be shown a prompt with 'New project'.
Select Business Intelligence Projects from the Project Types. As this is our first project, use Report Project Wizard in the templates pane.

Specify the name of the project as well as the location where the project will be placed.
On click of OK, you will be prompted with a report wizard screen. Click on Next to follow up to the next screen.

On the next screen, you will need to create a datasource for the report. This screen is similar to the Connection string creation wizard on creation of a udl file. However, here you just need to click on Edit to specify the server name and the database the will be used from that server. The connection string is automatically created.
This datasource can be made as a shared datasource for multiple reports to access. This can be done by checking in the checkbox present below.

On click of Next, you will be prompted with the Query Builder screen. Here you can add tables, select columns as well as execute the SQL statements therby created.



Based on what query suits your report, create the SQL statement and proceed forward. On next click, you will be prompted with the report type screen. You can choose as Tabular or matrix. To make things simpler, use the Tabular format.

On next click, you will come to the table designing screen, wherein you will be prompted to display the fields as Page, Group or Details. The relevant locations where the fields will be placed is shown at the side.

On next click, you will be prompted with the Table Style prompt, which contains a list to choose. Select any from them .

On next, you will be prompted with the deployment details screen. Specify the report server name ; normally it is http://localhost/ReportServer. if you are using another server then you can specify the location as http://servername/ReportServer . Also provide in the deployment folder.

Finally, the Report name needs to be entered and voila, you got your first report in place.

You can preview the report to change the data specs using the 3 tabs from the report designer - as mentioned previously.



Finally, click on Ctr+F5 and the deployment of the report will occur.

Incase you have any problems in creating your SQL server report, please let me know.