[CLOSED] Autodrill on dynamically created table statement

January 24, 2018, 03:49 PM

RyanIPG13

Hi everyone,

I did some searching before posting this and all I could find were articles > 3 years old, so wanted to ask for a fresh perspective.

Right now we're modifying a self-service guided adhoc app that was created for us and we want to turn on autodrilling using the hierarchies we've defined in the metadata. Users are finding autodrill VERY useful and this is the next step in that process.

The catch is that the table, dimensions and measures are all created on the fly because we have no idea which metadata, dimensions, etc the user will pick.

Here's the table statement that I'm using:

  

LET GATBNAME=&TBNAME
TABLE FILE GATBNAME
SUM
     &MEASURE
BY  &DIMENSION
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET BYDISPLAY ON
ON TABLE SET AUTODRILL ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &WFFMT.(<HTML,HTML>,<PDF,PDF>,<Excel 2000,EXL2K>,<Excel Formula,EXL2K FORMULA>,<HTML Active Report,AHTML>,<Active Report for Adobe Flash Player,FLEX>,<Active Report for PDF,APDF>,<PowerPoint,PPT>).Select type of display output.
ON TABLE SET HTMLCSS ON
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/enblue_light2.sty,
$
ENDSTYLE

Autodrill will NOT work in the case above. However, if I hard code in the table name, the dimensions, etc and hardcode a format of HTML, it DOES work. The ON TABLE SET AUTODRILL ON is not required when using the explicit table name, but I put it up there because it shows something I've attempted to do.

I've tried a couple different tricks like referencing the above code in an include, turning everything into an HTMTABLE and using HTMLFORM to write it out, but it seems like Autodrill just won't work unless the table is specifically referenced ahead of time.

Any ideas?

Thanks.

This message has been edited. Last edited by: FP Mod Chuck,

January 24, 2018, 04:45 PM

j.gross

quote:
LET GATBNAME=&TBNAME
TABLE FILE GATBNAME

Perhaps it's the LET.

What happens if you code

TABLE FILE &TBNAME

January 25, 2018, 02:08 AM

Chaudhary

RyanIPG13,
We did a lot of work on Auto-drill and our observation is that if we are creating chart using infoassist, Appstudio GUI it works. If we did some modification in code i.e any -SET command etc AUTO-DRILL does not supports .

January 25, 2018, 08:29 AM

RyanIPG13

Thanks Jack and Chaudhary!

I tried commenting out the LET command and still no go.

It appears that Chaudhary may be correct here. That's really unfortunate because you basically lose all of the cool functionality Autodrill can provide if you do any sort of coding by hand using variables for tables, etc.

I also tried ruling out any issues with the fex itself by creating a brand new report using the GUI where AutoDrill works and then slowly re-introducing the code from the hand-coded report through text editor.

When doing that, it continues to work with autodrill RIGHT up to the point when I start using variables for the table and fields. Then it just stops working.

I was hoping there was just a clever way to fake it out.

Darn..

January 25, 2018, 02:26 PM

John Mulvaney

Instead of embedding the DM into the code within the fex, could you instead write the fully resolved dynamic code out to a temporary fex and then run that? FOCUS would then perceive your dynamic code as static (I expect). I have not coded FOCUS in a long time, but something like:

* &WFFMT.(,,,,,,,).Select type of display output.

FILEDEF TEMPFEX DISK "tempfex.fex"
-RUN

-WRITE TEMPFEX TABLE FILE &TBNAME
-WRITE TEMPFEX SUM
-WRITE TEMPFEX &MEASURE
-WRITE TEMPFEX BY &DIMENSION
-WRITE TEMPFEX ON TABLE SET PAGE-NUM NOLEAD
-WRITE TEMPFEX ON TABLE SET BYDISPLAY ON
-WRITE TEMPFEX ON TABLE SET AUTODRILL ON
-WRITE TEMPFEX ON TABLE NOTOTAL
-WRITE TEMPFEX ON TABLE PCHOLD FORMAT &WFFMT
-WRITE TEMPFEX ON TABLE SET HTMLCSS ON
-WRITE TEMPFEX ON TABLE SET HTMLEMBEDIMG ON
-WRITE TEMPFEX ON TABLE SET STYLE *
-WRITE TEMPFEX INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/enblue_light2.sty,
-WRITE TEMPFEX $
-WRITE TEMPFEX ENDSTYLE

-RUN

EX tempfex
-RUN

January 26, 2018, 10:19 AM

j.gross

Reporting Server executes from Focstack, at which point the amper references have been resolved and appear to it as static FOCUS code.

I suspect that there is some preprocessing going on within Server or Client [files in the Repository can have associated attributes, now that Repository is a database rather than a physical file folder], to put finishing touches on the code before the step that fills focstack for execution. That may entail static analysis of the TABLE request, and looking up the referenced synonym -- which is impossible when those are coded as amper references.

You might test that hypothesis by comparing ECHO output vs. the raw fex stored in Repository, for a copy without amper references.