If you’re a seasoned writer of SQL Server Reporting Services (SSRS) reports, you know the process of trawling through databases, tables, fields, and indexes to deliver the right combination of headings and columns that present the underlying data as your users want to see it. But what if there was a way to deliver information without all that complexity?
Imagine a scenario where you need to advise users of IT equipment on what to do if they need to travel to one of your company’s other offices and want to have access to their data and emails. This can become quite an issue, especially if your offices are located in remote places. The traditional approach of providing users with a lengthy document containing all the necessary information doesn’t always work. Users often don’t have the time or patience to read through pages of explanations and duplications.
So, how can we simplify this process and deliver the information users need in a concise and tailored manner? One solution is to leverage the power of SQL Server and create a stored procedure that asks users for a few key pieces of information about their trip, such as their network account, the office they’re based in, the office they’re visiting, the device they’ll be using, and the dates of their visit. Based on these parameters, the stored procedure can generate a set of advice specific to the user’s needs.
Here’s an example of the code for the stored procedure:
alter proc [dbo].[sp_itadvice_travel_ssc] (@userid varchar(100), @baseid int, @destid int, @computer int, @date0 datetime)
as
declare @base_domain varchar(20)
declare @dest_domain varchar(20), @user_domain varchar(20)
declare @username varchar(50), @profilepath varchar(100)
declare @assist int
--table for results
create table #Temp (hcode varchar(20),message varchar(1000))
-- assumed that office-related data is in ITDB.dbo.Offices
select @base_domain = domain from ITDB.dbo.Offices where locid = @baseid
select @dest_domain = domain from ITDB.dbo.Offices where locid = @destid
-- @userid in the form: DOMAIN\username
set @user_domain = left(@userid,charindex('\',@userid)-1)
set @username = substring(@userid,charindex('\',@userid)+1,99)
--find profile path for DOMAIN_MAIN accounts; if exist, they have a roaming profile
set @profilepath = ''
select @profilepath = profilepath
FROM OPENQUERY(ADSI,
'<LDAP://DC=domain_main,DC=org,DC=com>;(&(objectCategory=Person)(objectClass=User));
sAMAccountName, profilepath;subtree')
WHERE (sAMAccountName = @username)
--the main logic...
-- no travel
if @baseid = @destid
insert into #Temp values ('0', 'You are not travelling anywhere!')
else
-- no destination domain i.e. outside of WAN
if @dest_domain ='' or @dest_domain is null
if @base_domain = 'DOMAIN_MAIN'
insert into #Temp values ('0-K','You will need to take a token dongle to access the network. Otherwise you will only be able to use webmail.')
else
insert into #Temp values ('0-X','You will only be able to access webmail.')
else
--MACHINE/PROFILE
begin
if @computer=1 --section shared laptop
begin
insert into #Temp values ('2SA','If you ask IT to setup the laptop for your personal use, specific local applications can be installed so you can continue to use them.')
insert into #Temp values ('2SE','For instance, Outlook can be setup to log you in to the standard screen (i.e. not webmail) and keep a local copy of all messages.')
if @base_domain = 'DOMAIN_MAIN'
if len(@profilepath)> 0
insert into #Temp values ('2SPr','You also need to ask IT to amend your profile; otherwise your laptop will take a long time to logon.')
else
insert into #Temp values ('2SP?','Check with IT - they may need to amend your profile.')
end
if @computer=2 --organisation pool laptop
begin
if @baseid = 1 -- central office
begin
insert into #Temp values ('5SB','Submission of this report will prompt IT to find a laptop for you to take.')
if datediff(day,getdate(),@date0)< 7
insert into #Temp values ('5ST','As you have only given '+ cast(datediff(day,getdate(),@date0)as varchar(3))+ ' days notice. you had better check that one is available now.')
else
insert into #Temp values ('5ST','As you have given ' + cast(datediff(day,getdate(),@date0)as varchar(3))+ ' days notice, this should not be a problem.')
end
if @base_domain = 'DOMAIN_MAIN'
if len(@profilepath)> 0 --roaming DOMAIN_MAIN profile
insert into #Temp values ('5SPr','You must either login as ''temporary'' or ask IT to setup your usual logon so you can use that instead.')
else
insert into #Temp values ('5-Px','Once there, you can logon with your usual logon')
else
insert into #Temp values ('5SP?','Check with IT - they may need to amend your profile.')
end
-- Applications
insert into #Temp values ('9-A','Access to applications such as Projects, Sharepoint or Accounts depends entirely on the speed of the local connection.')
-- search hcodes of advice given for any of the form '_S%'; these mean pre-visit IT Support is required
select @assist = count(*)from #Temp where hcode like '_S%'
if @assist >0
insert into #Temp values ('9SA','*Remember to arrange a visit to IT before you travel*')
end
select * from #Temp
Once the stored procedure is executed, it populates a temporary table with the advice based on the parameters provided. The resulting advice can then be displayed to the user in a report format.
Now, let’s integrate this solution into the established process in our organization. When the user runs the report and sees the resulting advice, they are advised to export it to PDF and email the file to IT Support. This way, the IT Support team knows to expect someone to visit and can make the necessary arrangements, such as reconfiguring the user’s laptop or disabling their roaming profile.
One additional step we can take is to leverage SQL Server’s ability to interact with Active Directory. By setting up a linked server to the Active Directory, we can retrieve information about the user’s network account, such as whether they have a roaming profile. This information can then be used to provide additional setup instructions if needed.
This approach simplifies the process for both IT departments and users. Users no longer need to sift through lengthy documents or understand the technical details. They simply provide a few key pieces of information, and the system generates tailored advice specific to their needs. IT departments can easily manage and update the logic of the stored procedure as new situations arise or the existing logic needs adjustment.
In conclusion, using SQL Server to deliver tailored information is a powerful way to simplify complex processes and provide users with the information they need in a concise and user-friendly manner. By leveraging the capabilities of SQL Server, we can create efficient and effective solutions that meet the needs of both IT departments and end users.