Perquisites:

Your system must have the following components installed to take this tutorial:

  • Microsoft SQL Server database engine.
  • SQL Server Reporting Services or later (SSRS).
  • SSRS Report builder.

We are going to create a customized report with the list of employees with company name, branch name location with company logo dynamically.

We are going to keep filters CLBD drop-down filters, with Employee Name as text box and employee status as Enums drop-down.

 

Step-1: Open the Report Builder application. Select Blank Report


Step-2: Select the title and press the delete button same with the execution time.


Step-3: Right-click on the report page, select Insert-> select Page Header.


Step-4: Click on Insert Menu in the menu bar and click on Image


Step-5: Then click on the Header part in the report, the below image properties box will open.

 

Step-6: Give the image name and select the image source as External

 

Step-7: Adjust the Image properly

Step-8: Right-click on the Data Source click on the Add new data source, configure the database connection and click on the OK button.


Step-9: Follow the below query for queries CLBD drop-down filters, create data set for each one.

Select OrgID,OrgName,'{APIURLwithpath}'+CompLogo as CompanyLogo from hrms_org(nolock) where ParentOrgID<>0 and Status=1

 

 

Select LocCode,LocName from HRMS_LOC(nolock) where (OrgID=@Company or @Company=0)

 

Select BranchCode,BranchDesc from HRMS_BRANCH(nolock) where StatusID=1 and (LocCode=@Location  or @Location=0)

 

select A.DepCode,A.DepName from HRMS_DEPARTMENT(nolock) A inner join HRMS_DEP_BRANCH(nolock) B

on A.DepCode=B.DepCode where (B.BranchCode=@Branch  or @Branch=0)

 

 

 

Step-10: Report builder will create parameters like Company, Location, and Branch. Right-click each one of them and set the properties as below.

  • Set Allow null values in the general section.
  • In the Available Value section, select the ‘Get values from query’ option.



 

  • In the Default, value section select ‘Specify values’ and give the default value as 0 and click on the OK button.


 

Step-11: Right-click on the Parameter section add a new parameter manually as the Employee search box and click on OK.


Step-12: For Status Enums Drop down add a new Parameter as Status and go to the Available Values section. Select the Specify values section and add the value as below image.


Step-13: Right-click on the image and select Image Properties.


Step-14: Select the ‘Use this field’ as the Data set.Company Logo column.

 

Step-15: Drag and Drop the data set names values to the report header section.

Step-16: Insert the Page number into the report footer section.

 

Step-17:  Now using the below query we will create a table result set for the employee list.

Select M.EmpCode,M.EmployeeName,

Case when M.Gender='M' then 'Male'

when M.Gender='F' then 'Female'

else 'Other' end as Gender 

from HRMS_EMP_MASTER(nolock) M 

inner join HRMS_EMP_JOB(nolock) J on M.EmpCode=J.EmpCode

inner join HRMS_EMP_POSITION(nolock) P on P.EmpCode=M.EmpCode

inner join HRMS_ORG(nolock) O on J.CompanyID=O.OrgID

inner join HRMS_LOC(nolock) L on J.Location=L.LocCode

inner join HRMS_BRANCH(nolock) B on J.Branch=B.BranchCode

WHERE (o.OrgID=@Company OR @Company=0) AND (l.LocCode=@Location OR @Location=0)

AND (b.BranchCode=@Branch OR @Branch=0) AND (p.DepCode=@DepCode or @DepCode='')

and (M.EmployeeName like '%'+@EmployeeName+'%' or @EmployeeName='') and

(M.Status=@Status or @Status=0)

 

 

Step-18: Go to the Insert menu, click on the table -> Table wizard


Step-19: Select the Main Result Data set and click on next

 

Step-20: Drag and drop all the required fields into the container of the value and click the Next button.

 

Step-21: Click on the Next Button

 

Step-22: Click on the Finish button.


Step-23: A New table will appear on the report page like below.

 

 Step-24: Customize the table background color, header color, and fonts using the right-hand side properties box.


Step-25: Customize the table data properties.


Step-26: Save the Report using your customized name.


Step-27: In the Home, menu clicks on the RUN button to run the report.

 

Step-28: In the Running mode after filters applied, the report will look like this.