Create Cascading prompt for non-hierarchical data
I have requirement for creating a cascading prompt for non-hierarchical data using IDT.
Please find below the format of data.
I have Hierarchy like Student ID -> Student’s selected Courses -> Subjects selected by students.
Note: This hierarchy is not like normal Region -> State -> Branch hierarchy where 1 branch will map to only single state as well as Region,here one subject belogs to different courses.
First on IDT I have created three objects ID,Course,Subjects
Now In IDT,Please follow the steps as mentioned below:
1) Create Prompt for ID as shown below in parameter section & choose associated list of values as ID object.
2) in Parameters and Lists of Values section create LOV for ID object as shown below
Click on List of values based on business layer objects & give LOV name as Student_ID_LOV and click on EDIT query.
In query Filter pane drag ID object with operator In list and choose prompt as shown below:
3) Create LOV for Course object as shown below:
Click on List of values based on business layer objects & give LOV name as Courses_LOV and click on EDIT query.
Now Drag Course1, & ID objects in query panel and drag ID object to result panel with prompt selected as shown below:
4) Now create a Prompt for Course in Parameter section as shown below:
Where In Associated List of values section selecte previously created Course_LOV as shown below:
5) Now create LOV for subjects as shown below:
Click on List of values based on business layer objects & give LOV name as Subject_LOV and click on EDIT query.
Now Drag Subject1,Course1 & ID objects in result panel.
Drag Course1 & ID in filter query panel and filter type as prompt and choose previously created prompt Courses_Prompt & ID_prompt as shown below:
6) Now create Prompt for Subjects :
Choose previously created Subject LOV as associated list of values.
Now save the universe and publish the same.
Now create a report as shown below:
I have dragged Course , Subject & ID object in result panel and created prompted filters on subject object.
Note: while selecting a prompt I have selected “Use Universe Parameters” as shown below:
Now click on Run Query and choose ID first as shown below:
Now select Courses as shown below:
Now select Subjects as shown below:
Now on report create two variables as follows:
Now drag this newly created variables with subject object & result of report would as shown below:
If you are facing any issue creating as shown in document then please let me know.