Dynamic reporting (looking for advice on next steps)

[davetherave2]

So I wasn't sure how to title this as I am looking more to see if others have tried this and how they overcame the problem.

I'll set out the scenario first:

I'm in the process of moving our reporting platform from SSRS 2005 and a whole host of other "bespoke" solutions that I have inherited over time and although I looked at SSRS 2012 as a viable solution it just isn't flexible enough for our needs and the volume of different reports that potentially need to be generated.

The main aims of the solution were:

1) To make it fast (current solution is painfully slow due to a number of factors)
2) To make it work with a number of different data sources and aggregate result sets together
3) To make it self service so that it requires only updates and critical changes by the development team
4) Accessible to internal and external users with the variety of security/ authentication surrounding it.
5) Try to avoid having a billion and one reports that may only ever get used once or limited period of time but take hours, days or even weeks to build.
6) On the web and work on the multitude of devices available.


So I have completed a little prototype which is working really well with one report that I have built but although it serves a purpose as a first draft I just know that this is going to be an unwieldy beast to maintain when I start having to build more and more reports or make modifications to suit one set of users but not another set of users. Due to the level of filters, options, columns etc that are going to need to be required.

It also means any updates will need a site update and taken off line for a period of time to deploy and test changes to make sure they are working as expected.

So as I started working on one of our more complex reports where there is a lot of conditional filtering I had a bit of a brain wave in trying to build a kind of dynamic report builder that was more user friendly than the likes of using access, SSRS report design builder etc. These tools although excellent do require some level of understanding of basic database principles and require knowledge of how our data is structured.

So to avoid this I wanted to take the kind of UML/ story board and present the data in a pseudo sql view but give custom filters and describe the data changes. so for example:

Select 
 column 1 and column 2 and column 3 but not column 4
From 
 Main working report 
Where 
 Condition 1 is equal to {your selected condition}

Hopefully you get the idea of what I am trying to achieve.

Now I have the basics of this working in terms of doing the following:

1) Selecting a drop down with available data sources (think of these as tables of data)

1a) Column selection is going to be done afterwards (all columns are selected currently)

2) Select the appropriate filter you want to apply and then conditional operator that the filter can have eg Equals, Contains, Less Than etc.

3) This then shows the appropriate controls for the user to entre or select the conditional filter eg if it's a date filter then a date picker is selected, if it's a numeric then a numeric textbox is selected etc.


Now I am at the point where I need to potentially mix and match a number of filters together and get combinations of filters working together eg.

Select 
 column 1 and column 2 and column 3 but not column 4
From 
 Main working report 
Where 
 Condition 1 is equal to {your selected condition} 
 AND 
 Condition 2 is between {date value 1} and {date value 2} 
 OR
   (Condition 1 is less than {Your selected condition}  
      AND 
          Condition 3 is {true} )

Hopefully you have managed to get to this point (apologies for the war and peace like effort here)

So just wondering how best to achieve this in a nice easy solution for the users?

I have thought about a number of solutions but all seem to be a bit too technical or possibly a little bit beyond me at the moment (I have learnt so much in the past couple of days just getting to this point)

I'm trying to follow the KISS principle here but I have spent so much time looking at it that I feel I am over complicating things unnecessarily or maybe missing something obvious.

various options I have thought about include:

1) Selecting all the required filters and then ordering/combining them after they have been selected.

2) Select the filters as we go along and then asking if the new filter is part of a group of filters and then add the additional filters in that group together.

3) Provide some other solution of doing this.


So just wondering if others have done something similar or have thoughts about how to do this.

I'll try to post up some pictures tomorrow with what I am trying to achieve to make it a bit more visual for those interested in offering advice.


Again thanks in advance for any advice/ assistance anyone can provide with this.

 

[davetherave2]

What are you creating this in out of interest?

Your scenario sounds very similar to my own (especially the hundreds of reports that only get used once but take hours and days to create!) so I'd be interested to see what you come up with (we currently use SSRS 2008 R2 at the moment but have the same issues as you where basically all of the report requirements go through me!)

