SSRS Report viewer - Translating the toolbar control using java script

So, you need to translate or make custom changes to   ssrs  viewer  tool bar  controls? Javascript makes it easy to accomplish that. For our example, we will be using asp.net to  to change  4  different labels on the toolbar.
In order to accomplish that we will use four hidden fields to pass our values from the database and the below javascript functions below on window load.
Basically, we just read the element id from  the document and  change the title and innerHtml text as desired. The trickiest par was changing  the number "of" pages since the original code does not give it an id. For this instance, I created a second function to get the first instance of  a span tag using findFirstDescendant().
Pretty basic stuff but handy!

      // variables to hold our values
     <asp:HiddenField ID="find" runat="server" />
    <asp:HiddenField ID="next" runat="server" />
    <asp:HiddenField ID="of" runat="server" />
    <asp:HiddenField ID="btnexport" runat="server" />

 //toolbar   function
        window.onload = function () {
            var findLabel = document.getElementById('MainContent_find').value;
            var find = document.getElementById('ctl00_MainContent_ReportViewer2_ctl05_ctl03_ctl01');
            find.title = findLabel;
            find.innerHTML = findLabel;

            var nextLabel = document.getElementById('MainContent_next').value;
            var next = document.getElementById('ctl00_MainContent_ReportViewer2_ctl05_ctl03_ctl03');
            next.title = nextLabel;
            next.innerHTML = nextLabel;

            var ofLabel = document.getElementById('MainContent_of').value;
            var of = findFirstDescendant('span');
            of.innerHTML = ofLabel;

            var exportLabel = document.getElementById('MainContent_btnexport').value;
            var exportBtn = document.getElementById('ctl00_MainContent_ReportViewer2_ctl05_ctl04_ctl00_ButtonLink');
            exportBtn.title = exportLabel;
            //exportBtn.innerHTML = exportLabel;

        };

        function findFirstDescendant(tagname) {
            var parent = document.getElementById('ctl00_MainContent_ReportViewer2_ctl05');
            var descendants = parent.getElementsByTagName(tagname);
            if (descendants.length)
                return descendants[0];
            return null;
        }

English version of tool bar.

Toolbar translated to German as a result.

This is for showcasing only and if using this code on a production environment, one should implement error handling and such. Enjoy! 

HTML5, Bootstrap,Less, CSS,jQuery

The web is a wonderful land, full of names, acronyms and information. Although, I am not a designer ( and trust me, I don't claim to be one)  I enjoy  learning about all the latest web technologies  and front-end development frameworks. We all heard of the wonders of responsive design and sometimes we forget how useful it can be when visiting  a particular website on a mobile device.

I was recently approached by a friend who needed a simple web site to showcase his new tree care business. Reluctantly, I took the quest  to help a buddy out and polish on my somewhere deep in my soul hidden designer skills.
I worked  with him over the weekend and surprisingly everything came together. In my opinion, Bootstrap should be part of every front end developer tool box building a client facing applications.  It makes developing responsive  projects for the web easy and  fast.
We all seen the reusable bootstrap components such as  navigation, iconography, drop downs  everywhere. The big fonts, icon are a constant in any modern website.
CSS takes care of the rest. Displaying information in accord to screen size is simple and straightforward.
<div class="hidden-xs visible-sm visible-md visible-lg">
The above line will hide the particular div tag  on extra small screens and show  the information on larger devices. Pretty powerful stuff and really easy to implement.
Below desktop screen grab.

The website looks nice and clean on a smaller phone screen.

Building a simple website to showcase a small business or a hobby should not be complicated. HTML5, Bootstrap,Less, CSS,jQuery makes it easy for anybody with knowledge of front-end development  to hit the ground running. 

SSRS Dynamic report, how did that column just appeared in there ? Part 2

The report itself  is pretty straightforward. The only difference from a regular report here, is that we will need to map  the sequence and the field name for the columns. Other than that, easy! There is, of course, a limit on how many columns can be  displayed and this would have to observe each particular project requirement. For my example, it is 50 columns across.

We can call the fields for dataset1 whatever you like. I named mine C1,C2 ans so on.

The important part here is the expression to display the correct column name and the correct sequence they are supposed to appear.

We can use a lookup function in order to achieve that:

= Lookup(1, Fields!Sequence.Value, Fields!FieldName.Value, "MappedFields")

This would retrieve column 1 for example. In order to retrieve next column, we would increase the lookup number by one, for example.

And we show /hide the column according to data availability on data set 1, as below. 

All the above information, will be used to display data to our report from the below stored procedure. First of all, we will retrieve  the active columns and use a cursor to create a string that we will pass to get our data.

Once we have this data, we can loop through  and create our query using  the variable @ColumnString. 

Here we keep track of our sequence and the order in which the columns will appear in the report.

And here the full cursor script:

And finally,

we create our select statement and just run it!!!!

As you can see this can be a bit complicated to set up due to all the moving parts but once we have this working, adding, removing  or simply updating the report will be a breeze. Best of all, the end user will do it for you , while you can catch up on watching all those cats videos on YouTube. :)

SSRS Dynamic report, how did that column just appeared in there ? Part 1

