Hello,

Is it possible to create an excel report with the following format?

  Dept      Category         Sub-Cat          2007      2006
Grocery
          Coffee                             28         25
                            Mainstream       20         21
                            Premium          36         29

          Tea                                27         26
                            POD              24         23
  

Any help would be appreciated.

Thanks

This message has been edited. Last edited by: Shaun Lohman,

Shaun, are you new to the world of WebFOCUS?

Are you wanting to count across the years?
Not knowing your file names or fields.

TABLE FILE COFFEESALE
COUNT.ITEM
BY DEPARTMENT
BY CATEGORY
BY SUBCATEGORY
ACROSS YEAR
ON TABLE PCHOLD EXL2K
END

Also, please update your profile signature to show what version you are running.

Thanks for the response.
We are running 7.1.3.

The cacluation is actually a percentage. That part is not the problem. What I am having a problem with is displaying the total percentage for the coffee category, then display the percentages for the sub-cats within Coffe. Doing what you have suggested would give me,

  
Dept      Category         Sub-Cat          2007      2006
Grocery    Coffee          Mainstream       20%         21%
                           Premium          36%         29%
            Tea            POD              24%         23%
  

Do you see the problem now?

Thanks

Have you tried using

ON CATEGORY SUMMARIZE

Percentages can be a pain.

The percentage is wrong you are saying under each year column?

No, I can calcuate the percentages fine, its the formatting thats the problem.
Using the ON CATEGORY SUMMARIZE would give me

  
Dept      Category         Sub-Cat          2007      2006
Grocery    Coffee          Mainstream       20%         21%
                           Premium          36%         29%
*Total Coffee                               28%         25%
            Tea            POD              24%         23%
*Total Tea                                  24%         23%  

I want those totals displayed on the same line as Coffee,

  
Dept      Category         Sub-Cat          2007      2006
Grocery    Coffee                           28%         25%
                           Mainstream       20%         21%
                           Premium          36%         29%
            Tea                             24%         23%
                           POD              24%         23% 

The tool doesn't work that way, but if you were to play fancy work with computes and hold files you might be able to do what you want. I don't have any exampes, never saw a need for that way.

Of course there are others out their that may have the answer at their fingertips, but they are probably sleeping now as they are in European countries.

Of course perhapsdefinining a blank sub-cat field might work as well. Will play with one of the IBI 'play files' and see.

Try summing the information separately and then matching and/or joining the information together. I had to use the CAR table to try to put together and example and then change to fields that would make sense for you...not sure it worked to well. I'm hoping you get the idea with the code below.



SET ASNAMES=ON

TABLE FILE COFFEESALE
COUNT
FIELD AS 'PERC1'
AND COMPUTE SORT/A1 = 'A';
BY YEAR
BY DEPT
ON TABLE HOLD AS H001
END

TABLE FILE COFFEESALE
COUNT
FIELD AS 'PERC2'
AND COMPUTE SORT/A1 = 'B';
BY YEAR
BY DEPT
BY CATEGORY
ON TABLE HOLD AS H002
END

TABLE FILE COFFEESALE
COUNT
FIELD AS 'PERC3'
AND COMPUTE SORT/A1 = 'C';
BY YEAR
BY DEPT
BY CATEGORY
BY SUBCATEGORY
ON TABLE HOLD AS H003
END


MATCH FILE H001
PRINT *
BY SORT
BY YEAR
BY DEPT
RUN

FILE H002
PRINT *
BY SORT
BY YEAR
BY DEPT
BY CATEGORY
AFTER MATCH HOLD AS H005 OLD-OR-NEW
RUN

FILE H003
PRINT *
BY SORT
BY YEAR
BY DEPT
BY CATEGORY
BY SUBCATEGORY
AFTER MATCH HOLD AS H005 OLD-OR-NEW
END


DEFINE FILE H005
PERC/D7 = IF SORT EQ 'A' THEN PERC1 ELSE IF SORT EQ 'B' THEN PERC2 ELSE
IF SORT EQ 'C' THEN PERC3 ELSE 0
END


TABLE FILE H005
SUM
PERC
BY DEPT
BY CATEGORY
BY SUBCATEGORY
ACROSS YEAR
END

Unfortunately it's much more complicated than it should be. If anyone knows an easier way, I'd love to see it!

Good luck,

wow, thanks for the response Victoria. I will have to try that on Monday. It looks like what I need! I will let you know how it turns out.

Thanks

That did the trick.

Thanks Victoria