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.