[billyboyo]

I want to Total Sales Order Qty’s in monthly columns for Sales Rep and Item rows in a saved search.

I know I need to grab Sales Orders and group them by Sales Rep and Item to get my rows.

Then I need to create a formula column for each desired month that sums the applicable quantities using Case SQL statements.

While I’m having trouble finding any old notes or suiteanswers on the specifics, I believe I can muscle through that part (but hints would be appreciated).

But where I really need help is in making it flexible (i.e., a rolling 8 month window).

I can hard code dates (i.e., 09/01/2015 thru 09/31/2015) in the formulas and label the column Sept 2015, then replicate for Oct 2015 thru Apr 2016. But I’d need to manually delete the Sept column next month and add a May 2016 column.

I guess I could at least roll the columns by using {today}’s month+1, {today}’s month+2, etc. in my formulas. But then I’d be stuck with generic month column labels Month +1, etc.

Is there any way to set dynamic column headings based on a reference date (i.e., a formula)?

Any way to pass a start date (if not today) to the search / formulas?

For that matter, any way to pass parameters to a saved search / formula?

Ideally, the user would like to specify a date range (obviously possible thru a Filter) and have the search provide monthly columns appropriately named by month/year.

That makes me wonder, are filter values available to the search formulas?

Sorry for the poorly structured question(s). Feel free to comment on any segment.
This is a cached copy. Click here to see the original post.

[Author]

Posts