Problems with Report Builder
After using the Report builder for some time now, I think I have found a couple of problems that I hope can be addressed sooner rather than later.
Number 1 No support for Stored Procedures: This one shocked me to be honest, stored procedures have been at the heart of Microsoft's methodology for databases for so long, this almost smacks of them not expecting enterprise style clients to use this tool (I'm sure this isn't the case) Essentially my major gripe is that there is no way to include a stored procedure and prompt the user for the values to run it, as a part of the model. This means that only tables or views can be a part of the model and as such I cannot fulfill the requirements of the reporting system, quite a number of the pieces of information we present to clients are calculated fields and as such we need a stored procedure to calculate the values......
Number 2 One word... Performance: Performance is always an issue in reporting systems and I'm certain this is one area that will see plenty of attention from the development team in the coming months, essentially I think performance will be a problem... the means via which we can control the user's construction of a report leaves plenty of room for them to build monster queries that will bring down a server, the alternative being limiting the models to the point whereby they are nearly unuseable or forcing filters on table entities.... either way I think this is one area I think needs attention...
I think report builder is a wonderful product, my criticisms of it are minimal. I only hope microsoft commits to turning it into a great product!!!
Oracle RLS and Report Builder
First issue I am looking at now that I am proofing the Oracle report builder is how it is going to integrate with Oracle RLS.
Within out reporting system row level security is somewhat important as it stops clients from seeing data that doesn't belong to them. We managed to get around the datasource in RS for normal reports by using a "prompt" driven datasource..... but Report builder doesn't seem to like this kind of datasource and states that it needs one with stored credentials.
When we first hit the issue with the need to store credentials we tossed around the idea of writing a Custom data processing extension to make the connection to Oracle. Looks like this may now be my only choice, so I will start looking at how I can use this to implement a connection with Row level security intact
How To: SSRS Requests
I thought a good idea would be to begin posting some How To's up here, at the moment I am working on a how to for a Custom Data Extension. This should be ready soon but if anyone is reading this and has a request for a How To regarding an SSRS topic, please comment
Rant # 2 SSRS has no Page Report Level variables
I didn't intend this blog to end up as a repository for my various rants about the technology, somehow however that is what my last post (and now this one) will have ended up being.........oh wellEssentially I think microsoft screwed up. But let me elaborate.I think one thing which is almost certainly necessary to accomplish some tasks within a report is to have the ability to have variables. Ones you can declare yourself and access at various levels within the report. To my knowledge (and please correct me if I am wrong) this isn't possible in SSRS.Some cool stuff is there I haven't seen before like the ability to call C# code modules and this has been drastically improved. BUT these are only calls to static methods, there is no way to say invoke a class and use this to carry information throughout the report. But that is a nice to have.... one ESSENTIAL is to have the ability to store and pass information throughout the reports, I just don't understand the need to place boundaries through which you can't pass information, namely the Page Header -> Details -> Page footer, even within the detail section itself......this flaw has caused us to jump through hoops on a number of occasions, pushing many functions into the stored procedure rather than in the report where it belongs.....
Problems with Page Headers
Allrighty, this is my first in a line of entries relating back to problems we have experienced using page headers and the workarounds we have implemented to get our reports running. For any who are unfamiliar with what I am talking about please see pic:
So, here's the problem. Essentially for whatever reason you aren't able to place fields returned in the dataset into this Page Header area...... WHY? I don't know but it sucks big time, in every other reporting tool I have used this isn't an issue and most often in the reports I have been working on it is necessary to place data as it is returned from the database into this header information.The Workaround
ok so here comes the ugly part, to workaround this what we ended up doing was to add an additional detail line to the report, make the cells (this is important) invisible and shrink the row size down to really small. Then reference the textboxes that are in the detail line from the header. This works....
Note: you can't make the entire row invisible as SSRS will deem that there is no data in it and you fields in the heading will show up blank..... stupid huh???
Report Builder for Oracle
One of the requirements of the reporting system we are building is that it will support Ad Hoc reports. Anyone who has been involved with developing reports for any period of time will realise that this is somewhat of a holy grail for reporting systems..... many have tried only to find a castle full of frenchman...... you silly english pig dogs....
Well when I first got my hands on the report builder that came in SSRS 2005 I was quite encouraged. Here finally I thought is the answer, only to have my hopes dashed to pieces as it did not support connectivity to Oracle datasources... (I even went so far as to setup a DTS package to migrate the data into a SQL server database to see how it worked.....for some reason management wouldn't agree to this..... gee I wonder why)
Anyhow I am here to spread the good word, I have just begun looking at the tech preview for SSRS report builder's Oracle support and so far so good...... now I just have to figure out how tom implement this in a way that won't expose our servers to the entire world :)
Introduction
Ok I realise this is bad form, but I have to begin this blog with two apologies.
Firstly let me apologise for the appauling title of this blog.... I realise its a really bad Star Trek pun, but it was Microsoft that changed the acronym from MSRS to SSRS so I just couldn't resist
Secondly Let me aplogise upfront, as I suck royally at keeping journals / diaries / blogs and as such would be surprised if this lasts beyond a week......
Ok enough of the apologies, let me introduce myself. My name is Greg Thomson, I am a 26 year old Australian working in the US. My current job has me leading a project involving SSRS and as such I thought it wouldn't hurt to share my experiences.
