Creating a Report without using Report Wizard in SSRS

We already created a Report using Report wizard in SSRS (SQL Server Reporting Services). Lets now create a report without using report wizard in SSRS i.e. a Custom report using MS SQL Server Reporting Services (SSRS).

STEPS TO FOLLOW: –

Step 1. Go to Start – All Programs – Open Business Intelligence Development Studio (BIDS). Click on create a New project. This will show New project screen. Choose Report Server Project from the template under Business Intelligence Projects tab. Specify desired  Name, Location, Solution name for the new report server project.

Step 2. Go to Reports under Solution Explorer, Then Click Add and choose New Item from the menu.

Step 3. Choose Report option from the Template window and provide a desired name for your report.

Step 4. This will show you the working environment where we will design and preview our report.

Step 5. Drag and drop table from the toolbox to the workspace. This will pop up Data source properties window. Configure this by providing appropriate connection settings and test your connection. If everything goes well then clicking Test connection will show success.

Step 6. Go to Query Designer and click on “Edit as Text” to disable it (if it is already clicked by default). Disabling this will show all the options to create query automatically else we have to write it manually.

Step 7. Query designer have all the options to make your query work easy as it will generate automatic query according to the options you select. Go to Add Table and choose your desired tables/views and Execute the query to see the output. If eveything is fine then it will show you the choosen result below the Query pane. Click Finish. Step 8. All the selected columns will come into our dataset. Drag the desired columns onto the table in the design pane. Also, you can add as many columns (To the right/left) as you want by clicking right-click and choose Insert column option.

Step 9. When you are done with adding your columns to the table. Click on  Preview button to generate the report.

Step 10. This will generate our desired report which we can export further to any format such as pdf, Excel, Image, etc.

With this we complete our post on Creating a report without using Report wizard in SSRS (MS SQL Server Reporting Services). We will further play with other features of SSRS and will go into the complex report generation. Please rate if it helps you and your queries are always welcome.

Create a report using Expressions in SSRS

Expressions in SSRS i.e. MS SQL Server Reporting services are used frequently in creating reports which helps in controlling content and report appearance. Expressions can use built-in functions, custom code, report and group variables, and user-defined variables. In this article, we will create a report and then implement some expressions to make the appearance of our report eye catchy. Expressions in SSRS is expalined below:-

1. Helps you customize the content, eye catchy appearance, and interactivity of your report.
2. Widely used throughout a report to retrieve, calculate, display, group, sort, filter, parameterize, and format data.
3. They are written in Microsoft Visual Basic, and saved in the report definition.
4. They operates on a cell by cell basis.
5. To see the actual data from the evaluated expressions, you must preview the report.
6. An expression is represented as [fx] button and begins with an equal sign (=) followed by a combination of field identifiers, constants, functions, and operators.Example: =Sum(Fields!Sales.Value)

Let’s create a report and perform some of the simple expressions to understand the concept and functionality of Expressions in SSRS.

STEPS TO FOLLOW –

Step 1. Create a report by any of the below preferable method implemented previously in my SSRS tutorials section i.e.

 Report generation Using Report wizard.

 Report generation without using Report wizard.

 NOTE: – I am creating a report without using Report wizard. You can choose any method.

Step 2.   Now, let’s play with expressions and manipulate our created report. My report output looks like a black and white television screen. Let’s make it lively and eye catchy with the use of expressions.

 Step 3. We will take some scenarios to understand the working of expressions in a better way and implement them in our report.

First Scenario – Color Change Expression.

Problem – Under Salary column, change the color to Green if salary is greater than 20,000 else change the color to Red.

Solution– Go to the design pane and right click on the salary field. Choose Text box properties and go to the Font part. Click on [fx] button of the color section to write our color change expression. Write the expression as: –     =IIF(Fields!Salary.Value>20000,”Green”,”Red”) and click OK button.

 Preview the report to see the implementation of color change expression.

Second Scenario – Pattern Matching (LIKE statement) Expression.

Problem – Under Location column, change the color of the fields to Blue which contains “ca” else remains the color to default.

Solution – In the design pane, right click on the Location field and choose Text box properties. Go to the Font part and click on [fx] button of the color section. This will pop up Expression window where we will write our expression for pattern matching. The expression will be like this: –

=IIf(Fields!Location.Value.ToLowerInvariant().Contains(“ca”),”Blue”,”Transparent”)

Go to the preview pane and see the output of pattern matching expression.

Note: – If we will remove ToLowerInvariant() then, “ca” will not be equal to “Ca” or “cA”. To make these entire equal, we added this to the expression else we could simply use =IIf(Fields!Location.Value. Contains(“ca”),”Blue”,”Transparent”)

Third Scenario – Set Alternate Row Color Expression.

