Today we faced an interesting issue, was the limitation technical or human or combination of both, not entirely sure about that. Thought it’s worth sharing as it definitely ties with the thought process which involves both the aspects. I am simplifying the scenario as well as abstracting the client details for obvious reasons.
The requirement goes something like this…
There are different users in the system who have different levels of access and play different roles. Some are responsible for entering the data and some of them can look through the entered data, edit them if required as well as draw reports out of them.
The technical stack consists of SharePoint as the base, InfoPath is used as the forms technology and the data from the forms finally lands into the SQL Server database in the backend. The InfoPath is connected to the external list which is using BCS in turn to get the data from the same SQL Server.
There is a reporting and an analytics need on the data present in the external list. The issue is that the external lists are not easily exportable into excel, so then what is the alternative?
The solution…is it?
Since it is tied into the SharePoint thing, your brains starts to spin and yell some fancy SharePoint terms and you go SharePoint WorkSpace, SharePoint Designer and even Report Builder 3.0. The challenge is that there isn’t anyway to consume that information using these tools directly unless you opt for a customized solution (involving code) and not a configurable one.
So you start to think harder and in the process start making things more complicated, it’s a “DEV TRAIT” there is a natural affinity towards complicated solutions …you also start looking into code based solution or maybe 3rd party solutions to make it happen.
Wait a min…need a break!!!
Now it sounds familiar isn’t it…there is a problem and then you start looking for a solution, a solution comes to your mind but has some limitations, you start looking for addressing that limitation next and in the process you get carried far far away from the actual problem that you were trying to solve…has happened to me multiple times, not sure about you?
I think this is exactly the time to take a break…and then comes the eureka moment!
Any external data items that are brought into SharePoint using BCS are not stored in SharePoint’s content database. Here is blurb from an MSDN article just in case you want to check on that.
“Notice that unlike a SharePoint list whose data is stored in the SharePoint content database, the data in an external list is stored only in the external system. External data is brought into the SharePoint list at run time when you navigate to the list.”
So there is a simple solution now to the reporting\analytics need; instead of going around the way and accessing the data from the SharePoint’s external list we can use the SQL Server database table\view directly and access it. Since the (committed)information always remains the same you will get the same results as the SharePoint list and you could build fancy report or do pivoting using excel etc. And it worked just fine for this situation.
More than the technical part of it, the intent of sharing this incidence was to highlight how we at times get trapped in our own thought process and force ourselves into a different direction than what we initially desired!