select @@servername
create database sample_ssrs
use sample_ssrs
--drop table film
-- drop table director
--drop table genre
create table film(FilmId int,Title varchar(20),ReleaseDate datetime,DirectorId int,StudioId int,Review varchar(20),CountryId int,LanguageId int,GenreId int, Genre varchar(20))
create table Director(DirectorId int,FirstName varchar(20))
create table Genre(GenreId int,Genre varchar(20))
--adding primry key to film table
ALTER TABLE film
ALTER column filmid int NOT NULL;
ALTER TABLE film
ALTER column Directorid int NOT NULL;
ALTER TABLE film
ALTER column genreid int NOT NULL;
ALter table film
ADD constraint pk_filmId primary key (filmid,DirectorId,GenreId)
--adding primry key & foreign key assigning to director table
ALTER TABLE director
ALTER column Directorid int NOT NULL;
alter table director
add constraint pk_diretor_directorid primary key(directorid)
ALTER TABLE film
ADD CONSTRAINT FK_film_DirectorId FOREIGN KEY (DirectorId)
REFERENCES Director(DirectorId);
----adding primry key & foreign key assigning to genre table
ALTER TABLE genre
ALTER column genreid int NOT NULL;
alter table genre
add constraint pk_genre_genreid primary key(genreid)
ALTER TABLE genre
ADD CONSTRAINT FK_genre_GenreId FOREIGN KEY (GenreId)
REFERENCES Genre(GenreId);
-- adding records
insert into director values(1,'Charley')
insert into Genre values(1,'Action')
insert into film values(1,'Batman','1/2/2018 12:20:1',1,1,'Best',1,1,1,'Action')
insert into director values(4,'Charley')
insert into Genre values(4,'Action,drama')
insert into film values(4,'IronFist','1/2/2018 12:20:1',4,4,'Best',4,4,4,'Action')
select * from film
select * from director
select * from genre
-- New SSRS Project
--IN RHS open Solution explorer Add new item in Reports (.rdl extention file)
--View--open ToolBox and add TextBox and design color add name it
--connecting to data source
--View--Open Report data or press ctrl+alt+d
In data sources --add data source --connect to server instance and database & test connection…
Check Data set
--add data set --In Query, use a database embedded in my report--choose data source--choose database name and query type text--query designer--right click and add table..select particular column names to add in report
--in data set selected columns are displayed
In view --ToolBox--Report items add table -- in table click data and add selected columns--insert column right left as required
In Preview--see result
Press start and If ssrs configured it will deployed on website url as per configuration..
Reference:
https://www.red-gate.com/simple-talk/sql/reporting-services/sql-server-reporting-services-basics-building-ssrs-reports/
https://www.codeproject.com/Articles/798553/Create-First-SSRS-Report-with-SQL-Server-Analysis
https://www.red-gate.com/simple-talk/sql/reporting-services/ten-common-sql-server-reporting-services-challenges-and-solutions/
https://www.red-gate.com/simple-talk/sql/reporting-services/sql-server-reporting-services-basics-customizing-ssrs-reports/
