Report By User with Scheduler

ReportServer (EN)
1

Hi

I created a reports with BIRT with 3 parameters “start_date”, “end_date”, and “username”.

My query is:
SELECT SITE ,USERID ,USER_FULL_NAME,SUM(NUMPAGES)
,SUM(CASE WHEN PRINTJOBCOLOR = ‘N’ THEN NUMPAGES ELSE 0 END) AS TotalMono
,SUM(CASE WHEN PRINTJOBCOLOR = ‘Y’ THEN NUMPAGES ELSE 0 END) AS TotalColor

FROM dbo.VW_REPORTS
WHERE (FINALACTION = ‘C’ OR
FINALACTION = ‘P’ OR
(FINALACTION = ‘F’ AND RELEASEMETHOD = ‘R’))
AND FINALDATE BETWEEN ? AND ?
AND USERID = ?

GROUP BY SITE, USERID, USER_FULL_NAME
ORDER BY SITE, USERID ASC

Where: FINALDATE BETWEEN “start_date” AND “end_date”
AND USERID = “username”

In Report Server I created these 3 parameters too. Where:

“start_date” - is a Date/Time Parameter
“end_date” - is a Date/Time Parameter
“username” - is a Data Source Parameter with the query below:

SELECT DISTINCT USERID
FROM PR_STATS
WHERE USERID = ${_RS_USER.getUsername()}

With this setting the Report work very well, I put the date time information manually and to username I use the user login (${_RS_USER.getUsername()).

Now I am testing the scheduler function, it is work very well.

but I would like made it:

I would like send this report by user once time week with user information. In my tests the report was sending to users, but the report generated was created with root user information, because I used root to create the scheduled.

My question is: May I generate report by user? May I get de username or email information the Recipients to email? May I set the “start_date” to get the actual date - 1 week and the “end_date” with actual date?

Regards

Cadu

2

I would have SQL Server do this work for you.

http://dba.stackexchange.com/questions/1426/how-to-the-get-current-date-without-the-time-part

SQL Server DATEADD function for one week prior:

DATEADD(month, DATEDIFF(month, 0, GETDATE()), -7)

and

SQL Server GETDATE function for current date:

GETDATE()

DISCLAIMER: Make sure you verify the results by running it in SQL Server first! Dates can be tricky in any DBMS (Oracle, MySQL, etc.). Especially ranges! Don’t forget about factoring midnight (11:59:59 or 23:59:59) (assuming there are time stamps in your data) for your end date! :slight_smile:

If it works, please post your working code here for the benefit of others.

3

Another option besides doing it in SQL as Tom explained is using $today expressions which are available for filters and parameters:

If your report has a date parameter you can switch it into formula mode via its context menu. You can then enter an expression that is calculated off the date of the report execution.
For example: ${today.addDays(-5)} would designate the current date minus 5 days. Be aware that these expressions always contain date and time information, so you might want to strip the time component by adding a call to clearTime() at the and.

Access to the report recipient unfortunately is not possible: As a report might have multiple recipients but is only executed once and then sent to all, it wouldn’t be of much use, either. You could built a solution using scripts, where you schedule the script and the script executes the report for each user, but this is an somewhat advanced topic.

Cheers,
Thorsten

4

Thorsten

Thank you for your explanation!

I should set my parameter “start_date” as ${today.addDays(-7).clearTime()} in formula as default and “end_date” as ${today} in formula as default

I scheduled the report and worked very well!!

Now when I executed this report manually in parameters/preview the parameter “start_date” showed me ${today.addDays(-7).clearTime()} and not for example: 15 Jan, 2015 00:00:00 and the parameter “end_date” showed me ${today} and not for example: 22 Jan, 2015 10:30:00. Is possible settings to showed me date format e not ${today} when I executed this report manually?

How I built a solution using scripts? Do you have a example for it?

Best Regards

Cadu

5

Hi

Do you have a any suggestion about it:

Is possible settings to showed me date format e not ${today} when I executed this report manually?

How I built a solution using scripts? Do you have a example for it?

Best Regards

Cadu

6

Hi Cadu,

I am not quite sure I understand the question. Do you want the parameter configuration page in ReportServer to display the date instead of the formula?
This is, I am afraid, not possible at the moment.

As for an introduction to scripting. Have a look at the Administration Guide and the Scripting Guide (http://reportserver.net/en/shop/).
Following is a very simple example of a script that executes a report with a given id.

import net.datenwerke.rs.core.service.reportmanager.ReportExecutorService
import net.datenwerke.rs.core.service.reportmanager.entities.reports.Report

def id = 38l

def service = GLOBALS.getInstance(ReportExecutorService.class)
def report = GLOBALS.findEntity(Report.class, id)

return service.execute(report, "PDF")

Regards,
Arno