Home > DeveloperSection > Beginner > Creating SSRS (SQL Server Reporting Service) in MSSQL Server 2014

Creating SSRS (SQL Server Reporting Service) in MSSQL Server 2014


Reporting Reporting  SQL Server 
Ratings:
0 Comment(s)
 819  View(s)
Rate this:

Creating SSRS (SQL Server Reporting Service) in MSSQL Server 2014

 

Open Sql server data tools referred in the image below:-

Go to File->New->Project. It will open a new dialog .Select Business Intelligence from installed templates -> Reporting Services. When you select Reporting Services on right side of there will be two options- Report Server Project Wizard and Report Server Project .Select desired option .If you want to create report from wizard then choose Report Server Project Wizard .it will guide you how to create report .if you choose Report Server Project it will create empty report project .Here I will create report through wizard. The following image show the new project dialog-

 

Now give your project name and solution name and select desired location in new project dialog and click ok.it will display a new dialog shown below-

Click on Next button it will open new dialog called as select data source dialog show below-

Give data source desired name and select type Microsoft SQL Server and enter connection string in text box or click on Edit button. Edit button click open a new connection properties dialog. Enter server name, login server details and enter or select desired database .Click ok. If want to test connection click on Test Connection button if connection succeeded it will display success message box.

If click on ok button  in connection properties dialog it return back to data source dialog click  on Credentials button it will display the following dialog-

Enter the user name and password for data source or select use windows authentication whatever applicable for your setting and click ok button in data source dialog click on next button it will open the following   design query dialog-



My query is-
SELECT        emp.emp_id, emp.emp_name, emp.emp_address, emp.emp_contact_no, emp.emp_zipcode, st.state_name, cty.city_name, c.country_name
FROM            employee AS emp INNER JOIN
                         country AS c ON emp.emp_country_id = c.country_id INNER JOIN
                         state AS st ON st.state_id = emp.emp_state_id INNER JOIN
                         city AS cty ON cty.city_id = emp.emp_city_id

You can user query builder or write your own query and click on Next button display  the following dialog-

Select Tabular option and click next it will display the following table design dialog-


 

Select desired field for page, group and details .in my case I choose state_name as group and click on next button it will display the following  table layout dialog –

Select desired option and click on next button it will display the following table style dialog-

 

Select table style   according to requirement or continue with the default style and click on next button- it will display the following deployment location dialog-

 

Enter deployment folder name and report server url as requirement or continue with default settings and click on next button it will display the following dialog-

 

Enter report name and click on finish button. Design view of report dialog-

 

Report preview dialog-


My table structure -


USE [EmployeeDb]
GO
/****** Object:  Table [dbo].[city]    Script Date: 4/13/2016 7:10:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[city](
    [city_id] [bigint] IDENTITY(1,1) NOT NULL,
    [state_id] [bigint] NULL,
    [city_name] [nvarchar](max) NULL,
 CONSTRAINT [PK_city] PRIMARY KEY CLUSTERED
(
    [city_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[country]    Script Date: 4/13/2016 7:10:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[country](
    [country_id] [bigint] IDENTITY(1,1) NOT NULL,
    [country_name] [nvarchar](max) NULL,
 CONSTRAINT [PK_country] PRIMARY KEY CLUSTERED
(
    [country_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[employee]    Script Date: 4/13/2016 7:10:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[employee](
    [emp_id] [bigint] IDENTITY(1,1) NOT NULL,
    [emp_name] [nvarchar](max) NULL,
    [emp_city_id] [bigint] NULL,
    [emp_country_id] [bigint] NULL,
    [emp_state_id] [bigint] NULL,
    [emp_address] [nvarchar](max) NULL,
    [emp_contact_no] [nchar](15) NULL,
    [emp_zipcode] [numeric](6, 0) NULL,
 CONSTRAINT [PK_employee] PRIMARY KEY CLUSTERED
(
    [emp_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[state]    Script Date: 4/13/2016 7:10:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[state](
    [state_id] [bigint] IDENTITY(1,1) NOT NULL,
    [country_id] [bigint] NULL,
    [state_name] [nvarchar](max) NULL,
 CONSTRAINT [PK_state] PRIMARY KEY CLUSTERED
(
    [state_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[city] ON

INSERT [dbo].[city] ([city_id], [state_id], [city_name]) VALUES (1, 1, N'allahabad')
INSERT [dbo].[city] ([city_id], [state_id], [city_name]) VALUES (2, 1, N'kanpur')
INSERT [dbo].[city] ([city_id], [state_id], [city_name]) VALUES (3, 1, N'lucknow')
INSERT [dbo].[city] ([city_id], [state_id], [city_name]) VALUES (4, 1, N'banaras')
INSERT [dbo].[city] ([city_id], [state_id], [city_name]) VALUES (5, 3, N'Mumbai')
SET IDENTITY_INSERT [dbo].[city] OFF
SET IDENTITY_INSERT [dbo].[country] ON

INSERT [dbo].[country] ([country_id], [country_name]) VALUES (1, N'india')
SET IDENTITY_INSERT [dbo].[country] OFF
SET IDENTITY_INSERT [dbo].[employee] ON

INSERT [dbo].[employee] ([emp_id], [emp_name], [emp_city_id], [emp_country_id], [emp_state_id], [emp_address], [emp_contact_no], [emp_zipcode]) VALUES (1, N'Sunil', 1, 1, 1, N'Teliarganj allabad', N'9090909090     ', CAST(211003 AS Numeric(6, 0)))
INSERT [dbo].[employee] ([emp_id], [emp_name], [emp_city_id], [emp_country_id], [emp_state_id], [emp_address], [emp_contact_no], [emp_zipcode]) VALUES (2, N'Manoj Kumar', 2, 1, 1, N'Civil Lines ', N'7887878778     ', CAST(324434 AS Numeric(6, 0)))
INSERT [dbo].[employee] ([emp_id], [emp_name], [emp_city_id], [emp_country_id], [emp_state_id], [emp_address], [emp_contact_no], [emp_zipcode]) VALUES (3, N'Tarun Kumar', 1, 1, 1, N'beli allahabad', N'3243545667     ', CAST(211002 AS Numeric(6, 0)))
INSERT [dbo].[employee] ([emp_id], [emp_name], [emp_city_id], [emp_country_id], [emp_state_id], [emp_address], [emp_contact_no], [emp_zipcode]) VALUES (4, N'Aditya Kumar Patel', 5, 1, 3, N'East Andheri   Mumbai', N'3445656778     ', CAST(422002 AS Numeric(6, 0)))
SET IDENTITY_INSERT [dbo].[employee] OFF
SET IDENTITY_INSERT [dbo].[state] ON

INSERT [dbo].[state] ([state_id], [country_id], [state_name]) VALUES (1, 1, N'uttar pradesh')
INSERT [dbo].[state] ([state_id], [country_id], [state_name]) VALUES (2, 1, N'punjab')
INSERT [dbo].[state] ([state_id], [country_id], [state_name]) VALUES (3, 1, N'maharashtra')
INSERT [dbo].[state] ([state_id], [country_id], [state_name]) VALUES (4, 1, N'madhya pradesh')
SET IDENTITY_INSERT [dbo].[state] OFF






Don't want to miss updates? Please click the below button!

Follow MindStick