hi, I have two drop downs in my report. The first one has tw...

[Mohammad Faran Shabbir]

hi,

I have two drop downs in my report. The first one has two values

 

Sales

Profit

Based on the selection on this drop down I want to show

Sales Amounts

Sales Increase

if Sales is selected in first drop down otherwise Ill show

Profit Amounts

Profit Increase

Is it possible Any help/hint is highly appreciated.

Thanks

[Les Johnson 3]

Have 2 drop-downs for Profit/Increase and use JavaScript to display/hide depending on which Sales/Profit is selected.

[Mohammad Faran Shabbir]

Thanks for your response. So javascript is the only way or is there some way to do it in InfoAssist as well

[Martin Yergeau]

You need to create (or have) a file that contains your available selections and have your two drop down (DD) pointing to the same source.

Then DD-1 use CODE1 and DD-2 use CODE2. Automatically, when the selection will be made on DD-1, DD-2 will adjust its available selections

Here a sample

DEFINE FILE CAR

ORDER /I1 = IF COUNTRY EQ 'ENGLAND' THEN 1 ELSE 2;

CODE1 /A10V = IF COUNTRY EQ 'ENGLAND' THEN 'Sales' ELSE 'Profit';

CODE2 /A20V = IF COUNTRY EQ 'ENGLAND' THEN (IF CAR EQ 'JAGUAR' THEN 'Sales Amounts' ELSE 'Sales Increase')

ELSE (IF CAR EQ 'ALFA ROMEO' THEN 'Profit Amounts' ELSE 'Profit Increase');

END

TABLE FILE CAR

BY ORDER NOPRINT

BY CODE1

BY CODE2

BY CAR NOPRINT

WHERE (COUNTRY EQ 'ENGLAND' AND EDIT(CAR, '9') EQ 'J') OR COUNTRY EQ 'ITALY';

ON TABLE PCHOLD FORMAT XML

END

[Manoj Chaurasia]

Hi Mohammad

Welcome to myibi. I havent done this in a while but one other option that may be helpful to you in the HTML composer on the parameters tab. If you click on the line between the chaining link and go to the settings there are conditional settings you can try.

 

image.png16721005 88.5 KB

[David Beagan]

Hi Mohammed,

Is there a field in your reporting data source with values

Sales

Profit

 

And a field in your reporting data source with values

Sales Amounts

Sales Increase

Profit Amounts

Profit Increase

 

If you do have fields with the values then you could use WITHIN in the metadata to create a hierarchy relationship between these two fields.

Then after creating filters for these fields in InfoAssist, the parameters would get chained at runtime, in a way that exhibits the behavior you are looking for.

[Mohammad Faran Shabbir]

Thanks for your response. How can I pass variable/define to javascript

[Brent Mason 2]

Just let them choose Sales or Profit and Amounts or Increase because one can reasonably assume that the second drop down reflects the choice made in the first drop down. Reinforce this for the user with how you name the measures in the reports.

-DEFAULT &Measure = 'Actual';

-DEFAULT &Output = 'Amount';

 

-IF &Measure.(Actual,Budget). EQ 'Actual' AND &Output.(Amount,Change). EQ 'Amount' THEN GOTO Actual_Amount

-ELSE IF &Measure.(Actual,Budget). EQ 'Actual' AND &Output.(Amount,Change). EQ 'Change' THEN GOTO Actual_Change

-ELSE IF &Measure.(Actual,Budget). EQ 'Budget' AND &Output.(Amount,Change). EQ 'Amount' THEN GOTO Budget_Amount

-ELSE GOTO Budget_Change

 

-Actual_Amount

 

TABLE FILE GGSALES

SUM DOLLARS/P21MBC AS '&Measure Sales &Output'

BY HIGHEST 2 DATE

 

END

-RUN

-EXIT

 

 

-Actual_Change

 

TABLE FILE GGSALES

SUM COMPUTE VAR/P21MBC = PARTITION_REF(DOLLARS, TABLE, +0) - PARTITION_REF(DOLLARS, TABLE, +1); AS '&Output in &Measure Sales,from Prior Month'

BY HIGHEST 2 DATE

 

END

-RUN

-EXIT

 

 

-Budget_Amount

 

TABLE FILE GGSALES

SUM BUDDOLLARS/P21MBC AS '&Measure Sales &Output'

BY HIGHEST 2 DATE

 

END

-RUN

-EXIT

 

 

-Budget_Change

 

TABLE FILE GGSALES

SUM COMPUTE VAR/P21MBC = PARTITION_REF(BUDDOLLARS, TABLE, +0) - PARTITION_REF(BUDDOLLARS, TABLE, +1); AS '&Output in &Measure Sales,from Prior Month'

BY HIGHEST 2 DATE

 

END

-RUN

-EXIT

[Mohammad Faran Shabbir]

Unfortunately it doesnt seem to be applicable in my situation. Anyway thanks for your help!

[Mohammad Faran Shabbir]

Hi David,

I need to show custom values based on the selection of parent filter and not the values already present in data source of child filter thats why I cant use WITHIN.

If I try to make it work programmatically, it doesnt populate the child filter, I only see All there.

-TYPE &PARENT_FILTER.(<Sales,SALES>,<Profit,PROFIT>,).PARENT FILTER.

-SET &PARENT_VALUE=IF &PARENT_FILTER EQ SALES THEN SALES ELSE PROFIT;

-TYPE &CHILD_FILTER.(&PARENT_VALUE).CHILD FILTER.

The child filter above doesnt get populated at all even though SALES is selected

[Mohammad Faran Shabbir]

hi Chuck, Thanks very much for your response. Actually I need to do it programatically since I need to show custom values in child filter based on selection of parent filter. It looks like your solution relates to applying filters on data source level.

[David Beagan]

What you are trying with the -SET &PARENT_VALUE wont work because by the time that -SET is executed, its too late.

Assuming I am understanding you correctly that the child filter values are in the data source, I tried a simple example. It seemed to work.

I created some data:

performance.txt

Sales Amount 400

Sales Increase 300

Profit Amount 200

Profit Increase 100

 

And a master file with a Define for the parent level (SALES_PROFIT field), based on the child value that is in the data (AMOUNT_INCREASE field).

performance.mas

FILENAME=SALES_PR, SUFFIX=FIX, DATASET=baseapp/performance.txt, $

SEGMENT=SALES_PR, SEGTYPE=S0, $

DEFINE SALES_PROFIT/A08 = IF AMOUNT_INCREASE CONTAINS 'Sales' THEN 'Sales' ELSE 'Profit'; WITHIN='*SALPRO',$

FIELDNAME=AMOUNT_INCREASE, ALIAS=E02, USAGE=A16, ACTUAL=A16, WITHIN='SALES_PROFIT', $

FIELDNAME=DOLLARS, ALIAS=E02, USAGE=P12, ACTUAL=A12, $

 

Then a fex to get the autoprompt

performance.fex

-TYPE &SALES_PROFIT.(<Sales,Sales>, <Profit,Profit>).

-TYPE &AMOUNT_INCREASE.(FIND AMOUNT_INCREASE IN PERFORMANCE |WITHIN=SALES_PROFIT).

 

And it seems to give the desired prompting: