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?

Crooked lines! Create a straight line graph in SSRS when having null or empty data points.

A "simple" report to display product usage for  multiple plants over a date range. Sounds simple enough..  If you look at the attached graph, and the red line ,  the line stops at the dates it does not have readings for, which make the trend line hard to understand. How you can get the software to do this?

 In previous version of SSRS Microsoft reporting tool (2005 I believe) , the software would "calculate the gaps" if you will, automatically; making the display more readable  and easier to understand . When developing using the 2012 version,I had to take the responsibility to calculate the empty points (in sql) to have a straight line where the software insisted on making it crooked and ugly. The base solution uses various cursors to calculate the "missing data points" and this worked greatly for this scenario but it might not work where speed is top priority. For here is how this was pulled off.:

1. First we need a cursor  to insert all possible periods, plants into a temp table
2. A really important step is to define which points will be displayed(plotted to the graph, the squares where there is data) I simply used a case statement to set a flag to 'S" if there was data and the item needed to be display(be plotted) and 'N' if otherwise. Also, we need to calculate the interval between plots that have data. I do it by using a case statement and simply counting the  periods we created above where there is no data . Also for this particular project , there was the need to hide/show some values dependent on whether  a period had been closed or not and since the number of products could be huge I had to create my own color pallet to display the line graph since SSRS just repeats the colors once there are so many lines displayed  to which I will not take into consideration for this post. This refers to the #Temp table you see below.
   
   
3. Now we redefine which records have markers.
   
   
4. Now we calculate the interval value
   
   
5. The cursor to calculate empty points.
   
   
   
      
6. And finally the result.
   

It was pretty laborious to put it together but once I figured by hand- literally with a calculator-  how to calculate the missing empty points, it was just a matter of trial and error until the final project came together.