Monthly Columns

[berks321]

I am trying to report on "Net Amount" for Account Number by month. The report always starts on the 1st day of the year, then I need each month to show the running "Net Amount" to the end day of that month. i.e. January would be a total Net Amount for that one month, the February column would be January + February, the March column would be January + February + March etc.

I also have a filter in place to ensure that only the results required to the selected end date are shown.

I have tried the below for each monthly column but it is showing the total for the whole, accumulated selected period not just the monthly period (in the below case it should be for January only):
case
when to_date([Physical Layer].[(D_CAL)].[CAL_KEY],'YYYYMMDD' ) < to_date(cast(cast(extract(year, current_date),varchar(4)) || '0201', varchar(8 )),'YYYYMMDD')
and ?pCurrencyType? = 'Functional' then
[Physical Layer].[F_ACCOUNT].[NET_AMOUNT_FUNC]
when to_date([Physical Layer].[Transaction Date (D_CAL)].[CAL_KEY],'YYYYMMDD' ) < to_date(cast(cast(extract(year, current_date),varchar(4)) || '0201', varchar(8 )),'YYYYMMDD')
and ?pCurrencyType? = 'Transactional' then
[Physical Layer].[F_ACCOUNT].[NET_AMOUNT_TX]
else 0
end

Is this possible in one query or am I going to need 12 queries - one for each month to achieve this?

[berks321]

Basically, I'd like to have advice on whether I can do some sort of for statement on the below line:
[Physical Layer].[F_ACCOUNT].[NET_AMOUNT_FUNC]

[navissar]

How's about this:

running-total(
#/*january*/#
case when extract(month,[date])=1 and extract(day,[date])=31 then [NetAmount]
#/*February - special leap year treatment*/#
when (extract(month,[date])=2 and extract(day,[date])=28  and
(mod(extract(year,[date]),4)>0 or (mod(extract(year,[date]),100)=0 and mod(extract(year,[date]),400)>0) 
)OR
(extract(month,[date])=2 and extract(day,[date])=29  and
(mod(extract(year,[date]),4)=0 and (mod(extract(year,[date]),100)>0 OR mod(extract(year,[date]),400)=0)  )
then [NetAmount]
#/*Let me explain that bit. A leap year happens when a year is divided by four. However, if a year is divided by 100 (like 1900) but not by 400 then it is not a leap year*/#
#/*March*/#
when extract(month,[date])=3 and extract(day,[date])=31 then [NetAmount]
.
.
.
.
.
else 0 end
)

[berks321]

Thank you for your reply but I'm not sure that will give me the separate columns/data items that I need for the final output. I have tested my date logic separately and it is working correctly so that isn't the issue. Do you have any other suggestions?

[navissar]

How can I have any other suggestions when you have given me no new information, and didn't even explain why it was you rejected my first suggestion.

[BigChris]

I haven't been all the way through your calculation...it looks rather like the data is stored as a string rather than a date. Anyway, could you manipulate your calculations so that you end up with something like:
January - total(if(month([YourDate]) <=1 and ?pCurrencyType? = 'Transactional') then ([Physical Layer].[F_ACCOUNT].[NET_AMOUNT_TX]) else (0))
February - total(if(month([YourDate]) <=2 and ?pCurrencyType? = 'Transactional') then ([Physical Layer].[F_ACCOUNT].[NET_AMOUNT_TX]) else (0))
etc...