In answer to your request, before reading your whole post I'd come up with the idea of grouping the filters (option 2) - my idea was, that anything in a group would be linked with AND operators, and groups would be linked with OR operators.

I'm trying to get rid of the need for your users to select whether the link is AND or OR (As I think that's a pretty database-y thing to get your head around). My logic may be flawed, but I think the grouping idea should work for all scenarios...

Technologies being used are:

SQL 2012 for DB
MVC.Net 5 (C#)
Telerik's Kendo UI Professional tool set
Telerik's DataAccess as my ORM
Bootstrap 3.1 for general layout and css needs.

I'm running the ideas by a reporting group and so other developers at the moment and trying to see what is the easiest way to achieve my ultimate aim.

I see now why these sort of products cost lots of money.

 

[davetherave2]

Hi there.

Yes I have got a lot further with this.

I have made some great strides over the last day or so.


Initial Load up of the screen




A Report is selected and then the list of available filters for that report are listed.



A Report and filter is selected prompting the user to decide what condition will be applied.



A filter using a couple of kendo date pickers (Also shows the sql in a more user friendly manner underneath)



A filter using a kendo combo box (Also shows the sql in a more user friendly manner underneath)




A filter using a numeric text box (Also shows the sql in a more user friendly manner underneath)



A filter using a kendo multi select (Also shows the sql in a more user friendly manner underneath)






So I am still at the prototyping stage but I have come with a system that hopefully forms the basis of being fairly flexible but allows our non-techie users to build up their own reports.

I am just working on the grouping of filters so that multiple conditions can be included and evaluated together. Then once I have this working from the front end it's the fun putting this altogether and getting the dynamic sql built under the hood.


so the idea is that the system works on groups of filters.

so

 (Condition 1 = x) 
AND 
(Condition 1 != Y AND Condition 2 = 3) 
OR 
(Condition 3 = true)

I Understand I could go even deeper than this but that is going to a level where I think it is better for the developer to build the query rather than the user.

I expect many users to have simple 1 filtered reports but at least this way I can build something that is slightly more a akin to what they are used to when they filter excel spread sheets.

To keep track of the filters I am using Kendo's MVVM features to keep everything on the client and group everything together then the idea will be to push this back to the server to save.

Then the fun begins getting this to work and also provide an editing screen to alter these once set.

 

[davetherave2]

Have you thought about using the report builder 3.0 tool? Users may need some training with it and creating the data models may be a pig but it does everything you've asked for

Yes I did consider it. I did trial the report builder with SSRS 2005 and users just found it too difficult to use. Although a couple are familiar with using access even that was a bit hit and miss.

The whole point of this new reporting platform is to build reports in a more bespoke way and provide nice visualizations of data. Such as map views, graphs, grids etc.

There will be occasions where I need to build specific reports for the users but that is something that will only need to be in the exception rather than the rule.

 

[davetherave2]

Right so you have this in a database.

Can you not setup default reports for each table - to pull data out, basically a Select a FROM b WHERE c.... and give users a range of options for a/b/c?

Or does your system need to output data in a specified format? I guess in that case you would need to start by grouping reports in terms of similarity. Then you give users an option when running the report - e.g a Purchase and sales report between x dates, excluding y data etc.

When you compile reports by similarity - is there any way you could reach out to alter the required format - to make it easier to develop? You can then take that as a basis to prioritise the design, but the first part should always be to just get all the data out, so something could be done in the interim, even if it is just an excel EUC tool.

Currently I have a master extract which works in this initial stage but our reporting needs can change wildly from one team to the next and also from one client to the next.

I am actually making some progress and now have the grouping of filters working.
So I am now at the stage of saving the settings and then making sure this works.

The idea is to build the "cubes" (I know using the term cubes incorrectly) of data for this process and then the user can build their own reports and then share them with other users if they want too.

Then I can focus on building more "bespoke report" solutions that require a greater level of support and skill to build.

In a way this is challenging my skill set to the max and I think I have learnt more in the past month than I have this entire year.

I'll post up some images when I have it to a more complete state.

 

[davetherave2]

One of our current reporting solutions revolves around an Access front end with sql server backend. For the most part this works and the business analyst person has managed to use it fairly successfully. The problem we have with it is down to using massive views or something out of the norm. Unfortunately the person that wrote this left some time ago and when you look at some of the queries it is like opening up Pandora's Box. Also the slight language differences between T-SQL and Access SQL always catch me out.

The building up of queries for me will be done in dynamic linq but if it requires a more complex effort then I will build the stored procedure up behind the scenes and take the filters in. But as I said this hopefully will be the exception rather than the rule.

I guess once I have this first version finished it is then time to look at the next stage of doing grouping, joining etc. That I think could get very complicated and will require a lot of thought.

 

[davetherave2]

So have been getting this closer and closer to completion and thought I would share the updates.

Initial Screen



Adding a filter successfully


Invalid or incomplete filter validation


Adding a new group of filters


saved a report with filters


So I think the UI for this is turning out quite well at the moment.

So I need to sort out the column selection and trying to think how best to do this. my options are:

1) drag and drop list
2) checkbox list
3) multiselect list


I'm thinking checkbox list may be the easiest to implement but I think the drag and drop gives me greater flexibility in getting the columns in the order the user wants.

I have really enjoyed this project and it has really pushed my knowledge to the limit and I don't think I have used/created so much JavaScript in my life

 

[davetherave2]

So have pretty much got the building side of things done now so a couple of updates for those still interested:


Reorganised the layout due to adding column selection now.




Now with the column layout (this is a sortable, drag-drop list)




Showing drag drop of columns for reordering in action





So after thinking about this and deciding that none of my original design ideas really worked I have kind of combined a checkbox list with a drag and drop list to give a simple way to enable, disable columns and reorder them.

This took up most of the day to get it working and saving back to the database correctly but I am really pleased with this first draft.

All I have to do now is sort out the pseudo sql section to show the fields correctly as it still says all available fields.

I hope this serves as a nice springboard for others looking to do something similar.

Any comments, suggested will be greatly received.

 

[davetherave2]

So another quick update.

Well after a bit of playing and speaking to the very nice people at Telerik I have managed to get a fully customised grid of data to come up and using my own custom built excel exporter code (built over the top of NPOI's) I have this also extracting to excel as well.

Now just building the admin interfaces for setting up the report sources, columns, filters, controls etc. but this should be simple compared to this part of the system.

 

[davetherave2]

I'm quite interested in how you manage the dynamic query in the background; care to post some code?

I will see what I can do with this as it is fairly bespoke to my needs and the way I have built the database behind it. But the principles could probably be applied in the same way for others looking at a similar solution.

 

[davetherave2]

I'm quite interested in how you manage the dynamic query in the background; care to post some code?

Well as I said the code is fairly bespoke and not really available for public consumption at the moment. But I will give you a kind of run down of how this works.

First you need to understand the structure of how I tackled this from a concept approach:


Take for example the following sql
eg

SELECT
 column1 
 column2, 
 column3, 
 columnN+1
FROM 
 MyDataSource 
WHERE 
 Column1 = SomeValue

So I have essentially split this out into three layers for building the reports:

1) Data Source (Table or View)
2) Columns (With aliases if required) this also understands the data type expected eg string, datetime, int etc.
3) Conditions (filters as I have called them)

So when a user creates their reports we know what columns should be picked, the datasource and then the conditions that should be applied and how they should be combined. The columns and data source are simple select and insert statements nothing too fancy their but the where clause is where all the magic happens.

so if we take a simple where clause like:

Column1 = Value1 AND Column2 IN ('Value2','Value3')

We know the following:

1) We have one group
2) We have two different conditions to filter on
3) They should be combined by an AND clause

so in the database I save the user choices based on the following:

1) The Group of filters with any adding clauses (will go over this in the next statement)
2) The filters and the order they have been added with any adding clauses


