SSRS - Build a "dropdown" box on the report

As we know, SQL Server Reporting Service only provides very limited interactions to end users. Once the report is rendered, the report itself will become a static item. Interactions mostly need to be done from  external applications. Well, there are quite a lot external applications can be used to get interactive result, such as PerformancePoint server in Sharepoint. But except for them, there are some ways to make the RS report looks better. So today I would like to show a "dropdown" box in reporting service.

Firstly let's see what will it look like:

When click the "+" next to the textbox, it will become

Click one of the options in the expanded dropdown box, the selection will appear in the Options textbox

In this case, this "dropdown" box allows multi-selection

To build this type of report, we do not need 3rd party components, customisation of the reporting service, or creation of the CRIs. What we need is just a bit imagination:

The first thing we need to do is to have a report parameter. The purpose of this parameter is to hold the value we selected from the "dropdown" box. In my report, I call it "SelectedValues"

To demonstrat the value we selected, I created a textbox on top of the "dropdown" box, to show the paramter value. Having it or without it will not affect the final result, I created it simply because a long string will be "cut" in the report parameter field, then you cannot see what happened in the design.

As you can see, the expression for this field is quite simple

 =Parameters!SelectedValues.Value  

Now we need have the options for our "dropdown" box. To make it simple, I have the query below

 select 'Option A' as Col  
 UNION ALL  
 select 'Option B' as Col  
 UNION ALL  
 select 'Option C' as Col  

Create a dataset by using this query, then we are ready to format the report:

• Put a tablix component onto the report 
• Change the tablix component to 2 x 2 size
• The row group has the col value from the dataset
• The title row has the expression to show the parameter "SelectedValues" value
• Set the tablix border to none
• Format background color for the first cell of the row group to grey

The final layout of the report looks like below

Now we need to have some special settings. (To make things simple, I will use column number + row number to reference the cell, for example, C1R2 is the grey cell in picture above)

Right click the row group from the bottom panel, go to Visibility tab, and check the radio button "Hide", so by default this row group is hidden from the layout.

Tick the checkbox "Display can be toggled by..." and from the dropdown box, select textbox C2R1. In below screenshot, the item I used to toggle the visibility of the row group, is textbox14

