Monday, September 19, 2016

SSRS: Change Text Color for Better Readability

When using a tool that is meant to visualize data, it helps to make the visualization pleasing to look at. Typically, this kind of discussion would spiral out of control into an abyss of SSRS techniques and theories about visualization, but I just want to share a quick tip when working with tables containing a lot of numbers.

I have been working on a dashboard displaying the amount of helpdesk tickets that have been opened and processed. Part of this dashboard contains a table showing the amount of tickets that have been opened for different amounts of days; basically, divided into buckets if you will. Take a look at the screenshot below. Just glance at it and see what you can learn in just a second or two.

It’s not terrible, but you’re staring at a whole lot of black. Don’t get me wrong, I have no problem with that (my entire wardrobe is black), but since there are so many 0’s in the table, the user would have to take some time to make sure they are really reading the table to see the numbers they need. They could easily overlook something.

What I prefer to do, since there are so many 0’s throughout the table, is change the color to give all the 0’s a faded effect. Take a look at the next screenshot.

That is much easier to look at. The fields that have numbers can easily be recognized at a glance. To do this, on the design tab, select a text box and then view its properties in the properties pane of SSDT. Under the font section, click the dropdown next to the ‘Color’ property. At the bottom of the color palette menu is a link that reads ‘Expression…’. Click that and it opens the expression editor. The expression that we use here is as follows…

=iif (SUM(Fields!Day0_2.Value) > 0, "Black", "LightGrey" )

This is a simple ‘If, Then, Else’ expression. All it is stating is that if the value of the field in question is greater than 0, then the font will be black. If it is not greater than 0, then it will be light grey. Notice that I am actually using the sum of the values in that field. This is because I have row groups in the table that I can expand and collapse. If I were to remove the SUM part of the expression, then the colors would not turn out quite right.

Repeat this for each text box in the table. 

Wednesday, January 20, 2016

Using REST in SSIS

Today, I'm going to show you an easy way to use REST API calls to bring data into SSIS. Since I am doing this the easy way, I'm using Task Factory. That's right, Task Factory has a new REST Source that enables users to retrieve data from websites using the REST API. It's a rather powerful source component that can be used in a number of ways. This blog post is not really covering all it's capabilities however. I want to focus on general usage and how to split values into separate columns when retrieving data in JSON format.

For this example, I am going to use the Citrix GoTo Webinar API to retrieve data about past webinars. Since I can pull from any site that uses REST, I make it a point to use the GoTo Webinar API reference.

First of all, when using the REST Source, you have to create a connection manager. You will find that there are two different types. There is a generic REST connection and a REST OAuth2 connection.

REST Connection

OAuth2 Connection

In this Example, I am using the OAuth2 connection. You will notice in the screenshot below that it requires an API key and an access token. Both of these are provided by the website that you are connecting to. Not to go too far into details there, you have to register on the developer version of the website.

Now, inside the the editor, you need an API Endpoint URL. In the case of GoTo Webinar, Citrix provides the base URL, and then the different parts of the URL for the the individual operations. What I really want to focus on here are the output columns. As you can see in the shot below, each column that needs to be returned will have to be manually added by using the 'Add New Output Column' button at the bottom of the window.

The column name can be whatever you like, of course, but you must also specify the Token Path. This information is provided on the Citrix website referenced above for the API. You can see that all I am doing here is returning information about past webinars. What I want to focus on here is the StartTime column.

On the left, when I click on Preview/Test API I have the ability to preview the data. Notice in the results grid the contents of the StartTime column. This column contains both start and end times of each webinar. Not something I want.

I need to get just the start time. Back to the API Endpoint tab. I have requested the results be returned in JSON. This will determine how I pull back just the start time from the times token path.

On the StartTime column, I need to change the Token Path. in order to return only the start time value, use the following token path.


It should look something like this.

Now when I preview the results, I get just the start time in my StartTime column.

If needed, I could also add a column for the end time and use the same JSON expression to retrieve the endTime value.

As you can see, it is a very simple way to access data through REST API. Check out the free trial of Task Factory to try it out for yourselves.