Posts

Showing posts from May, 2016

SSRS 2005 Bug - Percentage calculated column when Exporting to Excel

Image
Nature of the Issue Faced :- Say you have 3 columns in a tablix/table on the report. There are 3 textboxes in each of these columns. Let us give them names as textbox1, textbox2 and textbox3. Textbox3 should be textbox1 - textbox2. We are using following formula to achieve this :- textbox3.Value = ReportItems!textbox1.Value -  ReportItems!textbox2.Value textbox1 and textbox2 have decimal values rounded off to 3 decimal places (e.g.12.412). On the report we had set the format of the cell to be 'P1' (eg 12.4) Now that we know the scenario, here is what the issue is - Everything works fine inside the cell of the report, however as soon as to export to excel the place where you have expression shows some arbitrary value like _385 -  _386 textbox1 has value of 76.0% and textbox2 has value of 74.1% in the above example! Solution :- Changing the formula to Round ( ReportItems!textbox1.Value,3) -  Round(ReportItems!textbox2.Value,3) rectified the issue. Cannot quite

ALTERNATIVE TO DISABLING A SQL JOB STEP (FOR SSIS PACKAGES)

Image
For relational data-warehouses where you have the data load scheduled during off hours, timing of different steps in a sql job plays a vital role to decide whether to carry on with the load or postpone it for the evening. We as a team recently encountered a scenario where we had to disable the execution of a ssis package (which ran through a sql job) because the current time crossed the threshold time. Now there are various ways to indirectly disable or bypass a job step, however all of these require admin's intervention. We needed an automated process wherein the package/load should not execute if the time limit is crossed. Solution - One of my manager came up with a logic which helped us tackle this situation. Here is how the package looked like:- Execute SQL Task that you see here controls whether the rest of the package should execute or not. Here is the script we used inside :- Select current time >=  thresholdtime then 1 else 0 end as intTimeCheckover This