Select the cell C1R1,  set its border style to Solid, border color to light grey, and set attribute "CanGrow" to false (we don't want our "dropdown" box expand by its contents)


Now we have a textbox looks like a drondown box. In preview mode, we can see somthing like below. Time to design the action.


Click cell C1R2 (the cell has the value "[Col]"), and from the pop up context menu, select "Text Box Properties".

Go to Action tab, select radio button "Go to report". In the dropdown box "Specify a report", select the report we are working on.

Click "Add" button under this dropdown box to add a parameter. You should see the parameter "SelectedValues" under the parameter Name dropdown list.

Next to the parameter name, we need to have the parameter value passed into the report. Because I want to make a multi-selection "dropdown" box, what I did is click the small "fx" button, in the pop up expression window, input the expression:

 =IIF(ISNOTHING(Parameters!SelectedValues.Value), Fields!Col.Value, Parameters!SelectedValues.Value + "," + Fields!Col.Value)  

So what we did here, is we created a drill through action on the cell C1R2, when click this cell, it will go to itself with the current report parameter value, and the cell value.

That's it. Now you have a "dropdown" box on your report. The parameter values will be passed into the the report itself continually, like screenshot shows below:

Certainly there are many other ways to improve this "dropdown" box. For example, in this "dropdown" box we can select "Option" multi times, make the result ugly. To fix it, we can fix it by using "replace" function in the parameter expression.

There are lot more to improve, certainly. But what I want to show you, is by using some native functions in the reporting service, we can still build some interaction behavious. Just like when I chat with my friends, the technology could have the boundary, but our imagination is endless :D

Telstra SMS API Quick Demonstration

Telstra recently opened its Dev portal to public. So I just spent some time on its SMS API, and record what I found here.

Overall, Telstra did a good attempt for developers. The SMS API Telstra published allows end user to send 1000 sms for free per month. But there are still quite a lot things need to be improved: documentation, tutorials, API functionality, error handling, etc.

Now time for the demonstration. The application I built is like this:

When I send a SMS from the application, my mobile looks like

And then from the application screenshot, you can see the response I made from my mobile.

To do this, firstly you need to have a developer account. You can register through the link here. In Telstra Dev centre, there are three APIs are available at the moment, as shown below

What we need today is the SMS API. Now click My Apps link, you should be navigated to the My Apps portal.

Click the button "Add a new App" on your right hand side, then you should see the new app registration window like below. Remember in this post we are going to build the SMS App, so just check it. The call back url can be used for application verification in a real world scenario. But in this demonstration, I just create a dummy one.

After you create the App, your request should be shown as pending

Just give it a few hours. When you come back, you should see the Pending status has been changed to Approved. Now expand the App, you can see the consumer key and consumer secret.

Now in Visual Studio, I created a winform project, and add a reference for Newtonsoft.Json (find here), as we need to parse json for the API.

The code itself is quite simple, step by step, we need to

• Create a Token by using consumer key and consumer secret;
• Send SMS;
• Refresh to get the response;

Long in short, the complete code looks like this:

 public partial class Form1 : Form  
   {  
     private TelstraToken TheToken;  
     public Form1()  
     {  
       InitializeComponent();  
     }      
     private async void button1_Click(object sender, EventArgs e)  
     {  
       string ConsumerKey = "<consumer key>";  
       string ConsumerSecret = "<consumer secret>";  
       TheToken = await GetAccessToken(ConsumerKey, ConsumerSecret);  
       string Mobile = txtMobile.Text;  
       string Sms = txtSMS.Text;  
       SMSMessage SMS = new SMSMessage();  
       SMS = SendSMS(Mobile, Sms, TheToken);  
       lstSMS.Items.Add(SMS);  
       lstSMS.DisplayMember = "Mobile";        
     }  
     private void btnRefresh_Click(object sender, EventArgs e)  
     {  
       SMSMessage item = (SMSMessage)lstSMS.SelectedItem;  
       CheckStatus(item, TheToken);  
       if (!string.IsNullOrEmpty(item.ResponseContent))  
       {  
         txtResponse.Text = "Response: " + item.ResponseContent + Environment.NewLine + "RepondTime: " + item.AcknowledgedDt.ToShortDateString();  
       }  
       MessageBox.Show(item.Status);  
     }  
     private async Task<TelstraToken> GetAccessToken(string consumerkey, string consumersecret)  
     {  
       TelstraToken Token = new TelstraToken();  
       string AccessUrl = @"https://api.telstra.com/v1/oauth/token";  
       HttpClient authClient = new HttpClient();  
       HttpContent httpContent = new FormUrlEncodedContent(new Dictionary<string, string>   
       {  
         {"client_id", consumerkey},  
         {"client_secret", consumersecret},  
         {"grant_type", "client_credentials"},  
         {"scope", "SMS"}  
       });  
       HttpRequestMessage Request = new HttpRequestMessage()  
       {  
         Method = HttpMethod.Post,  
         RequestUri = new Uri(AccessUrl),  
         Content = httpContent  
       };  
       try  
       {  
         var ResponseMessage = await authClient.SendAsync(Request);  
         var Response = await ResponseMessage.Content.ReadAsStringAsync();  
         if (ResponseMessage.IsSuccessStatusCode)  
         {  
           var AuthToken = JsonConvert.DeserializeObject<object>(Response);  
           JObject jObj = JObject.Parse(AuthToken.ToString());  
           Token.AccessToken = jObj["access_token"].ToString();  
           Token.ExpiryDt = DateTime.Now.AddSeconds(double.Parse(jObj["expires_in"].ToString()));  
         }  
       }  
       catch (Exception ex)  
       {  
         MessageBox.Show(ex.Message);  
       }  
       return Token;   
     }  
     private SMSMessage SendSMS(string mobile, string message, TelstraToken token)   
     {  
       SMSMessage msg = new SMSMessage();  
       if (token.ExpiryDt <= DateTime.Now)  
       {  
         MessageBox.Show("Session Expired!");  
         return null;  
       }  
       WebClient Client = new WebClient();  
       Client.Headers.Clear();  
       Client.Headers.Add("Authorization: Bearer " + token.AccessToken);  
       Client.Headers.Add("Content-Type", "application/json");  
       string command = "{\"to\":\"" + mobile + "\", \"body\":\"" + message + "\"}";  
       try  
       {  
         byte[] buffer = Client.UploadData(@"https://api.telstra.com/v1/sms/messages", "POST", Encoding.Default.GetBytes(command));  
         string bufferstring = Encoding.Default.GetString(buffer);  
         JObject o = JObject.Parse(bufferstring);  
         msg.MessageID = o["messageId"].ToString();  
         msg.SendDt = DateTime.Now;  
         msg.Status = "PEND";  
         msg.Mobile = txtMobile.Text;  
         msg.Message = txtSMS.Text;  
       }  
       catch (Exception ex)  
       {  
         MessageBox.Show(ex.Message);  
       }  
       return msg;  
     }  
     private void CheckStatus(SMSMessage msg, TelstraToken token)  
     {  
       string status = string.Empty;  
       if (token.ExpiryDt < DateTime.Now)  
       {  
         MessageBox.Show("Session expired!");  
         return;  
       }  
       WebClient Client = new WebClient();  
       Client.Headers.Clear();  
       Client.Headers.Add("Authorization: Bearer " + token.AccessToken);  
       try  
       {  
         byte[] buffer = Client.DownloadData(string.Format("https://api.telstra.com/v1/sms/messages/{0}", msg.MessageID));  
         string bufferstring = Encoding.Default.GetString(buffer);  
         JObject o = JObject.Parse(bufferstring);  
         msg.Status = o["status"].ToString();  
         byte[] Response = Client.DownloadData(string.Format("https://api.telstra.com/v1/sms/messages/{0}/response", msg.MessageID));  
         string ResponseString = Encoding.Default.GetString(Response);  
         //JObject oR = JObject.Parse(ResponseString);  
         JArray a = JArray.Parse(ResponseString);  
         foreach (JObject jo in a)  
         {  
           if (jo["content"].ToString() != "N/A")  
           {  
             msg.ResponseContent = jo["content"].ToString();  
             msg.AcknowledgedDt = DateTime.Parse(jo["acknowledgedTimestamp"].ToString());  
           }  
         }  
       }  
       catch (Exception ex)  
       {  
         MessageBox.Show(ex.Message);  
       }  
     }  
     private class SMSMessage  
     {  
       //unique id of the message  
       public string MessageID { get; set; }  
       //datetime of the message was sent  
       public DateTime SendDt { get; set; }  
       //current status of the message  
       public string Status { get; set; }  
       //message body  
       public string Message { get; set; }  
       //sms recipient  
       public string Mobile { get; set; }  
       //datetime when the message acknowledged  
       public DateTime AcknowledgedDt { get; set; }  
       //response  
       public string ResponseContent { get; set; }  
     }  
     private class TelstraToken  
     {  
       public string AccessToken { get; set; }  
       public DateTime ExpiryDt { get; set; }  
     }  
   }  

Just some quick explanation:

• The token will be expired after certain time (3600), therefore when I create the Telstra token, I will record the expiry date time of the token.
• There is 160 characters limitation of the SMS API, but we need to handle by ourselves, e.g. read amount of characters in the application. Otherwise it will return server error 400.
• When send the message, I will store the message into the SMSMessage class, and list it into the listbox. For the listed message, I can check its status by click refresh button.

The problem of this API is that it will send the message via a random Telstra owned mobile number. As you can imagine, we could treat it like a fraud SMS. So hopefully it can be improved in its future release.