SSRS report paging tips

A couple of weeks ago I need to format some SSRS reports with 2 specific features:

• Keeping the column headings at the top when scrolling - a la 'Freeze Panes' in Excel
• Remove pagination for the web based reports

I managed to find the way to do both of these, applied them, then carried on with whatever else I was doing. I don't spend a lot of time in SSRS, probably a couple of days per month, so when I needed to do the same thing to a report today - I couldn't remember where the settings where! So to

try and remember these settings, I'm going to cast them down here, in the vain hope that 1) writing about them will make them stick in my mind a bit more, and 2) failing that I can come back here and remind myself!

To keep the column headings at the top of the report whilst you scroll, (like you can do in Excel with 'Freeze Panes'), set the FixedHeader property of the table (in the Layout section) to True

Removing Pagination from web based reports is achieved by setting the InteractiveSize - Height property of the Report (in the Layout section) to 0

Exceptional DBA Awards 2010

I'm extremely proud and excited to have been chosen as a finalist for this year's Exceptional DBA Award. The field is very strong, and I'm up against some big names in the SQL community.

It's an amazing achievement to have made it this far, but I can only go further with your support, so please vote for me at www.exceptionaldba.com

The Future of Monitoring

For some time now I have been participating in usability sessions with Red Gate Software for their product SQL Response. This has been a great chance to shape a commercial application to do what I wanted it to do. I've seen ideas of mine appear in their latest version (ok,ok - other people MAY have had similar thoughts too...)

The team behind the application are now looking to create a new version, and are looking for input from real users, with real monitoring and performance issues. They are going to build the next version of their product to do exactly what users want, so get along to their website at The Future of Monitoring, have a look at what they've done so far and contribute YOUR ideas.

They are even giving away free copies of their new monitoring tool (when it's finished) - all you have to do is submit a design (doesn't have to be anything fancy, they just want ideas), and you'll be entered into the draw.

COUNT(DISTINCT) OVER() aggregate window function

Recently have been using some of the new partition and aggregate window functions in SQL Server 2005.(see here for the BOL entry).

However when I tried to change a count(distinct ...) query, I ran into an error!

create table TableA(
id int,
category varchar(10),
subcategory varchar(10),
sometext varchar(100)
)
go
insert into TableA (id, category, subcategory, sometext)
select 1,'Category A','fruit','apple'
union select 2,'Category A','fruit','banana'
union select 3,'Category A','fruit','cherry'
union select 4,'Category A','animal','cat'
union select 5,'Category A','animal','dog'
union select 6,'Category A','animal','lion'
union select 7,'Category B','vegetable','carrot'
union select 8,'Category B','vegetable','potato'
union select 9,'Category B','vegetable','onion'
union select 10,'Category B','name','alice'
union select 11,'Category B','name','bob'
union select 12,'Category B','place','london'
union select 13,'Category B','place','manchester'
go


from this sample data we can easily count the number of subcategories per category using


select category, count(distinct subcategory)
from TableA
group by category

which gives

category CountSubcategory
---------- ----------------
Category A 2
Category B 3

(2 row(s) affected)

but if we try to use the OVER() clause

select category, count(distinct subcategory) over(partition by category)
from tableA

we get

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'distinct'.


Check BOL, and there's nothing that says that the DISTINCT argument cannot be used in the COUNT function when used in an aggregate window function. How confusing. Then I discover I'm not the only one to have the same thought, as this item on Connect shows.





So if you have the same issue and want this resolved, please log on to Connect and vote this up.

MSc Business Intelligence at Dundee University

Whilst I don't normally like just repeating what others blog about, once in a while you come across something that you just have to share!

Tony Rogerson has blogged about a new MSc in Business Intelligence that Dundee University are hoping to start in January. I say 'hoping' as if no-one registers then it wont be run! Although having spoken to Mark Whitehorn, the organiser, if the amount of interest generated so far is anything to go by, it is a certainity.

Tony doesn't mention it, but the course will run over 2 years for those wanting to complete it part-time via distance learning, and you will be required to attend in person for 2 weeks per year. The content seems to be product-agnostic, teaching the concepts of BI, although there will be some discussion of various technologies and techniques, and the final module will use MDX as the implementation language.


