List of months depending on lease term

[BHScripter]

Hi:

I need to list a payment schedule for a quote sheet for a potential lessee. My SQL query returns the lease month term as an integer which could be 6, 12, 4, etc. It also returns the proposed lease start date, lease end date, and the monthly rent being quoted. Within my report, it is easy to get to the payment schedule for the first month and the last month since they are named months within the query. However, I need to somehow list the the months in between in #'s such as month 2, month 3, etc. with the monthly payment (the monthly quoted rate). I want it to work so that only the correct # of months show up based on the quoted lease term. I was thinking some sort of crosstab but not sure how to set up the formula to get the month's in between the start and end month to show up in the crosstab.

Thanks for any help.

 

[RTag]

What is the database type ?

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[BHScripter]

SQL Server 2008

 

[beacon5]

Hi Brianna,

You can do one of two things:

1) If the months are static and you only need 12, then you can create a formula for each month and use this as a column header. You can use the DateAdd function and a date parameter to change the values of the formula. This is a type of manual cross tab.

2) You can go the traditional cross tab route, but you'll need a data source that lists all the months or dates you want to use (assuming, of course that you want columns to display even if there is no data). Once you have this setup, you can use this data source as the column and use a formula to increment some value based on how you want to handle the leases.

Hope this helps,
beacon

 

[BHScripter]

Hi:
yes, I believe I will have to go the manula route as the lease could be varying # of months. I will have to create up to 18 objects and then use dateadd and then check to see if it should display.

 

[beacon5]

That's a pain, but it's usually the most reliable...at least, that's what I've found.

I recently had to do this same thing, but had to create 200 formulas for a weekly class schedule with variable output. It's a great looking report, but it took forever to setup initially and I hope I never have to update it.

Unfortunately, I don't have a web server to upload to...otherwise, I'd post that report for you to use and save you some time.

 

[BHScripter]

Thanks much! Just checking that I wasn't making it more difficult than it should be.

 

[beacon5]

Nope...you're making it just the right amount of difficult! =)

 

[RTag]

In SQLServer 2008 you can easily generate a list of dates and join it with your data. Please check the function fnGetDatesInPeriod in this example:

http://r-tag.com/ListOfDates.aspx.

It is for days, and you need it for months, so replace DATEADD(DAY, 1, CurrentDate) with DATEADD(mm, 1, CurrentDate).

You will need to use a command or a stored procedure, but the report will be simple.

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.