How to create stunning dashboards with SSRS (SQL Server Reporting Services) and basic html

It is probably one of the most frustrating things to search for dashboard software. Sure there are several applications that will allow you to develop the dashboard, but you will need to fork out several thousands for the server software to host dashboards privately.

In my case, our company had a server totally of the grid. No internet access was allowed and we needed dashboards. The server contained huge amounts of information and security was a major issue. This was the reason we could not allow our dashboards to be hosted by other companies on the web.

I found an interesting link that showed the new version of SSRS will have some dashboard capability. You can see for yourself at What's new in SSRS 2016. You will need some basic experience with the Microsoft BI Tools like Power BI (a free desktop dashboard and data mining tool) its simple interface will be familiar to Excel users.

We have already installed SQL server 2014 and was developing reports in SQL Server Data tools 2013, so we had to look for some kind of other solution for the dashboards we need. I know that SSRS has an independent rendering service and It can take a report and render it to an "HTML document" when you request the report using a report link or URL.

My immediate reaction was to try and design a report that looks like a dashboard and then request the report from the server. Well, this was a big mistake, because the more items you put on a report, the more difficult it becomes to place items in fixed positions. The report will look great in the development stage, but once the data fills the report, the position of the items on the report moves dynamically based on the content that fills the report.

There has to be a better way of rendering the report where the positions can stay where I wanted them to be. Eventually it dawned on me, maybe I can upload an "HTML Document" to the SSRS report manager and access this document in the same way as a report, using the report server to render the HTML document.

The Test: 

I created a notepad document with a bit of html code:

Then I saved it as a html document "Test.html"

I then opened my report manager and uploaded the new "Test.html" file. 

I know you can upload any type of file into the report manager, but not all the files will actually be accessed. My test to this point has shown nothing new, but can the report server that renders the reports, host an html file without configuring a web server like IIS?

To test it, I first clicked on the "Test.html" file to render like a report, and what do you know. the report server does render the HTML code.

 
So lets start building our first Item for the dashboard to test and see if it renders correctly.

I quickly created a new HTML document in Visual Studio and changed the back color of the main body to #080808 and removed the margin.

I then added a Div to contain the main contents, and placed an html table to host each dashboard item

I then created a table row (<tr>) and in my row I placed a div. Due to previous dashboards I have created I know my screen size and if I want 4 items in my first row they should be about 465px wide. The height will be governed by the <div> that holds the report.

I thought that it will look good to have 3 sections for each dashboard control

• Header
• Main control or the Container for the report
• Footer

So to do this, I added 3 div's as to contain all the sections of the dashboard control

We will now look at each div from here:

1. Header of the dashboard Item

I added a bit of style to the header to test the limits of the SSRS rendering, so I added a bit of gradient background to the header

2. Main Container to host the report 

In this section we want to host the actual report to do so I will use an IFrame control to show the independent content. The use of the IFrame to show the report allows me to have several reports that can refresh its content independently from other hosted controls on the same dashboard.

Due to me not having any reports ready I left the source (src="") empty for now. I will add the link once I have a report put in the container.

3. Footer for Information 

Similarly to the main container I wanted to display information from a report. Again, I used an IFrame to handle the hosting of the report

Please pay attention the the height differences between the main container and the footer when it comes to the IFrame sizes. these will be important to remember when we develop our reports.

Once I did these sections I saved my test dashboard and uploaded the new "TestDashboard.html" to myreport manager and ran the document as a report.

Even though this does not look too bad the HTML code did not render as I expected, so I linked to the report via a web browser using a URL to collect the report, and it looked much better..

In the browser you can see the gradient effect in the header, the IFrame's has taken the style settings and the container positioned itself correctly. So All we need to do now is get some reports on this control.

I added a new report to a new project and set the size of the report and back color to the same color as the container div

I then connected to my data set and added a bar chart that I formatted 

Important! - If you want your report to update every few seconds or minutes, the you can set the Auto Refresh property on the Report to collect any new information from the data source. In the image below I set some of my reports to auto refres every 15 seconds.

Now I need to upload this report to the same folder in my Report manager where I hosted the HTML File. So i configured my settings in Visual Studio to deploy my new report

I quickly set up my data source in the report builder ant tested the report. Our next step is a bit tedious, because we will have several controls on the dashboard, as we develop them we will need to update the (src="") source for the IFrames for each control and then Upload the updated HTML file.

I know my report is named IssueStatus and to call the report from a URL I want to hide the toolbar's. To do so I add a key to the end of the URL (&rc:Toolbar=False) the full URL looks like this

http://localhost/ReportServer?/SSRS Dashboards/IssueStatus&rc:Toolbar=False

as you can see the report loads by itself without a toolbar. Now I will copy this link into the source for the IFrame

Now I save and upload my updated HTML file back into the report manager, and run the URL for the report from a browser:

Hey this looks good, and it proved the concept that you can build a dashboard by using an HTML frame to host several small reports. 

I worked on several more sections and added their links to each section of the dashboard. The final result looks like this:

Yes the image is a bit small, but you can see by adding the HTML shell to host every report in its own container, the issue that causes the items to dynamically move has been removed as each control is a report by itself. Subsequently Each part of the dashboard also refreshes at different times.