Problem – Make the even rows color to LightGrey and odd rows color to White.

Solution – In the design pane, choose the entire row from the Tablix. Go to the Properties window and click on the drop down button under the Background color option. Select Expressions and write the following in the expression window.

=IIF(RowNumber(Nothing) MOD 2,”LightGrey”,”White”)

In the preview pane, you can see the output for the alternate row color expression.

Fourth Scenario – Setting Report Header Properties.

Problem – Make the Report header professional.

Solution – It’s very easy and depends upon your creativity and innovation how you design the report. Select the header part of the report from the Tablix and go to the properties window. Now you can select and play with all the given options to make your report professional.

Output will be like this – I have made changes to the Text alignment, Vertical alignment, Background color, Font and Border style.

These scenarios are very simple to implement and helpful to strengthen yours writing expression skills. Below is the list of expressions you can use depending upon various scenarios.

Create a report using Report wizard in SSRS

Aim – We will create a simple report with the help of Report wizard using Microsoft SQL Server Reporting Services (SSRS) 2008 version. With the step by step implementation of reporting services, we will also try to understand the working environment for the report making. Let’s create a report without wasting time on theoretical part as doing practical will make you understand better.

STEPS TO FOLLOW:-

Step 1. Open Business Intelligence Development Studio (BIDS) — Go to Start – All Programs – MS SQL Server 2008 R2 – Business Intelligence Development Studio.

Step 2. This will open a Start page. Go to Create project and choose Report Server Project Wizard. We need to provide Name, Location and Solution name for the project.

Step 3. Clicking OK button will pop up Welcome Report wizard screen. You can tick the check-box if you do not prefer to see this screen next time.Step 4. Now we need to select a data source from which we will obtain data for creating this report.Either we can choose Shared Data Source (If data source is already been present) or can create a new Data source by providing data base credentials.

I will be creating a new data source so that we can understand this step clearly. Give a desired nameand type for the data source. I am using MS SQL Server as my database, so will choose it from theType drop down box. Click on Edit button.

It will lead to the Connection properties screen containing the fields as mentioned below:-

• Server Name – Choose the Server name from the drop down box.
• Log on to the Server – Select the preferred Authentication method and provide valid credentials for that method (As i am using client MS SQL Server, so i will use SQL Server Authentication method to get connection with my server).
• Connect to a Database – As the connection established, all the tables or views will be populated in the drop down box and we can choose our desired table for creating report.

NOTE :- If everything goes well then clicking on Test Connection button will result into Success else you have to configure your connection correctly.

Step 5. Now we need to concentrate on this step as we are going to design our query to fetch the desired data from the chosen table. Click on Query Builder to generate automatic query for our task. You can also write it by your own but i prefer shortcuts.

Click on Add Table button present at the extreme right on top and choose the tables whose report you want to generate and click OK. I will be selecting a single table to perform this report creation by Report wizard method.

Step 6. Clicking Next button will lead to Report type screen. Select the desired Report type and click Next.Step 7. Choose how you want to see your data in the report format according to your need. This is the screen where you can design the layout for your data. Click Next button.

Step 8. Choose the type of Layout for the table. I will be making the report using Stepped layout but you can also try Blocked layout and can analyze the difference between these 2 layouts.

NOTE:- Include subtotals and Enable drill down features will be covered later as we move forward towards the complex report generation but if you are anxious to know then you can tick the check boxand can learn it from the output you get. You can select Enable drill down check box if you want to make a drill down report by Report wizard.

Step 9. Choose a style for the table and click Next. Choose the Deployment location for the report and click Next button.Step 10. We are about to see the output for our report. Provide the name for the report and clickFinish button to create a new report using Report wizard.NOTE :- If we select Preview report check box, then we directly can see our report in the Preview Paneelse we will land into the Design Pane.

Step 11. Clicking Finish button leads to the Design pane which is known as Workspace. Let’s understand the basics of our working environment.In our working Environment we will have features like:-

1. Toolbox containing tools such as table, rectangle, line, matrix, sub-report, etc.
2. Dataset containing columns which are generated by our SQL query which we passed in Query builder.
3. Two types of modes named as Design and Preview mode.
4. Solution Explorer and Properties window pane for the report.
5. Work space, Built in fields, parameters and many other features.

Step 12. Now, it’s the time to raise the curtains and see our output for the report which we have created by spending so much of time.That’s Great !!! We get the success for our first report creation using MS SQL Server Reporting Services (SSRS) by Report Services Wizard. You can also deploy your report if you have theadministrative rights.Deploying your report to the server will make you to see it via web interface.I guess it was very simple for you to implement this task. We will be creating custom reports in our next part using MS SQL Server Reporting Services – SSRS 2008. Your comments and views are welcome.