Could this be another issue with cfoutput "group"?

Solved

Posted on 2012-08-29

I have a report that shows the amount deducted from each purchase order for insurance purposes. Each PO has multiple line items and right now my report is only factoring in the first line item for each PO so the amount that is showing as being deducted is not correct. So in my query, I have two records that are returned for Voucher#22520, one for each line item. Right now the first line item is the one being reported on. Do I need to find a way to group by Voucher in order to get the Sum of both line items so I can report correctly on the deduction?

13 Comments

Could this be another issue with cfoutput "group"?

```
<cfoutput query="AmountToPayOnNextCheck" startRow="#StartRow_AmountToPayOnNextCheck#" maxRows="#MaxRows_AmountToPayOnNextCheck#" group="VendCode" groupcasesensitive="true">
<cfset VARIABLES.companyTotal = 0 />
<cfset totalWrapAmount = 0 />
<cfset totalCheckAmount = 0 />
<cfoutput group="PONo">
<cfset VARIABLES.total = 0 />
<cfoutput>
<cfset VARIABLES.total = variables.total + AmountToPayOnNextCheck.TotalAmt />
</cfoutput>
<cfset VARIABLES.companyTotal = VARIABLES.companyTotal + VARIABLES.total />
<cfset totalWrapAmount = totalWrapAmount + val(AmountToPayOnNextCheck.WrapAmount)>
<tr>
<td width="10%" bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2) eq 0>FFFFFF<cfelse>F0F0F0</cfif>" align="center"><input type="checkbox" name="po_#POID#" value="#POID#" /></td>
<td bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2) eq 0>FFFFFF<cfelse>F0F0F0</cfif>" align="left"><span class="con">#AmountToPayOnNextCheck.VendCode#</span></td>
<td bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2) eq 0>FFFFFF<cfelse>F0F0F0</cfif>" align="center"><span class="con">#AmountToPayOnNextCheck.RuleID# - #AmountToPayOnNextCheck.PONo#</span></td>
<td bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2) eq 0>FFFFFF<cfelse>F0F0F0</cfif>" align="right"><span class="con">#dollarformat(VARIABLES.total)#</span></td>
<td bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2) eq 0>FFFFFF<cfelse>F0F0F0</cfif>" align="center"><span class="wrap">#dollarformat (AmountToPayOnNextCheck.WrapAmount)#</span></td>
<td bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2) eq 0>FFFFFF<cfelse>F0F0F0</cfif>" align="center"><span class="con">#AmountToPayOnNextCheck.projCode#</span></td>
<td width="10%" bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2 eq 0)>FFFFFF<cfelse>F0F0F0</cfif>" align="center"><span class="con">#AmountToPayOnNextCheck.Lot#</span></td>
<td width="10%" bgcolor="##<cfif (VARIABLES.temp_po_row_count MOD 2) eq 0>FFFFFF<cfelse>F0F0F0</cfif>" align="center"><span class="con">#dateformat(AmountToPayOnNextCheck.DueDate,"mm/dd/yy")#</span></td>
</tr>
<cfset VARIABLES.totNoTax = variables.totnotax +#total#>
<cfset VARIABLES.temp_po_row_count = VARIABLES.temp_po_row_count + 1>
<cfset totalCheckAmount = VARIABLES.companyTotal - val(totalWrapAmount)>
</cfoutput>
<tr>
<td bgcolor="##cbdad6"></td>
<td bgcolor="##cbdad6" align="right"><span class="hdr">PO Total: </span></td>
<td bgcolor="##cbdad6" align="right"><span class="hdr">#dollarFormat(VARIABLES.companyTotal)#</span></td>
<td bgcolor="##cbdad6"></td>
<td bgcolor="##cbdad6"></td>
<td bgcolor="##cbdad6"></td>
<td bgcolor="##cbdad6"></td>
<td bgcolor="##cbdad6"></td>
</tr>
<tr>
<td bgcolor="##cbdad6"></td>
<td bgcolor="##cbdad6" align="right"><span class="hdr">Wrap Total: </span></td>
<td bgcolor="##cbdad6" align="right"><span class="hdr">-#dollarFormat(totalWrapAmount)#</span></td>
<td bgcolor="##cbdad6"></td>
<td bgcolor="##cbdad6"></td>
<td bgcolor="##cbdad6"></td>
<td bgcolor="##cbdad6"></td>
</tr>
<tr>
<td bgcolor="##cbdad6"></td>
<td bgcolor="##cbdad6" align="right"><span class="hdr">Check Total: </span></td>
<td bgcolor="##cbdad6" align="right"><span class="hdr">#dollarFormat(totalCheckAmount)#</span></td>
<td bgcolor="##cbdad6"></td>
<td bgcolor="##cbdad6"></td>
<td bgcolor="##cbdad6"></td>
<td bgcolor="##cbdad6"></td>
<td bgcolor="##cbdad6"></td>
</tr>
</cfoutput>
```

<cfoutput query="AmountToPayOnNextCh

<!--- these variables reset whenever VendCode changes ...--->

<cfset VARIABLES.companyTotal = 0 />

<cfset totalWrapAmount = 0 />

<cfset totalCheckAmount = 0 />

<cfoutput group="PONo">

<!--- this is a new PO, so reset the total --->

<cfset VARIABLES.total = 0 />

<!--- loop and total up all the line items for current PO --->

<cfoutput>

TEST: total = old #variables.total# + totalAmnt #AmountToPayOnNextCheck.To

<cfset VARIABLES.total = variables.total + AmountToPayOnNextCheck.Tot

</cfoutput>

... print out details about current PO number

</cfoutput>

</cfoutput>

> maxRows="#MaxRows_AmountTo

I'd be a little leery of doing calculations with a grouped output AND paging. Because the totals only reflect what's displayed on the screen, not necessarily the overall total. In other words PO#123 might have 10 line items. Items 1-4 ($400) appear on page1 and the rest ($3200) on page2. Neither page would show the grand total for the PO $3600

TEST: total = old 001906 0 + totalAmt 273.00

TEST: total = old 001906 273 + totalAmt 3.00

Thanks!

What you really need to do is establish a row number for the overall group ie PO Number. So if you had 2 distinct PONumbers with 5 line items, you'd end up with:

QueryRow | PONum | LineItem | New RowNumber

1 | PONumber X | Line Item X |

2 | PONumber X | Line Item X | 1

3 | PONumber Y | Line Item X |

4 | PONumber Y | Line Item X | 2

5 | PONumber Y | Line Item X | 2

Then you use that row number for paging rather than the query row numbers. That's the only way you can guarantee the results won't be split across pages.

SELECT Columns, (Code to generate row numbers over PO)

FROM Table

WHERE NewRowNumber BETWEEN {startRow} AND {endRow}

Fortunately MS SQL 2005+ support various row numbering functions that can do this. As long as your base query isn't absurdly complex. The tricky part is you must also change any existing pagination logic that uses the query row count to calculate the number of pages ie << Show Prev Page 1 | Page 2| .... Show Last >>

It's doable, but "doing it right" is definitely more involved than non-grouped pagination.

Sure that's the simplest solution. But only if it's a very small dataset, that doesn't grow. Otherwise, you end up with slow pages that display too much data for users to consume. So an alternate approach to pagination, like the one above, is still needed.

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

TSQL Update Field Older Than 5 Days | 10 | 36 | |

SQL 2016 where are the managment tools? | 4 | 22 | |

Start a field with integer 1 | 7 | 28 | |

How can I find a table in SQL server 2016 | 4 | 18 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**11** Experts available now in Live!