Sales figues by Year... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Sales figues by Year...

Author

Topic

steve_c
Yak Posting Veteran

54 Posts

Posted - 2008-02-18 : 19:22:44

Hi all :) I'm getting on with SSRS really well but am a bit confused as to go about the following report and hoping someone could help!

I have to design a sales figures report. Each client has a salespersonid and each order has an order value. I need to produce a sales figures report for a financial year. I have the start and end date of the financial year stored as values in a config table. What I need to produce is a report with months as columns and clients as rows, like this:

Client April May June
Cli A £100 £120 £0
Cli B £120 £0 £200

So the report is broken down by clients and months and the orders in that month totalled up.

Any pointers in how best to do this?

Thanks in advance as usual :)

Stephen.

tprupsis
Yak Posting Veteran

88 Posts

Posted - 2008-02-18 : 22:17:30

Looks like a straightforward crosstab report. For your datasource, just select your client, month of the order date (using your date range in the config table), and the sum of the order value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-19 : 00:15:11

Use a matrix in SSRS. Select client field as row group and month of date field as column group and matrix will build the above output for you. In data portion take SUM(SalesFigurefield) from dataset.Provide appropriate format specifiers to get required o/p.

steve_c
Yak Posting Veteran

54 Posts

Posted - 2008-02-20 : 12:44:37

Hi sorry I still don't know how to do this. I've been trying for 2 days now :(

I need to produce a sales figure report for a financial year. The financial year runs from 01-apr to 31-mar. This start and finish date is stored in my config table.

Say I need to generate a sales report for financial year 2008. This runs from 1st Apr 2007 to 31st Mar 2008. I need all months, even if there are no invoices generated in that month for that salesperson but only totals from clients which have been invoiced in each month.

I have:
Invoices
invoiceid int
salesperson int
invoicetotal decimal(19,2)
invoicedate datetime
clientid int

Clients
clientid int
clientname varchar(150)

I need a report which has each month along the top, as columns from Apr > Mar. Each row should be a client.

I really need help on this as I can't find any other pointers :(

Thanks in advance all,

Stephen.

EDIT:
I've added a table called months which has 12 months in, with real month id and also financial year month id. With the following query it nearly works:

SELECT     months.month, ISNULL(invoices.cinvoicesubtotal, 0) AS cinvoicesubtotal, ISNULL(invoices.clientid, 0) AS clientid, ISNULL(clients.clientname, '') 
                      AS clientname
FROM         invoices INNER JOIN
                      clients ON invoices.clientid = clients.clientid RIGHT OUTER JOIN
                      months ON MONTH(invoices.invoicedate) = months.realmonthid

but once I add an invoice date range, the empty months do not show up...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-20 : 12:57:17

try like this, you need pass startdate & enddate as params from report:-

SELECT m.clientname,[April],[May],...,[March]
FROM
(
SELECT c.clientname,i.invoicedate,i.invoicetotal
FROM Invoices i
INNER JOIN Clients c
ON c.clientid=i.clientid
WHERE i.invoicedate BETWEEN @startdate AND @enddate
)m
PIVOT
(
SUM(invoicetotal) 
FOR MONTHNAME(invoicedate) 
IN ([April],[May],...))p

steve_c
Yak Posting Veteran

54 Posts

Posted - 2008-02-20 : 13:00:14

quote:

Originally posted by visakh16

try like this, you need pass startdate & enddate as params from report:-

SELECT m.clientname,[April],[May],...,[March]
FROM
(
SELECT c.clientname,i.invoicedate,i.invoicetotal
FROM Invoices i
INNER JOIN Clients c
ON c.clientid=i.clientid
WHERE i.invoicedate BETWEEN @startdate AND @enddate
)m
PIVOT
(
SUM(invoicetotal) 
FOR MONTHNAME(invoicedate) 
IN ([April],[May],...))p

Thanks will give that a try when I get home - only thing with that is the financial years are hard coded - if I ended up distributing the app to another company in another part of the world which used a diff financial year, it would be a problem...

steve_c
Yak Posting Veteran

54 Posts

Posted - 2008-02-20 : 18:11:02

Hi - sorry no joy :(

Have tried the following:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[rptsalesfiguresforemployee_clients] 
	-- Add the parameters for the stored procedure here
@startdate datetime,
@enddate datetime
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT m.clientname,
[April],
[May],
[June],
[July],
[August],
[September],
[October],
[November],
[December],
[January],
[February],
[March]
FROM
(
SELECT c.clientname,i.invoicedate,i.invoicetotal
FROM Invoices i
INNER JOIN Clients c
ON c.clientid=i.clientid
WHERE i.invoicedate BETWEEN @startdate AND @enddate
)m
PIVOT
(
SUM(invoicetotal)
FOR 
MONTHNAME(invoicedate)
IN (
[April],
[May],
[June],
[July],
[August],
[September],
[October],
[November],
[December],
[January],
[February],
[March]
))p
END

I get:

Msg 102, Level 15, State 1, Procedure rptsalesfiguresforemployee_clients, Line 43
Incorrect syntax near '('

It's not really the way I wanted to do it anyway as the financial year period is hard coded into the query :( I'm pretty stuck now. I'd be really grateful for any more input!

Stephen.

steve_c
Yak Posting Veteran

54 Posts

Posted - 2008-02-21 : 06:51:31

OK I think I am nearly there! Just need someone to help me finish it off! I have added a months table with monthid, realmonthid, monthname. April being the first month of the financial year, it would have monthid 1, realmonthid 4. I join this table to the invoices table on the month part of the invoice date. I set the datefrom and dateto to the beginning and end of the financial year in question. I have the following query now:

SELECT
months.monthid,
months.month,
months.realmonthid,
SUM(invoices.cinvoicesubtotal) AS Total,
clients.clientname,
invoices.invoicedate

FROM

months
INNER JOIN
invoices ON months.realmonthid = MONTH(invoices.invoicedate)
INNER JOIN
clients ON invoices.clientid = clients.clientid
WHERE invoices.invoicedate BETWEEN '01-apr-2007' AND '31-mar-2008'

GROUP BY
months.monthid,
months.month,
months.realmonthid,
clients.clientname,
invoices.invoicedate

ORDER BY months.monthid

This works but only brings up the months where invoices have been placed - how would I get all of the other months to display properly?

tprupsis
Yak Posting Veteran

88 Posts

Posted - 2008-02-21 : 12:32:52

Try a UNION with the second query pulling a list of all records from months WHERE NOT EXISTS something in the invoices table. For example:

UNION
SELECT
months.monthid,
months.month,
months.realmonthid,
0 AS Total,
'',
NULL
FROM months
WHERE NOT EXISTS
(
SELECT *
FROM invoices
WHERE months.realmonthid = MONTH(invoices.invoicedate)
AND invoices.invoicedate BETWEEN '01-apr-2007' AND '31-mar-2008'
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources