Formatting Data for Reporting
-
I would like to create a hierarchal report that groups data by VP, Districts the VP oversees, and Location details for each District.
My hope is to produce a report as seen in the enclosed illustration. I have also included sample code.
My question is, is it possible to conform this presentation in SQL? SSIS? Or would this require VBA? Any feedback is greatly appreciated!
DESIRED REPORT PRESENTAION:
SAMPLE CODE:
CREATE TABLE #Hier_Data (Loc varchar(10), District varchar(10), VP varchar(15))
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc1', 'District 1', 'John Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc2', 'District 1', 'John Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc3', 'District 1', 'John Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc4', 'District 1', 'John Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc5', 'District 1', 'John Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc6', 'District 2', 'John Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc7', 'District 2', 'John Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc11', 'District 3', 'John Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc12', 'District 3', 'John Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc13', 'District 3', 'John Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc14', 'District 3', 'John Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc16', 'District 4', 'John Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc17', 'District 4', 'John Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc18', 'District 4', 'John Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc21', 'District 5', 'John Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc22', 'District 5', 'John Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc23', 'District 5', 'John Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc24', 'District 5', 'John Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc25', 'District 5', 'John Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc26', 'District 6', 'Jane Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc36', 'District 7', 'Jane Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc37', 'District 7', 'Jane Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc38', 'District 7', 'Jane Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc39', 'District 7', 'Jane Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc41', 'District 9', 'Jane Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc42', 'District 9', 'Jane Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc43', 'District 9', 'Jane Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc44', 'District 9', 'Jane Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc46', 'District 10', 'Jane Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc47', 'District 10', 'Jane Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc48', 'District 10', 'Jane Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc49', 'District 10', 'Jane Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc50', 'District 10', 'Jane Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc51', 'District 11', 'Jane Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc52', 'District 11', 'Jane Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc53', 'District 11', 'Jane Joe')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc56', 'District 12', 'John Joe Jr.')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc57', 'District 12', 'John Joe Jr.')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc58', 'District 12', 'John Joe Jr.')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc59', 'District 12', 'John Joe Jr.')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc60', 'District 12', 'John Joe Jr.')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc66', 'District 14', 'John Joe Jr.')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc71', 'District 15', 'John Joe Jr.')
INSERT INTO #Hier_Data (Loc, District, VP) VALUES ('Loc72', 'District 15', 'John Joe Jr.')
SELECT Loc, District, VP FROM #Hier_Data
-
Have you tried using Reporting Services (SSRS)? The tools that you mention are not really presentation tools, which is what you need here. A Tablix in SSRS should get you close.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
You can also try attaching you tables to Excel and format the data however you'd like there. Excel has become a great reporting tool in recent years.
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
SQL Server is a database tool, not a reporting tool.
SSIS is an ETL tool, not a reporting tool.
SSRS is a reporting tool. Use the correct tool for the job.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply