Using app studio. I am developing a dashboard-style report, a single screen with a layout that encompasses three table reports, a bar-chart, and a panel to serve as the target for drilldowns from the bars of the chart. My report is one of several that the user can select from a common launch-page, which will supply parameters reflecting options and filters selected by the user. The report will open in a separate window.

I've designed the page using the HTML/Document editor in App Studio. That's pretty easy. And I have procedures that generate the component outputs, given the parameters. But I don't understand how the HTML page can be utilized to fulfill my requirements.

a. The components on the page all need to use the parameters passed from the launch page. In the HTML/Doc facility, there seems to be an assumption that the external procedure is self-contained. How do the component fexes each "receive" the user's options and filters?

b. The data retrieval, from the Oracle tables, is similar for all my tables and the chart, and is time-consuming. To save execution time, I would like to first pull the data into a hold file that will serve as the common data source for the chart and reports, and then let the fexes for the respective output components run against the one hold file. How do I ensure that the process to form that hold file precedes the running of all the outputs?

c. How does the report fex that is called by the launch page invoke the html file?

I'd welcome comments and suggestions from anyone who's been there and done that,

This message has been edited. Last edited by: j.gross,

Hi Jack,
What you are describing sounds like a fairly straight forward stand alone application.
I've not used App Studio yet, but my guess is the HTML Composer in Dev Studio works similarily.

You might want to try this as a test with an HTML page and 1 report with filters that need to link up to parameters submitted from the HTML page.

Your HTML Page will have a RUN correct?

