I think I have see this topic before but I couldn't find it or i might be just day-dreaming. So here am I asking this question again.

Ok here is what I am getting from SQL 2005 .



And I want to make a drill down menu on the title alphabetically. So if a user clicks on "A" user will get all Titles starting with "A". (kind a like Olap tool ).
So my navigation menu will look some thing like this :-



I know I can code 27(26 alphabetical and 1 for other ) report with "WHERE" function but I think there must be a simple way to do that.

Can anybody please help me out on this ?

Thank you on Advance.

Merry Christmas and Happy New Year to all of you and your loved ones.

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

Hello,
You can try something like this:

  
-* File drmi.fex
-DEFAULT &INIT=0
-SET &INIT=IF &INIT EQ 0 THEN FOC_NONE ELSE HEXBYT(&INIT, 'A1');
-*
DEFINE FILE CAR
INIT/A1=EDIT(CAR,'9');
END
TABLE FILE CAR
PRINT
     MODEL
     DEALER_COST
     RETAIL_COST
BY  LOWEST CAR
WHERE INIT EQ '&INIT';
ON TABLE SET BYDISPLAY ON
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
TYPE=TITLE,
     COLUMN=N1, COLOR=YELLOW,
-REPEAT #DM FOR &I FROM 0 TO 26;
-SET &INIT=IF &I EQ 0 THEN &I ELSE 64+&I;
-SET &DM=IF &I EQ 0 THEN 'ALL' ELSE HEXBYT(&INIT, 'A1');
     DRILLMENUITEM='&DM', FOCEXEC=drmi(INIT='&INIT'),
-#DM
$
ENDSTYLE
END

Now THAT is COOL!

Thank You Danny,

That was really great of you.

Danny,

That was really cool code --- So, of course, I could not help but tinker a little with it --- just a thought

-**** EXAMPLE OF A DRILL MENU WITH ONLY THE VALUES THAT EXIST
-DEFAULT &INIT=0
-SET &INIT=IF &INIT EQ 0 OR 'ALL' THEN FOC_NONE ELSE &INIT;
-SET &I = 0;
-SET &LET0='ALL';
-*
DEFINE FILE CAR
INIT/A1=EDIT(CAR,'9');
END
TABLE FILE CAR
BY INIT
WHERE INIT EQ '&INIT';
ON TABLE SAVE
END
-SET &NBR = &LINES;
-REPEAT #LET FOR &I FROM 1 TO &NBR;
-READ SAVE &LETTER.A1.
-SET &LET.&I = &LETTER.EVAL;
-#LET
TABLE FILE CAR
PRINT MODEL
DEALER_COST
RETAIL_COST
BY LOWEST CAR
WHERE INIT EQ '&INIT';
ON TABLE SET BYDISPLAY ON
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON

ON TABLE SET STYLE *
INCLUDE = endeflt,
$
TYPE=TITLE,
COLUMN=N1, COLOR=YELLOW,
-REPEAT #DM FOR &I FROM 0 TO &NBR;
-SET &DM=IF &I EQ 0 THEN 'ALL' ELSE &LET.&I;
DRILLMENUITEM='&DM', FOCEXEC=dynpyctr(INIT='&DM'),
-#DM
$
ENDSTYLE
END

Happy New Year to all!

Ah, the iterative power of FOCUS development!

Happy New Year to all!

Nice one Danny and Vivian!

As a further iteration, I'd like the original list of values to remain as part of the pop-up menu each time I click on 'CAR' so I can view more quickly the full list rather than clicking 'CAR' then clicking 'ALL' each time I wanted to see the original.

