Reporting v. Editing

Comments on my recent article A Report to display and edit User Rights were mainly positive though there was a strong, sceptical contingent. That scepticism was based on the widely accepted boundary between reports, which simply display data, and applications, which are geared towards editing data.

History

So, why are reports and applications traditionally separate? I think the reasons are twofold:

- software: in the bad old days, software had quite narrowly-defined capabilities. One package would display the contents of a record on the screen and provide a means of amending it; another would aggregate the data over a number of records, format and paginate it for printing. Often an application would have to launch another piece of software in order to appear to be running a report.

- work roles: in most organisations hierarchies, there are the users who simply input and check the data and others such as managers who want to monitor how this month compares with last month or how different regions are doing year on year. This division of labour amongst users mimics the division of functionality between software packages.

Perhaps the big break with the past started when a report no longer simply interrogated a set of tables and views, but in requesting a resultset from the database it was also allowed to run code instead or as well. In SQL Server, this is achieved by basing a report on a stored procedure (or custom code).

POTENTIAL

What is the potential of SSRS? Lets look at what it essentially does:

• it takes some user input
• waits for you to click a button
• goes away and runs some code
• returns something on the screen which signals that it has finished.

It also allows the same sequence of events from within the context of the date returned, i.e. by drill-down or drill-through. This is really a very general sequence of events and parallels the way most users engage with a database system to get things done.

MOTIVATION

Given the possibility then, what are the positive reasons for breaking down this historic barrier?:

- the user experience: in running a report to the screen, a user may well have all the information they need to decide what changes to make to the underlying data. Why then should they have to switch from the reporting package, open up an application (which may have a completely different look and feel), enter the parameters all over again to obtain the record which they can then update?

- application development: in my workplace, we're all striving to give everything a web front-end, so remote users can have a similar on-line experience to those in head office. Years of time and effort have been invested in producing traditional Windows applications. Then along came SS Reporting Services and suddenly, the reporting side of things had already reached the finishing post. So why not use it to provide some of the easier application functionality?

- security: there is already SQL server security available on tables, fields and stored procedures as well as that provided by Report Manager itself. I'm not an expert on web security, but I wonder how less secure this arrangement is than using an actual ASP.NET application? The spectre of SQL injection in parametrised queries remains whatever the front end.

SCOPE

I think the strength of RS comes when maintaining what might be called user-related data. (RS already notes down when a report has been run, by whom and when). Let me give an example of a piece of work I'm currently embarking on. This involves replacing a Windows application which allows users to select customer records from our customer database according to a wide range of criteria. This quite naturally carries over to SSRS, especially with the multi-value parameters which arrived with SS2005. But the next step is then to give the user a sort of MySelections library which then involves storing:

• the SQL generated
• the parameters used
• some user-supplied descriptive text to allow later retrieval
• the database ids of the records selected

Previously stored selections can then be re-run on other occasions, i,e. refreshed. Stored selections can also be used to return different related data for each set of customer ids selected, e.g. one report to show sales, another to show customer feedback for the same customers. Choosing a selection could even use the stored details to populate the parameter boxes of a report so the user can review and amend them for new situations.

OBSTACLES

Reporting Services still has a couple of areas which betray its limited aspirations:

• the View Report button. This needs to have a more generic label, I.e. Submit
• there needs to be a little more flexibility with the placing of parameters, rather than the two default columns, read left to right. However too much flexibility could bring back all the tedium of where to place boxes on the screen and make sure they're lined up

CONCLUSION

Perhaps we should let the users decide. Given the reception SSRS has had so far within our organisation, any expansion of its capabilities will only be welcomed. Our far-flung offices in the more remote parts of the world have trouble getting good bandwidth and so far only Report Manager (and some fledgling ASP.NET apps) are giving that consistent user experience across the organisation.