I have a couple of specific requests for searches that I am struggling with:
- Transaction Report - I am searching a large date range of transactions. I want to identify the latest transaction (by date) and then return the item rate from that latest transaction.
The only grouping options available on the Item Rate field appears to be Maximum (or Minimum or Average) - that seems to return the highest rate, not the rate of the latest transaction (as identified by the Maximum summary on the Date field.
Is there a way to achieve this in a saved search?
2. Highlighting Summary Search
I have a complex search with complicated formulas with a Sum function to show totals.
I have been asked to highlight the results based on a comparison between 2 of the Sum'd fields - but I can't see to get this to work.
Examples of the 2 fields are:
CASE WHEN (TO_CHAR({TRANDATE},'MM')=TO_CHAR({TODAY},'MM')) AND (TO_CHAR({TRANDATE},'YY')=TO_CHAR({TODAY},'YY')) THEN (CASE WHEN {type} = 'Item Fulfillment' THEN ({appliedtotransaction.netamountnotax} / {appliedtotransaction.quantityuom}) * {quantityuom} ELSE {netamountnotax} END) END
and
CASE WHEN (TO_CHAR({TODAY},'MM')-1 > 0) THEN CASE WHEN (TO_CHAR({TRANDATE},'MM')=TO_CHAR({TODAY},'MM')-1) AND (TO_CHAR({TRANDATE},'YY')=TO_CHAR({TODAY},'YY')) AND (TO_CHAR({TRANDATE},'DD')<=TO_CHAR({TODAY},'DD')) THEN (CASE WHEN {type} = 'Item Fulfillment' THEN ({appliedtotransaction.netamountnotax} / {appliedtotransaction.quantityuom}) * {quantityuom} ELSE {netamountnotax} END) END ELSE CASE WHEN (TO_CHAR({TRANDATE},'MM')=TO_CHAR({TODAY},'MM')+11) AND (TO_CHAR({TRANDATE},'YY')=TO_CHAR({TODAY},'YY')-1) AND (TO_CHAR({TRANDATE},'DD')<=TO_CHAR({TODAY},'DD')) THEN (CASE WHEN {type} = 'Item Fulfillment' THEN ({appliedtotransaction.netamountnotax} / {appliedtotransaction.quantityuom}) * {quantityuom} ELSE {netamountnotax} END) END END
My attempt to compare these was wo include both of these into a singe Case When statement to compare if one is less than the other:
CASE WHEN (CASE WHEN (TO_CHAR({TRANDATE},'MM')=TO_CHAR({TODAY},'MM')) AND (TO_CHAR({TRANDATE},'YY')=TO_CHAR({TODAY},'YY')) THEN (CASE WHEN {type} = 'Item Fulfillment' THEN ({appliedtotransaction.netamountnotax} / {appliedtotransaction.quantityuom}) * {quantityuom} ELSE {netamountnotax} END) END) < (CASE WHEN (TO_CHAR({TODAY},'MM')-1 > 0) THEN CASE WHEN (TO_CHAR({TRANDATE},'MM')=TO_CHAR({TODAY},'MM')-1) AND (TO_CHAR({TRANDATE},'YY')=TO_CHAR({TODAY},'YY')) AND (TO_CHAR({TRANDATE},'DD')<=TO_CHAR({TODAY},'DD')) THEN (CASE WHEN {type} = 'Item Fulfillment' THEN ({appliedtotransaction.netamountnotax} / {appliedtotransaction.quantityuom}) * {quantityuom} ELSE {netamountnotax} END) END ELSE CASE WHEN (TO_CHAR({TRANDATE},'MM')=TO_CHAR({TODAY},'MM')+11) AND (TO_CHAR({TRANDATE},'YY')=TO_CHAR({TODAY},'YY')-1) AND (TO_CHAR({TRANDATE},'DD')<=TO_CHAR({TODAY},'DD')) THEN (CASE WHEN {type} = 'Item Fulfillment' THEN ({appliedtotransaction.netamountnotax} / {appliedtotransaction.quantityuom}) * {quantityuom} ELSE {netamountnotax} END) END END) THEN 1 ELSE 0 END
And then highlight that when it equals 1 - but it doesn't seem to work.
I did see a SuiteAnswers article that said Summary Highlighting will not work when Summary Group is used - does that mean that I can't do any summary highlighting on a report that includes any group field, or just if I try to calculate the highlighting on a group field?
Is this feasible?