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.
