Using SSRS with Silverlight, HTML5, JavaScript, Flash, Google Charts and other third party products.

SSRS, Powerpivot, Powerview, Excel Services, Powerpoint Services, Sharepoint Dashboarding all offer variety of data visualizations. But most of them end up with a static graphic image on a web browser, which is neither interactive nor appealing enough to the end users. Many architects take the route of custom development or try seeking reusable assets (third party / in-house) that can be used to bring a wow-factor on the user interface. These report platforms are not packaged with those rich interactive graphical capabilities that can blend on a portal or by itself serve the high-end UI needs.

For example, the charts and graphs that are generated by SSRS report output, are in the form of static images. There is no way that user can click on a chart/graph and it would drill down to the next level of hierarchy. Also if I have two charts/graphs and I want to configure them in a way such that I click on one of them and the other shows context sensitive information related to the same. Often reporting systems require such graphical interactivity, but the present stack of reporting tools are not yet capable of presenting such information with user interactivity.

There are different options that can be taken to provide reporting using a rich UI, which are mentioned as below:

1) Develop UI using Silverlight applications (.xap)

2) Develop UI using Silverlight + .Net or plain .Net

3) Develop UI using HTML5 + JavaScript + Adobe Flash based graphics

4) Develop UI using online charting services like Google Charts

5) Develop UI using third party tools.

A sample architecture diagram is shown below where application layers makes calls to SSRS web services. Above mentioned options would fit in one or other layer and have its own advantages and limitations.

Below listed are different chart and graph options to generate interactive charts and graphs, and blend with your UI along with SSRS reports.

1) Highcharts

2) JS Charts

3) gRaphael JavaScript Library

4) amCharts : JavaScript / HTML5 Charts

5) RGraph JavaScript Chart and Graphs

6) FusionCharts : JavaScript, HTML5 and Flash based data visualizations

7) JPowered JavaScript graphing library

8) CHAP Links Library using Google Charts Visualization

9) TeeChart JavaScript and HTML5 charting library

10) jQuery Sparklines plugin

11) jqPlot : jQuery plotting plugin

12) Microsoft Silverlight Toolkit

13) Infragistics NetAdvantage Ultimate

14) Dojo Charting

15) CanvasXpress - JavaScript library based on HTML5 Canvas Tag

16) Flotr2- JavaScript library based on HTML5 Canvas Tag

Using Microsoft Office Project Server with MS BI ( SSIS, SSAS, and SSRS )

Microsoft Office Project Server (MSPS) is one of the healthiest source of data in the microsoft ecosystem. Many departments especially CIOs have the greatest potential and probability to make extensive use of the data contained in Project Server. Almost every organizations have different projects for which they carry out planning, tracking, monitoring, resource assignments and related activities. MS Project Server is a chef's knife for this purpose.

From a technical standpoint, the way MSPS stores data is very interesting. Like Sharepoint, it stores data internally into SQL Server. But unlike Sharepoint, it gives a very neat and clean mechanism to use to data it stores internally in the form of a database intended for reporting known as Reporting database and is operated using a service known as Report Data Service. Also it has a service called Cube Build service (CBS), which can be operated using a web based console known as Project Web App (PWA).

The Reporting database (RDB) is the staging area for generating reports and OLAP cubes. Data in the Reporting database is comprehensive and is updated nearly in real time. The tables and views are optimized for read-only report generation; for example, the RDB tables are denormalized to provide redundant data and reduce the number of relational tables. As data is updated in real time in RDB, in case if you are considering extracting data from it to some other data store, consider reading how data gets to the RDB and Report Data Service. Schema documentation of the reporting database as well as the OLAP cubes is available and  can be downloaded from Project 2010 Reference: Software Development Kit, in the documentation\schemas subdirectory.

Microsot Office Project Server 2010 Architecture Diagram can be seen below:



As apparent in the above diagram, MS Project Server is very well integrated with Sharepoint 2010. Hence using reporting related tools like Excel Services, Performancepoint Services and BI + Dashboarding capabilities in-built into Sharepoint, a rich reporting platform can be provided to end users from data contained into Project Server 2010.

From an MS BI perspective,

• SSIS can be used to extract data from reporting database and merge this data into a corporate warehouse
• SSAS can be used to source and enhance cubes and OLAP database exposed by project server
• SSRS can be used to generate reports on the top of OLTP reporting database and cubes contained in OLAP database exposed by Project Server.

I seriously wish that perhaps Sharepoint can expose such databases for reporting and analysis, as that makes it very easy to facilitate reporting and analysis of the content stored in sharepoint.

To understand more about Project Server, you should consider reading about Project Server Architecture and Project Server Programmability. Also consider reading more about how to configure reporting for Project Server 2010.

HTML5 Browser Compatibility for BI Solutions

Consumers of a BI solution are increasing and BI solution are increasingly becoming web-based. Technologies like Silverlight are not supported on platforms like iOS and browsers like Safari Mobile. HTML5 due to its capability to render rich media on mobile devices, is receiving more and more adoption day by day. Below mentioned are some nice references that can be handy for various purposes when you are playing with html5. Some of these purposes can be cross browser compatibility, local storage on devices, feature support across devices, testing your application compatibility for html5 and others.

