Friday, July 29, 2011

NextReports : Use hidden parameter inside another parameter

NextReports has a powerful support for parameters. You can have simple parameters, hidden parameters, chained parameters, procedure parameters. Parameters with values selected from table columns or from manual sources will have their type automatically selected. Parameters can have single or multiple selection. All of these will define how the user interface is created, what UI components will be used for selection.

Till version 4.1 only parameters used inside the main query had a meaning and were taken into account. New version will bring also a new feature regarding parameters usage.

A hidden parameter can now be used inside the source of another parameter without of being used inside the main query. Let's take a simple example.

We have a PROJECTS and a USERS tables. We want to select one or more projects for the user logged inside a NextReports Server, those projects which have the current user as project manager.

NextReports Designer has a special parameter __USER__ which has to be declared as following :


Value is passed to this parameter on the server and it means the name of the current logged user.
This parameter is defined as 'hidden', so it cannot be selected / modified from the UI.

We have to define our Project parameter and to use __USER__ parameter inside its source:


So, now we have a Project parameter which will filter data regarding to the logged user on the server. We design our report and we will use the Project parameter as a criteria :


The automatically created query inside the editor looks like this :
SELECT
    S1.PROJECT_ID,
    S1.NAME,
    S2.USER_NAME
FROM
    ANYTIME.SPP_PROJECTS S1,
    ANYTIME.SYS_USERS S2
WHERE
    S1.PROJECT_MANAGER_ID = S2.USER_ID AND
    S1.PROJECT_ID IN ${Project}
ORDER BY
    S1.PROJECT_ID 



No comments: