I have a table store_master with 3 columns - region char(4), state char(2), store_number number(5)
the data looks like this
WEST, CA, 1
WEST, CA, 2
EAST, NY, 30
EAST, NY, 31
EAST, NY, 32
WEST, CA, 3
WEST, CA, 4
WEST, CA, 5
EAST, PA, 50
EAST, PA, 51
SOUTH, GA, 100
SOUTH, GA, 101
WEST, NV, 10
WEST, NV, 11
WEST, NV, 12
EAST, NY, 33

The rpeort should look like this.. (Region - state - all store numbers for the state seperated by '-' and presented inside a grid)
-------------------------
|EAST  | NY |30-31-32-33|
-------------------------
|EAST  | PA |50-51       |
-------------------------
|SOUTH| GA |100-101    |
-------------------------
|WEST  | CA |1-2-3-4-5 |
-------------------------
|WEST  | NV |10-11-12  |
--------------------------

I am new to webfocus and have no idea how to create this report. Could anyone please help me in creating this report?

Something like this:

DEFINE FILE STORE_MASTER
CONCAT_STORES/A100=IF REGION EQ LAST REGION AND STATE EQ LAST STATE THEN CONCAT_STORES || '-' || STORE_NUMBER ELSE STORE_NUMBER;
END

TABLE FILE STORE_MASTER
SUM CONCAT_STORES
BY REGION
BY STATE
END

You will have to mess with the "A30" format of CONCAT_STORES to match the maximum number of STORE_NUMBERS that will be on one line together with room for the hyphens.

Note that N. Selph's code works well, but it assumes a couple of things. Most importantly, your data has to be in Region, State, Store order. Doesn't look like yours is. If it is not, you can get it that way by adding a little code before his (hers?), like so:

TABLE FILE STORE_MASTER
BY REGION
BY STATE
BY STORE_NUMBER
ON TABLE HOLD
END

DEFINE FILE HOLD
CONCAT_STORES/A100=IF REGION EQ LAST REGION AND STATE EQ LAST STATE THEN CONCAT_STORES || '-' || STORE_NUMBER ELSE STORE_NUMBER;
END

TABLE FILE HOLD
SUM CONCAT_STORES
BY REGION
BY STATE
END

This code also assumes that store number is alpha, rather than numeric, as the concatenation feature (|) does not work on numeric fields. If store number is integer, you could change the define like so:

DEFINE FILE HOLD
CONCAT_STORES/A100=IF REGION EQ LAST REGION AND STATE EQ LAST STATE THEN CONCAT_STORES || '-' || EDIT(STORE_NUMBER) ELSE EDIT(STORE_NUMBER);
END

Note also that strong concatenation (||) will not give you spaces on wither side of the dashes. You'll have to use weak concatenation (single |) for that. Then you'll end up with multiple spaces after the dash, in some cases. If that gives you trouble, you could email me, as this post is starting to get rather long.

I tried with code and it is giving the following error.
TABLE FILE WM_STORE_MASTER
BY REGION
BY STATE
BY STORE_NUMBER
ON TABLE HOLD
END
DEFINE FILE HOLD
CONCAT_STORES/A450=IF REGION EQ LAST REGION AND STATE EQ LAST STATE THEN
CONCAT_STORES || '-' || EDIT(STORE_NUMBER) ELSE EDIT(STORE_NUMBER);
END
TABLE FILE HOLD
SUM CONCAT_STORES
BY REGION
BY STATE
END
0 NUMBER OF RECORDS IN TABLE= 158 LINES= 158
0 ERROR AT OR NEAR LINE 26 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC282) RESULT OF EXPRESSION IS NOT COMPATIBLE WITH THE FORMAT OF FIELD:
CONCAT_STORES
0 ERROR AT OR NEAR LINE 34 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: CONCAT_STORES
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

Could any one help me fix this error?

Try with field format less then A256.

Your DEFINE for CONCAT_STORES does not work because you're appending other fields to CONCAT_STORES. It is defined as 450 characters, but you're appending STORE_NUMBER (which will be 4 characters long, for example) to CONACT_STORES which is 450 characters - 450 + 4 is longer that 450, hence the error.

The best way to provide a solution is to give you a working example.

I will user the EDUCFILE file, provided with each installation of FOCUS.

-SET &ECHO=ALL;
TABLE FILE EDUCFILE
PRINT COURSE_CODE
BY EMP_ID
ON TABLE HOLD AS HOLD01
END
DEFINE FILE HOLD01
CONCAT_COURSE/A200 = IF EMP_ID EQ LAST EMP_ID
THEN (SUBSTR(200, CONCAT_COURSE, 1, ARGLEN(200, CONCAT_COURSE, 'I4'), ARGLEN(200, CONCAT_COURSE, 'I4'), 'A190')
|| '-' || COURSE_CODE) ELSE COURSE_CODE;
END
TABLE FILE HOLD01
SUM CONCAT_COURSE
BY EMP_ID
END
-* Syntax for Substring function: SUBSTR(inlength, parent, start, end, sublength, outfield)

Because you want to concatenate more data to an existing field, you have to use the SUBSTR function to take a piece of the original field and append the extra field.

You have to make sure that the concatenated field is large enough (A200 in my example) to include all the store numbers and a little more - to accomodate the SUBSTR function.

I followed Pietro's example and it worked like a charm. I truly appreciate all you people for your time and effort in helping beginners like me.

Is there any way I can strip out the leading zeroes from my output string, which is given below.
002-009-014-015-022-024-030-032-033-035-037-
040-048-051-052-064-097-118-137-140-177-198-203-205
DEFINE FILE HOLD01
CONCAT_STORES/A600 = IF REGION EQ LAST REGION AND STATE EQ LAST STATE
THEN (SUBSTR(600, CONCAT_STORES, 1, ARGLEN(600, CONCAT_STORES, 'I3'), ARGLEN(600, CONCAT_STORES, 'I3'), 'A590')
ELSE EDIT(STORE_NUMBER, '$$$999');
END

This message has been edited. Last edited by: <Mabel>,

What is the format of STORE_NUMBER in the Master?

FIELD=STORE_NUMBER,STORE_NUMBER, P6 ,P8 ,MISSING=ON ,$

NUMBER(5) IN ORACLE

Then, I'm surprised that EDIT(STORE_NUMBER, '$$$999') would work, since EDIT will only work with integer and alpha.

Meanwhile, here is my example updated:

-SET &ECHO=ALL;
TABLE FILE EDUCFILE
PRINT COURSE_CODE
BY EMP_ID
ON TABLE HOLD AS HOLD01
END
DEFINE FILE HOLD01
YCOURSE_CODE/I8 = EDIT(COURSE_CODE);
XCOURSE_CODE/D8c = YCOURSE_CODE;
WCOURSE_CODE/A8 = FTOA(XCOURSE_CODE, '(D8c)', 'A8');
ZCOURSE_CODE/A8 = LJUST(8, WCOURSE_CODE, 'A8');
CONCAT_COURSE/A200 = IF EMP_ID EQ LAST EMP_ID
THEN (SUBSTR(200, CONCAT_COURSE, 1, ARGLEN(200, CONCAT_COURSE, 'I4'), ARGLEN(200, CONCAT_COURSE, 'I4'), 'A190')
|| '-' || ZCOURSE_CODE) ELSE ZCOURSE_CODE;
END
TABLE FILE HOLD01
SUM CONCAT_COURSE
BY EMP_ID
END


The definition of YCOURSE_CODE is only there so that I can have a P8 field as per your field STORE_NUMBER. Basically, I use the FTOA function to convert the numeric field to alpha (XCOURSE_NUMBER) (the lower case c in "D8c" is very important, it removes the thousands comma), then left-justify the converted field (ZCOURSE_NUMBER).

Thanks a lot Pietro.. You have a gr8 weekend!

IBI is suppose to be working a new feature where you can get the individual values within the bar. Plus the total at the top.

Lenny