Month: November 2007

How multi-value query parameters in SSRS are treated by SQL Server

By robfarley

This is something that has bothered for me for a while. In SQL Server Reporting Services, you write a query against a data source, and that becomes the data set for the report. Brilliant. I like to write the query in Management Studio first, and then paste it across. But this doesn’t always work, and multi-value parameters is why. Multi-value parameters are those parameters where you’ve ticked the checkbox to say that you can select multiple options. Like in the image there to the right. Now, in T-SQL, it is not considered valid to write: WHERE mycol IN (@ListOfMyCols) It’s … Continue reading How multi-value query parameters in SSRS are treated by SQL Server

How Jim McLeod passed 70-444

By robfarley

Jim McLeod is a DBA, and a good friend of mine. And today, he is due congratulations, because he passed the exam 70-444 – Optimizing and Maintaining a DB Admin Solution in SQL 2005. Now he only needs to do the Infrastructure Design exam (70-443) to become MCITP:DBA. Did he take one of the courses I teach, that Microsoft recommends to do before trying the exam? No. Did he spend hours in study, looking for those small things that he would need? No. Did he download one of those illegal copies of the exam? No, of course not. That would … Continue reading How Jim McLeod passed 70-444

How to format the subtotals of a Reporting Services matrix differently, using InScope

By robfarley

Here’s the scenario. Your SQL Server Reporting Services (SSRS) report has a matrix, showing the sales of each salesperson per year. You put conditional formatting in place so that cells in the matrix that have sales over 1000000 are in a different colour – which is done by putting a formula in the property ‘color’, like =iif(me.value > 1000000, “Red”, “Black”) But then you put subtotals into your matrix (right-clicking on the column/row field to get the option), and you find that all the subtotals are being coloured Red, because together, they add up to more than 1000000. And this … Continue reading How to format the subtotals of a Reporting Services matrix differently, using InScope

It’s a big week for downloads

By robfarley

SQL Server CTP5 is now available for public download. Yesterday I wrote about Visual Studio 2008 being live and ready for download to MSDN Subscribers, and today there will be more activity on the Microsoft servers again. This latest CTP includes lots of great stuff, including a lot enhancements around SSIS, SSAS and SSRS, Intellisense for T-SQL, the Resource Governor, Transparent Data Encryption, SQL Filestream, and GeoSpatial support. And that’s not even half of it. 18 improvements listed at https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5470. Whew! Get yourself into this – you might find all kinds of benefits for your business.