Wednesday, 7 November 2018

Schedule reports like a Boss

Boss: I want the report of user created for each department for the previous week every Monday morning at 6:00 AM

Developer: Okay Boss. I will create a report for you. You can run it every Monday morning.

Boss: Okay,but I don't want to run it. It will be good that when I log in Monday morning the report is emailed to me and I can review it with a cup of coffee..

Developer : oooohkk. 


So where do we start?!!
1) Simple LiveReport to fetch the users created in the last week
select 
K1.Name as Username, 
K2.Name as Department, 
K1.MailAddress,
CreateDate  
from DTree , KUaf K1, KUaf K2
where SubType = 142 
and DataID = K1.ID
and K1.GroupId = K2.ID
and K1.Deleted = 0
and CreateDate > %1

and CreateDate <= %2


Where %1 and %2 are set as following parameters
%1 - Last Week Start
%2 - Last Week End


2) Create webreport with source as LiveReport and make a neat table 
[/* Title: Blank Reportview */]
<title>User created during the week : %1 and %2</title>
<table>
<tr>
<th> Name </th>
<th> Department </th>
<th> Email Id </th>
<th> Create Date </th>
</tr>
[LL_WEBREPORT_STARTROW /]
<tr>
<td> [LL_REPTAG=UserName DECODE:'?':'' /] </td>
<td> [LL_REPTAG=Department DECODE:'?':'' /] </td>
<td> [LL_REPTAG=Mailaddress DECODE:'?':'' /] </td>
<td> [LL_REPTAG=CreateDate DECODE:'?':'' /] </td>
</tr>

[LL_WEBREPORT_ENDROW /]

</table>


3) Schedule the report
Go to destination tab of the WebReport and select Email as destination.
Select Set scheduled and then make it available for monday and dont forget to click on the forever checkbox :)




4) Close your laptop and go home.