Hi all,

So, we've been given a macro-enabled Excel sheet that shows department totals for our retail stores showing totals for the current week, then right below that totals for quarter-to-date for the sub-dept., and then the totals of all sub-departments (with those total types) below it all. For example:

Departments | Purchase Margin % | Ratio SPread % | Etc.

Meat Deli  CW | 30.30 | 8.90 | ...
          QTD | 30.30 | 26.11 | ...
Block      CW | 16.50 | 100.00 | ...
          QTD | 16.50 | 100.00 | ...
Total Meat CW | 21.85 | 20.46 | ...  <---- A subtotal/total of just the meat related sub-depts.
          QTD | 21.96 | 20.71 | ...
Produce    CW | ...
          QTD | ...
Deli       CW | ...
          QTD | ...

Total Fresh CW | ...  <---- A grand total of all sub-dept. totals.
           QTD | ...

Is there anyone here that has done something similar, and can point me in the right direction for how to implement it in code?

Any help is appreciated. Thanks!

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

Hi CoolGuy,
It's not the only way for sure 'cause with Focus there are always multiple ways

But one way could be to create one hold file per type of row having their one id, then merge them together to create the final report.

-* CREATE DETAIL ROWS
DEFINE FILE CAR
DET_ID/I3  = 100;
TOT_ID/I3  = 100;
END
TABLE FILE CAR
SUM RETAIL_COST
    TOT_ID
    DET_ID
BY COUNTRY
BY CAR
BY MODEL
ON TABLE HOLD AS DETDATA FORMAT FOCUS
END
-RUN

-* CREATE SUB TOTAL ROWS
TABLE FILE CAR
SUM RETAIL_COST
    COMPUTE TOT_ID/I3  = 200;
    COMPUTE DET_ID/I3  = 100;
BY COUNTRY
BY CAR
BY TOTAL COMPUTE MODEL /A24 = 'Car SubTotal';
ON TABLE HOLD AS STOTDATA FORMAT FOCUS
END
-RUN

-* CREATE SUB TOTAL ROWS
TABLE FILE CAR
SUM RETAIL_COST
    COMPUTE TOT_ID/I3  = 300;
    COMPUTE DET_ID/I3  = 200;
BY COUNTRY
BY TOTAL COMPUTE CAR   /A16 = '';
BY TOTAL COMPUTE MODEL /A24 = 'Country Total';
ON TABLE HOLD AS TOTDATA FORMAT FOCUS
END
-RUN

-* MERGE ALL DATA TOGETHER AND DISPLAY IN THE PROPER ORDER
TABLE FILE DETDATA
SUM RETAIL_COST
BY COUNTRY
BY DET_ID NOPRINT
BY CAR
BY TOT_ID NOPRINT
BY MODEL
MORE
FILE STOTDATA
MORE
FILE TOTDATA
END
-RUN

The result may not be that relevant, but it's the technic that is important to look at.

Thanks Martin! Really appreciate you chiming in on this. Will play around with your code example and see where I can get from it.

Perfect exercise for FML...

Is FML apart of WF8?? Or is that another add-on?

I would agree that FML could help you here. It is in WF8 as I have used it for some reporting. If you're using the GUI, you can access it in AppStudio by clicking the matrix tab at the bottom of the reporting canvas area. You'll have to define a field as the 'For Row' first. I can't seem to access the Tech Support site or I would link the FML documentation.

FML let's you do row-level calculations and reporting, useful for things like accounting roll-ups and Excel-like reporting.

capples,

I go to try and select the Matrix tab and I get a pop=up that says "FOR item does not exist."

How do I get it to exist? lol

Click on Technical Library at the top, select your version, search on FML...
OR, click on the Search button and type in FML...

CoolGuy,

You will have to define a For Row first before you can use the Matrix tab. For example, you will probably have to structure your data in a way where Departments is your 'For' row and then you report on the different metrics using FML. To define the 'For' row, click the field and then from the 'Field' menu click For Row below Sort Across.

It's a little difficult to explain all the details in a forum post for FML , so if you can get to the documentation via the Technical Library, it should walk you through most of it. I would link it but like I said, the Technical Library is timing out for me when I try to access it today :\

Tom and capples,

Thank you for your help and advice. This is the first time in some time I've felt this "newbie" with WF8. I had never really understood what the Matrix tab was for or that FML was even a thing that I could use. Learned something new today!! Thank you!!! lol

I've got the docs up and am looking into this FML thing now. Also, mulling over Martin's example too.

Thank you all!