January 1, 2007 at 5:48 pm
I am writing a report for a client that has a membership database, and he wants a report t show a month by month report of new and re-newing members for the current year and last year.
ie.
Store: StoreName
Jan 07 - New Members: 1, Jan 06 - New Members: 54
Feb 07 - New Members: 0, Fab 06 - New Members: 34
This I can do just fine. But the help I need is for the totals.
When I do the totals for the "last year" new members, its obviously not very helpful to him because its counting all months of last year (which will have data), but the totals for this year (2007) will not have any data in it at all for the comming months (obviously).
How do I do a SUM function to onyl SUM up the data upto the current month of this year?
ie. if the report was run today (2007/01/02) then I would get a SUM of all new members in Jan 07 and (in another column) a SUM of all new members in Jan 06 (and NOT Feb 06, March 06 etc..)
Please any help would be greatly appreciated
January 4, 2007 at 8:00 am
This was removed by the editor as SPAM
January 4, 2007 at 9:18 pm
Hi Mark. I'm no expert but this may be of some use. I would not try to do the sum function in your report; I would include what you require in your dataset. You could try something similar to the logic used in the sample reports from Microsoft: SQL Server 2000 Report Pack for Financial Reporting, which is available from http://www.microsoft.com/downloads/details.aspx?familyid=1D416069-FFB1-44D4-A20D-C89A1D5EA791&displaylang=en
The following is part of the query for an income statement dataset:
CASE
WHEN fiscal_period = @CurPeriod and normal_bal = 1 THEN per_net_amount
WHEN fiscal_period = @CurPeriod and normal_bal = 2 THEN per_net_amount*-1
ELSE 0 END AS CurPeriodAmt,
CASE
WHEN normal_bal = 1 THEN per_net_amount
ELSE per_net_amount * -1
END AS YTDAmt
FROM vIncomeStatement
WHERE (fiscal_period BETWEEN 1 AND @CurPeriod) AND (fiscal_year = @FiscalYear)
Certainly by using the fiscal_period between 1 and the current month you would be able to stop getting the whole of last year amounts.
Regards,
Mark Finnie
PS. If you know how to get negative numbers in brackets to line up with positive numbers please reply to my post of January 2, 2007.
January 7, 2007 at 2:58 pm
Thanks for the Reply Mark,
I agree that my SQL has to change I suppose, I wil let you know how it goes and how I solved it.
Cheers!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply