Differing date ranges per record 1

[gbuchman1]

Hi,
I am doing a report which shows the totals of grants which were given within a fiscal year. However the grant years do not coincide with the fiscal year, and I must show how much posted to the fiscal year that is WITHIN the grant period (these amts are attributed to their respective grants). For example, the fiscal year(FY) is 9/1/08 to 8/31/09, and the two grant periods that fall within the fiscal year are 10/1/07 thru 9/30/08 (GP1) and 10/1/08 thru 9/30/09 (GP2). As such, I need to show how much posted within the FY for each grant period. Specifically, I must show how much posted during 9/1/08 thru 9/30/08 for GP1 and how much posted during 10/1/08 thru 8/31/09 for GP2. I can easily get the total of these two on one line, but I cannot get the total split by grant period, one amount on each line, as shown above. So I not only have a date range in the record selection, but I also have a subdate range per record. And there are other grant periods that have different date ranges as well. Would anyone know how to do this? I am thinking that I could add a date range to the SQL expression I am using to select the dates, but am having problems doing this. Any help will be greatly appreciated, and I have Crystal 11. Thanks in advance!

 

[lbass]

We need an idea of how your fields display. For example, is there a grant period start date and a grant period end date? Or? Are the amounts that you have to distribute per date so they can be easily attributed to one fiscal year or another?

-LB

 

[gbuchman1]

Thanks for your reply, LB. Your second thought is true - the amounts that I have to distribute are per date and as such they can be easily attributed to grant periods. I have a record selection that only includes the transactions that posted in one fiscal year.

More details:
The records I am accessing are posted transactions. Each transaction has a GL account number, a posted date, and an amount. I have created several SQL expressions which identify which GL accounts constitute which grants. The tricky part however is that the grant period is not identified on the transaction. SO I look to the posted date to see which grant period the receipt posted in. (See above for my example of this). From there I could identify how much is affiliated with each grant period.

Here is an example of part of a SQL expression in which I am trying to split apart the $ amt which posted into its 2 grant periods (part of each of these periods fell within the fiscal year):

WHEN (tblGLBLBalance.sCodeIDf_0 = '335005' AND tblGLBLBalance.sCodeIDf_1 IN ('0279','0946') AND
tblGLBLBalance.sCodeIDf_2 = '0045' AND (tblGLBLBalance.dtmPostTo in dateserial(year({?Start Date}),
month({?Start Date}),day({?Start Date})) to dateserial(year({?End Date}),month(6),day(30)))
THEN ' 19.21 Century Community Learning Centers - prior grant period'
WHEN (tblGLBLBalance.sCodeIDf_0 = '335005' AND tblGLBLBalance.sCodeIDf_1 IN ('0279','0946') AND
tblGLBLBalance.sCodeIDf_2 = '0045' AND (tblGLBLBalance.dtmPostTo in dateserial(year({?End Date}),
month(7),day(1)) to dateserial(year({?End Date}),month({?End Date}),day({?End Date}))))
THEN ' 20.21 Century Community Learning Centers - current grant period'

 

[lbass]

First, this isn't a SQL expression, as SQL expressions do not accept parameters, so this must be a command. I don't see why you are trying to do this within the command versus in a formula after the command returns data. You could do this within a formula:

if codes = this grant then
(
if {table.date} < date(2010,10,1) then
"This grant - Prior Period" else
if {table.date} >= date(2010,10,1) then
"This grant - Current Period"
) else
if codes = a different grant then
(
if {table.date} < date(2011,1,1) then
"a different grant - Prior Period" else
if {table.date} >= date(2011,1,1) then
"a different grant - Current Period"
) else
//etc.

-LB

 

[gbuchman1]

Thanks LB. This will work for the amounts, but I also have one other situation. It has to do with the labeling of the lines. Basically it is that I want to have two lines for the same grant - one line for last year's grant amount and a second line for this year's grant amount. Both of these amounts must fall within the fiscal year, since that is my record selection. So with this in mind, is there a way that I can do this same formula within a SQL expression? I already have a SQL expression that lists each grant, and works great but it gives just one line per grant. But because part of the prior grant period AND part of the current grant period both fall with the fiscal year, I need to have two lines for many of the grants. So I just want to add this date restriction to the SQL expression that I already have.
The alternative is to have two columns, one for each grant period for the same grant. In that case, I can use the formula that you shared above.

 

[lbass]

The formula I suggested WOULD result in different lines. Assuming you have an outer group on the grant, then insert a group on the suggested formula.

SQL expressions only return one value or at least one value per execution of the expression, and you can't use parameters, so again, I'm wondering whether you are instead using a command.

-LB

 

[gbuchman1]

Ok, now I understand. I will insert a group on the suggested formula and try that. Will let you know how it turns out. Thank you!

PS. The SQL expression that I use for the groups does not include parameters. What I showed in this thread was just a proposed addition to the SQL expression. I was trying to use a parameter on this to get the date ranges, and had forgotten that parameters are not allowed in SQL expressions.

 

[gbuchman1]

I tried this formula and it is working beautifully! LB, you made my day...and weekend. Thanks so very much!