Skip to Content

Issue:

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

1st Sub Report should show the fund data based on country and the 2nd 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

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

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

Example:

Selection Prompt:-

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

Report Output:

1st 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 1st Sub Report all the funds related to country “USA” is shown in report output.

2nd 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 2nd 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 1st 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 1st 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.

Prompt Window_1.jpg

 

Step 5: To add 2nd Sub report Repeat Step 4 for but this time uncheck “Set as prompt” option for Country prompt, we will have 2 prompts for 2nd 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.

Prompt Window_2.jpg

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)

Main Report Layout.jpg

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..

Data Passing.jpg

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

Parameter Passing_1.jpg

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

Parameter Passing_2.jpg

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.

Final Prompt Window.jpg

Thanks

Anurag Singh

To report this post you need to login first.

1 Comment

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

Leave a Reply