Customized complex reports from Mssql ---- Is it possible in Sharepoint?

Question

Hi,

Here is what I need:

- Pulling data directly from Mssql server. (BDC can)
- Data is shown in a sortable/filterable grid. (BDC can)
- Export data in pdf or excel format (BDC cannot but there might be some work around)
- Let the user directly define queries to generate customized report. (BDC cannot as far as I know. every time a new report is generated, a new definition xml file has to be imported.)

The last one is really bothering me. By now I haven't found a good way to let normal user to create their own report based on the Business Data Catalog. Can anyone give me some suggestions?

I'm also wondering, with more and more big companies migrating their data to sharepoint to report from Oracle/Mssql, how do they use sharepoint to show complex data/reports? +
I know in a lot Business Intelligent software you can let normal user to generate ad-hoc reports based on queries or selecting fields in different database. How is this possible in sharepoint? If it's not, how are other big enterprises using sharepoint to do it? I'm a bit confused here.

By the way, I have also implemented some webpart from c# programming. If I use webpart to generate reports, then I can give the web part properties to let the user to define the queries. However, the data results cannot be displayed in a nice sortable and filterable generic data grid.



Thanks a lot!

Answer 1

Hi sspanzer

I would try BDC Object Model

- Create a custom webpart that has interactive UI to capture user inputs on how the report is to be rendered.
- Read those inputs and form your own sql query or pass those indications by some means to a stored proc which then takes care of it.
- Parse the result and render.

You can refer this to know more about BDC Object Model: http://sharepointmagazine.net/technical/administration/everything-you-need-to-know-about-bdc-part-7-of-8

Hope this helps.

Cheers

Answer 2

Hi

The BDC is a service to surface and render data stored in database or provided by a Web Service.

The data will be most likely render in a tabular form using an OOB BDC webpart or Custom WebPart (SPGridview, etc.).

For tabular data and exporting capabilities you might want to use Excel Services and Pivot Tables, or just enabled SSRS or any Web-enabled BI solutions you have running in your emterpise and render then though an Iframe.

SSRS provides a couple of webparts when it is not configured to run in integrated mode.