I have requirement for creating a cascading prompt for non-hierarchical data using IDT.

Please find below the format of data.

.Table .jpg

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.

ID prompt.jpg

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.

Student lOV1.jpg

In query Filter pane drag ID object with operator In list and choose prompt as shown below:

Student lOV3.jpg

Student LOV2.jpg

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.

Courses LOV1.jpg

Now Drag Course1, & ID objects in query panel and drag ID object to result panel with prompt selected as shown below:

Courses LOV2.jpg

4) Now create a Prompt for Course in Parameter section as shown below:

Course Prompt.jpg

Where In Associated List of values section  selecte previously created Course_LOV as shown below:

Course Prompt1.jpg

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.

Subjects LOV1.jpg

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:

Subjects LOV3.jpgSubjects LOV4.jpg

6) Now create Prompt for Subjects :

Subjects prompt.jpg

Choose previously created Subject LOV as associated list of values.

Subjects prompt1.jpg

Now save the universe and publish the same.

Now create a report as shown below:

Report.jpg

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:

report prompt.jpg

Now click on Run Query and choose ID first as shown below:

Prompt1.jpg

Now select Courses as shown below:

/wp-content/uploads/2015/05/prompt2_714409.png

Now select Subjects as shown below:

/wp-content/uploads/2015/05/prompt2_714409.png

Now on report create two variables as follows:

/wp-content/uploads/2015/05/variable1_714411.png

/wp-content/uploads/2015/05/variable2_714412.png

Now drag this newly created variables with subject object & result of report would as shown below:

/wp-content/uploads/2015/05/report1_714414.png

If you are facing any issue creating as shown in document then please let me know.

Thanks,

Swapnil.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply