• Marek Vavrovic

SSRS: List: Writing Christmas Bonus Letter.

Updated: Aug 31

Demo:

I want to create a letter which will look like this

Data source : NorthWind database

Data set:


Use Northwind;

go


Select TitleOfCourtesy +' '+FirstName+' '+LastName as FullName,

Title as JobPosition,

Address,

City,

Revenue,

SUM(Revenue)over () TotalReveue,

(Revenue/SUM(Revenue) over()) Perc_Share,

Revenue*0.01 as YourXmasBonus

from

(

Select [LastName],[Address], [FirstName], [Title], [TitleOfCourtesy], [City], [Country],

Sum((UnitPrice*(1-Discount))*Quantity) As Revenue

from Employees e

Inner join Orders o

on e.EmployeeID= o.EmployeeID

inner join [Order Details] od

ON O.OrderID = oD.OrderID

Group by [LastName],[Address], [FirstName],[Title],[TitleOfCourtesy],[City],[Country]

)Result

Group by [LastName],[Address],[FirstName],[Title],[TitleOfCourtesy],[City],[Country],[Revenue]


Step 1:

Add Data source and dataset

Add Page Header by right clicking on the report page > Add Page Header

Step 2:

Right click inside the Header area > add Text box. Write a text in the text box : Christmas bonus letter.

Because this text box is inside the header, the text of the text box will be visible on each page.

Step 3:

Insert a List inside the report body

Step 4:

Add FullName field from the dataset.

Report preview

Step 5:

Go to Page Breaks. Add Page break > Between each instance of a group.

Report preview

Step 6:

Add fields Address, City JobPosition from the Dataset to the list. Basically everything besides the text box with "Christmas bonus letter" need to be inside the List.

Step 7:

Insert a textbox inside the list. Right click on the text box > expression

Add an expression as on the picture.

="Dear associate,"


Step 8:

Insert a textbox inside the list. Right click on the text box > expression

Add an expression as on the picture.

="We are very aware of the hard work and dedication that you all have put in the projects. On behalf of the company, I would like to appreciate each one of you. Having successfully completed every project before time, our clients are very happy with us, and the company is proud is of its employees."

Report preview

Step 9:

Insert a textbox inside the list. Right click on the text box > expression

="The company made a profit of " +FormatCurrency(Fields!TotalReveue.Value,0) + " this year. You have enriched this package with " + FormatCurrency(Fields!Revenue.Value,0) + ", which represents "+FormatPercent( Fields!Perc_Share.Value,0) +" of the total profit. That's why we decided to give you a financial bonus, one percent of your sales this year, which is:"

Report preview


Step 10:

Drag the field YourXmasBonus inside the List

Step 11:

Move the rest of the fields inside the List and finish the report.


1 view0 comments

Recent Posts

See All