-**** EXAMPLE OF A DRILL MENU WITH ONLY THE VALUES THAT EXIST
-**** Keep Original list of values in the menu to keep from having to click 'ALL' 
-****      to see original values
-DEFAULT &INIT=ALL
-*
-SET &INIT=IF &INIT EQ ALL THEN FOC_NONE ELSE &INIT;
-*
FILEDEF TESTSAVE DISK TESTSAVE.FTM (APPEND
-RUN
-WRITE TESTSAVE DRILLMENUITEM='ALL',FOCEXEC=test(INIT='ALL'),,

-*
SET HOLDLIST=PRINTONLY
-*
DEFINE FILE CAR
INIT/A1=EDIT(CAR,'9');
END
-*
TABLE FILE CAR
SUM
COMPUTE LINE/A75='DRILLMENUITEM='''|INIT|''',FOCEXEC=test(INIT='''|INIT|'''),';
BY INIT NOPRINT
ON TABLE SAVE AS TESTSAVE FORMAT ALPHA
END
-*
-RUN
-*
TABLE FILE CAR
PRINT MODEL
      DEALER_COST
      RETAIL_COST
BY CAR
WHERE INIT EQ '&INIT';
ON TABLE SET BYDISPLAY ON
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=TITLE,
COLUMN=N1, COLOR=YELLOW, BACKCOLOR=GREEN,
-INCLUDE TESTSAVE.FTM
$
ENDSTYLE
END

Note the double comma at the end of the -WRITE and the blank row beneath are required.

Happy New year to all!

I would recommend one or two more enhancements to the code. Since this program will call itself for every drill-down, the list of drill menu options (file TESTSAVE.FTM in the above example) should be saved to a semi-permanent location (like FOCCACHE). Then a DM variable test could be used to avoid re-creating the drill menu options file with every call to the program.

The use of a system variable containing the running focexec name (like &FOCFOCEXEC) could be used, instead of the hard-coded focexec name in the drill-down statement, to avoid problems if the name of the program is changed.

And this is the beauty of community cooperation -- Always getting a better product. And what led us to develop the Focus and WebFocus Keysheets and Dates books.

Always better and better.

OK Vivian, you talked me into it (plus it's a slow end-of-year day and I like small challenges )...Here is a new version using Dan Satchell's ideas with a little code tightening.

• Use Dialogue Manager variable (&FR) to trigger first pass of data once and skip if run again
  
• Keep drill menu stylesheet code (testsave.ftm) available in FOCCACHE for duration of session
  
• Use &FOCFOCEXEC to store drill fex name (&FEXNAME) in case name of fex changes
  

And here is the enhanced code:

-DEFAULT &INIT=ALL,  &FR=0
-*
-SET &INIT=IF &INIT EQ ALL THEN FOC_NONE ELSE &INIT;
-SET &FEXNAME=EDIT(&FOCFOCEXEC,'$999999999');
-*
DEFINE FILE CAR
INIT/A1=EDIT(CAR,'9');
END
-*
-IF &FR EQ 1 GOTO SKIPTESTSAVE;
-*
TABLE FILE CAR
SUM
COMPUTE LINE/A75='DRILLMENUITEM='''|INIT|''',FOCEXEC=&FEXNAME(INIT='''|INIT|''' FR=1),';
BY INIT NOPRINT
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE AS FOCCACHE/TESTSAVE FORMAT ALPHA
END
-RUN
-*
-SKIPTESTSAVE
-*
TABLE FILE CAR
PRINT MODEL
      DEALER_COST
      RETAIL_COST
BY CAR
WHERE INIT EQ '&INIT';
ON TABLE SET BYDISPLAY ON
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=TITLE,
COLUMN=N1, COLOR=YELLOW, BACKCOLOR=GREEN,
DRILLMENUITEM='ALL',FOCEXEC=&FEXNAME(INIT='ALL' FR=1),
-INCLUDE FOCCACHE/TESTSAVE.FTM
$
ENDSTYLE
END

BTW, Works in HTML and DHTML formats. And, oddly, it works in AHTML format but, not in the column title. The code automatically adds a hyperlink for every row value in the first column. The good news is the menu still pops-up with the menu of values we want.

Edit 1 1/1/2013: Thanks Michael for the eagle eye.
Edit 2 1/2/2013: Add -RUN after the first TABLE request to ensure TESTSAVE.FTM is stored in FOCCACHE before the -INCLUDE in the second TABLE request is executed.

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

Wow. It is really interesting to see how a small idea can bring so much brain storming. Thank you all for help and creative ideas.

I found a minor typo:

quote:

ON TABLE SAVE AS FOCACHE/TESTSAVE FORMAT ALPHA

Should be:

quote:

ON TABLE SAVE AS FOCCACHE/TESTSAVE FORMAT ALPHA

Jan 1, 2013 and already spanked. Thanks Michael. Correction made in the full code above.

Happy 2013!