Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

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


Mohammad Faran Shabbir

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...