articles

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

Sunil Singh6093 13-Apr-2016

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

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

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-

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

 

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-

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

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

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

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.

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

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-

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

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-

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

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-

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

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

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

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 –

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

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

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

 

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

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

 

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-

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

 

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

 

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

Report preview dialog-

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


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





Updated 04-Mar-2020

Leave Comment

Comments

Liked By