SQL server

SSRS not working properly after upgrade to IE10

I was doing some work on SSRS (SQL Server Reporting Services) recently, and found that some of the features were not working as they should. I use IE (Internet Explorer) to do my report management functions, but for some reason, I was not able to see drop down lists or certain menu items.

I was checking my settings and then I suddenly remembered that an automatic update had kicked off on my laptop. I checked and found that this auto update upgraded IE from version 9 to 10. Now IE 10 for some reason does not support all the features of SSRS! I was using 2008 R2.

The good news is that there is an easy fix to this situation. Open up IE and hit the F12 key to open up the developer tools (also available under the tools menu). A developer window will open up on the bottom of the IE screen. Click on the Browser Mode: IE10 menu item and select IE 9. Your browser window will be refreshed and you should have all the features of SSRS back to how they were.


SQL server, SQLServerPedia Syndication

Breaking down dates using DATEPART.

You may have written ad-hoc queries to pull data for a certain date range. As this report gets popular over time and the date range expands, you may be asked to do a break down of the report. For example: per month or querter.

The magical function that helps with this purpose is the DATEPART function. With the DATEPART function, you can extract (otherwise tricky) calendar references like quarter, month, week, weekday, etc without having to guess the correct condition in your where clause.

Below is an example of how this function is used to break down a yearly report into quarters.

SELECT subq1.QuarterPart, sum(subq1.OrderPrice) from
CASE DATEPART(quarter, OrderCompletedDate)
    when 1 then ‘Q1’
    when 2 then ‘Q2’
    when 3 then ‘Q3’
    when 4 then ‘Q4’
end as QuarterPart
from Orders where OrderCompletedDate BETWEEN ‘2012-01-01’ AND ‘2013-01-01’
GROUP by subq1.QuarterPart
order by subq1.QuarterPart asc

Compare this with having to figure out and write where clauses like: ” where OrderCompletedDate >= ‘2012-01-01’ and OrderCompletedDate < ‘2012-04-01’ “. Now imagine doing this for a weekly breakdown.