From reading through the forums, it feels like HOLD files are going to be my solution, but I'm hoping someone can help me make this as simple as possible.

I have sales transactions in a detail table (view), and I need to sum the sales by Day, Month and Year and display them each as columns in the same report. Seems simple enough, but I can't seem to figure out how to get all 3 on the same report. I can do a BY for the day and get a total, a BY for the month and get a total (and so on)...but those are 3 separate reports.

I've posted my code in case it will help.

TABLE FILE SALES_CURR_FY_US
SUM
'J1.CUSTOMER_US.SALESPERSON'
'SALES_CURR_FY_US.SALES_CURR_FY_US.EXTENDED_FET_PRICE/P19.2CM'
BY 'SALES_CURR_FY_US.SALES_CURR_FY_US.PRICING_BRANCH'
BY 'SALES_CURR_FY_US.SALES_CURR_FY_US.GL_YR_MONTH'
BY 'J1.CUSTOMER_US.OUTSIDE_SALES'
BY 'J1.CUSTOMER_US.REGION'
BY 'SALES_CURR_FY_US.SALES_CURR_FY_US.SALES_SOURCE'
HEADING
""
FOOTING
""
WHERE SALES_CURR_FY_US.SALES_CURR_FY_US.PR_BR EQ 18 OR 7;
WHERE SALES_CURR_FY_US.SALES_CURR_FY_US.SALES_SOURCE EQ 'WHSE' OR 'DIR';
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE HOLD AS REGHOLD FORMAT ALPHA

Thank you for any and all suggestions!

Jeralee

This message has been edited. Last edited by: Kerry,

Have you heard of a multi-verb request? Here is an example.

TABLE FILE CAR
SUM SALES
BY COUNTRY
SUM SALES
BY COUNTRY
BY CAR
SUM SALES 
BY COUNTRY
BY CAR
BY MODEL
END

Notice how the BY phrases are nested. You can only use PRINT as the last verb.

You can do an additional search on the Forum using multi-verb as a keyword.

Let me know if that helps or if you need more assistance.

Thank you Ginny! That gets me closer. Here is the code I have now...2 problems with it:

TABLE FILE SALES_CURR_FY_US
SUM
'SALES_CURR_FY_US.SALES_CURR_FY_US.EXTENDED_FET_PRICE/P19.2CM'
BY 'SALES_CURR_FY_US.SALES_CURR_FY_US.GL_DATE'
-*BY 'SALES_CURR_FY_US.SALES_CURR_FY_US.PRICING_BRANCH'

SUM
'SALES_CURR_FY_US.SALES_CURR_FY_US.EXTENDED_FET_PRICE/P19.2CM'
BY 'SALES_CURR_FY_US.SALES_CURR_FY_US.GL_DATE'
BY 'SALES_CURR_FY_US.SALES_CURR_FY_US.GL_YR_MONTH'
-*BY 'SALES_CURR_FY_US.SALES_CURR_FY_US.PRICING_BRANCH'

SUM
'SALES_CURR_FY_US.SALES_CURR_FY_US.EXTENDED_FET_PRICE/P19.2CM'
BY 'SALES_CURR_FY_US.SALES_CURR_FY_US.GL_DATE'
BY 'SALES_CURR_FY_US.SALES_CURR_FY_US.GL_YR_MONTH'
BY 'SALES_CURR_FY_US.SALES_CURR_FY_US.GL_DT_FISCAL_YEAR'
-*BY 'SALES_CURR_FY_US.SALES_CURR_FY_US.PRICING_BRANCH'
HEADING
""
FOOTING
""
WHERE SALES_CURR_FY_US.SALES_CURR_FY_US.PR_BR EQ 18 OR 7;
WHERE SALES_CURR_FY_US.SALES_CURR_FY_US.SALES_SOURCE EQ 'WHSE' OR 'DIR';



1) If I try to add another BY (for something other than a date - PRICING_BRANCH in this case, I get a "multi-verb specification is invalid" error. I've looked for that in the forum with no luck.
2) The output is summing improperly. It's giving me the same totals for each SUM.

