[SOLVED] Summary & Detail Report

September 22, 2010, 10:44 AM

I want to write a report that Will have Summary and detail information. Any ideaa or help how can I start:

Summary Totals Whole company :
Period 1 Period 2   Period 3

Detail Totals:
Division 1
Period 1 Period 2   Period 3
Division 2
Period 1 Period 2   Period 3
Division N
Period 1 Period 2   Period 3

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

September 22, 2010, 11:14 AM

njsden

Search documentation for multi-verb requests to see if it helps.

TABLE FILE blah
-* Summary
SUM
   ....
BY ....
BY ....
-* Details
PRINT ...
      ...
BY ....
BY ....
END

If not, compound reports are always an option.

September 22, 2010, 01:29 PM

Arif

That works but it creates more columns than i want to see in report

Forexmaple:

  
TABLE FILE CAR
SUM
SALES
RETAIL_COST
DEALER_COST

BY
COUNTRY

PRINT
  SALES
RETAIL_COST
DEALER_COST
 BY 'CAR.ORIGIN.COUNTRY'
It createes
[CODE]
COUNTRY SALES RETAIL_COST DEALER_COST SALES RETAIL_COST DEALER_COST

I just want:
COUNTRY SALES RETAIL_COST DEALER_COST
[/CODE]

September 22, 2010, 01:48 PM

Francis Mariani

Many ways to do this, here's one:

TABLE FILE CAR
SUM
SALES
RETAIL_COST
DEALER_COST
COMPUTE MODEL/A24 = '';
BY COUNTRY
ON TABLE HOLD AS H001
END

TABLE FILE CAR
PRINT
SALES
RETAIL_COST
DEALER_COST
BY COUNTRY
BY MODEL
ON TABLE HOLD AS H002
END

TABLE FILE H001
PRINT
SALES
RETAIL_COST
DEALER_COST
BY COUNTRY
BY MODEL NOPRINT
ON MODEL SUBHEAD
"MODEL: <MODEL"
WHEN MODEL NE ''
ON MODEL SUBHEAD
"SUMMARY TOTALS:"
WHEN MODEL EQ ''

MORE
FILE H002
END

September 22, 2010, 02:29 PM

Arif

Great it seems like the solution. One more question in this report can I have user selected output formate. For example, user runs the report using dashboard and select the formate, Excel, HTML, ATHML, or PDF?

Arif

TABLE FILE CAR
PRINT
SALES
RETAIL_COST
DEALER_COST
BY COUNTRY
BY MODEL
ON TABLE HOLD AS H002
END

TABLE FILE H001
PRINT
SALES
RETAIL_COST
DEALER_COST
BY COUNTRY
BY MODEL NOPRINT
ON MODEL SUBHEAD
"MODEL: WHEN MODEL NE ''
ON MODEL SUBHEAD
"SUMMARY TOTALS:"
WHEN MODEL EQ ''

MORE
FILE H002
END[/quote]

September 22, 2010, 02:48 PM

Don Garland

You can use HTML Composer and create a launch page

< !-- Generated by Report Layout Painter -->
< !DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<HTML>
<HEAD>
<META id=mycharsetmeta http-equiv=Content-Type content="text/html; charset=ISO-8859-1">
<META id=Generation content="Created in release 7611, Generation 3.0">
<script id=IBI_RelCallBack type=text/javascript>function AdjustChildrenPosition(){
}
</SCRIPT>

<script id=IBI_OptionsScript type=text/javascript>
var cgipath = "cgipath";
var ibirls = "ibirls3";

var rltdyncalendar = "rltdyncalendar";
var gmap = "ibigmap";
var olap="olap";
var olappanebase="olappanebase";
var olapdrill="olapdrill";

var ibixmltree="ibixmltree";

var ibiOptions = new Array(cgipath,ibirls);
</SCRIPT>

<script id=IBI_nls src="/ibi_html/javaassist/nls.js" type=text/javascript></SCRIPT>

<script id=IBI_nlsVars src="/ibi_html/javaassist/nlsvars.js" type=text/javascript></SCRIPT>

