The Mailbag: Referencing Assemblies in Reporting Services; some SQL help

Thu Oct 18, 2007 by Jeff Smith in miscellaneous, report-writing, group-by, reporting-services

As David Letterman would say, wake the kids, call the neighbors, it's time for The Mailbag! Just some quickies today.

Christopher writes:

Greetings Jeff,

First and foremost, great job with all of the blogs. I have a questions
that I cannot seem to get a straight answer for. I am working with SQL
Server Reporting Services (SSRS) and have the need to create VB
functions to customize the reports generated. For example, a setter/getter to
display information that would not be readily available from the
query. SSRS allows this type of custom Visual Basic code to reside in the
report itself, but since most of my code is across multiple reports, it
makes 0 sense to place the same code in each report. Do you know of a
way that I can create a "code library" so that there is one location for
all of the code and all of the reports can access it?

Thank you very much for your time and effort.

Sincerely,
Christopher McGraw

Chris, Here's all the information that you need: Using Custom Assemblies with Reports (from SQL Server 2005 Books On Line). I can't really add much to it -- it explains everything in detail.

Traner writes:

I have two tables, table1's primary key is table2's foreign key.
Table2 has multiple dates that relate to table1.

ie.

Table1 : Table2
JOE : 10/18/07
DAVE : 11/14/07
JOE : 11/27/08

I am trying to get highest date from table2 related to table1. Do you
know how this can be accomplished. Any assistance will be greatly
appreciated.

That really isn't the clearest way to convey your schema, but I think I know what you mean. Let's assume that Table1 has two columns, ID and Name, and that table2 has 3 columns, ID, Date and Value.

First, to get the highest date per ID, you simply use the MAX() aggregate function along with GROUP BY when selecting from Table2:

select ID, Max(Date) as MaxDate
from Table2
group by ID

Be sure to read up and practice with GROUP BY and aggregate functions if you are not familiar with them -- they are very powerful and very fundamental to SQL.

Now, you can select from Table1 and join to the above SQL statement in a derived table to return all rows from Table1 and the maximum date from table 2:

select Table1.ID, Table1.Name, t2.MaxDate
from Table1
inner join
( select ID, Max(Date) as MaxDate
from Table2
group by ID
) t2 on Table1.ID = Table2.ID

As you can see, we have enclosed our previous SQL statement in parenthesis, and assigned it an alias ("t2" in this case), and we can join to it and select from it just like any other table. So, we have simply selected from Table1 and joined to a summarized version of Table2 that only returns the maximum date per ID.

Finally, notice that all we are returning from Table2 is the date; if there are other columns from Table2 that we wish to return (for example, the "Value" column), we must add another join to Table2, that returns only the row with the maximum date per ID:

select Table1.ID, Table1.Name, t2.MaxDate, Table2.Value
from Table1
inner join
( select ID, Max(Date) as MaxDate
from Table2
group by ID
) t2 on Table1.ID = Table2.ID
inner join Table2 on t2.ID = Table2.ID and t2.MaxDate = t2.Date

Notice that the "on" clause indicates that we are joining on ID as you might expect, but also on MaxDate = Date, so that we only return the row in Table2 that has the maximum date per ID.

If you are using SQL Server 2005, this can be done a little easier, with only one join, using the new Rank() function:

select x.ID, x.Name, x.Date, x.Value
from
(
select Table1.ID, Table1.Name, Table2.Date, Table2.Value,
rank() over (partion by Table1.ID order by Table2.Date DESC) as Rank
from Table1
inner join Table2 on Table1.ID = Table2.ID
) x
where x.Rank = 1

More on Rank() and partitions here. Hope this helps you out.