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.
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 thought that it will look good to have 3 sections for each dashboard control
- Header
- Main control or the Container for the report
- Footer
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.
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
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.
