Enspire HR

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.

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.