Doing Stuff with Django: Using Django with SQLServer reporting

If you have a requirement to launch a SQLServer Reporting Services report from a Django site, here is the method I used. It isn't as straight forward as it might first seem:

First if you need to pass data from Django to the report, passing the parameters on the URL isn't always a good idea as it allows an end user to potentially view data they shouldn't see by changing the URL parameters manually. I used a SQL table to store the parameters and generated a GUID to pass to the report. The report can then retrieve the parameters using a SQL query using the GUID as a key. A request params row needs to be populated in a table, so a form is needed to request some of the request parameters while others may be calculated.

The model for the request parameters table is:

class params(models.Model):

guid=models.CharField(null= False,max_length= 40, editable= False)

report_type = models.ForeignKey( 'report_type', editable=False )

start_date = models.DateTimeField()

end_date = models.DateTimeField()

#... add any other parameters you need

def __unicode__(self):

return self.description

A ModelForm is then used with settings to include only the fields that require user input:

from django.forms import ModelForm

from .models import params

import logging

log = logging.getLogger(__name__)

class paramsForm(ModelForm):

class Meta:

model = params

fields = ( 'start_date', 'end_date' )

Two urls are defined, one to direct the user to the view that displays the model form template and another to direct the user to a separate view whose only job is to redirect to the SQLServer report. Because of HTTP restrictions this redirect can't be carried out in the first view because a POST (which comes back from the Model Form template) can't be redirected:

from django.conf.urls import patterns, url

from xgenca_report import views

urlpatterns = patterns('',

url(r'notification_report/', views.notification_report, name='notification_report' ),

url(r'showreport/', views.show_report, name= 'show_report')

)

The two views are shown below. The non user editable parameters are set up in the first view and the URL to the SQLServer report is stored in the session. The second view then redirects to this URL.

from .models import report_type

from myapp.forms import paramsForm

from django.shortcuts import redirect, render_to_response

from django.contrib.auth.decorators import login_required

from django.template import RequestContext

import hashlib

import random

@login_required

def notification_report(request, *args, **kwargs):

form = paramsForm(request.POST or None)

if request.method == 'POST':

#if form.instance.start_date and form.instance.end_date:

if form.is_valid():

obj = form.save(commit= False)

#... set other parameters here if needed

obj.report_type = report_type.objects.get(report_description= "Notification" )

guid = hashlib.sha1(str(random.random())).hexdigest()

obj.guid = guid

obj.save()

url = 'http://reportserver/ReportServer/Pages/ReportViewer.aspx?%2fMYPROJECT%2fNotificationsReport&rs:Command=Render&report_guid=' + guid + '&report_guid=' + guid

request.session[ '_reporturl'] = url

return redirect( '/report/showreport')

return render_to_response( 'myapp/notification_report.html' , {'form': form,}, context_instance=RequestContext(request))

# View used to redirect the user to the report URL (this can't be done in the original view because of HTTP restrictions that prevent a POST being redirected

def show_report(request):

return redirect(request.session.get( '_reporturl'))

The template looks like:

{% extends "base.html" %}

{% block content %}

<h3 align="center">{% block title %}Notification Report{%endblock%}</h3>

<tr>

</td>

{% csrf_token %}

</form>

</td>

</td>

</tr>

</table>

{%endblock%}

Configuring Apache for Basic Authentication to the SQLServer Reporting Services Server

One final problem is that when the user is redirected to the report server they may be asked to log in. I got round this problem by configuring my Apache web server for basic authentication against the report server as follows:

LoadModule proxy_html_module modules/mod_proxy_html/mod_proxy_html.so

LoadModule xml2enc_module modules/mod_proxy_html/mod_xml2enc.so

Include modules/mod_proxy_html/proxy_html.conf

# Set up proxy for SQL Server reports

ProxyPass http://yourreportserver/ReportServer/

ProxyPassReverse http://youreportserver/ReportServer/

SetEnv force-proxy-request-1.0 1

SetEnv proxy-nokeepalive 1

SetEnv proxy-initial-not-pooled 1

ProxyHTMLEnable On

ProxyHTMLURLMap http://yourreportserver/ReportServer/ /ReportServer/

# Base64 encoding of "yourdomain\youreportserveruser:yourreportserveruserpassword"

RequestHeader set Authorization "Basic base64string"

</Location>

Substitute the italic strings above with the information specific to your environment.

mod_proxy_html is a third party module downloaded from http://www.apachelounge.com/download/win32/modules-2.2/mod_proxy_html-3.1.2-win32.zip - open the zip and dump the mod_proxy_html folder under Program Files (x86)\Apache Software Foundation\Apache2.2\modules. The necessary config is included in the above section. (It may be included in your version of Apache.)

The three SetEnv directives prevent hanging when proxying IIS pages over Apache – they essentially force the use of HTTP 1.0 instead of 1.1 between corvos and endor.

The string in the RequestHeader section can be produced with any base64 encoder – you can find some JavaScript ones online. Alternatively, log into the report server using suitable credentials and use a browser debugging tool to snoop your own request headers (for Firefox, Live HTTP Headers or Firebug’s Net panel can do this.)

Doing Stuff with Django

Pages

Sunday, 1 September 2013

Using Django with SQLServer reporting

Configuring Apache for Basic Authentication to the SQLServer Reporting Services Server

No comments:

Post a Comment