Note : This post is part of T-SQL Tuesday #005, this months subject is “Reporting” and it’s hosted by Aaron Nelson (@sqlvariant).
I needed an overview report with the names of all the applications we are responsible for. It was intended for the application support teams who could find their applications name in the report and click on it to be forwarded to another report with data on their application.
I found that a report styled like the index of a book worked well for this situation.
This demo is based on the vendor list of the Adventureworks demo database, I’m creating a view that get’s the list of “applications” from the Purchasing.Vendor table.
I also needed a table with the letters of the alphabet.
Create table Alfabet(Letter char(1))
Insert into Alfabet(Letter) values('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('G'), ('H'), ('I'), ('J'), ('K'), ('L'), ('M'), ('N'), ('O'), ('P'), ('Q'), ('R'), ('S'), ('T'), ('U'), ('V'), ('W'), ('X'), ('Y'), ('Z')
Create view Application
as
Select
VendorID as ApplicationID,
Name
From Purchasing.Vendor
To get the correct resultset for my report I built a stored procedure.
After creating the temp table I fill it with a list of applications, using a CTE to make 4 equal(ish) parts.
Create table #AppList(ApplicationID int, Application varchar(50), isLetter int, NTileNr int, RowNumber int)
;with ApplicationList
as
(
Select
NULL as ApplicationID,
Letter as Application,
1 as isLetter
From Alfabet
union all
Select
a.ApplicationID,
a.Name,
0
From Application a
)
Insert into #AppList(ApplicationID, Application, isLetter, NTileNr)
Select
ApplicationID,
Application,
isLetter,
NTILE(4) over(Order by Application)
From ApplicationList
After that I add a rownumber per part to be able to show them side by side in the result set later on.
;with ApplicationList
as
(
Select
Application,
ROW_NUMBER() over(partition by NTileNr order by Application) as RowNr
From #AppList
)
Update #AppList
SET RowNumber = RowNr
From ApplicationList
Where #AppList.Application = ApplicationList.Application
And finally we create a resultset by returning the application names in 4 columns accompanied by the ID which we need to link to the detail report and an isLetter field that will help us determine if this particular value came from the Alphabet table. This is useful because I’m going to apply a different style to those letters in the report.
Select
App1.ApplicationID App1_ApplicationID,
App1.Application App1_Application,
App1.isLetter App1_isLetter,
App2.ApplicationID App2_ApplicationID,
App2.Application App2_Application,
App2.isLetter App2_isLetter,
App3.ApplicationID App3_ApplicationID,
App3.Application App3_Application,
App3.isLetter App3_isLetter,
App4.ApplicationID App4_ApplicationID,
App4.Application App4_Application,
App4.isLetter App4_isLetter
From(Select Distinct RowNumber From #AppList) AppList
Left Join (Select RowNumber, ApplicationID, Application, isLetter From #AppList Where NTileNr = 1) App1 on App1.RowNumber = AppList.RowNumber
Left Join (Select RowNumber, ApplicationID, Application, isLetter From #AppList Where NTileNr = 2) App2 on App2.RowNumber = AppList.RowNumber
Left Join (Select RowNumber, ApplicationID, Application, isLetter From #AppList Where NTileNr = 3) App3 on App3.RowNumber = AppList.RowNumber
Left Join (Select RowNumber, ApplicationID, Application, isLetter From #AppList Where NTileNr = 4) App4 on App4.RowNumber = AppList.RowNumber
Order by AppList.RowNumber
Drop table #AppList
Next step is to create a report, this is what it looks like after simply starting with the wizard :
After that there’s a couple of things that need to be done to make it look better :
- Remove the column header row
- Make the “Index” title larger en center the text
- Make the columns wider
- Create an extra column on the left and leave it empty
- Switch the border style of the grid to none
Now it should look similar to this :
The final step is to make the letters more visible. We can set the text box properties for all the columns. We can use expressions to change the horizontal alignment and the style and size of the font of the column only where it needs to have a different view because it’s a letter and not an application name.
Enter the following expressions, make sure the AppX_isLetter column has the right number of the column for every column in the report.
- Alignment – Size –> =IIf(Fields!App1_isLetter.Value=1, “Center”, “Default”)
- Font – Style –> =IIF(Fields!App1_isLetter.Value=1, “Bold”, “Normal”)
- Font – Size –>=IIF(Fields!App1_isLetter.Value=1, “14PT”, “10PT”)
And we’re done (almost) :
The final thing we need to do is enable the Application field as a hyperlink :
The thing I don’t like here is that I would love to be able to have an expression ability for the “Enable as a hyperlink” feature as well. Now the application is clickable even if there is no detail available and the letters can also be clicked on…
Note : You can name your Alphabet table “Alphabet” instead of the Dutch “Alfabet” if you like, just make sure the query knows about this change as well 
The scripts and the demo report can be dowloaded here :
BookStyleIndexReport.sql
Index.rdl
