Tina K
asked on
CR previous function in using sum, or running total, or formula, just doesn't work
Hi Experts - I'm tearing my hair out over this problem which doesn't seen to have a solution, according to the great and powerful Google.
I have a report that shows all month end inventory postings. The detail section is hidden, the group footer shows the part id along with a sum of the locations:
Drill down will show the detail section, providing inventory locations and bin qty's:
In an effort to save space, we would like to hide the data in the group footer if it hasn't changed from the previous month. I was using this formula in the section expert:
Works dandy, except I realized this is only looking at the rows of records in the detail level - which makes sense since sums, running totals and formulas can't be used with the previous function.
What can I do to suppress the group footer if it's a duplicate of the previous group footer? I've tried all sorts of three formula tricks and assigning 1/0 to variables, but it all comes back to not being able to use any of these with the previous function. Super frustrating.
Thanks in advance!
I have a report that shows all month end inventory postings. The detail section is hidden, the group footer shows the part id along with a sum of the locations:
Drill down will show the detail section, providing inventory locations and bin qty's:
In an effort to save space, we would like to hide the data in the group footer if it hasn't changed from the previous month. I was using this formula in the section expert:
if
{Command.PART_ID} = previous({Command.PART_ID})
and
{Command.QTY} = previous({Command.QTY})
then true
Works dandy, except I realized this is only looking at the rows of records in the detail level - which makes sense since sums, running totals and formulas can't be used with the previous function.
What can I do to suppress the group footer if it's a duplicate of the previous group footer? I've tried all sorts of three formula tricks and assigning 1/0 to variables, but it all comes back to not being able to use any of these with the previous function. Super frustrating.
Thanks in advance!
ASKER
Thanks IdoMillet - I'll give this a try at work tomorrow, I'll let you know!
What are you grouping on?
A group footer should only show once.
mlmcc
A group footer should only show once.
mlmcc
ASKER
I cant seem to get anything working based on idomillets suggestion. Maybe i need a clearer explanation? I can't do a previous function on a variable that stores a value. None of us at work can connect to experts exchange, so this is making the whole process slow and painful.
Previous only works on database fields.
Your report is showing the current inventory from all locations?
I assume you are grouped by part number then month?
Do you need all fields (part number, quantity, price ($Each), and Part Created) to be the same?
mlmcc
Your report is showing the current inventory from all locations?
I assume you are grouped by part number then month?
Do you need all fields (part number, quantity, price ($Each), and Part Created) to be the same?
mlmcc
ASKER
Hi mlcc. Yes, grouping is by part number, then by posted date. I want to suppress if the part id ans qty are the same as previous. We dont need to see the counts repeated if the monthly post hasn't changed.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, mlcc, let me play with this and get back to you
In the suppress expression of the section, use these variables to suppress the section.
You coud set a local boolean variable to TRUE, reset the variable values, and then return the boolean variable to achieve both the update of the variables and the suppression within the suppress expression.