Crystal Report Prompts has effect in one sub report and has no effect in other sub report

[Former Member]

Issue:

There is one requirement to create a crystal report having 2 sub reports,

1^st Sub Report should show the fund data based on country and the 2^nd Sub Report should show the country data based on Fund. We are using Bex query
as backend and it has 3 selection parameters

Selection parameters:

1. Calendar date
2. Country
3. Fund

1^st Sub Report should show ALL FUNDS for the selected COUNTRY irrespective of fund selected in selection screen.

2^nd Sub Report should show ALL COUNTRIES for the selected FUND irrespective of country selected in selection parameter.

Example:

Selection Prompt:-

1. Calendar Date: 3^rd June 2015
2. Country: USA
3. Fund: 1001

Report Output:

1^st Sub Report:

                                                         
Country: USA

Fund	Country	Date	Key Figure
1001	USA	1/1/2015	100000
1001	USA	2/1/2015	200000
1002	USA	2/1/2015	300000
1003	USA	3/1/2015	400000

*Though the fund is restricted with “1001” in selection prompt but it has no effect in 1^st Sub Report all the funds related to country “USA” is shown in report output.

2^nd Sub Report:

                                                                          
                                                                       Fund: 1001

Fund	Country	Date	Key Figure
1001	USA	1/1/2015	100000
1001	USA	2/1/2015	200000
1001	Germany	2/1/2015	150000
1001	Brazil	3/1/2015	100000

*Though the country is restricted with “USA” in selection prompt but it has no effect in 2^nd Sub Report all the country related to Fund “1001” is shown in report output.

Solution:

In order to achieve this requirement we should follow the below mention steps.

Step 1: Select the BEX query to create crystal report.

Step 2: Select the mandatory prompt if any in order to run the report.

Step 3: Don not drag any dimension or measures on Main Report (report layout) keep it blank.

Step 4: To add 1^st Sub report - Select sub report option -> choose the same query as we have used for the main report,

when the prompt window arises uncheck the below highlighted option (Use Bex query defined default values at runtime) and uncheck “Set as prompt” for Fund prompt, by doing this we will get only 2 prompts for 1^st Sub report i.e. “Calendar Date” and “Country”, -> select ok -> drag the sub report in the
body of main report, double click on sub report and drag all necessary dimensions and key figures in report layout.

 

Step 5: To add 2^nd Sub report Repeat Step 4 for but this time uncheck “Set as prompt” option for Country prompt, we will have 2 prompts for 2^nd sub report and they are “Calendar Date” and “Fund”, 

->Select ok -> drag this sub report to the body of main report.

Double click on report and choose necessary dimension and Key figure.

Step 6: After completing Step 5 we will have 2 Sub reports in the body of Main report. (For reference screen shot is attached below)

Step 7: Right Click on Sub Report 1 -> Edit Sub Report -> Go to parameter tab->Right Click on any Parameter ->Edit Subreport Data Passing Links..

Step 8: Pass the parameters “Calendar Date” and “Country” for 1^st Sub Report from main report

Step 9: Repeat Step 7 for Sub Report 2 and Pass the parameters “Calendar Date” and “Fund” for 2^nd Sub Report from main report.

Step 10: Refresh the main Report you will be able to see only 3 prompts, select prompts as required and run the report you will be able to get the desired output as mentioned in above Example.

Thanks

Anurag Singh