Looping Through a Table 1

[dnfrantum]

I am not sure what function to use in this scenario...
I have a table of dates and amounts, e.g. OCT-07, $25.00
I have a column header of dates, e.g. OCT-08. I am trying to sum by group the amounts, but the following formula doesn't work because it returns a false on the first record in the table...
IF {@DATE} = {Table.ROWDATE} THEN
{Table.Amount}

I get $0.00 returned for this formula. I evaluated the {table.ROWDATE} and it is returning OCT-07 and the Column Header is OCT-08.

I suspect I have to either loop through or use the WHILEREADING or WHILEPRINTING, but I am not familiar with any of the above in CR.

I am using CR XI. Any help or advice is always appreciated.

Thanks in advance,
Donald

 

[MCuthill]

Donald,

Perhaps I am misunderstanding your question, but I beleive to acheive the result above that you need only Group your report on the Date Field and create a Summary (of "Sum" type) on the Amount Field.

For example, if there were 2 transactions for October 31st, 2008; it would display as follows:

....GH1: 31-OCT-08 ... $100.00
Details: 31-OCT-08 ... $75.00
Details: 31-OCT-08 ... $25.00

If you want to be grouped on a month or a week (instead of an individual day/record value), I beleive you can set the scopre of the group in the group options (I use CR10, not XI)

Hope this helps!

Mike
--------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure, in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."
Author R.A. Salvatore via "Drizz't"

 

[dnfrantum]

I should mention that this is a manual cross tab and that the dates are the column headers.

Thanks in advance,
Donald

 

[dnfrantum]

That returns the correct data, but I need to columnize it. Any ideas?

Thanks in advance,
Donald

 

[MCuthill]

Ah, thanks for the clarification Donald.

I think you were on the right path, but perhaps need to ensure you are comparing apples to apples (so-to-speak). Perhaps the Database Field is a Date/Time and the @Date is just a date field?

Perhaps something to the effect of:
IF Date({@DATE}) = Date({Table.ROWDATE}) THEN {Table.Amount} ELSE 0

I am unsure what else may help, as I am unfamiliar with a manual crosstab (though I can assume what one is).

Is this the same problem you have inquired about in the other posting regarding CrossTab's today?

Hope this helps!

Mike
--------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure, in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."
Author R.A. Salvatore via "Drizz't"

 

[dnfrantum]

Thanks Mike. It is kicking my butt. Hopefully, the Tek-Tips calvary will come to the rescue.

Thanks in advance,
Donald

 

[lbass]

If the column header is Oct-08, why would you expect it to match a row with Oct-07? The point of the conditional formula is to check for matches. You need to insert a sum on the formula at the group level, and then suppress the detail section. Only those records that match Oct-08 will contribute to the sum.

-LB

 

[dnfrantum]

I don't expect it to match, but the debit-credit in the following formula:

//{@startperiod} = parameter period with correct formatting applied
//{@totextperiod} = {table.period} with correct formatting applied

IF {@startperiod} = {@totextperiod} THEN
{table.debit} - {table.credit}

returns $0.00; should return $756,234.00

Thanks in advance,
Donald

 

[lbass]

The formula is for one row. Are debits and credits posted in the same row? Change the formula to

IF {@startperiod} = {@totextperiod} THEN
{table.debit}

Create a second formula for credit, and a third for the calculation:

sum({@debit},{table.groupfield})-sum({@credit},{table.groupfield})

I think you should show some sample (multiple rows) of data at the detail level so we can see what your data is like.

-LB

 

[dnfrantum]

I am not sure how long you want this to be, but here goes:

//SQL Query in use:
Select DISTINCT [Group] = CASE substring(T0.Segment_0,1,1)
                          WHEN '4' THEN 'INCOME'
                          WHEN '6' THEN 'EXPENSE'
                          WHEN '7' THEN 'OTHER INCOME'
                          WHEN '8' THEN 'OTHER EXPENSE'
                          WHEN '9' THEN 'OTHER EXPENSE'
                          ELSE NULL
                          END,T0.FatherNum as [Title Acct],T0.Segment_0 as [Natural Acct],T1.SYSDeb as [Debit], 
T1.SYSCred as [Credit],T1.RefDate,T2.Code 
from OACT T0 RIGHT JOIN JDT1 T1 ON T0.AcctCode = T1.Account
             INNER JOIN OFPR T2 ON T1.FinncPriod = T2.AbsEntry
WHERE T0.GroupMask in ( '4','6','7','8','9') 
ORDER BY T0.Segment_0,T1.RefDate