So the above clause is split like this:

Group 1 (No adding clause)
Filter 1
Column1 = Value1 (Order 1 and Adding Clause is AND)
Filter 2
Column2 IN ('Value2','Value3') (Order 2 and No adding clause)


So this way I know when recombining the where clause back together I know what groups we have and the order of the conditions to make sure they are combined in the right order so we don't have a scenario like:

Column2 IN ('Value2','Value3') Column1 = Value1 AND

Obviously this will throw up a sql error.


So if we had a multi group condition like:

( Column1 = Value1 AND Column2 > Value2) 
OR 
(Column1 = Value3 AND Column3 BETWEEN Value4 AND Value5)

So we now have 2 groups which are split like so:


Group 1 (with OR adding clause) (Order 1)
Filter 1 with AND Clause (Order 1)
Column1 = Value1
Filter 2 with no adding clause (Order 2)


Group 2 with no adding clause (Order 2)
Filter 1 with AND Clause (Order 1)
Column1 = Value3
Filter 2 with no adding clause (Order 2)
Column3 BETWEEN Value4 AND Value5


So the filter groups are relatively simple in terms of data storage:

They consist of a couple of columns

ID UniqueIdentifier PK
UserReportID UniqueIdentifier FK (reference to created user report) 
AddingClause NVARCHAR(20) 
Sequence INT

Then the filters themselves are stored like so:

ID UniqueIdentifier PK 
FilterGroupID UniqueIdentifier FK (reference to the filter group) 
ColumnID UniqueIdentifier FK (reference to the column we are filtering on) 
FilterCondition NVARCHAR(20) 
ValueField1 NVARCHAR(MAX)
ValueField2 NVARCHAR(MAX) 
AddingClause NVARCHAR(20) 
Sequence INT

In both scenario's if the condition is the last item and has no adding clause then an empty string is stored to indicate that this is an EMPTY operation.


As I don't know if the filter values are int, strings, guids etc then the safest thing to do is store them as strings and then because I know what the column data type is I can cast it in c# to the correct type.

If I have a contains clause then the list of items is stored as a comma separated list in valuefield1 but if I have a between clause then I will save the values in field1 and field2, all other options are stored in valuefield1.


So now we have the data stored within the system it is simple to then pull this out, put it in the correct order and then run through some logic to safe cast all the data types and build the sql statement together.

so it is literally as simple to do something like this in c#

string sqlStatement = String.Format("Select {0} FROM {1} WHERE {2}" ,columns, dataSource, whereClause)

Obviously there is a bit more to it but I essentially output the elements into distinct string elements and add them together.


Obviously I have some code that gets me the various bits and builds up the sql for me nicely. This is then executed through my ORM and returns me back a DataTable with the information which then bound to the resulting grid I have. Although I do feel I should put some additional safety code in for this to prevent sql injection (unlikely as the free type elements are checked before saving)

Although I am not a fan of doing sql in this way it feels wrong, it is the best way of doing it rather than trying to do the dynamic sql on the server as this tends to be difficult to debug. (Having had to manage some hefty beasts in the past)

The nice thing is that I am able to store the sql statement in it's fully constructed format and then I can run it later if someone reports any issues (eg they get some expected results and want to query why data they thought should appear in their results isn't present)

Also this gives me an idea of what sort of reports are being built and if any reports should be generically created and accessible to all users.

I hope that answers your question adequately.

 

[davetherave2]

Nothing to add, but this is looking really good!

Thanks a lot.

I feel over the past couple of months my development skills have hit a new level recently and when I look back at some of my earlier pieces (say no older than 6 months ago) I see where I struggled and tried to apply my limited knowledge with just enough success to get it working but it was horribly over-engineered and complex code.


I guess as developers we all feel like that when looking at our own code some months after it has been in production. It still amazes me even now when I see some of my early learning code still in production after 7 years of life and I think to myself "I hope that doesn't break as its going to be a nightmare to fix"

 

[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.