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 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.