Create your HTML Page.
Create your parameter controls and populate them.
Create your report procedure with your variables in it. In this procedure I would resolve your variables by using a
-DEFAULT
(Ex:-DEFAULT &VARIABLE_NAME = 'APPROPRIATE VALUE'
Add your Run Button and when you hook the run button up do the following.
Right click and choose create Hyperlink
Add an action and choose External Procedure
Choose your procedure with the parameters.
Target type is window.
Target/Template is new window.
When you click ok you will get a New Parameters box. There are actually many way to do this. You should see the variable names from your procedure.
Under control type click the elipse and choose the parameter control that you want associated with a variable.
I choose Parameter groupign options: Do not create a form. Click OK. Your parameters and variables should now be linked and initialized. Go to the parameters tab and you should see the link.
Run it and see if it works.

If you suspect the parameter is not passing the variable, but the following at the top of your external procedure.

-? &
-EXIT

Then run the procedure through the HTML launch page run button. This should give you a list variables available to that procedure.

If you do not see it. Remove the code I gave you above and try right clicking your run button. Choosing Hyperlink Properties and see of you have unresolved variables.
If the composer puts group boxes on your HTML page that you don't want, just delete them.

Like I said there are many ways to go about this. This might get you going.

There are classes that cover this. I'm not sure of the most current course number. The online instructor lead are very good and they would walk you through what you need to know.

Please be aware that things will probably look a little different in App Studio and I'm giving you what I see in Dev Studio.

Good Luck.

Sorry, I don't know why the last message popped in a winking smiley face.

As to your question about how to extract data to use for all reports on your HTML page.

I'm going to point you to FOCCACHE.
Here is some information:

FOCCACHE - Reuse data within the same browser session (HOLD, SAVE, SAVB)
Hold table goes away when web browser is closed or 2 hours whichever is shortest.
Code Example:
ON TABLE HOLD AS FOCCACHE/HOLDFILE_NAME
END

I encourage you to do some document searching to understand more. I'm sure you will get some more information from others. I suggest you get the parameters working first on test version of the data.

You can save off a copy of your HOLD file temporarily for testing.

I usually save my temp HOLD files to BASEAPP. I usually create a HOLD file for this purpose with the format XFOCUS.

Before the step that creates the hold file put the code:
APP HOLD BASEAPP

This should write the hold file to BASEAPP and be available for all your reports. Remember to delete it when you're ready to move on to FOCCACHE.

Msondra, I think your "winkie" was caused by you typing a semicolon followed by a right parenthesis.

There are a lot of elements in your requirements.
A. So first, it is a good idea to have a separate procedure that runs ahead of the various reports and charts and extracts all of the data from ORACLE that will be used by each of the reports and charts.
B. This extracted data should be saved as a named HOLD file (call it say CHART_DATA) and saved into an area called FOCCACHE. Make sure to research and read about FOCCACHE.
C. Then all of the reports and charts should be built using CHART_DATA. Each of these reports and charts should not need any WHERE statements.

Sandy --

To complete the picture, I understand your suggestion would have the launch page a fex, which will first do some prep (generate the Hold file or files to foccache), and after that -HTMLFORM the .htm file.

-- My recollection is that Product at one point objected to invoking .HTM layout files via -HTMLFORM. Perhaps that has changed. --


As I understand the architecture, the web-page in turn, once it is rendered in the browser, shoots requests to WF to run the reports, targeting them to their respective containers in the web-page.

If there are parameters (e.g., formatting options, rather than filters) that need to be available to the component report procedures, the prep step can store (HOLD) them too in foccache, and the downstream procedures can simply -READFILE them in.


Is that accurate?

-Jack

Another technique that you might want to consider is using the JQuery get() or post() functions to run your initial request for the focacche hold file, then on the completion of that request fire of the subsequent requests.

You can use the javascript drill down option as a way to pass variables back up the parent page, and fire off other reports, drill downs as well.

The WF architecture for running reports/html pages is a rather serial process. Using JQuery you can build dependencies and run things in parallel when applicable.

This really only touches on one aspect of what your trying to accomplish, but thats a pretty big high level statement in your question. Its probably best to break it into its individual elements for more concise advice.

Regarding data retrieval, if appropriate I prefer to run a nightly job in ReportCaster to handle that piece and create a permanent app table. Whatever the target DB for your app table (FOCUS or Oracle), put indexes on the parameter fields to get the best performance for the WHERE clauses in the report/graph programs.

quote:

Originally posted by eric.woerle:
Another technique that you might want to consider is using the JQuery get() or post() functions to run your initial request for the focacche hold file, then on the completion of that request fire of the subsequent requests.

You can use the javascript drill down option as a way to pass variables back up the parent page, and fire off other reports, drill downs as well.

The WF architecture for running reports/html pages is a rather serial process. Using JQuery you can build dependencies and run things in parallel when applicable.

This really only touches on one aspect of what your trying to accomplish, but thats a pretty big high level statement in your question. Its probably best to break it into its individual elements for more concise advice.

That sounds enticing, but I'd really have to ramp up on Jquery to feel confident I can pull that off. Maybe next time around. But meanwhile I'd appreciate some starter-set code illustrating scheduling the reports to wait for the extract to complete and then run as parallel queries against the WFRS.


Regarding the FOCCACHE approach in general, I wonder - Is that limb sturdy enough to support a production application? Can some members who've ventured out on that limb chime in with their war stories? --- I'd like to know what members' experiences have been --- Unqualified successes, qualified successes (and caveats), and abject failures.

-Jack

Here is a copy of one of the functions I've created using the ajax function from JQuery. In this example I have to prime my data, then run the report, then run a follow up report upon completion. I hope this gives you an idea of what I'm talking about with using JQuery. This assumes that all of the "Clickable" elements are on your parent page. But there are also ways to call parent functions from child elements if you need to have a report execute multiple other reports on the page. Basically, your Drilldown can execute the OnExecute() function on your HTML page, and load other graphs along with the drill down etc.

//Begin function load images
function load_images(execForm){
$("#dvLoading").css({'display':'block'});
document.getElementById('report1').onreadystatechange = function(){};
document.getElementById('go').onclick=function(){};
document.getElementById('resetAll').onclick=function(){};

//In this project I had to do a primer of images that could change at any time.  I use the ajax command from JQuery and issue a post. This fires off a fex, that issues a linux command to run an ETL and waits for a response
$.ajax({
	type: 'post',	
	url: 'ibi_apps/WFServlet.ibfs',
	data: {IBIMR_action: 'MR_RUN_FEX',
		 IBIMR_sub_action:'MR_STD_REPORT',
		 IBIMR_fex: '/WFC/Repository/BPP_Reporting/UC_Registry/bppreg1025.fex'}
	 })
.done( function(returnData){
// When the data returns I take the returned value and put it to a variable because I need to pass it on to my next fex
		var timesCompleted = 0;
		var randomNumber = replace_string(returnData);
		document.getElementById('inputhidden1').value = randomNumber ;
// I use IBI's OnExecute function to run my report
		OnExecute(execForm);
		$("#dvLoading").css({'display':'none'});
// I then have to run a follow up process once the report has completed running and returned to the user.  I first have to know what browser the user is using 		
		if(	navigator.appName == 'Microsoft Internet Explorer'){
// If the browser is Microsoft Internet Explorer, I have to use the onreadystatechange attribute and wait for it to complete.  Because IBI first fills the page with a loading GIF, I 
// have to count the amount of times the ready state completes and run the last fex after the appropriate amount of "Completions"
			document.getElementById('report1').onreadystatechange = function(){
				
				if(document.getElementById('report1').readyState == 'complete'){	timesCompleted=timesCompleted + 1; }
				if (timesCompleted == 2){
					document.getElementById('go').onclick=run_procedures;
					document.getElementById('resetAll').onclick=resetAll;
				}
				if (timesCompleted == 3){
					$.ajax({
						type:'post',
						url: '/ibi_apps/WFServlet',
						data: {IBIMR_action: 'MR_RUN_FEX',
					   		IBIMR_sub_action: 'MR_STD_REPORT',
		   					IBIMR_fex: '/WFC/Repository/BPP_Reporting/UC_Registry/bppreg1027.fex',
							RAND_NBR: randomNumber,
							TERM_CD:document.getElementById('combobox1').options[document.getElementById('combobox1').selectedIndex].value }
					})
					.done (function(returnData){
						IbComposer_ResetDownChainControls('combobox1');
						timesCompleted = 0;
						document.getElementById('go').onclick=run_procedures;
						document.getElementById('resetAll').onclick=resetAll;
						document.getElementById('report1').onreadystatechange = function(){};

					});
				}
			}
		}
		else{
// For Firefox or Chrome, I can use JQuery's bind as they don't have onreadystate like IE (I think thats why I did this.  its been a year since I built this).  Again I need to count the 
// the completions			
			$( "#report1" ).bind("load.idCard",function( event ){
				timesCompleted=timesCompleted + 1;
			//	alert(timesCompleted + " from bind");
				if (timesCompleted == 2){		
					$.ajax({
						type:'post',
						url: '/ibi_apps/WFServlet',
						data: {IBIMR_action: 'MR_RUN_FEX',
					   		IBIMR_sub_action: 'MR_STD_REPORT',
		   					IBIMR_fex: '/WFC/Repository/BPP_Reporting/UC_Registry/bppreg1027.fex',
							RAND_NBR: randomNumber,
							TERM_CD:document.getElementById('combobox1').options[document.getElementById('combobox1').selectedIndex].value }
					})
					.done (function(returnData){
						IbComposer_ResetDownChainControls('combobox1');
						timesCompleted = 0;
						document.getElementById('go').onclick=run_procedures;
						document.getElementById('resetAll').onclick=resetAll;
						$( "report1" ).unbind( event );
					});
				}
			});
		}
	});
}
//End function load images  

I use FOCCACHE pretty regularly. It seperates files from one another so that users can run the same reports with different results, and then continue to interrogate their specific results. The above example uses FOCCACHE and has been in production for over a year now. This "application" allows the registrar team to generate student ID cards. This can be bulk or individual runs of the ID cards.

If you want everyone to have the same data, consider using reportcaster to write the FOCUS file after each of your ETL runs. Then its always available and people don't have to wait for that initial run.

Eric --

Some follow-up questions re FOCCACHE.

1. What controls how long foccache persists. I presume there's some config setting that controls when the folder expires.

2. If I run multiple instances of BI Portal in tabs in a single IE instance, or in separate IE instances (as in, open in a new window) -- will those get separate foccache folders, or will they share one -- basically, what defines a "browser session"?

Hi Jack,

I had a similar request -- I did not use Foccache although it sounds like that is fine.
Ended up creating separate Focus databases from the SQL tables. With proper hierarchical design and indexing you will get the speed you need.

You can set up your fexes to load the databases through reportcaster or by using deferred processing. Run those overnight so they are ready for the user in the morning -- if that timing works in your environment.

As Msondra said you will be able to pass all of your parameters from the html screen - just make certain that they are all resolved before trying to run. You can use -DEFAULT &XXX in your fexes, then, when you reference the fex in the html and set up the hyperlink, the variables will be there.

If you create Focus DBs, you do not have to be concerned with timing out or losing a cached area -- users will select their own filters through your html scrren --

As far as drill downs, etc, those can be done the usual way in the stylesheet.

Hope this helps.

Vivian

quote:

Ended up creating separate Focus databases from the SQL tables. With proper hierarchical design and indexing you will get the speed you need.

The Oracle data table, with additive measures, has over a dozen primary-key component columns which the user may filter or sort on, and about a half-million rows.

I was thinking along your lines, of creating a hierarchical Focus database. That led me to wondering what is the best way of
(1) ordering the segment key fields, and
(2) deciding whether to devote a segment to each or to combine some ("highly correlated") consecutive keys.

-- My hunch is that the top segment key should be the one that has the fewest distinct values (the one that, if it were the sole key, would yield the lowest cardinality). And, proceeding inductively, after n top keys have been identified, the next one (#n+1 in the series) should be the one induces the least increase in cardinality: the one among the remaining candidates that yields the smallest cardinality for a projection of the data on the list of n+1 keys when it is used in the final slot.
-- That approach, by pushing the higher-fragmentation branch-points further down the segment list, should result in the fewest possible segment instances in the Focus file, and hence the greatest efficiency in extracting the answer-set for a query. Note that the row count for the lowest segment in the structure is invariant.

Anyone with practical (production) experience to cite, please chime in.


It also occurred to me that one could create multiple projections, each covering just a subset of the available dimensions. If we maintain a control table, listing
- the available data sources;
- which of the dimension keys of the full table each covers and which not;
- and the cardinality of each table,
then the application could analyze the filter parameters and aggregation parameters of a query, determine which available tables cover all the requisite keys, and direct the query to that table. Such a structure (of materialized projections, their metadata, and a decision tool for selecting the minimal adequate data source), would make the data structure quite tunable. Sort of a poor man's cube.

Again, has anyone out there explored such a structure for online queries of aggregated measures?


--- I'm opening this as a separate thread.

This message has been edited. Last edited by: j.gross,