GL_DATE EXTENDED_FET_PRICE GL_YR_MONTH EXTENDED_FET_PRICE GL_DT_FISCAL_YEAR EXTENDED_FET_PRICE
2009/11/02 $272,589.39 2009/11 $272,589.39 2010 $272,589.39
2009/11/03 $370,445.63 2009/11 $370,445.63 2010 $370,445.63
2009/11/04 $294,706.40 2009/11 $294,706.40 2010 $294,706.40

I will keep looking for other 'multi-verb' posts that may help.

Thank you,
Jeralee

Try making your multi-verb request dimensions higher to lower:

1)
BY GL_DT_FISCAL_YEAR

2)
BY GL_DT_FISCAL_YEAR
BY GL_DT_YR_MONTH

3)
BY GL_DT_FISCAL_YEAR
BY GL_DT_YR_MONTH
BY GL_DATE

I was able to get the request to run with the additional BY phrases by placing them before the EXTENDED_FET_PRICE sum of the sales. So I must have just been out of order with that code.

I still have the problem of the totals not being correct however.

TABLE FILE SALES_CURR_FY_US
SUM
'SALES_CURR_FY_US.SALES_CURR_FY_US.EXTENDED_FET_PRICE/P19.2CM'
BY 'SALES_CURR_FY_US.SALES_CURR_FY_US.PRICING_BRANCH'
BY 'J1.CUSTOMER_US.OUTSIDE_SALES'
BY 'J1.CUSTOMER_US.REGION'
BY 'SALES_CURR_FY_US.SALES_CURR_FY_US.GL_DATE'
SUM
'SALES_CURR_FY_US.SALES_CURR_FY_US.EXTENDED_FET_PRICE/P19.2CM'
BY 'SALES_CURR_FY_US.SALES_CURR_FY_US.PRICING_BRANCH'
BY 'J1.CUSTOMER_US.OUTSIDE_SALES'
BY 'J1.CUSTOMER_US.REGION'
BY 'SALES_CURR_FY_US.SALES_CURR_FY_US.GL_DATE'
BY 'SALES_CURR_FY_US.SALES_CURR_FY_US.GL_YR_MONTH'
SUM
'SALES_CURR_FY_US.SALES_CURR_FY_US.EXTENDED_FET_PRICE/P19.2CM'
BY 'SALES_CURR_FY_US.SALES_CURR_FY_US.PRICING_BRANCH'
BY 'J1.CUSTOMER_US.OUTSIDE_SALES'
BY 'J1.CUSTOMER_US.REGION'
BY 'SALES_CURR_FY_US.SALES_CURR_FY_US.GL_DATE'
BY 'SALES_CURR_FY_US.SALES_CURR_FY_US.GL_YR_MONTH'
BY 'SALES_CURR_FY_US.SALES_CURR_FY_US.GL_DT_FISCAL_YEAR'
HEADING
""
FOOTING
""
WHERE SALES_CURR_FY_US.SALES_CURR_FY_US.PR_BR EQ 18 OR 7;
WHERE SALES_CURR_FY_US.SALES_CURR_FY_US.SALES_SOURCE EQ 'WHSE' OR 'DIR';


Output:

BRANCH OUTSIDE_SALES REGION GL_DATE Daily Sales YR/MO MTD Sales FY YTD Sales
18 CHACONB . 2009/11/02 $41,625.39 2009/11 $41,625.39 2010 $41,625.39
18 CHACONB . 2009/11/03 $44,853.52 2009/11 $44,853.52 2010 $44,853.52
18 CHACONB . 2009/11/04 $44,887.50 2009/11 $44,887.50 2010 $44,887.50
18 CHACONB . 2009/11/05 $30,658.07 2009/11 $30,658.07 2010 $30,658.07

Thank you Frances! I was in the middle of my last post when you replied. Making the changes you suggest, I think that's it!!!