Tag Archive: SSRS


I’ve seen a number of projects posted using the Amazon Kindle as a “standalone” wireless display and decided it was time to join the party. I picked up a secondhand kindle from eBay for £20 and got cracking.

First step was rooting/jailbreaking the kindle. Pretty easy using the instructions from http://www.mobileread.com/forums/showthread.php?t=88004

I then enabled SSH over WiFi so I didn’t have to mess around booting into diagnostics mode and plugging in to USB every time I wanted to make changes.

Lees-Air:~ leetickett$ ssh root@192.168.0.229

Welcome to Kindle!

#################################################
#  N O T I C E  *  N O T I C E  *  N O T I C E  # 
#################################################
Rootfs is mounted read-only. Invoke mntroot rw to
switch back to a writable rootfs.
#################################################
[root@kindle root]#

The most helpful project to get me started was https://github.com/antimega/kindleframe it uses PhantomJS and pngcrush on a linux server to prepare the image. Handily I already have a Centos server I could easily use for this. I tweaked Chris’ script a little- starting with the shell script called by cron;

#!/bin/sh
cd /var/www/html
phantomjs update.js
convert morning.png -resize 80% screenshot.png
convert morning.png -rotate 90 screenshot.png
pngcrush -c 0 -nofilecheck screenshot.png screenshot_crush.png

Then update.js. I had to tackle a few issues;

  • Cycling between different pages (achieved by dividing the current minute by 5 and choosing a URL based on the remainder)
  • My SSRS report takes 15 seconds to run (using setTimeout(takeScreenShot, 20000); ensures the report has loaded before the screenshot is taken)
  • My SSRS report requires authentication (simply adding page.settings.userName = ‘x’; and page.settings.password = ‘y’;)
var page = require('webpage').create();
var d = new Date();
var n = d.getMinutes();
var url = '';
var px_top = 120;
var px_left = 10;
if (n%5==0)
{
 url = 'http://pvoutput.org/intraday.jsp?id=26603&sid=24317';
}
else if (n%5==2)
{
 url = 'http://pvoutput.org/list.jsp?id=26603&sid=24317';
}
else
{
 url = 'http://192.168.0.188/ReportServer/Pages/ReportViewer.aspx?%2fTEL+Wall+Thing%2fWallDash&rs:Command=Render';
 page.viewportSize = {
  width: 1920,
  height: 1080
 };
 page.settings.userName = 'xxx';
 page.settings.password = 'yyy';
 px_top = 45;
 px_left = 470;
}
page.open(url, function () {
	page.clipRect = { top: px_top, left: px_left, width: 1000, height: 750 }; 
	page.evaluate(function() {
    	document.body.bgColor = 'white';
	});
        var elapsed = 0;
	setTimeout(takeScreenShot, 20000);
	function takeScreenShot() {
            	page.render('screenshot.png');
            	phantom.exit();
    	}
});

Then added the script to cron to run every minute and voila.

Once SSH’ed onto the kindle you’ll need to;

mntroot rw

Then proceed to create the script to grab the image and display it (I also added a few lines to display the battery %age in the bottom corner);

#!/bin/sh
cd /var/tmp/root
rm screenshot_crush.png
eips -c
eips -c
wget http://user:pass@tickett.dyndns.com:777/html/screenshot_crush.png
eips -g screenshot_crush.png
local batt=$(cat /sys/devices/system/yoshi_battery/yoshi_battery0/battery_capacity)
batt=${batt%%\%*}
eips 1 39 "$batt"

I’ve setup port forwarding on my firewall to allow traffic through on an obscure port, added a .htpasswd and you’ll notice i’m using an external domain name in my script (i’ve substituted something fictitious)- this allows me to use the display anywhere with an internet connection (literally anywhere as I can tether to my mobile phone).

And here it is;

IMAG1435

IMAG1433

IMAG1432

Here’s how the SSRS report looks in it’s full glory (you can see the area i’ve chosen to display on the kindle);

wall_dash

