[solved]: Guided Adhoc

September 28, 2017, 10:00 AM

[solved]: Guided Adhoc - SubTotal

Hi,
How do I add Subtotal to Guided Adhoc Report.
Since the user can pass any By Field , not sure how to pass it for Subtotal.

Thanks

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

September 28, 2017, 11:53 AM

BabakNYC

Depending on what your sort variable is you can append the SUBTOTAL syntax to it. Let's say your sort field is called &RP_FN01B_Sort16. You can create a variable called &SUBTOTALS like this:

 
-SET &FINDIT        = POSIT(&RP_FN01B_Sort16,1250,' BY ',4,'I4');

-SET &FINDIT2       = IF &FINDIT EQ 0 THEN &RP_FN01B_Sort16.LENGTH ELSE &FINDIT;
-SET &FIRSTBY       = SUBSTR(1250,&RP_FN01B_Sort16,1,(&FINDIT2),(&FINDIT2),'A75');

-SET &SUBTOTALS     = IF &SUBTOTALS EQ 'Off' OR '' THEN 'FOC_NONE' ELSE 'ON ' | &FIRSTBY | ' SUBTOTAL' ;

Then in your TABLE FILE part just place &SUBTOTALS somewhere after the WHERE.

September 28, 2017, 12:21 PM

MartinY

-SET &BY1 = 'COUNTRY';
-SET &BY2 = 'CAR';
-SET &SUM1 = 'RETAIL_COST';

TABLE FILE CAR
SUM &SUM1
BY &BY1
BY &BY2
ON &BY1 SUBTOTAL
END

September 28, 2017, 02:03 PM

BabakNYC

I assumed by guided adhoc, BM wants to combine the BY fields into one list like this:

  
-SET &FINDIT        = POSIT(&RP_FN01B_Sort16,1250,' BY ',4,'I4');

-SET &FINDIT2       = IF &FINDIT EQ 0 THEN &RP_FN01B_Sort16.LENGTH ELSE &FINDIT;
-SET &FIRSTBY       = SUBSTR(1250,&RP_FN01B_Sort16,1,(&FINDIT2),(&FINDIT2),'A75');

-SET &SUBTOTALS     = IF &SUBTOTALS.(<Off>,<On>).Subotal?. EQ 'Off' THEN 'FOC_NONE' ELSE 'ON ' | &FIRSTBY | ' SUBTOTAL' ; 

-TYPE &SUBTOTALS

TABLE FILE CAR
SUM RETAIL DEALER
BY &RP_FN01B_Sort16.(BY(<COUNTRY>,<CAR>,<MODEL>)).Select Sort fields.
&SUBTOTALS
END