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:

Users can bookmark their favorite reports in the browser and run the report without having to select the report from the list first.
Website developers can embed reports in existing web pages, allowing seamless integration of myDBR into any website.
Reports can be published to be used without the need to log in to myDBR. These reports are accessible to anyone.
Combined with the export capability and HTTP Basic Access Authentication, data can be easily exported from myDBR reports.
Getting report images from myDBR to be used inside an IMG tag in an external application.

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, etc.). 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 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 in 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 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 generates a unique ID for the object. embed=2 is meant for embedding myDBR reports into a 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. The language code (fi_FI, sv_SE, en_US, etc.) determines the myDBR user interface and used formatting for 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 requested. The number behind the 'p' refers to the order number of the parameter in the report's stored procedure (p2 = stored procedure's fixed second parameter).
s1, s2, s3, ... Fixed report parameters that will be shown to the user if more parameters are requested. Otherwise, they behave the same as 'p' parameters.
h Security hash, calculated by myDBR. This parameter ensures that the URL is valid and comes from a trusted source.
r Report ID/report procedure name.
sr MD5 hash of the report procedure name, can be used as an alternative to the 'r' parameter.
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 manually, you need to know the URL hash seed value used in the installation. Follow these steps to calculate the hash:

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 the 'sp' parameter which contains the MD5 value of the stored procedure name.
For each 'p' and 's' parameter: concatenate 'p'/'s' + parameter number + the URL-encoded parameter value.
Optional report location parameter 'm' with its value.
The seed from the Environment settings.

The hash is a SHA1 value obtained from this concatenation.

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 the 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 where the user can change the date but not be able to change the first two parameters. We designate the first two parameters with 'p' and include them in the hash calculation. The 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 URL-encoded parameter value
'm' with value for the report's folder (this parameter is optional)
The URL hash seed from Environment settings

This would produce a string like '20p1200p2My%20paramsecret'. The hash is a SHA1 value derived from this string (d94be41fedd8ae66014a364120a020f01049d828).

Since 'u' parameters are user-changeable, they are not included in the hash.

The full URL would be in the form:

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

Calculating the URL with the report name instead of the report ID would result in 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 the 'sp' parameter instead of the 'r' parameter and use the 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 handled within 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. Instead of prompting the user to authenticate using the login screen, myDBR uses the provided username and password. Due to the plain text transmission of credentials, it is recommended to use an HTTPS connection for security.

Using basic access authentication an extra header 'X-MYDBR-AUTH: 1' is required.

With Basic Access Authentication, the username and password are sent in HTTP headers. If myDBR is configured to use Single Sign-On (SSO) authentication and a Basic Access Authentication request is made, myDBR will redirect the authentication call to use its internal authentication system. However, when using myDBR internal authentication or Active Directory authentication, no redirect occurs, and authentication proceeds through the standard 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 employ HTTP Basic Access Authentication when scheduling reports to run periodically. Use curl or wget and insert the following command into your crontab (Linux/Mac) or Windows Task Scheduler.

curl -u"username:password" -H "X-MYDBR-AUTH: 1" "http://myserver.com/mydbr/report.php?r=630&m=85&h=d46af7ad9a26bc574e10aa1b8c591a18892c57592"