Dynamic reporting (looking for advice on next steps)

[Deanje]

Big post

That's interesting, thanks.

I see you've pre-empted my question regarding SQL injection; I was curious to see how you're protecting all of this.

I'm looking to do something similar myself, but it's a bit simpler in that the datasource is static i.e. I'm looking to dynamically generate a set of criteria based on what the user's looking for against a single view.
However, you've got a nice corporate shield, I've got a bunch of vagrants I have no doubt would try to be hilarious by filtering by ;DROP TABLE [master].[sys].[sysdatabases];.

 

[davetherave2]

In a way I am giving my users static views on data as they choose a data source initially but I am limiting what they can enter and if it is textual information then this comes a predefined list of options that are stored in the database.

I have created a free text based control but this is disabled as I don't think this is suitable at the moment as I am not giving them the option to do wildcard searching eg LIKE. If they want to do this then they can do it via the grid controls which don't touch the database layer.

But I still may change the way the final where clause is constructed and make it parameterized just as an added safety measure.

But this is just another safety layer I am sticking in to ensure the security of the system can not be compromised.

 

[davetherave2]

Well I have been making some improvements (well what I think are improvements) to make the system a little bit cleaner and function a little nicer including things like:

1) Dynamically loaded tooltips explaining what report sources are and what filters do.
2) Tidied up the filter addition buttons now to make it a lot cleaner and have it in a dropdown list like so:

From This:



To This:


3) The best bit I think, the ability to share reports with other users that are registered users within the system.

My user report screen:



So from this screen the user has the ability to create a report, share it with users, select the report, export to excel and edit (feature still being worked on)

Then the users have another screen showing reports they have access too and they have the ability to either remove their access to the report, select it or export it. They can not share it with other users or edit it (may be a feature I build later if our user base requires it)


On top of this I have built a complete admin back end so that I can manage and maintain the system and add, remove, edit report sources, filters, controls and column options.

It's fairly simple and if anyone is interested I can post some pics of that as well.

This project really has shown me how much the web has advanced in the last couple of years and also shows the value of having a flexible and useful MI/BI system. I really have enjoyed this project (probably far too much) and I am only at the start of what I want to do.