myDBR allows you to run reports directly by providing the required information in the request URL. Direct URLs are useful in the following cases:
myDBR passes all parameters required to run the report in the URL. The parameters in the report are the stored report's parameters. If not all the required parameters are provided, the missing parameters are requested from the user.
The report parameters passed in the URL are labeled with a sequence number (1=first parameter, 2=second...). Those parameters that are requested from the user, and that the user can change are marked with 'u'. Parameters derived from the database are considered as protected parameters and are not user changeable.
The advantage of the user changeable / protected parameters is that it eases up the report authoring process. When you know that a user is passing on a protected parameter, you can trust that user can use that parameter in the query.
The direct URL has the following parameters which can be added and changed by the user:
u1,u2,u3,...Report parameters. The number behind the 'u' refers to the order number of the parameter is the report's stored procedure (u2 = stored procedure's user changeable second parameter)
e1,e2,e3,...Report parameters that the user can change before the report is run. The number behind the 'e' is the same as in 'u'
hdr=0Removes myDBR header and footer from the output. This can be used for embedding the report or creating a printable report.
getchart=1Makes image report to return just the image data. Allows usage in IMG-tag.
embed=XWhen used, the code generated includes only the generated objects code (embed=1), No <head>-tags are generated. This is used when myDBR's output is used inside another web page. The ID passed on is to generate a unique ID for the object. embed=2 is meant for embedding myDBR reports to Dashboard.
embed_imageOverride default setting and use embedded images in the chart.
image_formatOverride default image format setting
lang=language_codeCan be used to override the user's language setting. Language code (fi_FI, sv_SE, en_US, etc.) determines the myDBR user interface and used formatting on dates, decimal numbers, etc.
The following standard parameters cannot be modified by the user:
p1,p2,p3,...Fixed report parameters not shown to the user if more parameters are asked. The number behind the 'p' refers to the order number of the parameter is the report's stored procedure (p2 = stored procedure's fixed second parameter).
s1,s2,s3,...Fixed report parameters which will be shown to the user if more parameters are asked. Otherwise behaves the same as 'p'-parameters.
hSecurity hash, calculated by myDBR. This parameter ensures that the URL is valid, comes from the trusted source.
rreport ID/report procedure name
srmd5(report procedure name), can be used as an alternative to r-parameter
mm=folder id for the report
You can generate a direct access URL by accessing the report inside myDBR and copying the URL. If you add '&hdr=0' to the URL, the headers and footers will be omitted. Please note that 'u' parameters are user changeable. See 'URL parameters' below.
To calculate the URL hash by yourself you need to know the URL hash seed value used in the installation. To calculate the hash do following:
Hash is a SHA1 value from this.
We have a report 'sp_DBR_myreport' with ID 20 (r=20) and URL hash seed 'secret'. The report resides in folder 7 (the m-parameter in URL this parameter is optional). The report definition looks like this:
create procedure sp_DBR_myreport (inAccountID int, inNotice varchar(30), inDate date ) begin ... end
Now we'd like to create a direct URL in which the user could change the date but not be able to change the first two parameters. We name the first two parameters with 'p' and include those in the hash calculation. Parameter list with example data looks like this:
The hash calculation generates a string with: reportID + each 'p'-parameter + (optional 'm7') + URL hash seed, where:
This would produce a string with '20p1200p2My%20paramsecret'. The hash is a sha1-value from the string (d94be41fedd8ae66014a364120a020f01049d828).
The 'u' parameters are user changeable, so it will not be part of the hash.
The full url would be of form:
Calculating the URL with report name instead of report ID would be done from the hash string: 'sp_DBR_myreportp1200p2My%20paramsecret'
If one wishes to hide the report procedure name and still use the portable URL, you can use sp-parameter instead of the r-parameter and use md5-value from the report name. md5('sp_DBR_myreport') = 3842a2ce71288a713015c033acf52c41m so the hash string would be 3842a2ce71288a713015c033acf52c41p1200p2My%20paramsecret
To embed a report in your web site you can use the
<object data="http://mysite.com/mydbr/report.php?r=14&u1=2009-10-01&m=1&h=123456789abcdef&hdr=0" type="text/html" height="500px" width="100%"> </object>
You can make a report which produces an image chart (ChartDirector / Graphviz) to produce the pure image by adding
&getchart=1 to URL. This will make the image accessible directly via an IMG tag.
This allows you to embed a report image from myDBR to your intra/extranet or into your own application without any extra effort. Example:
<img src="http://mysite.com/mydbr/report.php?r=14&u1=2009-10-01&m=1&h=123456789abcdef&getchart=1" />
You can also generate just the report object's content without any header information. This would allow you to insert a myDBR result into your own webpage/application using a simple Ajax call. All styling can be done in the receiving application.
Getting just the report object: Example:
<img src="http://mysite.com/mydbr/report.php?r=16&u1=2009-11-01&m=1&h=123456789abcdef&embed=1" />
myDBR also accepts connections using HTTP basic access authentication. In this method, a username and password are sent with the HTTP request and instead of asking the user to authenticate using the login screen, myDBR uses the supplied username and password. Since basic access authentication sends the username and password in plain text it is good practice to use an HTTPS-connection.
Using basic access authentication an extra header
'X-MYDBR-AUTH: 1' is required.
With basic access authentication, the username and password will be sent in HTTP headers. When myDBR is using SSO authentication and a basic access authentication request is made, myDBR will redirect the authentication call to use myDBR internal authentication. In case of myDBR internal authentication and Active Directory authentication no redirect is made and authentication is done through normal procedure.
An example call to obtain report output as a JSON object (&export=json added to the report call).
<?php $ch = curl_init(); curl_setopt($ch, CURLOPT_URL, 'http://myserver.com/mydbr/report.php?r=630&m=85&h=d46af7ad9a26bc574e10aa1b8c591a18892c57592&export=json'); curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1); curl_setopt($ch, CURLOPT_HTTPAUTH, CURLAUTH_BASIC); curl_setopt($ch, CURLOPT_USERPWD, 'username:password'); curl_setopt($ch, CURLOPT_HTTPHEADER, array('X-MYDBR-AUTH: 1')); $data = curl_exec($ch); curl_close($ch); echo $data; ?>
You can also use HTTP basic access authentication when you schedule reports to run periodically. Use curl or wget and put the following command into your crontab / Windows Task Scheduler.
curl -u"username:password" -H "X-MYDBR-AUTH: 1" "http://myserver.com/mydbr/report.php?r=630&m=85&h=d46af7ad9a26bc574e10aa1b8c591a18892c57592"