In addition to the menu and the API, EspressReport ES also allows you to deploy reports using URLs. Report URLs, like image URLs, allow reports to be called via an http call to the server. However unlike chart images generated by image URLs, report URLs cannot embed in an <img src> or other HTML tag. Report URLs are whole URLs that are run by pointing the web browser to the URL address, or by specifying a hyperlink to the report URL in another Web page.
Like Image URLs, report URLs can be used to generate reports from scratch, or to run existing report templates. However due to the number of available controls in reports, URL parameters are generally related to data input, and exporting.
The easiest way to generate report URLs is to have the Organizer do it for you. To generate a report URL in the Organizer, first select the report template (.pak, .rpt or .xml) that you would like to use. Then you can select Generate Report URL from the Publish menu, Click the button on the toolbar, or right-click and select Generate Report URL from the pop-up menu. A dialog will appear prompting you to specify some of the attributes for the generated report.
Report URL Options Dialog
The first option allows you to select whether to use the optimized memory exporting. This feature is useful for reports that use a large amount of data. Rather then fetching all of the data from the source at once. This feature will only export 5000 records at a time. This allows reports that use a lot of data to be run without running out of system memory. This feature is only applicable if the report draws its data from a database.
The second option allows you to select the format in which you would like to generate the report. Available formats include: HTLM, DHTML, PDF, Excel (XLS), Excel 2007 (XLSX), CSV, text, and rich text.
The third option allows you to select single page or multi page export. This option is only applicable to HTML or DHTML reports. Selecting single page will generate the entire report within a single html file. Selecting multi page will generate a new html file for each page of the report.
If your report is parameterized, then there are two ways in which the report can be run. You can either pass parameters directly into the uRL as arguments, or have the URL generate a parameter prompt. If the report contains parameters, then several options will appear in the report URL options dialog as pictured above. To generate an HTML parameter prompt check the For HTML Parameter Page option.
If you select to generate a parameter prompt, you can also specify a CSS file to use to format the prompt. The dialog has options allowing you to specify the file and http path to the CSS file. (An example of a parameter page CSS file is available under <ERESInstallDir/help/examples/URL>).
If you select not to generate a parameter prompt, then a dialog will open asking you to select parameter values which will be added as arguments to the generated URL.
Report URLs can have an username and password passed in as arguments in the URL. If the administrator is generating the URL then an option will appear in the report URL options dialog as pictured above that allows you to select the user for which the URL is being generated. Clicking the button will bring up a list of defined users.
The URL can also be configured to read the username and password from the session of the URL is going to be run in a JSP or servlet environment. If this option is enabled the URL will try to read the username and password from session parameters USERNAME and PASS.
If an user other than the administrator generates the URL, then an option named For Current User will appear. Checking this option will embed the current user in the URL.
In either case if an user is selected for the URL the encoded username and password are added as arguments to the URL. If no username or password is supplied in the URL then when it is run, the user will be re-directed to a login page before viewing the report.
User Login Dialog
By default, the URL will redirect to the login page under the ERES context (i.e. http://machinename:port/ERES). If you have deployed ERES in a different context, you can use the JSPCONTEXT parameter to pass in a different location.
Once you have finished specifying options for the report URL a new dialog will open containing the generated URL string. If you're running Organizer as an applet, then the URL will load in a new browser window (allowing you to copy and paste).
Generated URL
When run, URLs will generate the report in the specified format and stream it back to the browser. For some formats like PDF or Excel, you will need to have the appropriate plug-in installed on the client in order for the report to display correctly.
To run a report contains encrypted data, you need to do two things in order to view the data.
You need to create an XML file that gives the database URL, database driver, name of column to be decrypted, and the function to be applied when the data is being retrieved.
You need to include the
ReplaceColumnInfoListoption in the command line, i.e.servercommand.txtbefore you start ERES server.
For more details and an example for viewing encrypted data, please see Section 3.2.2.1.2 - Querying Encrypted Data.
Rather than having the Organizer generate the report URL, you can write your own. You can run an existing report template, or you can pass a new data source to a report template, or even create a new report (un-formatted). You can control various report data and output properties using URL parameters. You can also modify an URL generated by the Organizer.
The syntax for report URLs is fairly simple. Every URL begins with a call to the Server: http://machinename:port/context/LookupServlet?. Following the question mark users need to specify whether the URL will return a chart or report using the URLTYPE parameter. Following the URLTYPE users can add parameters to the URL. Parameters are separated by the “&” character.
For example the following URL will run the ProductList.rpt template in the help/examples/URL directory of the ERES installation and return it in PDF format.
http://machinename:port/context/LookupServlet?URLTYPE=FORREPORT
&USERNAME=username&PASS=password&TemplatePath=http://machinename:port
/ERES/help/examples/URL/ProductList.rpt&ExportFormat=PDF
Generated Report
As you can see the URL streams a PDF file back to the browser.
To run this example, you will need to add this file in the Organizer (see Section 2.1.4.1 - Adding and Modifying Files) and set privileges for it (see Section 2.3.2 - Setting User Privileges). The URL includes the USERNAME and PASS parameters. You can either supply these parameters, or remove them. If they're removed, you will be re-directed to the login page before viewing the report. You may also need to modify the URL for the template file depending on how you have deployed ERES.
In order for an URL to run, you must at minimum specify a report template, or specify data source - either a file or database. Also, any input parameter string should use the “+” character in place of a space. An actual space character may disrupt the creation of the report.
URL parameters allow you to specify which report template to use, as well as data source information, and output format appearance such as PDF or HTML.
Login parameters allow you to pass username and password into report URLs. A login is required if the URL references a template file (using the TemplatePath parameters). If login information is not specified in the URL then the URL will re-direct to a login page before showing the report.
- USERNAME:
This allows you to specify an username. If you generate an URL in Organizer and select to use an user, the username will be encoded in the generated URL. You can also specify the username as plain text.
&USERNAME=user- PASS:
This allows you to specify the user password. If you generate an URL in Organizer and select to use an user, the password will be encoded in the generated URL. The password can also be specified using plain text.
&PASS=password- USESESSION:
This allows you to have the server read the username and password from the session. If this option is enabled the information will be retrieved from session parameters
USERNAMEandPASS. This option is only available if you will be deploying the URL within a servlet or JSP application.&USESESSION=true- JSPCONTEXT:
This allows you to specify the location (context) of the ERES installation. This location is used when the URL re-directs to the login page. The default value is
/ERES, and the URL will work without setting this parameter in most deployments. You only need to specify this parameter if ERES is deployed in a context of a different name&JSPCONTEXT="/MyReports"
- TemplatePath:
This allows you to specify the report template you would like to run. A report template is saved with its data source information, so if a different data source is not specified, it will retrieve data from the source with which it was designed.
&TemplatePath=http://machinename:port/ReportFiles/reportfile.rpt![[Note]](../../../images/note.png)
Note You can use both absolute and relative paths for the template file location.
- DataFilePath:
This allows you to specify either a text or XML file from which you would like to retrieve data for the report. For more on data file specifications, please see Section 3.1.5 - Data from Text Files.
&DataFilePath=http://machinename:port/reportdata.txtNote You can use both absolute and relative paths for the data file location. Also, only XML files in the Quadbase format can be specified (The format generated when you export XML data from EspressReport). Other XML formats will not work in Report or Image URLs.
- DataSourceClass:
This allows you to specify a class file that will retrieve data to plot the chart. For more on using class files please see Section 3.1.6 - Data from Class Files.
&DataSourceClass=package.class- DBSourceInfo:
This allows you to specify a database and SQL query to be used to retrieve report data. The parameter takes five input values: database URL, database driver, username, password, and query. Each input value is separated by a semi-colon. For more on connecting to a database, please see Section 3.1.3 - Data from a Database.
&DBSourceInfo=url;driver;username;password;queryIf you do not wish to enter an username or password use the space character “+” instead. Do not drop the input value. The parameter will not run with insufficient inputs. In your SQL statement be sure to substitute the “+” character for any spaces. Also, substitute the word
equalorequalsfor the “=” sign.- QueryParamName:
This parameter is used if the report you're using contains a parameterized query.
QueryParamNamespecifies the name of the query parameter you will be supplying a value for. (Parameter names are specified when you create the query. For more on this, see Section 3.1.3.2.2 - Parameterized Queries) It is always followed by theQueryParamSize, and theQueryParamValueparameter.&QueryParamName=customerID&QueryParamSize=1&QueryParamValue=4- QueryParamSize:
This parameter is used to specify the number of values that will be passed into a particular report parameter. As detailed in Section 3.1.3.2.2.1 - Multi-Value Parameters, some queries can have multi-value parameters. This URL parameter allows you to specify if the parameter (indicated by the previous
QueryParamNameparameter) takes multiple values. This parameter is always followed by one or moreQueryParamValuearguments for each of the paramater values for a particular parameter. If you do not specify this parameter then it will assume that the number of values to be passed in is one.- QueryParamValue:
This parameter is used if the report you're using contains a parameterized query.
QueryParamValuespecifies a value that you would like to pass to the query parameter specified by theQueryParamNameargument. For multi-value parameters a separateQueryParamValueargument must be supplied for each distinct parameter value that you're passing in. For more on creating parameterized queries, please see Section 3.1.3.2.2 - Parameterized Queries.- FormulaParamName:
This parameter is used if the report you're running contains a formula parameter.
FormulaParamNamespecifies the name of the formula parameter for which you will be supplying a value. (Parameter names are specified when you write the formula, for more on this, see Section 4.1.6.2.6 - Formula Parameters). It is always followed by theFormulaParamValueparameter.&FormulaParamName=UserName&FormulaParamValue=Jeff- FormulaParamValue:
This parameter is used if the report you're running contains a formula parameter.
FormulaParamValuespecifies a value that you would like to pass to the formula parameter indicated by the preceedingFormulaParamNameargument. For more on formula parameters, see Section 4.1.6.2.6 - Formula Parameters.- ColumnMapping:
If you have specified a new data source for the report, this argument allows you to map columns from your data file, or database query result set to the report. Column mapping is specified using index values. Index values are assigned based on the order they appear in your data file from left to right, or the order in which they are selected in your SQL statement. Index values start with
0, so the first column is0and the second is1, etc. To perform column mapping, Specify the index values (separated by a semi-colon) from the data source that you would like to include in the report (in the order you want them to appear in the report). You can also provide names for the column in your data source with this parameter as well. This is optional, but setting names allows you to refer to columns by name for the other data mapping parameters. To add column names place them after the column index separated by a comma.&ColumnMapping=3,Category;1,Product;4,Price;5,Sales;10,EmployeeThis example will generate a report that has the fourth, second, fifth, sixth, and eleventh columns from the data source as the first through fifth columns in the report respectively.
Note Column order appears from left to right in the generated report.
- RowBreak:
This argument allows you to set row break columns for a summary break or crosstab report. Columns are specified by index number separated by semi-colons. You can also refer to column names that you established in the
ColumnMappingparameter. For more information about report mapping, please see Section 4.1.2 - Report Types and Data Mapping.&RowBreak=0;1 or &RowBreak=Category;ProductNote The numbers in this argument refer to the report columns and not the data source columns. Hence the above example sets the first two columns of the report to be row breaks. These columns would be the fourth and second columns from the data source (using the
ColumnMappingexample).- ColumnBreak:
This argument allows you to set column break columns for a crosstab report. Columns are specified by index number, and separated by semi-colons. You can also refer to column names that you established in the
ColumnMappingparameter. For more information about report mapping, please see Section 4.1.2 - Report Types and Data Mapping.&ColumnBreak=4 or &ColumnBreak=EmployeeNote The numbers in this argument refer to the report columns and not the data source columns.
- ColumnBreakValue:
This argument allows you to set column break value columns for a crosstab report. Columns are specified by index number, and separated by semi-colons. You can also refer to column names that you established in the
ColumnMappingparameter. For more information about report mapping, please see Section 4.1.2 - Report Types and Data Mapping.&ColumnBreakValue=3 or &ColumnBreakValue=SalesNote The numbers in this argument refer to the report columns and not the data source columns.
- Aggregation:
This argument allows you to specify column aggregation for non-row break columns in summary break reports, and column break value columns in crosstab reports. Columns are specified by index number followed by a comma and then the aggregation type. You can also refer to column names that you established in the
ColumnMappingparameter. Available aggregations areSUM, COUNT, SUMSQUARE, AVG, VARIANCE, MAX, FIRST, STDDEV, MIN, LAST, COUNTDISTINCT.&Aggregation=2,AVG;3,SUM or &Aggregation=Price,AVG;Sales,SUMBy default, non-row break columns in summary break reports have no aggregation, and column break value columns for crosstab reports use
SUM. For more information about report mapping, please see Section 4.1.2 - Report Types and Data Mapping.Note The numbers in this argument refer to the report columns and not the data source columns.
- PrimaryKey:
This argument allows you to set the primary key column for a master & details report. It only accepts one parameter - the column index of the primary key column. You can also refer to column names that you established in the
ColumnMappingparameter.&PrimaryKey=1 or &PrimaryKey=ProductNote The number in this argument refer to the report columns and not the data source columns.
- MasterField:
This argument allows you to specify which columns should be included in the master field for a master & details report. Columns are specified by index number, and separated by semi-colons. You can also refer to column names that you established in the
ColumnMappingparameter.&MasterField=0;2 or &MasterField=Product;PriceThe numbers in this argument refer to the report columns and not the data source columns.
- ReportType:
This argument allows you to specify which report type to use for the generated report. Available types are "Columnar", "CrossTab", "MailingLabels", "MasterDetails", and "Summary". Report types are discussed in Section 4.1.2 - Report Types and Data Mapping. The default report type is "Columnar".
&ReportType=MasterDetails
- Export Format:
This argument allows you to specify the format in which you would like to display the report. Available formats are
DHTML, PDF, TXT, CSV, XLSandXLSX. The default format isDHTML.&ExportFormat=PDFNote If you're generating a parameter page for the report (see Section 7.3.2.2.4 - Parameter Page Parameters) you can specify this argument more than once in an URL to give the user a drop-down list from which to select the export format. If you specify more than one format without generating a parameter page, the report will be generated using the format specified in the first argument.
- ExportOnServer:
This is a Boolean flag that allows you to specify whether the report should be written as a file on the server-side, or streamed back to the client. If this argument is set to
true, then theExportPathargument should also be used to indicate where the file should be written.&ExportOnServer=true&ExportPath=C:\ERES\ExportFiles\report.pdf- ExportPath:
This allows you to specify a file path to export the report to. This argument should always be used with the
ExportOnServerargument. When a file path is specified, the report will be exported in the selected format to the server-side.- MultiPageExport:
This is a Boolean flag that allows you to specify whether or not to use multi page exporting for HTML and DHTML export formats. By default multi page exporting is not used.
&MultiPageExport=falseNote This parameter will not work if you use a different data source than the one specified in the report template.
- OptimizeMemory:
This is a Boolean flag that allows you to specify whether or not to use memory optimized exporting when generating the report. This feature is explained in Section 7.3.1 - Generating URLs in Organizer. By default memory optimized exporting is not used.
&OptimizeMemory=true
- ForHTMLParamPage:
This is a Boolean flag that indicates whether or not to return a parameter page instead of a report. This option only works it the Report contains query or formula parameters. If this argument is set to
truethen the URL will return the parameter page, and ignore any parameter values passed into the URL. By default this argument will befalse.&ForHTMLParamPage=true- ParamPageCssStyle:
This allows you to specify a CSS file to format the generated parameter page.
&ParamPageCssStyle=http://machinename:port/files/ParamPage.cssNote You can use both absolute and relative paths to the CSS file.
- ParamPageTitle:
This parameter allows you to specify a title for the parameter page.
&ParamPageTitle=Select+Report+Options- ParamPageTitleFontName:
This parameter allows you to specify the font to use for the parameter page title.
&ParamPageTitleFontName=Arial- ParamPageTitleFontSize:
This parameter allows you to specify the font size for the Parameter page title. The size is specified as HTML size (i.e.
<font size=3>) rather than point size.&ParamPageTitleFontSize=5- ParamPageTitleFontStyle:
This allows you to apply a font style to the text of the parameter page title. The style is specified as an integer from
0 - 3. The numbers indicate the following styles:0- Plain,1- Bold,2- Italic,3- Bold + Italic.&ParamPageTitleFontStyle=1- ParamPageTitleFontColor:
This parameter allows you to specify the font color for the parameter page title. The color is specified with a six digit hexidecimal number like in HTML.
&ParamPageTitleFontColor=404040- ParamPageTextFontName:
This parameter allows you to specify the font to use for the prompt text in the parameter page.
&ParamPageTextFontName=Dialog- ParamPageTextFontSize:
This parameter allows you to specify the font size for the Parameter page prompt text. The size is specified as HTML size (i.e.
<font size=3>) rather than point size.&ParamPageTextFontSize=3- ParamPageTextFontStyle:
This allows you to apply a font style to the parameter page prompt text. The style is specified as an integer from
0 - 3. The numbers indicate the following styles:0- Plain,1- Bold,2- Italic,3- Bold + Italic.&ParamPageTextFontStyle=0- ParamPageTextFontColor:
This parameter allows you to specify the font color for the parameter page prompt text. The color is specified with a six digit hexidecimal number like in HTML.
&ParamPageTextFontColor=000000- ParamPageBorderThickness:
This parameter allows you to specify the thickness of the border drawn around the prompts in the parameter page. The thickness is specified in pixels. Specifying
0will draw no border around the prompts.&ParamPageBorderThickness=1- ParamPageBorderColor:
This parameter allows you to specify the color for the border drawn around the prompts in the parameter page. The color is specified with a six digit hexidecimal number like in HTML.
&ParamPageBorderColor=C6C6C6- ParamPageTableBackgroundColor:
This parameter allows you to specify the background color for the table containing the prompts in the parameter page. The color is specified with a six digit hexidecimal number linke in HTML.
&ParamPageTableBackgroundColor=FFFFFF- ParamPageAlignment:
This parameter allows you to specify the alignment in the page for the table containing the prompts. Options are
LEFT, RIGHT,andCENTER. The default alignment isCENTER.&ParamPageAlignment=LEFT- ParamPageLayout:
This parameter allows you to specify the layout of the parameter page. You can either align the parameter prompts vertically (default) or horizontally. The options for this parameter are
HORIZONTALandVERTICAL.&ParamPageLayout=HORIZONTAL- ParamPageResetEnabled:
This is a Boolean flag that allows you to show/hide the reset button for the parameter form. By default the button is shown.
&ParamPageResetEnabled=false- ParamPageOuterBorder:
This is a Boolean flag that allows you to draw only the outer border for the parameter prompt table. This parameter will have no effect if the border thickness is set to zero.
&ParamPageOuterBorder=true
The following examples are sample report URLs that use one or more of the parameters listed in the preceding section. To use these, simply change machinename to your host name, port to the server port that you are using, and context to your servlet context. All of these examples use templates that are in the help/examples/URL directory, so you'll need to make sure the templates have been added to the Organizer and that you have permission to view them.
The URLs include the USERNAME and PASS parameters. You can either supply these parameters, or remove them. If they're removed, you will be re-directed to the login page before viewing the reports.
These URL examples are also included in a file named ReportURL.txt under help/examples/URL.
Example 1: Draw data from a text file
This example draws data from the Sample.dat file under the help/examples/DataSources/text directory, and creates a simple columnar layout. To run this example, you will need to add help/examples/URL/Example1.rpt to the Organizer (see Section 2.1.4.1 - Adding and Modifying Files) and set privileges for it (see Section 2.3.2 - Setting User Privileges).
http://machinename:port/context/LookupServlet?URLTYPE=FORREPORT
&USERNAME=username&PASS=password&DataFilePath=http://machinename:port
/ERES/help/examples/DataSources/text/sample.dat&ColumnMapping=3;1;4;
5;10&TemplatePath=http://machinename:port/ERES/help/examples/URL
/Example1.rpt&ExportFormat=DHTML
Generated Report
This report draws data from fourth, second, fifth, sixth, and eleventh columns of the sample.dat file. You may need to modify the URL for the data and template files depending on how you have ERES deployed.
Example 2: Draw data from a database and generate a summary break report
This example uses the Woodview HSQL database that is included with the ERES installation under help/examples/DataSources/database also names the columns in the data mapping parameters. To run this example you will need to have the HSQL JDBC driver in your classpath. To run this example, you will need to add help/examples/URL/Example2.rpt to the Organizer (see Section 2.1.4.1 - Adding and Modifying Files) and set privileges for it (see Section 2.3.2 - Setting User Privileges).
http://machinename:port/context/LookupServlet?URLTYPE=FORREPORT
&USERNAME=username&PASS=password&DBSourceInfo=jdbc:hsqldb:help
/examples/DataSources/database/woodview;org.hsqldb.jdbcDriver;sa;+;
select+c.categoryname,+p.productname,+p.unitprice,+p.stainprice,+
p.unitsinstock+from+categories+c,+products+p+where+c.categoryid+
equal+p.categoryid+and+(c.categoryname+equal+'Side+Chairs'+or+
c.categoryname+equal+'Arm+Chairs')&ColumnMapping=0,Category;1,Product;
2,Price;3,StainPrice;4,UnitsInStock&RowBreak=Category
&Aggregation=Product,NONE;Price,AVG;StainPrice,AVG;UnitsInStock,SUM
&ReportType=Summary&TemplatePath=http://machinename:port/ERES
/help/examples/URL/Example2.rpt&ExportFormat=PDF
Generated Report
You may need to modify the URL for the template file depending on how you have ERES deployed.
Example 3: Open a parameterized report and generate an HTML parameter prompt
To run this example, you will need to add help/examples/URL/RegionalSales.rpt to the Organizer (see Section 2.1.4.1 - Adding and Modifying Files) and set privileges for it (see Section 2.3.2 - Setting User Privileges).
http://machinename:port/context/LookupServlet?URLTYPE=FORREPORT
&USERNAME=admin&PASS=admin&TemplatePath=http://machinename:port
/ERES/help/examples/URL/RegionalSales.rpt&ForHTMLParamPage=true
&ParamPageCssStyle=http://machinename:port/ERES/help/examples/URL
/ParamPage.css&ParamPageBorderThickness=1&ParamPageTitle=Please+
Select+Parameters&ParamPageOuterBorder=true
&ParamPageTableBackgroundColor=F7F7EF&ParamPageBorderColor=6C6464
&ExportFormat=DHTML
Generated Parameter Prompt
The URL first returns a parameter prompt based on the parameters specified in the URL and formatted with the .css file under help/examples/URL. Clicking in this page returns the report with the specified parameters.
Generated Report
You may need to modify the URL for the template and CSS file depending on how you have ERES deployed.