<script id=IBI_ibigbl src="/ibi_html/javaassist/ibi/html/js/ibigbl.js" type=text/javascript></SCRIPT>

<script id=IBI_ibigblloadCss type=text/javascript>
ibigblloadCss(null);
addIntlTranslatedJS("composertrans.js");
</SCRIPT>
<TITLE>HtmlPage</TITLE>
<script id=clientEventHandlersJS type=text/javascript>
//Begin function window_onload
function window_onload() {
 
UpdateData();
 
// TODO: Add your event handler code here
//add onInitialUpdate() function to make changes before initial run of the reports
}
//End function window_onload
</SCRIPT>

<script for=window eventname="onload">window.onload = function() { window_onload(); }</SCRIPT>

<META content="MSHTML 6.00.6000.17080" name=GENERATOR></HEAD>
<BODY style="OVERFLOW: auto" persistentuniqueid="compUid_1" nextelementuniquenumber="3" elementtype="21" edaconnectionrequired="true">
<iframe id=report1 style="Z-INDEX: 1; LEFT: 100px; OVERFLOW: auto; WIDTH: 490px; POSITION: absolute; TOP: 235px; HEIGHT: 320px" tabIndex=1 src="" persistentuniqueid="compUid_2" elementtype="2" executebuttonId="form1Submit" requests_list="0" autoExecute="False" name="report1"></IFRAME>
<FORM id=form1 style="Z-INDEX: 2; LEFT: 100px; WIDTH: 114px; POSITION: absolute; TOP: 130px; HEIGHT: 105px" onsubmit="OnExecute[this);return false;" method=post requests_list="0" fexlist_list="report1" form_type="2" default_slider_type="4" form_prompt_location="1" form_number_of_visible_rows="4" form_number_of_columns="4" vert_dist_between_controls="10" form_hor_dist_between_controls="10" form_dist_between_desc_and_input="10" name="form1">
<INPUT id=form1Submit style="Z-INDEX: 3; LEFT: 5px; BACKGROUND-IMAGE: url[/ibi_html/javaassist/ibi/html/describe/run16.gif); WIDTH: 38px; BACKGROUND-REPEAT: no-repeat; POSITION: absolute; TOP: 78px; HEIGHT: 22px; BACKGROUND-COLOR: lavender" tabIndex=2 type=submit value=" " name="ITEM1">
<INPUT id=form1Reset style="Z-INDEX: 4; LEFT: 48px; BACKGROUND-IMAGE: url[/ibi_html/javaassist/ibi/html/describe/reset.gif); WIDTH: 38px; BACKGROUND-REPEAT: no-repeat; POSITION: absolute; TOP: 78px; HEIGHT: 22px; BACKGROUND-COLOR: lavender" tabIndex=3 type=reset value=" " name="reset1"> 
<FIELDSET id=form1_formbodyid style="PADDING-RIGHT: 0px; PADDING-LEFT: 0px; Z-INDEX: 5; LEFT: 5px; PADDING-BOTTOM: 0px; OVERFLOW: auto; WIDTH: 104px; BORDER-TOP-STYLE: none; PADDING-TOP: 0px; BORDER-RIGHT-STYLE: none; BORDER-LEFT-STYLE: none; POSITION: absolute; TOP: 5px; HEIGHT: 73px; BORDER-BOTTOM-STYLE: none" tempheight="73" tempwidth="104" formbody="1">
<LABEL id=label1 style="Z-INDEX: 6; LEFT: 0px; WIDTH: 82px; CURSOR: default; POSITION: absolute; TOP: 0px; HEIGHT: 51px" tabIndex=4 for=edit1 elementtype="50" name="edit1">WFFMT
<SELECT id=edit1 style="LEFT: 0px; POSITION: absolute; TOP: 29px" tabIndex=5 size=1 persistentuniqueid="compUid_3" vspace="0" indeterminate="false" CHECKED="false" width="0" loop="1" maxLength="5" hspace="0" height="66496044" readOnly="false" start="fileopen" defaultlocation="29,0,51,82" boundtovariable="1" name="edit1">
<OPTION value=PDF selected noinput="0" displaytext="PDF">PDF</OPTION>
<OPTION value=HTML noinput="0" displaytext="HTML">HTML</OPTION></SELECT></LABEL></FIELDSET></FORM>
<INPUT id=layoutinfo style="LEFT: -100px; POSITION: absolute; TOP: -100px" type=hidden resourcectrlids="form1Submit;form1Reset">
<INPUT id=IBIMR_domain style="LEFT: -100px; POSITION: absolute; TOP: -100px" type=hidden value=devgarla/devgarla.htm name="IBIMR_domain">
<INPUT id=IBIMR_folder style="LEFT: -100px; POSITION: absolute; TOP: -100px" type=hidden value=#test2oz7imos name="IBIMR_folder">
<INPUT id=ibif_ex style="LEFT: -100px; POSITION: absolute; TOP: -100px" type=hidden value=app/cartest.htm name="ibif_ex">
<INPUT id=ibiapp_app style="LEFT: -100px; POSITION: absolute; TOP: -100px" type=hidden ismre="1" name="ibiapp_app">
<xml id=focus_xmlelement>
<script type="text/xml">
<rootxmlnode top="20" left="778" width="150" height="130">
<variables>
<variable controltype="7" parametercreatedinreslay="0" type="unresolved" desc="WFFMT" name="WFFMT" default="" textvarname="" accept="0" select="0" create="1">
<link linktype="default" from="compUid_3">
<condition default="1" name="Default" whattodowithcontrol="4" valuescompareoperator="0" parameterscompareoperator="0" conditionmultiselectoperator="0">
<data_info displayfield="WFFMT" datafield="WFFMT" datasource="car.mas" datatype="0" selectedvalue="" operation="" slider_range_from="" slider_range_to="" sourcetype="typeMaster" linktype="none"></data_info></condition></link>
<requestid id="0"></requestid></variable></variables>
<input_controls>
<input_control bindcontrolid="compUid_3" elementtype="8" requiredfield="0" name="edit1" id="edit1" multiple="0" inbinding="1" onetimepopulated="1">
<link linktype="default">
<condition default="1" name="Default" whattodowithcontrol="0" valuescompareoperator="0" parameterscompareoperator="0" conditionmultiselectoperator="0">
<data_info datatype="0" datasource="car.mas" addalloption="0" sourcetype="typeMaster" cacheruntimedata="0" accept="0" operation="NONE" selectedvalue="PDF" modifiedrequest="1">
<static_values>
<static value="PDF" display="PDF" selected="1" noinput="0"></static>
<static value="HTML" display="HTML" selected="0" noinput="0"></static></static_values></data_info></condition></link></input_control></input_controls>
<other_bound_objects></other_bound_objects>
<requests nextrequestsid="0">
<request requestid="0" targetname="report1" ibif_ex="report1" targettype="iframe" sourcetype="typeAdhocfex" activereport="0" reportcolumns="" ibiapp_app="">
< ![CDATA[-* File cartest.fex
TABLE FILE CAR
PRINT
SALES
RETAIL_COST
DEALER_COST
BY COUNTRY
BY MODEL
ON TABLE HOLD AS H001
END
 
TABLE FILE H001
PRINT
SALES
RETAIL_COST
DEALER_COST
BY COUNTRY
BY MODEL NOPRINT
ON MODEL SUBHEAD
"MODEL:
WHEN MODEL NE ''
ON MODEL SUBHEAD
"SUMMARY TOTALS:"
WHEN MODEL EQ ''
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &WFFMT
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
ENDSTYLE
END
MORE
FILE H001
 
END]]></request></requests></rootxmlnode></script>
</xml></BODY>
<script id=IBI_loader type=text/javascript>
doBeforeLoad();
</SCRIPT>
</HTML>

September 22, 2010, 04:04 PM

saikumar

Arif,

you can also try in a simple way like below:

Top of report
-DEFAULTH &OUTFORMAT = 'HTML';

final display after WHERE clause
ON TABLE PCHOLD FORMAT &OUTFORMAT

Thanks,
Saikumar.

September 23, 2010, 11:46 AM

Arif

Great thank you all for your help!