The cost of the course (if you enrol this year, - no guarantees this price will stay) is £1,700 per year, which when compared to the cost of commercially available courses that come in at about £2,000 for 5 days training, is a very good price. I also see the value of an MSc far outweighing the value of any product-specific certification (mentioning no names), but that's just a personal view.


I'm hoping to secure some support from my current employer, and get myself on the course ASAP. Haven't been in formal education for over 15 years, so it might be a bit of a shock to the system.


Anyway don't take my word for it, check out Tony's blog for some more info and get in touch with Mark (email address on Tony's blog)

Manchester SQL Server User Group

Attended my first EVER user group meeting a couple of weeks back. I had been meaning to get to one of these ever since they started in Manchester earlier this year, but always something else had conspired against me.

Sure I've done SQL Bits, and various MSDN/TechNet type stuff, but this was different, more personal and certainly made me feel more involved. Looking forward to many more of these, and maybe next time I will network properly - I talked to many people but didn't get their details!

If you've never attended one of these, or perhaps are not sure they are for you, don't worry just get along to one. You'll meet people working in SQL at all levels, and even if you have nothing in common with anyone else, then you'll be able to share your 'uniqueness' with them. There's no pressure to network, speak or contribute, but after just one I bet you'll be itching to get more involved!

They are organised by the UK SQL Server User Group , the Manchester one by Chris Testa-O'Neill ( blog ), who shows great enthusiasm for the community.

Next Manchester one is planned for 15 Oct 2009, more details here, but check out the UK SSUG website for other events near you. They do Leeds, Dundee, Reading, Bracknell, Cardiff and the obligatory London.

Linking 2 tables based on row positions - SQL2000 vs SQL2005

A simple (and common??) scenario - you have 2 tables of ordered integers and want to combine them into one recordset where the first row of the first table is paired with the first row of the second table and so on, like

Table1
val(int)
---
1
3
5
9

Table2
val(int)
---
4
9
14
17

we want to return

val val
--- ---
1   4
3   9
5   14
9   17

And we want to achieve this WITHOUT the use of cursors (obviously!).

If we could assign a row number to each table then we can join on the row number, row1 = row1, row2=row2, etc... now SQL 2005 gives us a couple of ways of doing this using either the RANK() function or the ROW_NUMBER() function.

if object_id('tempdb..#t1') is not null drop table #t1
if object_id('tempdb..#t2') is not null drop table #t2

create table #t1 (val int)
create table #t2 (val int)

insert into #t1 values (1)
insert into #t1 values (3)
insert into #t1 values (5)
insert into #t1 values (9)

insert into #t2 values (4)
insert into #t2 values (9)
insert into #t2 values (14)
insert into #t2 values (17)

using RANK()

select t3a.val, t3b.val
from
(select val, rank() over (order by val) as seq from #t1) t3a
join
(select val, rank() over (order by val) as seq from #t2) t3b
on t3a.seq=t3b.seq

or using ROW_NUMBER()

select t3a.val, t3b.val
from
(select val, row_number() over (order by val) as seq from #t1) t3a
join
(select val, row_number() over (order by val) as seq from #t2) t3b
on t3a.seq=t3b.seq

But in SQL2000 these functions do not exist. An elegant way to achieve this is to join the table to itself and determine the 'rank' by counting the number of elements that are less than or equal to the current i.e.

select t11.val, count(*) as seq
from #t1 t11, #t1 t12 where t11.val>=t12.val group by t11.val

returns

val seq
--- ---
1   1
3   2
5   3
9   4

hence we can use this as the ranking function

select t3a.val, t3b.val
from
(select t11.val, count(*) as seq
from #t1 t11, #t1 t12 where t11.val>=t12.val group by t11.val) t3a
join
(select t21.val, count(*) as seq
from #t2 t21, #t2 t22 where t21.val>=t22.val group by t21.val) t3b
on t3a.seq=t3b.seq

[Credit goes to Richard Dean for the original idea]