April 7, 2010 at 6:46 am
Hello all, still learning here so please bear with me.
I am attempting to build an AR aging report through SSRS. I have the base report which includes
INV_NBR
CUST_NBR
CUST_NAME
INVC_DT
BAL
There is one parameter "date" that the user inputs for their aging date.
My first expression column is AGE:
=DateDiff("d",Fields!INVC_DT.Value,Parameters!date.Value)
Second expression column is "Current" (less than 31 days, if the expression is true it plugs in the "BAL" -balance - under this column)
=IIF(DateDiff("d",Fields!INVC_DT.Value, Parameters!date.Value)<31,Fields!BAL.Value,"")
But when I get into the next buckets (31 to 60 days, 91-120 days) I can't figure it out. I need it to calculate the date difference, determine if it is Greater than or equal to 31 AND less than or equal to 60, two conditions. I worked on this for 4 hours, and I'm stumped.
The last bucket (Over 180 days is not problem because it's one condition).
Any ideas?
SQL2000, btw.
Thanks for the help!
Tammy
April 7, 2010 at 2:59 pm
I think, I have not tried this recently, you can do a calculated field for age in the dataset like you are doing in the report, then you can reference that field in each of cells in the report. Makes it all simpler. So your cellsl in the report would be
Age: =Fields!Age.Value
Current: =IIF(Fields!Age.Value < 31, Fields!Bal.Value, "")
31-60: =IIF(Fields!Age.Value >= 31 and Fields!Age.Value <= 60, Fields!Bal.Value, "")
>60: =IIF(Fields!Age.Value > 60, Fields!Bal.Value, "")
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 8, 2010 at 12:49 pm
Perfect! I never even considered the dataset.
Worked like a charm!
I can't thank you enough.
Tammy 😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply