So till now
we have seen that how we can use SQL Server integration service to
collect data from various sources like excel, CSV, SQL Server, oracle etc
and then we can dump this data into a centralize database like data warehouse
How we can use SQL Server analysis service to create a cube
and by creating a cube you can then get a multi dimension view of the data
Now we are on the final phase
where we would like to and view this analyse data
and that is done by using SQL Server reporting service
We have seen how we can collect data by using SSIS
how we can analyse data by using SSAS
Now we are on the final phase where we would like to go and display this data by using SSRS
In this lab
will try to understand how we can use
SSRS to create a simple basic report first
so here’s a simple table we have called as countrymaster
and we would like display this country master using SQL Server reporting service
Let’s start with some small examples
and then gradually move towards bigger examples
The first step is go ahead and open your business intelligence studio
and from the business intelligence studio
go ahead click on new project and let's select the reports server project wizard
let’s give a nice name to this saying
Display
conuntries
press okay
The first thing what we need to supply here is
the data source
The data source
where your country master is lying so currently our country master
is on the server that is WIN-BQBER
HWWYBH
and this
country master table is lying in this database that is MyCustDB
Go back to our dialog box, let’s click on edit
You can also see before I click on edit here you can also see that is you can have different types of data sources
currently our data lying in SQL Server but in case
Your data is lying in analysis service as a cube and in case your data is lying in XML
you can chose them accordingly
For now our
countrymastertable
is lying in SQL Server
Let’s go ahead and select on Microsoft SQL Server click on edit
and
Give first the server name
I will just copy and paste the server name from here
Our database name is
MyCustDb
I will just test the connection
I will say okay and I will say next
Now we need to provide the table or the query
From where we need to extract data
currently we need to extract data from this country master over here
I'll click on this query builder button over here
This query builder button what it does is its actually opens up a very nice
query designer over here
Add this table that is country master
Select the columns, currently I want to display both the country id as well as a country name
You need to select is
what kind of report it is? Is it a tabular report or a cross matrix report
Currently just select tabular. I will talk about cross matrix in the coming up labs so leave this matrix for now select on tabular, do a next
When we talk about a report, a report is divided into three sections
the first section is
page level data for example page numbers
page headers whatever it is
The second is in case you report some kind of groupings for example if want to group by
Some country name or state name right
and the third one is the actual data
Currently we don’t have any kind of page level data or grouping data
I will select both of these columns into details for now
This first section is where you will go and put page level data
Second section where you will put group level data
and the last one is details
Don’t worry about first two section will talk about later on
I have just selected the available fields into details
Now you can chose your table style so let me just select
corporate
Reporting services
reports actually get hosted into IIS
Host your report keep that as it is
and
also I am keeping the folder name as it is I will talk about both this folder name as well as the report server url later on
Rpt Display
country
and finish
Once you have created the project you know you will see two tabs on your screen
One is the design tab and other one is the
preview tab
The design tab useful you know when you want to go and change the text of your report for example you can see we have a very unfriendly name over here
Rpt display country so I can change the saying that
list of
countries
That’s more user friendly and descriptive
Second I can change
the column name as well the text of the coulmn names
The design tab is useful when you want to go and change look and feel of your report
and the preview tab helps you to see how your report looks actually looks like with the data
I have clicked on the preview tab
We will display the report
The output of the report how it's look like
The design tab is for designing and the preview tab is to preview the output of the report
Click on view and let’s click on solution explorer
on the solution explorer he has created a file called as RptDisplayCountry.rdl
This .rdl file is where all your report
structure gets stored
When you are designing this report
all the meta data of how this report will look like what are the fields what is the data source everything is getting stored into this .rdl file
The internal format of this .rdl file is an XML
If you right click on this file and if you say view code
you will find that
the complete
file structure is an XML you can see over here
the look and feel like fonts etc everything is in XML over here
We don’t need to know about how this internal XML file is but
just for a knowledge sake we need to understand that whenever we are doing designing over here
It modifies this XML file
i.e the .rdl file
We are able to design the report we are able to preview the report
When we say our end users want to go and see this report
They will not be installing such kind of complicated business intelligence studio
End users would like to go and see this report on simple browsers
End users would like to go and see this report on applications like win form application or WPF application
For that we need to publish this report on internet information server
Click on view
click on solution explorer
right click on your project and say
deploy
The time you do deploy
you can see in your output window it says that deploy started
and then it deploys this report on to the report server
IIS web application
This report server web application is a readymade web application which gets created
when you install SQL server reporting services
If you see your solution explorer structure
inside the report server
it has gone ahead and it has created a folder called as DisplayCountries
Our project name was displaycountries
He has created a folder called as display countries and inside the displaycountries
he has deployed your report that is Rptdisplaycountry
Once this report is deployed on to IIS
now anybody can go consume this report on a browser anybody can go consume this report on a win form application WPF application wherever they want it
First try to view this report inside a simple browser
In order to view this report just go and browse to this local host report server url
because there is where the report has now been published
As soon as we go and click on this report server it actually goes and displays
the folder insides the report server web application
He has displayed all the report
server folders which are present in the report server application. Currently our folder display countries let’s click on that
and inside that folder we currently have
only one report that is RptDisplayCountry
There our report is displayed on the browser
and with the report he you can see that he has displayed here a default tool bar
Inside this tool bar there are some readymade facilities like you can export this report to csv, to pdf, excel, word etc
This was a very simple example you where we were trying to understand how we can use SSRS to create a simple report
and how we can go and see this report inside a browser
I hope you enjoyed this lab now in the next lab we would like to go and implement a search facility on the report
This report is good but what if I want to go and do a simple search over here where I will say that if I put IND I should only see IND right
In the next lab will go and see their how we can go and create input parameters for our report