gregg

sjongenelen Wrote: read the forums :) set the data source timezone to blank Luckily this one came up in search results, doesn't seem this snag that well covered in forums?

Seems to be quite a few views of this post but not much by way of responses... am hoping by adding more detail about the calculations, even if pseudo that it will trigger a reply or comment by someone far more familiar with OLAP and MDX queries. I think one way to possibly solve it is by getting the sum of the measure above, but it is the sum of the measure for each of the dates (days) in the date range for each row in the fact table (i.e. every date between the start date and end date). Struggling to figure out how to do this in MDX thou!! Any help appreciated. Post Edited by gregg at 01/18/2010 23:33

Hi Sherman, Ok, it has been a little longer than I hoped to get back and try this out. (XMas silly season and all ;-) ) What I am not currently seeing is a way to calculate using olap the daily rate (amount/period_days) spread between the start and end dates... Code: Post Edited by gregg at 01/14/2010 16:00

Thanks Sherman. Yeah I did pick up on the calc being wrong but the core problem for me is about getting the amount spread over the time period so didn't bother picking that ;-) I'll take a look at what you have suggested sometime over the next couple of days and post back to the thread here how I get on.

Thanks Spera, I see where you are coming from. I will investigate this potential way of solving it and post up a response in next few days on how I am going.

Thanks Sherman for the prompt reply to this post. You may have noticed I also made a post in the jasper reports forum. I will try to place a link to this entry to avoid duplicate postings - was just trying to ensure I got someones attention ;-). Not sure the above is going to solve what I am perceiving the tricky part of the problem which is spreading the product payment over each calendar day that the product is 'valid' for. I 'think' the above will simply sum all product amounts for a given payment date? Exactly, the end date can easily be calculated in SQL too. So for example on the first example I gave, a £90 product purchased on 01/01/2009 and assume for simplicity that all months have 30 days and the product period is 90 days: The amortised revenue report should show for each calendar day of the valid product period the total (sum) of the amount/period for all products whose period covered that calendar day. A similar calculation would need to be done for all products / periods / amounts and the final report calculation that is wanted is like so: 01/01/2009 | £1.00 (for example just 1 product/period/amount valid on this date) 02/01/2009 | £1.00 (still just the one product/period/amount valid on this date) ... 05/02/2009 | £3.00 (for example identical 3 products(period/amount) spread over this date too) ... 30/03/2009 | £3.00 (still just the 3 products) ... 01/04/2009 | £2.00 (now just 2 products spread payment over this date as the first product amount was spread over 01/01/2009 to 30/03/2009) ... etc

The problem in a nutshell: I work for an online service provider whose products allow access for a customer for a set period of time. I can get sql results for our product purchases which comprise the following information: product id, purchase amount, product period (days), date of purchase What the finance team want is an amortised financial report whereby for each calendar day, the total amount of the all products active over that period. So for example a product for 3 months, for simplicities sake say 90 days. If the product cost £90 then the amortised amount is £1 per day from the start and end date of that product subscription. If there were two identical products with same start and end date period then for that 3 month period, the daily amortized amount would be £2. What is wanted is a total amount for each calendar day based on the above calculation. Just using the community edition jasper server and ireports 3.5 at this point in time (shouldn't be a hinderance to being able to develop a solution though I hope). Thanks in advance for any suggestions. EDIT: DUPLICATE ACTIVE POST IS located jasperforge.org/plugins/espforum/view.php Post Edited by gregg at 12/02/2009 09:03 Post Edited by gregg at 12/02/2009 09:04

The problem in a nutshell: Work for an online service provider whose products allow access for a customer for a set period of time. I can get sql results for our product purchases which comprise the following information: product id, purchase amount, product period (days) What the finance team want is an amortised financial report whereby for each calendar day, the total amount of the all products active over that period. So for example a product for 3 months, for simplicities sake say 90 days. If the product cost £90 then the amortised amount is £1 per day from the start and end date of that product subscription. If there were two identical products with same period end and start dates then for that 3 month period, the daily amortized amount would be £2. What is wanted is a total amount for each calendar day based on the above calculation. It would not seem like it's that easy to do in SQL/ireport/JasperServer but I thought maybe there is a neat and easy way to do this type of thing in JasperAnalysis/JasperServer? (If anyone can think of a way in the former then feel free to add your thoughts of course). Just using the community edition 3.5 at this point in time (shouldn't be a hinderance to being able to develop a solution though I hope). Thanks in advance for any suggestions.