1) Difference between HTML4 and HTML5

2) Download Free HTML5 Ebook

3) How to test cross browser compatibility

4) HTML5 certification exam 70-480: Programming in HTML5 with JavaScript and CSS3

5) HTML5 resources

6) New iPhone 5 and iOS 6 features for HTML 5 Developers

SSRS Report prototypes using Google Charts

Contemplating and conceptualizing design of any application starts with almost no tools at hand. MS Office - Powerpoint and Excel, or similar products are the tools that remain available with the design and development teams for developing application screen design prototypes. MS Visio is generally used to develop wireframes. In an application / report centric design, users preference remains viewing reports blended with the web application itself. As the need for a mature prototype develops, html - css - javascript based prototype starts getting developed.

Report prototypes are generally developed as excel spreadsheets or raw reports exported to excel, which are evidently totally isolated and hard to visualize how they would gel with the hosting environment. Google charts provides excellent variety of visualizations. The best part about this visualizations are:

• Visualizations are interactive, but they don't use Silverlight. Interactive term is used very loosely but its a very valuable term. SSRS reports are rendered as an image file which is not interactive. But these visualizations have one or other form of user interaction feature available along with tooltips.
• These visualizations are generated using HTML5 so they are cross-browser as well as mobile device compatible. Also visualizations are drawn using SVG or VML.
• Embedding these visualization and populating it with data is mere couple of html and javascript tags. Visualizations are exposed as google javascript libraries. Include those libraries in your page, create objects from the exposed object model and add data in the form of a very simple array. And your report visual is ready.
• Visualization such as treemap / intensity map, motion charts etc are also available which missing in SSRS.
• These visualizations are exposed in the form of classes, and they also have event listeners. This means you can bind user interaction on this visualizations with your server side code too.

One might also think why not use these visualizations in production environments ? The main reason I would have resistance against using these in production, is that firstly these are provisioned for free on google infrastructure. So you can't commit any SLA with confidence to end-clients regarding performance. Next google publishes a deprecation policy that supports backward compatibility for 3 years. This means that once any visualization is classified as deprecated, after 3 years that would disappear from google charts. This is not acceptable in any serious production environments. If I compare this with Microsoft policies, any deprecated or even discontinued products like Proclarity / PPS 2007 have a support policy for 10 years. But of course these products are not free.

So I suggest that its one of the best tools to use to create great report prototypes that are almost closest to the actual reports. Below is a sample code to create a pie-chart.

SSRS reports on iPhone, iPad, Android, Windows Mobile for Mobile UI

Viewing applications on mobile devices might sound like a small problem statement, but the actual solution requirement is much broader than just resolution adjustment. Some of the major challenges involved in the solution architecture are:

1) Single codebase for the model and controller layer, and using the same for creating different view layers for different devices and platforms.

2) Whether to create a webapp optimized for devices or whether to create a native application that calls webservices / displays web content.

3) HTML5 is supported in different capacities by different browsers, and most of the microsoft frameworks do not emit HTML5 by default. In fact features like local storage is not supported below IE8+. Also its quite heavier to use as the data exchange format for devices, compared to JSON.

4) Whichever framework is used, cross-browser compatibility is always an implicit / explicit business mandate.

5) Using same navigation design, for different sized devices like iPhone, iPad, Tablets and Desktops, would not be admired by users from a usability and user experience perspective.

6) REST based services are more faster and lighter to use compared to WCF based webservices. But WCF has got a very wide support, features and integration with .NET.

Below are some pointers that can be kept in mind while designing the solution / technology architecture:

1) HTML5 is supported by most modern browsers used on different devices. Creating a web application with HTML5, CSS3 and JavaScript is the most advisable step if you are completely inexperienced in mobile application development.

2) Web-based frameworks like jQueryMobile, Sencha, and Dojo can leverage the existing web based SDKs / codebase to build more sharper mobile applications in faster, easier and efficient manner. These frameworks have built-in libraries to use REST and JSON too for client-server communication. Even if web application is converted native application in the future, http/rest/json based communication protocol is supported by platforms like android, iOS and others.

3) To take your web application development framework to the next level, use frameworks like appMobi, Appcelerator, PhoneGap, ApplicationCraft and others to build native applications using JavaScript.

4) More about how to build iOS application from scratch can be read from here.

I have implemented architectures, where we create .NET user controls that makes programmatic calls to SSRS webservice for reports execution. The HTML output returned by SSRS report is collected and rendered in the control. These controls are hosted in .NET pages, which are hosted on Sharepoint. Finally when server leaves control, entire web content gets transmitted as HTML. By introducing HTML5 conversion wrappers at different layers depending upon the design of the solution, not only SSRS reports but any web application can be optimized for mobile devices.

There is lot more to consider like performance, navigation design, user experience, local data storage and others such points. Feel free to share your thoughts and experiences by commenting on this post.