Hi,

I have the launchpage/parameter page of the report which has the state selection wherein the users can select all states at once or individual states at once and based

on it,the summary report would get displayed.

This is the code for the summary report :

 -DEFAULT &STATE='';

ENGINE SQLORA SET CONNECTION_ATTRIBUTES USER1/database1,databasepwd
ENGINE SQLORA SET DEFAULT_CONNECTION USER1

SQL SQLORA PREPARE REPORT1 FOR
SELECT * FROM SCHEMA1.TABLE1;
END

DEFINE FILE REPORT1
HEAD1/A3='65%';
HEAD2/A3='80%';
END


TABLE FILE REPORT1
PRINT
CNT_3_75
CNT_2_85
CNT_1_95
HEAD1
HEAD2
WHERE TAB EQ ''
ON TABLE HOLD AS MYFILE
END
-RUN

-READFILE MYFILE
-RUN
-TYPE &CNT_3_75
-TYPE &CNT_2_85
-TYPE &CNT_1_95
-TYPE &HEAD1
-TYPE &HEAD2

TABLE FILE REPORT1
PRINT
TAB_NAME
SEGMENT
DATA
CNT_3_75
CNT_2_85
CNT_1_95
COMPUTE CHK_DRILLDOWN/A10 = IF DATA CONTAINS '%' THEN 'Y' ELSE IF DATA CONTAINS 'Total' THEN 'N' ELSE 'U'; NOPRINT
WHERE STATE IN (&STATE)
HEADING
"REPORT1"
"SOURCE:DATA"
"Data Current as of DATE"
ON TABLE HOLD AS HOLD_FILE FORMAT HTMTABLE
ON TABLE SET PAGE NOLEAD
ON TABLE SET HTMLCSS OFF
ON TABLE SET STYLE *
INCLUDE=IBFS:/WFC/Development/Stylesheet1.sty,
$
TYPE=DATA,
     COLUMN=N4,
     WHEN=CHK_DRILLDOWN EQ 'U',
     FOCEXEC=IBFS:/WFC/Development/Report1.fex(TAB_NAME=N1 SEGMENT=N2 DATA=N3 ACCESS='&HEAD1'),TARGET=_blank,
$
TYPE=DATA,
     COLUMN=N4,
     WHEN=CHK_DRILLDOWN EQ 'N',
     FOCEXEC=IBFS:/WFC/Developent/Report1_total.fex(TAB_NAME=N1 SEGMENT=N2 DATA=N3 ACCESS='&HEAD1'),TARGET=_blank,
$
TYPE=DATA,
     COLUMN=N5,
     WHEN=CHK_DRILLDOWN EQ 'U',
     FOCEXEC=IBFS:/WFC/Development/Report2.fex(TAB_NAME=N1 SEGMENT=N2 DATA=N3 ACCESS='&HEAD2'),TARGET=_blank,
$
TYPE=DATA,
     COLUMN=N5,
     WHEN=CHK_DRILLDOWN EQ 'N',
     FOCEXEC=IBFS:/WFC/Developent/Report2_total.fex(TAB_NAME=N1 SEGMENT=N2 DATA=N3 ACCESS='&HEAD2'),TARGET=_blank,
$
ENDSTYLE
END

-HTMLFORM BEGIN
<style>
table, th, td {
    border: 1px solid black;
	border-collapse: collapse;
	}
td {
    padding-left: 8px;
    padding-right: 8px;
}
</style>
!IBI.FIL.HOLD_FILE;
-HTMLFORM END
-EXIT 

I have hyperlinks on the columns 4 and 5 respectively and based on which it drills down to the detail report.

The code which Iam using for the drilldown(detail) report is as shown below:
Report1.fex :

 ENGINE SQLORA SET CONNECTION_ATTRIBUTES USER1/database1,databasepwd
ENGINE SQLORA SET DEFAULT_CONNECTION USER1

SQL SQLORA PREPARE REPORT2 FOR
SELECT
    COLUMN1
  , COLUMN2
  , COLUMN3
  , COLUMN4
FROM USER1.TABLE2
WHERE TAB_NAME = '&TAB_NAME'
AND SEGMENT = '&SEGMENT'
AND ACCESS IN (
                              SELECT (CASE
                                        WHEN '&DATA' = '1 UNITS'
                                          OR '&ACCESS' IN ('Congested') THEN 'Congested'
                                        ELSE NULL
                                      END) FROM DUAL
	       )
AND 1 = (CASE WHEN '&DATA' = '1 UNITS' THEN (
       (CASE WHEN (EXEC) LIKE ('CORE%) THEN
              (CASE WHEN ACCESS IN ('Congested','65%','80%') THEN 1 ELSE 0
                                                                           END)
              WHEN (EXEC) LIKE ('NOT CORE%') THEN
                     (CASE WHEN ACCESS IN ('Congested','65%','80%') THEN 1 ELSE 0
                                                                            END)
       END)
)
ELSE 0
END)
;
END

TABLE FILE REPORT2
PRINT *
END
-RUN
-EXIT 

Now the data gets refreshed once in every 2 weeks and instead of going to the Oracle database which Iam doing now, I want to get the data refresh done on the
Webfocus server itself and later schedule it using webfocus so that data gets refreshed once in every 2 weeks without going to the oracle server and for that I thought

of using the XFOCUS hold file but dont know how to proceed further for the drilldown report since it needs some dynamic parameter values from the summary report and

which in turn is dependent on the parameters selected from the launchpage/parameter page of the report and I want to do a XFOCUS hold file on the detail(drilldown)

report only..

I know how to do a XFOCUS file format on the master file but no idea on a sql which needs dynamic parameters.

Could anyone please let me know how to do this?
Thanks a lot in advance for all your help!

Any help is appreciated.

Regards,
IP

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

IP,

I'm confused. Why do you feel the need to retain the output as XFOCUS? Are you experiencing speed issues? If that is the case, I would first consider looking at indexes and other options to improve performance from Oracle. In general I wouldn't expect a large boost in performance unless you need to remove the connection latency or are pulling a large amount of information over from oracle to WF.

If the issue is the later, I would consider creating aggregated tables as a part of the process that creates the tables you are reading in the first place. In general I would expect that your Oracle server is going to be "Beefier" then your Reporting Server, so it would usually make sense to let it do the bulk of the work.

If you absolutely must save off the data as an XFOCUS table, considering that it seems that you are pulling the entire table over (again another indicator that this isn't the correct approach), you can just hold it in an app folder with format XFOCUS.

TABLE FILE REPORT1
PRINT
TAB_NAME
SEGMENT
DATA
CNT_3_75
CNT_2_85
CNT_1_95
BY [PRIMARY INDEX]
ON TABLE HOLD AS APP_PATH/MYTABLE FORMAT XFOCUS
END 

This will permanently hold the file in the app path that you specified. You can also use modify syntax to update/insert/delete records.

Remember that XFOCUS/FOCUS formats are Hierarchical tables. Aside from taking advantage of their hierarchical properties or a need to reduce connection lag between the Reporting Server and Oracle, I would question the need to hold the tables on the Reporting Server in the first place.

Hi Eric,

Iam not experiencing any speed issues but wanted the eliminate the need of going to the Oracle server once in every 2 weeks to update/refresh the data.

And can't I use only XFOCUS file format to download the data without using the MODIFY?

Also please let me know on how to do a XFOCUS on a SQL pass through which prompts for the parameter values from the summary report as we are only holding the drilldown(detail) report in the XFOCUS format.

Regards,
IP

You just need to add ON TABLE HOLD AS MYAPP/MYXFOCUSFILE FORMAT XFOCUS to your TABLE FILE REPORT2 request.

Hi Wep,

Actually Iam doing something like this :

 ENGINE SQLORA SET CONNECTION_ATTRIBUTES USER1/database1,databasepwd
ENGINE SQLORA SET DEFAULT_CONNECTION USER1

SQL SQLORA PREPARE REPORT2 FOR
SELECT
    COLUMN1
  , COLUMN2
  , COLUMN3
  , COLUMN4
FROM USER1.TABLE2
WHERE TAB_NAME = '&TAB_NAME'
AND SEGMENT = '&SEGMENT'
AND ACCESS IN (
                              SELECT (CASE
                                        WHEN '&DATA' = '1 UNITS'
                                          OR '&ACCESS' IN ('Congested') THEN 'Congested'
                                        ELSE NULL
                                      END) FROM DUAL
	       )
AND 1 = (CASE WHEN '&DATA' = '1 UNITS' THEN (
       (CASE WHEN (EXEC) LIKE ('CORE%) THEN
              (CASE WHEN ACCESS IN ('Congested','65%','80%') THEN 1 ELSE 0
                                                                           END)
              WHEN (EXEC) LIKE ('NOT CORE%') THEN
                     (CASE WHEN ACCESS IN ('Congested','65%','80%') THEN 1 ELSE 0
                                                                            END)
       END)
)
ELSE 0
END)
;
END

TABLE FILE REPORT2
PRINT *
ON TABLE HOLD AS MY_REPORT_APP/MYXFOCUSFILE FORMAT XFOCUS
END
-RUN
-EXIT 

 

Once I run the report, it is prompting for these values : &TAB_NAME, &SEGMENT, &DATA and &ACCESS and every time I don't want to give these values manually and these values comes from the summary report when clicked on the respective drilldown links and Iam wondering how to do a XFOCUS on the 2nd report in this scenario.
Please help.

Regards,
IP

According to me, when you are using SQL pass through it's to read an SQL table (or perform action on a SQL table).
Using SQL pass through it's to "bypass" the connector automatic code conversion.
Meaning that you don't let FOCUS converting your code to SQL.

If you want to use XFOCUS as the input table, use FOCUS code structure WITHOUT SQL pass through :

TABLE FILE xfocusFile
...
END

Anyway, what SQL pass through will do with a table that "is not" a SQL table ?

quote:

Actually Iam doing something like this :

ENGINE SQLORA SET CONNECTION_ATTRIBUTES USER1/database1,databasepwd
ENGINE SQLORA SET DEFAULT_CONNECTION USER1

SQL SQLORA PREPARE REPORT2 FOR
SELECT
COLUMN1
, COLUMN2
, COLUMN3
, COLUMN4
FROM USER1.TABLE2
WHERE TAB_NAME = '&TAB_NAME'
AND SEGMENT = '&SEGMENT'
AND ACCESS IN (
SELECT (CASE
WHEN '&DATA' = '1 UNITS'
OR '&ACCESS' IN ('Congested') THEN 'Congested'
ELSE NULL
END) FROM DUAL
)
AND 1 = (CASE WHEN '&DATA' = '1 UNITS' THEN (
(CASE WHEN (EXEC) LIKE ('CORE%) THEN
(CASE WHEN ACCESS IN ('Congested','65%','80%') THEN 1 ELSE 0
END)
WHEN (EXEC) LIKE ('NOT CORE%') THEN
(CASE WHEN ACCESS IN ('Congested','65%','80%') THEN 1 ELSE 0
END)
END)
)
ELSE 0
END)
;
END

TABLE FILE REPORT2
PRINT *
ON TABLE HOLD AS MY_REPORT_APP/MYXFOCUSFILE FORMAT XFOCUS
END
-RUN
-EXIT

From you above post I don't understand what you're trying to do.
You're using SQL pass through to CREATE a XFOCUS file when I though that you would like to USE as INPUT to your drilled report the XFOCUS file.

Confusing...

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

Hi,

Iam sorry for the delay in response.
Iam now using the XFocus as the input table.

Thanks a lot Martin and everyone

Regards,
IP