SQL Inquirer or SQL Developer?
There is a big difference between being able to inquire data from SQL and being a SQL Developer, and this is something not everyone understands. SSRS/SSIS/SSAS or the SQL full stack requires more than just knowing how to create report templates, ETL packages or building a cube. In fact, SQL full stack performs better and provides better results when the back-end that supports them is thicker or stronger, with stored procedures/views/functions managing the main business logic and maintaining the SQL full stack thinner. But for being able to create all that magic in the back-end, a SQL Inquirer does not suffice, you need to have the skills of a SQL Developer.
I've had the opportunity to use those skills in several requests, and those opportunities are the ones I enjoy so much because I love coding, and yes, I am from the old school, I learned to code using Basic, Turbo Pascal, Fortran, Cobol, and others back at the end of the 80s. But it was around 1995 when I was introduced for the very first time to SQL working with Oracle databases and since then I enjoy a lot coding in the back-end.
During all these years I have met many people that are asked if they can create a certain format in SSRS and they will answer 'NO', and they are correct because SSRS by itself does not provide all the 'awesome' ideas that each final user has in mind, BUT, when you know how to code in T-SQL your answer should always be 'YES'. I've always stated to my IT clients/users that everything is possible if we have the data available and SQL Server because the rest only depends on my abilities on both ends.
Recently we were asked to create a report in SSRS that would print a general information of branch offices (branch name, address, city, state, and zip code), data must be grouped by State, sorted by branch name and the final template would print 4 branches per page but in 2 columns, filling out the first column and then the second one from top to bottom and from left to right. After looking for solutions in SSRS and seeing all the restrictions when using multicolumn layouts we decided to create a stored procedure that returns the data already sorted in the two columns and the solution is as follows:
The results the user needed were as follows (each line represents a page break):
State 1
Branch 1 Branch 3
9999 Street Address Branch 1 9999 Street Address Branch 3
City, State1 City, State1
Zip Code Zip Code
Branch 2 Branch 4
9999 Street Address Branch 2 9999 Street Address Branch 4
City, State1 City, State1
Zip Code Zip Code
__________________________________________________________________________
State 2
Branch 5 Branch 7
9999 Street Address Branch 5 9999 Street Address Branch 7
City, State2 City, State2
Zip Code Zip Code
Branch 6
9999 Street Address Branch 6
City, State2
Zip Code
__________________________________________________________________________
State 3
Branch 8
9999 Street Address Branch 8
City, State3
Zip Code
__________________________________________________________________________
The solution was achieved using the code below in a stored procedure:
create proc [dbo].[Test1]
as
begin
create table #tmp
(rowid int identity,
[state] char(2) null,
value1 char(20) null,
value2 char(50) null,
value3 char(20) null,
value4 char(20) null
);
-- Generate the data
declare @i int = 1;
while @i <= 25
begin
insert into #tmp (value1, value2, value3, value4)
values ('Branch ' + convert(varchar(20), @i), '9999 Street Address Branch - ' + convert(varchar(20), @i),
'City', 'Zip Code')
set @i = @i + 1;
end;
-- Assigns the states
update #tmp
set [state] = 'AL'
where rowid = 1
update #tmp
set [state] = 'TX'
where rowid between 2 and 8
update #tmp
set [state] = 'CA'
where rowid between 9 and 17
update #tmp
set [state] = 'NM'
where rowid between 18 and 24
update #tmp
set [state] = 'FL'
where rowid > 24
--select * from #tmp
-- Arrange the data in 2 sets of branches per row
select rowid as NewRow, t.[state],
t.value1 as Col1, t.value2 as Col2,
t.value3 as Col3, t.value4 as Col4,
(select x.value1 from #tmp x where x.rowid = t.rowid+2 and x.[state] = t.[state]) as Col5,
(select x.value2 from #tmp x where x.rowid = t.rowid+2 and x.[state] = t.[state]) as Col6,
(select x.value3 from #tmp x where x.rowid = t.rowid+2 and x.[state] = t.[state]) as Col7,
(select x.value4 from #tmp x where x.rowid = t.rowid+2 and x.[state] = t.[state]) as Col8
into #tmp2
from #tmp t
--select * from #tmp2
-- create final tmp table
select cast(null as int) as NewRow, [state], 0 as [pagenumber],
Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8
into #tmp3
from #tmp2
where NewRow is null
declare @ii int=1, @state char(2), @pagenumber int = 1;
declare c_state cursor for
select distinct [state] from #tmp2
open c_state
fetch next from c_state into @state
-- Fetch the different states
while @@FETCH_STATUS = 0
begin
select @ii = min(NewRow) from #tmp2 where [state] = @state;
-- Remove the rows already existing in the second set of data
while @ii <= (select max(NewRow) from #tmp2 where [state] = @state)
begin
insert into #tmp3
(NewRow, [state], [pagenumber], Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8)
select NewRow, [state], @pagenumber, Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8 from #tmp2 where NewRow in (@ii, @ii + 1) and [state] = @state;
set @pagenumber = @pagenumber + 1;
set @ii = @ii + 4;
end;
fetch next from c_state into @state
end;
close c_state;
deallocate c_state;
select * from #tmp3 order by NewRow
end;
The stored procedure returns the following:
The SSRS format is as simple as shown below. One table with two columns, each column in the SSRS grid displaying the 4 columns in the results above per each branch.
Each group defined by the State and the Page Number:
And the Page Breaks as follows:
The Preview of the report will display:
By keeping it simple in the front and writing the majority of the complications of a specific format in the back allows you to achieve faster and better results plus make it easier for maintenance.
So, are you a SQL Inquirer or a SQL Developer?
Thanks!
No comments:
Post a Comment