Today, I got some tips that how to use Report Builder 3.0 to generate reports.
Report Builder 3.0 is a report-authoring tool that’s part of the Reporting Services suite of tools available in SQL Server 2008 R2. However, Report Builder is a free, stand-alone application that can be used by SQL Server developers and business users alike to create reports rich in data visualizations.
Find out the application from: https://www.microsoft.com/en-nz/download/details.aspx?id=6116
First of all, let’s see the first page of RB (the abbr. of Report Builder 3.0), from the above graph, it shows that different options to start up, let’s choose ‘Blank Report’ to build up the first report.
Right click on the Datasets on the left side, to ‘Add Dataset …’;
Then, give a name – ‘Northwind’ for the new query, and click ‘New…’ button;
Provide the name for Dataset, in this example, it calls ‘Northwind’, and choose ‘Use a connection embedded oin my report’, choose ‘Microsoft SQL Server’ from the drop-down list, then click ‘Build…’ button;
Give the Server name (according to the local database), and choose Northwind database, after successful Connection Test by ‘Test Connection’, click ‘OK’;
Double check the ‘Test Connection’ before start to design the report;
Then, go for ‘Query Designer…’;
Due to show ‘a table with a SupplierName column and a Product name column’, some columns needed have been chosen, then click all ‘OK’;
Now, find out ‘Table Wizard…’ from ‘Table’ on the navigation bar, and click it;
Choose the dataset named ‘Northwind’ that just set up , and ‘Next>’;
All chosen columns display in ‘Available fields’ area, move all of them into ‘Values’ area, and click ‘Next>’;
Do not choose ‘Show subtotals and grand totals’ & ‘Expand/collapse groups’ unless needed, and click ‘Next>’;
Choose an appropriate style for this table, which will show in the report, and click ‘Next>’;
Finish all steps above, RB will show like the above graph, and click ‘Run’ function on the navigation bar, the report will be automatically generated like the following graph:
These are the entire process to set up a basic report by using RB, then, let’s try some tasks to make more familiar with this application.
Task 1. Make a tabe showing the number of products in each category
For this task, the main difference according to the previous steps, is choosing different columns, and setting up Row groups:
So, the design of this report should like this:
After ‘Run’, we could get the report:
Task 2. Find customers’ orders for particular year
For this task, the main difference according to the previous steps, is writing query rather than choosing columns from tables:
In this query, it looks like a stored procedure, and there is a parameter, which allows to generate the report according the particular year:
Task 3. Make a table showing the total number of orders of each customer per year
For this task, the most difficult thing is we do not really know how many years in the database, it should be generated according to the records in the database automatically, so this time, we provide the query without ‘GROUP BY’, and use ‘Matrix’ to build a report:
Then, run this report, it will be:
The only thing that we need to do after finishing all steps to build a report, is making the final version more readable and attractive.
General speaking, Report Builder 3.0 is a very powerful tool to generate reports, and easy to use even embed into other system.
For all of above, it is from my training session, if anyone has any queries, or wants to discuss more, please do not hesitate to leave your comment or contact via Contact page.