Determining what Report Items To Choose To Make Report

Question

Determining what Report Items To Choose To Make Report

jbr14920929

Valued Member

Points: 64
More actions
August 27, 2014 at 12:47 pm

#295767

Hello,
I am new to SSRS and am trying to determine what building blocks (table, matrix, subreport, etc) I need to use in order to create the report I have shown below.
The report is supposed to show total sales, cost and resulting gross margin by customer for each month. Salesrep sales totals should also be calculated. The user would input the year for which they want the report for.
Report Layout:
Jan Feb Mar.... Year To Date Totals
SalesRep1
Customer1
Sales
Cost
Gross Margin
Customer2
Sales
Cost
Gross Margin
SalesRep1 Total Sales
SalesRep2
Customer3
Sales
Cost
Gross Margin
Customer4
Sales
Cost
Gross Margin
SalesRep2 Total Sales
.
.
.
Total Sales
The query that supplies the data is as follows:
SalesRep, CustomerName, SalesDate, SalesValue, Cost
Each record from the db is a sales order line item but if needed I could change the query to do the grouping and aggregations.
The report will be hosted on SQL Server 2008 Standard.
Thank you,
James

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63428 More actions · Answer 1

you need a matrix. You might want to use the Wizard to get the setup started. If you select Add New Report, in SSRS, it starts the Report Wizard and should walk you through most of it.

I have most of the report, and I did this:

Row Groups:

EmployeeID

CustomerID

Column Groups:

Order Year

order Month

Aggregate: SUM(LineTotal) -- I'm using AdventureWorks as a base.

jbr14920929 Valued Member Points: 64 More actions · Answer 2

Thank you pietlinden.

The Cost and Gross Margin which are to be shown under the Sales are missing. How would you add those in?

pietlinden SSC Guru Points: 63428 More actions · Answer 3

What does your dataset look like? Could you post some dummy data? Hate to push you into the deep end in your first lesson, but here goes... This is a great place to start: http://www.sqlservercentral.com/articles/Best+Practices/61537/

It's Jeff's explanation of how to get good help. If you follow his instructions, the likelihood that you'll get a tested solution that actually works increases dramatically.

jbr14920929 Valued Member Points: 64 More actions · Answer 4

Thank you for the link. Here is some sample data.

IF OBJECT_ID('TempDB..tblSales','U') IS NOT NULL

DROP TABLE tblSales

CREATE TABLE tblSales

(

ID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,

SalespersonRep nvarchar(255),

CustomerName nvarchar(255),

InvoiceDate datetime,

InvoiceLineAmount float,

COGSAmount float

)

SET DATEFORMAT DMY

SET IDENTITY_INSERT tblSales ON

INSERT INTO tblSales

(ID,SalespersonRep,CustomerName, InvoiceDate, InvoiceLineAmount, COGSAmount)

SELECT '1','Davy Jones','Customer F','Jun 25 2014 12:00AM','598','401.97' UNION ALL

SELECT '2','Tom Swift','Customer K','Mar 24 2014 12:00AM','163.8','0' UNION ALL

SELECT '3','Tom Swift','Customer I','Apr 3 2014 12:00AM','0','364.66' UNION ALL

SELECT '4','Frank Hardy','Customer C','Jun 12 2014 12:00AM','99.5','164.32' UNION ALL

SELECT '5','Trixie Beldon','Customer C','Apr 3 2014 12:00AM','167.4','201.37' UNION ALL

SELECT '6','Tom Swift','Customer C','Jul 4 2014 12:00AM','1932','1480.88' UNION ALL

SELECT '7','Tom Swift','Customer E','May 16 2014 12:00AM','0','240' UNION ALL

SELECT '8','Tom Swift','Customer A','Jul 3 2014 12:00AM','347.8','274.45' UNION ALL

SELECT '9','Tom Swift','Customer G','May 13 2014 12:00AM','189','133.25' UNION ALL

SELECT '10','Tom Swift','Customer J','May 16 2014 12:00AM','11.91','0' UNION ALL

SELECT '11','Frank Hardy','Customer C','Jul 31 2014 12:00AM','379.05','257.94' UNION ALL

SELECT '12','Frank Hardy','Customer B','Jun 11 2014 12:00AM','0','182.33' UNION ALL

SELECT '13','Davy Jones','Customer I','Jul 4 2014 12:00AM','239.5','347.82' UNION ALL

SELECT '14','Tom Swift','Customer L','May 29 2014 12:00AM','1400','0' UNION ALL

SELECT '15','Tom Swift','Customer D','May 5 2014 12:00AM','15696.8','0' UNION ALL

SELECT '16','Tom Swift','Customer C','May 16 2014 12:00AM','669','277.9' UNION ALL

SELECT '17','Tom Swift','Customer N','Mar 14 2014 12:00AM','499','293.7' UNION ALL

SELECT '18','Tom Swift','Customer C','Apr 3 2014 12:00AM','1189.15','528.78' UNION ALL

SELECT '19','Tom Swift','Customer L','Mar 27 2014 12:00AM','656','0' UNION ALL

SELECT '20','Tom Swift','Customer C','May 30 2014 12:00AM','439','282.29' UNION ALL

SELECT '21','Nero Wolfe','Customer C','Mar 19 2014 12:00AM','225','143.91' UNION ALL

SELECT '22','Tom Swift','Customer D','May 8 2014 12:00AM','-1800','0' UNION ALL

SELECT '23','Tom Swift','Customer D','May 21 2014 12:00AM','-3600','0' UNION ALL

SELECT '24','Tom Swift','Customer G','May 16 2014 12:00AM','159','99.89' UNION ALL

SELECT '25','Doc Hudson','Customer A','Jul 31 2014 12:00AM','253.8','200.04' UNION ALL

SELECT '26','Tom Swift','Customer A','Jun 10 2014 12:00AM','347.8','274.45' UNION ALL

SELECT '27','Davy Jones','Customer F','Jul 4 2014 12:00AM','719','278.7' UNION ALL

SELECT '28','Tom Swift','Customer O','Jun 17 2014 12:00AM','340.4','0' UNION ALL

SELECT '29','Barney Rubble','Customer W','Aug 8 2014 12:00AM','0','6' UNION ALL

SELECT '30','Tom Swift','Customer A','Mar 28 2014 12:00AM','369.55','305.07'

SET IDENTITY_INSERT tblSales OFF

pietlinden SSC Guru Points: 63428 More actions · Answer 5

I'm working on it... it's giving me fits getting it exactly right... (Seems it should be easy, but maybe it's not as easy as it looks...) more when I think I have something close.

pietlinden SSC Guru Points: 63428 More actions · Answer 6

I think I have it...

does this look right?

The next trick is going to be figuring out how to save this so it's compatible with SSRS 2008, since I did it in 2012...

jbr14920929 Valued Member Points: 64 More actions · Answer 7

That is very close. The sales rep total line should be the total of the sales not the gross margin. Other than that the report looks like it should. I like it.

pietlinden SSC Guru Points: 63428 More actions · Answer 8

The good news is that I think this is right... the hassle is that I am using SSRS 2012. (No 2012 functions in this at all, though.)

Here's the layout. (There should be a .PNG attachment showing it.)

The tricky part was the sequence of creating rows and columns groups

jbr14920929 Valued Member Points: 64 More actions · Answer 9

It looks correct now.

I have access to 2012 for me to look at the report file and then recreate it in 2008.

pietlinden SSC Guru Points: 63428 More actions · Answer 10

I can upload the RDL file... I based it on a stored procedure

CREATE PROCEDURE [dbo].[JamesSalesSummary]

AS

SELECT

SalespersonRep AS Sales_Person

, CustomerName

, YEAR(InvoiceDate) AS InvoiceYear

, MONTH(InvoiceDate) AS InvoiceMonth

, InvoiceLineAmount

, COGSAmount

, InvoiceLineAmount - COGSAmount AS LineProfit

FROM dbo.SalesData

It would probably be better for performance if you included the InvoiceDate in your dataset and filtered that. (so you don't use a filter on YEAR() which will cause a dreaded table scan).

Here's the 2012 report. The second tablix is just me rebuilding from scratch to see how to do it. Took may 45 seconds. The only hard part is the order in which to create the 3 extra rows (for COGS, Invoice Line Amount, and Line Profit), and then the extra totals around it.

jbr14920929 Valued Member Points: 64 More actions · Answer 11

jbr14920929

Valued Member

Points: 64

September 29, 2014 at 2:03 pm

#1748935

It works just like I wanted it to. Thank you.

pietlinden SSC Guru Points: 63428 More actions · Answer 12

pietlinden

SSC Guru

Points: 63428

September 29, 2014 at 2:08 pm

#1748936

Great! I must be learning!