Current Month Function

Question

Post reply

Current Month Function

Tek

SSCommitted

Points: 1697
More actions
December 9, 2010 at 11:44 am

#244078

I've been asked to create a report that display AMOUNT totals by year. So I created a Matrix table and used the Year(WorkDate) function. The format and all is great. I also added totals to the row and column for kicks.
However, I'm wanting to create a column that displays the SUM Amount of the current month, for instance, this month December. I cant create a new column within the column group because then it will create a new column for each year.
So I figure I create a column next to the TOTAL column. That way, I only get one column.
For the cell, I cant type SUM(Amount) because that will give a total of everything in the database.
How can I sum up the amount for only the current month. When user use the report again in January and so on, it should sum up only for the current month.
Hope all this was clear.
Thanks for any help.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Revenant SSC-Forever Points: 42467 More actions · Answer 1

I am afraid I am not getting it.

Could you post the code you are describing? (Both the DDL and DML that you have.)

Thanks.

Tek SSCommitted Points: 1697 More actions · Answer 2

Here's my Query for the Matrix tablix :

SELECT WorkDate, TimecardCode, Hours, Amount, Code, ClientName, Inception, YEAR(WorkDate) AS WorkYear, Sub_Code

FROM VIEW_InvoiceSummary

WHERE (YEAR(WorkDate) BETWEEN @startyear AND @endyear) AND (Code = 26)

When the report is ran, it looks like this:

--------|-- 2007 -- 2008 -- 2009 -- 2010 -- |-- Current Month -- Years Total

Client 1 ---- |-- $10 -- $10 -- $10 -- $10--| ----------------- $xxx

Client 2 ---- |-- $10 -- $10 -- $10 -- $10--| ----------------- $xxx

Client 3 ---- |-- $10 -- $10 -- $10 -- $10--| ----------------- $xxx

So this format is create. But it's the CURRENT MONTH column is what I'm trying to get. Under Current Month, I should only get the total for the current month of December.

i assumed I could add in the query, MONTH(WorkDate) as Month, and then use a SUM(Amount) in the cell. That doesnt work.

Revenant SSC-Forever Points: 42467 More actions · Answer 3

This seems incomplete: is it the report that is doing the aggregations and pivoting?

Revenant SSC-Forever Points: 42467 More actions · Answer 4

I have to get into meetings for the rest of the day, so I will take a stab based on what I think I know.

I suggest that you add a new clause to your SELECT:

SELECT ...,

CurrentMonth =

CASE

WHEN YEAR(WorkDate) = YEAR(getdate()) AND MONTH(WorkDate) = MONTH(getdate())

THEN Amount

ELSE 0

This will select Amount only if WorkDate is within the current year and month. You can then aggregate these values in the query that feeds your report.

Tek SSCommitted Points: 1697 More actions · Answer 5

Revenant (12/9/2010)
I have to get into meetings for the rest of the day, so I will take a stab based on what I think I know.
I suggest that you add a new clause to your SELECT:
SELECT ...,
CurrentMonth =
CASE
WHEN YEAR(WorkDate) = YEAR(getdate()) AND MONTH(WorkDate) = MONTH(getdate())
THEN Amount
ELSE 0
This will select Amount only if WorkDate is within the current year and month. You can then aggregate these values in the query that feeds your report.

AWESOME! Thats what I was looking for. MUCH THANKS!!!!