Direct URL access to reports

myDBR allows you to run reports directly by providing the required information in the request URL. Direct URLs are useful in the following cases:

URL parameters

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 the 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=0 Removes myDBR header and footer from the output. This can be used for embedding the report or creating a printable report.
  • getchart=1 Makes image report to return just the image data. Allows usage in IMG-tag.
  • embed=X When 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_image Override default setting and use embedded images in the chart.
  • image_format Override default image format setting
  • export=export_type See Export
  • lang=language_code Can 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.
  • h Security hash, calculated by myDBR. This parameter ensures that the URL is valid, comes from a trusted source.
  • r report ID/report procedure name
  • sr md5(report procedure name), can be used as an alternative to r-parameter
  • m m=folder id for the report

Generating a direct URL

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 the following:

Concatenate:

  • reportID (either the numeric ID or the stored procedure name if you wish to make a portable URL )
  • as an alternative to the reportID you can use sp-parameter which contains md5-value from the stored procedure name
  • For each 'p' and 's' parameter: concatenate 'p'/'s' + parameter number + the urlencoded parameter value
  • Optional report location parameter 'm' with value
  • The seed in the Environment settings

Hash is a SHA1 value from this.

Example

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:

  • p1 = number 200
  • p2 = text 'My param'
  • u3 = date '2010-02-01'

The hash calculation generates a string with: reportID + each 'p'-parameter + (optional 'm7') + URL hash seed, where:

  • reportID: either the numeric ID or the stored procedure name
  • For each 'p' parameter: concatenate 'p' + parameter number + the urlencoded parameter value
  • 'm' with value for report's folder (this parameter is optional)
  • The URL hash seed from Environment settings

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:

https://myserver.com/report.php?r=20&p1=200&p2=My+param&h=d94be41fedd8ae66014a364120a020f01049d828

Calculating the URL with report name instead of report ID would be done from the hash string: 'sp_DBR_myreportp1200p2My%20paramsecret'

https://myserver.com/report.php?r=sp_DBR_myreport&p1=200&p2=My+param&h=5512f73a48693516e4837833b02e2ecd81bb5ced

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

https://myserver.com/report.php?sr=3842a2ce71288a713015c033acf52c41m&p1=200&p2=My+param&h=c14a8b3dd1cf741cf2c230e1bc0a4385b4453dc5

Embedding reports on a webpage

To embed a report in your web site you can use the object tag. Example:

<object data="http://mysite.com/mydbr/report.php?r=14&amp;u1=2009-10-01&amp;m=1&amp;h=123456789abcdef&amp;hdr=0" 
	type="text/html" 
	height="500px" 
	width="100%">
</object>

Direct accessible image reports

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&amp;u1=2009-10-01&amp;m=1&amp;h=123456789abcdef&amp;getchart=1" />

Getting only the report object's content

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&amp;u1=2009-11-01&amp;m=1&amp;h=123456789abcdef&amp;embed=1" />

Using HTTP basic access authentication

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 the case of myDBR internal authentication and Active Directory authentication, no redirect is made and authentication is done through the 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"