I’ve chosen not to kill powerd and framework for now, so the kindle will still go to sleep. The next step- if I ever get round to it, will be trying to save power by sleeping or disabling WiFi between updates. I’ve found a little bit of information on how this might be possible (http://www.mobileread.com/forums/showthread.php?t=221497) but still some way off.

I will no doubt add some additional screens (weather forecast, router traffic, network monitoring etc), and it would be nice to have a way of scrolling between images using one of the kindle keys.

Wouldn’t it be great if you could buy something like this off the shelf? Why should we have to hack an existing device? I stumbled across an interesting kickstarter campaign doing exactly this, unfortunately it’s a tiny 2″ screen (https://www.kickstarter.com/projects/914874115/displio-wifi-display-that-tracks-whats-important-t)- maybe one day!

I’m also in the market for a bigger device (i’d love to replace the 21″ monitor which sits on my wall) but for a portable option 9″ or 10″ would probably suffice. A real shame the Kindle DX was discontinued, that would have been perfect (although could do with losing the keyboard). Also looks like a few similar devices; Asus eeeReader DR900, Onyx BOOX M96 (they seem quite expensive and I haven’t looked into whether they have been (or even need to be) rooted, or how easily something similar could be achieved on Android).

Other similar projects worth a look;

http://www.shatteredhaven.com/2012/11/1347365-kindle-weather-display.html
https://randomcoderdude.wordpress.com/2015/01/31/using-the-kindle-as-an-information-display
https://github.com/ufuchs/weather-on-kindle4nt
http://hackaday.com/2015/02/16/wi-fi-connected-e-ink-display/

Advertisements

I have just setup an SSRS report for my helpdesk to show open calls, unread e-mails, some KPIs and graphs. I bought a cheap monitor, wall mounted and attached a raspberry pi.

I started with the latest Raspbian image and spent some time tweaking it to boot into x and launch chromium with the homepage set to the URL of my report. Unfortunately it was pretty flakey and always prompted for username/password so I had to VNC in every morning to authenticate.

Yesterday morning chromium decided it no longer wanted to load the report. I tried running;

sudo apt-get update
sudo apt-get upgrade
sudo raspi-update

And rebooted… Or at least I tried to but it would no longer boot- hanging on a message along the lines of;

mmcblk0: error -110

Rather than start again from scratch I decided someone else must have already tackled this “kiosk” style scenario and went on the hunt. Enter Raspberry Webkiosk; http://www.binaryemotions.com/raspberry-webkiosk/ (after a quick trial I paid the €9.90 “donation” to get the full version).

The image took care of the operating system, booting straight into full screen chromium with the home page set to the report URL but I was still stuck with the authentication prompt.

I hoped I could simply enable Anonymous Authentication in IIS, but the report server virtual directories don’t appear in IIS and the information available online all suggests this can’t be done.

The next idea was to try and configure chromium to remember the login credentials. Whilst I was able to save the username and password the prompt still persisted.

Final idea was to try and use an asp/html script of some sort. I then remembered a site I stumbled across recently with some sample/demo reports; http://www.reportsurfer.com/ which didn’t require authentication. I took a look at the html source and could see some simple javascript being used to pass the credentials.

So I built a simple “proxy” page in the default IIS site to redirect to the SSRS report and pass the username & password (the script must be on the same hostname/port to work).

<html>
  <head>
   <script>
var _0x1751=["\x61\x66","\x76\x76"];

function getHTTPObject() {
    if (typeof XMLHttpRequest != 'undefined') {
        return new XMLHttpRequest();
    }
    try {
        return new ActiveXObject("Msxml2.XMLHTTP");
    } catch (e) {
        try {
            return new ActiveXObject("Microsoft.XMLHTTP");
        } catch (e) {}
    }
    return false;
}	

function runReport() {
  var http = getHTTPObject();
  var url= "http://rs-01/ReportServer/Pages/ReportViewer.aspx?%2fTEL+Wall+Thing%2fWallDash&rs:Command=Render";
  http.onreadystatechange = function() {
    if (http.readyState == 4) {
      if (http.status == 401) {
        runReport(); 
      }
      if (http.status == 200) {
        document.location = url;
      }
    }
  };
  http.open("get", url, true, _0x1751[0x0], _0x1751[0x1]);
  http.send(null);
  return false;
}
    </script>
  </head>
  <body onload="runReport();">
  </body>
</html>

To use the script yourself, you essentially need to amend the top line;

var _0x1751=["\x61\x66","\x76\x76"];

The first value is the username and the second value is the password (hex encoded just to add some really basic obfuscation). Obviously this is easily reversible so you should create a new account with only permission for the specific report(s) they should be able to access. You can use http://string-functions.com/string-hex.aspx to convert your login details to hex.

Then simply update the report URL;

var url= "http://rs-01/ReportServer/Pages/ReportViewer.aspx?%2fTEL+Wall+Thing%2fWallDash&rs:Command=Render";

And you’re all set!

helpdesk

In the past I’ve been a bit blasé with security- where something doesn’t work I would just add myself to the local administrator group and voila! Now I’m trying to better my habits and, when something doesn’t work, find out exactly what needs to happen to get it working. Today I was trying to publish some reports to a brand new deployment of SQL Server Reporting Services (SSRS) 2008 R2.

When first accessing the report server from a web browser, unsurprisingly I got a permissions error:

User ‘domainusername’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.

So I jumped onto the report server as an administrator and from the Site Settings area assigned the System Administrator to my account:

Still the same error:

This time I tried defining security at the root node (home) folder:

And now- everything works:

Following on from my previous blog entry about SSRS permissions- I now wanted to connect to Reporting Services from SQL Server Management Studio:

Connect to Server -> Cannot connect to serverinstance -> Additional Information: A connection to the computer cannot be established. (Microsoft.SqlServer.Management.UI.RSClient) -> Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) (mscorlib)

This was resolved by adding my account to the Distributed COM Users:

Now the connection is successful from Management Studio:

Although I was expecting to see folders, reports, data sources, datasets etc:

But I’ll save that for another time!

L

Microsoft SSRS (vs Crystal Reports)

I started using SSRS yesterday and am already growing to love it (i’ve been using Crystal Reports for about 3years!).

Top points to note so far:

-Dynamic sorting is handled within SSRS (requires manual work-around in Crystal Reports, although i believe a dynamic sort control is being delivered in Crystal Reports 2010)
-Dynamic grouping appears to be simpler
-Multiple datasets can be used within the same report (Crystal requires a single dataset per report/sub-report)
-Dynamic Parameters can have “complex” queries to return the list of values (CR only allows you to pick a field from the main report’s datasource)
-Parameter Default values can be calculated using a formula or SQL statement (this is a god send- allows you to default start/end dates for monthly reports for example to the 1st/last of the month- not possible in CR)
-Collapsable groupings (i’ve not used drill-down sub-reports in CR- these may offer “similar” functionality, but  i don’t think on the same level)

There’s more that i’ve forgotten or not yet realized :) I think a lot of it boils down to SSRS being part of Visual Studio and being able to harness a lot of it’s capabilities… that’s all for now.

On a different note: I finally jumped on the Skype bandwagon- search: leetickett

L

%d bloggers like this: