Reports need to interact with the outside world in many ways. To formalize this, we allow to pass values from the outside to the inside. Pentaho Reporting knows several classes of inputs:
- Environment variables
- Parameter: User supplied values usually coming from an interaction of the user with a user interface
- DataRow-Fields: part of the result-sets from the data-sources
- Expressions and Functions: Calculated values
Today, we will talk about environment variables. Environment variables are system level parameter. You can use them to configure reports based on where you deploy the report.
We provide several properties for use within the Pentaho BI-Server.
- serverBaseURL: the server URL, for example: “http://localhost:8080”
- pentahoBaseURL: the location of the Pentaho Web-application: “http://localhost:8080/pentaho”
- solutionRoot: The file-system location where the local copy of the solution repository can be found.
- requestContextPath: the servlet’s request context path pointing to the local web-application: “/pentaho/”
- username: The current user’s name
- roles: The roles as CSV-encoded list. The CSV string is quoted according to the Excel CSV quoting rules.
Inside the BI-Server, environment variables can also be used to interact with the server’s environment to query session and global parameter information. The report environment allows you to access values stored on the current user’s session or as global parameter by using a special prefix.
Requesting a environment property “session:name” will query the session context for an entry called “name” and returns the string representation of this object to the report.
Likewise, prefixing a environment property with “global:” will query the global parameter collection of the BI-Server. Therefore querying a environment variable “global:name” will look for a entry “name” in the Bi-Server’s global parameters.
Accessing Environment variables with Formulas
The Pentaho Reporting Engine offers the formula function “ENV” to access the report environment properties.
Definition:
=ENV(String)
To access the variable called “example” as plain text, use
=ENV("example")
To access a value bound to the server-side session variable “example” use
=ENV("session:example")
If the variable is a CSV encoded string, you can use the CSVARRAY function to parse the string into a array of strings. This array is then suitable to use for multi-selection parameters or SQL-IN clauses.
=CSVARRAY(ENV("roles"))
You can now use these variables as part of other formulas, for instance to compute URLs pointing to documents on the server server.
=ENV("serverBaseURL") & "/myotherwebapp/downloads/pentaho-report-designer.zip"
Accessing Environment variables as predefined fields
When you open up the Pentaho Report Designer, you will see several of the well-known environment variables listed as fields on the report itself. Using the ENV function is flexible, but not very convenient. So we added a automatic mapping to the reporting engine.
The various env:* fields you see in a report are what I call “well-known” fields. As Pentaho Engineers we know knows that they exist on the server and are heavily used by our users. So we added a mapping to the system so that they automatically become fields without having to use a formula function to read their values.
The list of “well-known” fields is defined in the global report configuration. The global configuration can be edited by creating or editing a file called “classic-engine.properties” inside your Pentaho Report Designer and Pentaho BI-Server directories.
Inside the Pentaho Report Designer, this file resides in the “resources” directory in your Pentaho Report Designer installation directory. For the BI-Server you can find this file in the “pentaho/WEB-INF/classes” directory. To make your reports work consistently inside the Report Designer and the Server, make sure that both files have matching definitions.
For example: If you want to map your environment-property “value” into the field “env::value” then you would add the following line to your “classic-engine.properties” file:
org.pentaho.reporting.engine.classic.core.env-mapping.value=env\:\:value
In case value contains a comma separated list (using the Excel-CSV quoting rules for strings that need to be quoted), then you can make that list available as array by appending -array to its name.
org.pentaho.reporting.engine.classic.core.env-mapping.value-array=env\:\:value-array
The engine will recognize the “-array” suffix and will look for a “value” property instead, parses that into a string-array and returns you the array. The array then can be used in a SQL-IN clause.
The prefix “env::” is a convention inside the reporting engine to separate environment fields from other fields. If you omit this, you have to make sure that none of the fields returned by your data sources and none of your expressions or functions provide a value under the same name. This would overshadow your environment property and renders it inaccessible.
can environment variables be used for text-field input in database connection setting?
cause i want to make a different database connection for different user (wich i got from env::username variable)
Nope that is not yet supported. Given the fact that this topic appears more and more these days, this is very likely to get into the 4.0 release.
how to access server session variable from javascript
I am trying to access the output-target parameter by adding org.pentaho.reporting.engine.classic.core.env-mapping.output-target=env\:\:output-target but that does not work. Can this be done?
If you want to see the value of the parameter, declare it as parameter. Environment is meant for rather static configuration details that are provided by the runtime system – like your current URL & so on.
Declare your parameter as string, and fill in the list of output types you want to support. For valid values, look over here: http://wiki.pentaho.com/display/Reporting/BI-Server+Reporting+Plugin+Documentation
What I am trying to do is to create links in the report that will open the linked reports in the same format as the current one. Lets say that I open the first report as Excel. Then when I click on a link in that report the next report should also open as Excel. So I want to set the URL to the following
=DRILLDOWN(“remote-prpt”; ENV(“pentahoBaseURL”); {“solution”; “steel-wheels” | “path”; “reports” | “name”; “Buyer Product Analysis.prpt” | “output-target”; ENV(“output-type”)}) but I can not get the current output-target. Is that possible? My current solution is a function with a bunch of if statements like ISEXPORTTYPE(“table/html”) that sets the output-target. It works, but I would like to get the current output-target instead of testing all possible…
Jonas, I wrote a short tutorial to show you how to your style of linking in a more efficient way:
http://www.sherito.org/2012/03/linking-to-report-with-same-export-type.html
HI Thomas,
I have couple of queries as below, pls let me know your views.
1.Reuse of Parameters – For instance in the demo Buyer Report there is a parameter “Choose a Line”. If you open the report in PRD the parameter name is “line”. We would like to determine if there is a way that other reports can reuse a parameter such as line without having to go into PRD and edit each report. Possibly a Global Parameter
2.whether there is a “Global Variable” available that can be used in User Console. For example lets say in one report the user set Line parameter to “Planes”. We know that other reports have a Line parameter but is it possible that the value “Planes” can be stored in some static variable which can then be accessed by other reports.
Thanks,
Amit
1) This is not possible. At the moment we have no shared global objects of any kind. However, the idea makes total sense – http://jira.pentaho.com/browse/PRD-3849
2) I think there is a JIRA case for that in the BI-Server for storing and loading parameter values in the user-console. With all the other projects going on, I am not sure whether there is actually a roadmap item for that. This aspect of a “global variable/global store” should probably go into that case as well. Conceptually, this is out of scope for the reporting engine, as the engine itself does not have a notion of users or sessions or global storages. The outside context is provided by whoever embeds the engine, and this is the place where that stuff needs to be implemented. So this is something that can only be solved by the BI-Server team.
Thanks for your inputs, so could pls guide me for any alternative workaround for above issues.
Thanks,
Amit
There are no workarounds. Both issues require coding inside (1) the reporting engine or (2) the BI-Server. There is no quick solution here.
How would you define a session variable as well known? env::session:variable?
I am writing a scriptable data source to access mongodb in PRD. Instead of creating new db connection every time when script is invoked, I want to store a single mongo pool object outside script so that it can be shared by multiple users
I created a mongodb object when server start up and stored a reference in session variable. Then I used ENV function to access it in script through a parameter. However what it returned is string representation.
How could I access this mongodb pool object in the script?
Hey Thomas,
fine article!
One question: It does not seem to work if I write something into the IPentahoSession or sessionContext, for example in PUCLogin.jsp:
userSession.setAttribute(“x1″,”ups1”);
session.setAttribute(“x2″,”ups2”);
And I expect this to use on a report like
session:x1 or session:x2 (or in a formula)
Do you have a clue why this is not working?
Any idea how to achieve this?
Would be great.
Two conditions have to be true: (1) Whatever you store in the session must be a string. Anything else will be ignored. (2) If you want to access that value directly as a field (for example: “session:your-token-here”) then you must register that field in the report configuration. If it is not registered, the only way to get to your data is via the formula functions.
Thanks a lot Thomas: you saved me with this tutorial and “ENV-Fields: Why, When and How to use them”.
Bye,
Andrea