//Parameters:
StartPeriod (STRING) = Query T2.Code
EndPeriod (STRING) = Query T2.Code

//Formula for Column Header(s):
//Step 1 - Convert to T2.Code to Date
stringvar x := {Command.Code};
date(val(left(x,4)),val(mid(x,6,2)),1)
//Step 2 - Conditional Select
IF ({@DateFormat}>={@DateFormatStartPeriod} AND {@DateFormat}<={@DateFormatEndPeriod})
THEN {@DateFormat}

//Formula for Amount:
IF {Command.Group} = "INCOME" THEN
{Command.Debit}-{Command.Credit}

//Output:





Thanks in advance,
Donald

 

[dnfrantum]

Sorry, the file didn't attach, I will try again. I also didn't say what was wrong. So far, the only problem with this version is that the first column is coming from an unknown destination and I am not sure how to remove it.

I have also repeated this approach as separate page headers for expense, other income, and other expense with the intention of creating formulas to arrive at the sum of income and expenses, other income and other expenses, and net profit.

Thanks in advance,
Donald

 

[dnfrantum]

Okay, how else can I post this file to provide an example? The cut and paste will mess up the columns, but that may be my only option:

Jan-09 Feb-09 Mar-09 Apr-09
May-09 Jun-09 Jul-09

GROSS
PROFIT
$0.00 ($1,966,962.23) ($3,423,953.78)
($3,069,253.95) ($3,198,678.48) ($4,229,619.51)
($3,737,636.77) $0.00

INCOME
$0.00 ($1,966,962.23) ($3,423,953.78)
($3,069,253.95) ($3,198,678.48) ($4,229,619.51)
($3,737,636.77) $0.00

Income-
Service $0.00
($1,966,962.23) ($3,423,953.78) ($3,069,253.95)
($3,198,678.48) ($4,229,619.51) ($3,737,636.77)
$0.00

4111
$0.00 ($1,966,962.23) ($3,423,953.78)
($3,069,253.95) ($3,198,678.48) ($4,229,619.51)
($3,737,636.77) $0.00




Thanks in advance,
Donald

 

[dnfrantum]

Can anyone help with this? I just need to figure out how to eliminate the errant column.

Thanks in advance,
Donald

 

[lbass]

I don't understand what I'm seeing here. Why are the numbers the same for each row? How did you arrive at these numbers--did you insert summaries on your amount field at the various group levelsl?

Regarding your "errant" column, do you understand why it is returning zeros? If this is a manual crosstab as you said earlier, then you need to check the detail level formula for that column and perhaps show it here. How does it relate to your record selection formula? Could it be zero because you haven't selected the correct date range?

-LB

 

[dnfrantum]

Sorry LB, I am kinda all over the place. I have been trying both approaches manual and regular crosstabs to get my report written. The data above is referencing the regular Crosstab. What I did was create 4 separate crosstabs on the same report: 1 for INCOME, one for EXPENSES, one for OTHER INCOME and one for OTHER EXPENSES. The reason you are seeing the same amounts at each level is that there is currently only one account that has been populated under INCOME. The first column came about afer I added the formulas for step 2 of column header select

//Step 2 - Conditional Select
IF ({@DateFormat}>={@DateFormatStartPeriod} AND {@DateFormat}<={@DateFormatEndPeriod})
THEN {@DateFormat}

I think if I could eliminate that column and get a subtotal for INCOME crosstab total - EXPENSES crosstab total, I would be set. Everything else about the report seems correct.

Thanks in advance,
Donald

 

[lbass]

You should remove your step 2 formula. All you need to do is add your Step 1 formula as your first column field and while it is highlighted, choose "group options" and choose "on change of month".

-LB

 

[dnfrantum]

One problem begets the next. If I remove the second formula, I have eliminated the incorporation of my parameters. I don't get the errant first column, but I do return all of the periods from the inception of the database. Do you have another method of incorporating the period parameters to limit the records returned?



Thanks in advance,
Donald

 

[lbass]

The parameters belong in the record selection formula: report->selection formula->record->enter:

{@DateFormat}>={@DateFormatStartPeriod} AND
{@DateFormat}<={@DateFormatEndPeriod}

-LB

 

[dnfrantum]

You are correct, I arrived at that about an hour ago. Also, I figured out how to get the summary data at the bottom. I created two additional cross tabs and populated the column with the date formula above and populated the summary with a formula that adds the totalIncome and totalExpense.

You were a tremendous help throughout. Thank you very much for your dedication.

Thanks in advance,
Donald