Running head: MS SQL SERVER REPORT BUILDER
MS SQL Server Report Builder
CS 631 – Advanced Database Systems
Dr. Gulsebnem (Sheb) Bishop
August 04, 2022
1
MS SQL SERVER REPORT BUILDER
I began by creating out the first report, click on file, then click on Blank Report.
I then proceeded to add a data source, to do so, right click on data source on the left panel
then click on add data source.
I gave the data source a name, in this case I will call it DataSource1, then I selected a
connection embedded the report. You can also use shared connections for the data source.
2
MS SQL SERVER REPORT BUILDER
To have a data source I can work with, I had to install the latest copy of SQL Server that I
could load my data source from.
3
MS SQL SERVER REPORT BUILDER
The main problem that will be experienced in this process is that the report builder will not
work if you don’t have SQL Server installed that can be your point of data extraction. Once
downloaded, the installation will begin for the SQL Server 2019 edition I was to work with.
Once you have selected the connection type, choose the authentication type for SQL Server
connection. In this case, I chose Microsoft authentication. Once done, test the connection to
confirm and ascertain that it’s successful.
4
MS SQL SERVER REPORT BUILDER
I then added the datasets from data source. Right click on datasets, then click on new dataset
then define the dataset name and choose embedded dataset. Use query select * from
employee to fetch data from the employee table in my data source.
This will add the datasets in the design window of SQL Report builder.
Click on query design and run the query command to populate the data from SQL Server data
source.
5
MS SQL SERVER REPORT BUILDER
Report 1
Once it’s successful this implies that the datasets are ready to use in the report design.
Once I completed this step, I proceeded to design the report.
I began by giving the title of the report.
To design the report, I clicked on insert that gave me a wide range of design tools and options
to choose from.
6
MS SQL SERVER REPORT BUILDER
In the first report, I designed it to show the total number of employees.
Report 2
In this report, I have used the count command to extract the total number of employees from
the employee database. I have also extrapolated a table and extracted both names of the
employees from the database.
7
MS SQL SERVER REPORT BUILDER
Report 3
In thethird report, I am going to show how to add more columns and how to run the report.To
add more columns, right click on last column while in design view then choose add column.
You can then add columns to the right or to the left of the design form.
This is how the report looks like when you run it.
8
MS SQL SERVER REPORT BUILDER
Report 4
Using the features provided, you can also extrapolate charts either bar graph or line graphs.
Using the datasets I created, this helps to give a detailed designed report and hence it
enhances efficient communication and representation of data.
These are the different chart types that be can be used in the report.
9
MS SQL SERVER REPORT BUILDER
For demonstration purposes, I am going to use bar graphs just to show how the report will
populate the data.
This is the output of the report.
Report 5
10
MS SQL SERVER REPORT BUILDER
The report builder can also transmit the report server. This will only be possible when you
have the transmission server that you want the report server to point to.
My SQL Server report builder offers a range of different options and report building
functionalities to enable reporting needs for any type of dataset injected to it from a data
source (Beard, 2016).
The following are some of the challenges and problems I experienced during the whole
process.
i)
Data Type and Format
The editor required more types that are being projected to the datasets to be pre-validated and
the correct formats to be selected from the point of data origin. This ensures data accuracy
and efficiency in the output design.
ii)
Query Parameters
During the design phase, it is required to have vast knowledge of queryin order to populate
data from the database, in order to get different filtered data, it is essential to have knowledge
of data query and data population from the database using query in order to have several
options or data processing to choose from other than the provided ones.
iii)
Report Data in Header/Footer
11
MS SQL SERVER REPORT BUILDER
When creating reports from scratch, it’s important to define the headers and footers properly.
In this case, I was required to give the report a title and also give the footer variables as well.
In this case, I gave my design the title based on the dataset I used and invoked the execution
time in the footer based on the time I ran the report.
iv)
SQL Server Management Studio
It’s important to have knowledge of SQL Server management Studio. This enables you to
create the database to be used. The different tables to be used include, the queries that will
enable the data to populate, the relationships within tables and finally the access points and
authentications for access.
V). Multiple Data from Different datasets
To be able to design data outputs from different datasets, it requires you to have knowledge
of creating data relationships between different tables.
In conclusion, data visualization is a very important aspect in data representation and data
output population into understandable information.MS SQL Server report builder has wide
12
MS SQL SERVER REPORT BUILDER
range of features that will enable report outputs to come out in well-designed format and this
ensures efficient report architecture.
13
MS SQL SERVER REPORT BUILDER
14
References
Beard, B. (2016). Building Reports Using Report Builder. In Beginning SQL Server R
Services (pp. 181-208). Apress, Berkeley, CA.