[SOLVED] Creating Daily, Monthly, Yearly totals to display on 1 report
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!
JeraleeThis message has been edited. Last edited by: Kerry,
WebFOCUS 7.7.03 Linux / Universe Db HTML/PDF/EXCEL/HTML Active
April 28, 2010, 10:30 AM
GinnyJakes
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.
I will keep looking for other 'multi-verb' posts that may help.
Thank you, Jeralee
WebFOCUS 7.7.03 Linux / Universe Db HTML/PDF/EXCEL/HTML Active
April 28, 2010, 11:10 AM
Francis Mariani
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
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
April 28, 2010, 11:13 AM
jseaburn
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';