Most reports are dynamic due to their ever changing nature( We are talking about the report data here). But every so often, you need to change the report itself which means adding and/or removing columns from the report to conform with new business requirements. If  you are adding/removing, unrelated data, there is no way around it. You will have to change the report, its logic  and then test  and redeploy it again. That is just how it works. Now, lets say , you have related data that is always changing such as one or various list of prices grouped together in  a "tier". For this example, these "tiers" might change from month to month or even be removed or recreated at a later time. Once the request comes in, you can go back to the first option or we can develop a dynamic report that will accommodate these changes seamlessly.
First we need to give our client a way to manage these levels or  "tiers" as we will call them. A simple interface will do:


Here is an example report, This was  run before we added one more level. FYI: we can also have static columns such as the first one that appears on the image below.


There are two parts for this solution. The first one is to create a dynamic query which will populate a table from which we can run our query. The second part,  is a stored procedure to feed our report.

• Procedure to create our view and update our example table

At this point, we will need to delete the columns from our display fields table if they were deleted. We can just check the current dynamic fields from our EXAMPLE_TABLE against our  display fields table and delete them similarly to what we did  for adding columns(ALTER TABLE).

Finally, we can run our query and populate our table with our recently dynamically created query.

There is a job which runs every night which executes this procedure and thus refreshes the table. So you can imagine the happiness of our user tomorrow when he gets a new copy of the report and magically the columns he added recently shows up  in there. No request, no test or deployment.

 I will show the report  in Part 2 next.

Batch search! Oh, you make my life so easy

You have a database with millions of records and you need to search for one 100 or even 1000's of those records. Fast! You have  a couple of minutes  before the boss calls again. You have 2 choices. Madly, start typing criteria after criteria, which will definitely take a long time to do, with no guarantee it will yield  the specific records you need  or a second option  would be to take the csv(comma delimited string) file your boss just sent you and use it to search your records  at easy.


First we need to upload our file and read it to a Data Table. Now, we can pass our result to the below method where we can attach a unique batch code to the set which will be useful later when performing our search.


 After we insert our records to the database we need to reset the controls and make it available so that our user can add the records from the file to the filter criteria.


Finally, we  use our batch code and add it to our criteria. (Using NHibernate here for example)


 For this example I only had two scan codes in my Test.csv file. Those two records were returned  since they matched my search criteria but you can see where I am going with this...

Creating Dynamic template email notifications that can be easily modified by a customer.

Your client needs to send out countless email notifications that are similar but could contain different fields such as pending order and  final approval information to different settings/companies/customers . First we give the option to view and modify a default template that will be assigned to the current need.


For this project we use Telerik RadEditor controls, which make it easy to manipulate text and save changes to the database. We also make predefined   fields available in a drop down and context menu for easy insertion and removal. These fields will be mapped to values in the database at run time depending on what the customer needs/chooses to display on the specific notification. The customer can then choose those fields from our drop down or click on the area where it needs to be inserted. In this case a little javascript goes a long way when inserting the values in the text editor. Take a peek at the OnClientItemClicked="InsertTextMessage" function.


Once everything is set up, we will use our friend SQL server to do all the dirty work and send out the notifications to the customer with the help of a stored procedure.

Here is what we get.

Amazing right! With a bit of work we can make everything easier  for our fellow co-workers and thus help that pesky bottom-line.

Moving rows up and down with reordering using a list view where each row could have multiple items assigned to it.

You display a list of data in an organized way using a list view. Everything looks good and you are a happy camper until the request comes in: "I want to be able to move these items up and down on this list as I please,  just with the power of my brain waves...".  I am still working on the brain waves stuff but lets analyzed the below list:



Let's say we need to swap two items on our list. Item with step number 3 will take  item with step number 2 place and step number also. And of course vice-versa. There are other scenarios such as deleting and item or even "cloning" (creating another item from an existing item)  which I don't discuss on this post but which can be addressed with the same approach.
First of all, we need to select the item which will be moved. Once the item is selected, we then give the user the ability to move the item up or down, depending on the item current location. Of course we could not move and item down if the current location was the bottom already.
I have a private method called SetSelectedControls(). In this method, I make certain controls either available or disabled for certain rows, dependent on the rows location and selection. A slimmed down version for reference:

Once an item is selected, I use the button event to get the necessary data  and thus proceed with the operation to swap the items. Both buttons events call the same method passing  the step number and the direction we need to move the item as parameters.


FYI: I use a data table to save my list items into session. You have to keep in mind that this scenario worked great for this situation due to the limited number of records this application will deal with but this is something to consider if your application will be dealing with a lot of records.


If we were  only to swap the rows and their steps, life would be so much easier but for this request though, each step could have multiple products assigned to it and to make the boss happy we will have to swap those too. Products are saved in a different table and we can reference them by step.   Here is the reorder table method:

       

Once we save the data table to session with the new order,we can just rebind the list and if needed keep the same step that was selected  before selected now in the new position on the list.


The same approach can be used when an item is deleted but in this situation we will have to deal with moving all the steps that were above the deleted item down one step but it should not be any problem , right?