Skip to Content
0

Regarding sub report data source

Feb 08, 2017 at 06:29 AM

80

avatar image

Hi All,

Greetings!

I am developing a little complex report in Crystal Reports and I am having an issue in sub report data source. The main report is getting data set from a stored procedure which is fairly complex and use to take little more time to execute, around 10+ seconds (as it has lot of complex calculations).

Now, I need to insert total 5 different sub reports which will work on a subset of data from the main data set (main report data set). This is I need to do in sub report only because I need to do some sub groping (I can achieve this only by sub report). Now the problem I am facing is that when the report is running, it is taking huge time due to those 5 sub reports, which in turn calling the same stored procedure (with more filtering - where condition).

My question is, can I pass the sub set of the main data set from main report to sub report? if yes, how? if no, can anyone suggest other approach to fix this issue?

For your reference, I am using SAP BusinessObjects Crystal Reports 2013 Support pack 1. (version 14.1.1.1036)

Thanks in advance!

Regards,

Sanjoy Jana.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

6 Answers

Abhilash Kumar
Feb 08, 2017 at 08:01 AM
0

Hi Sanjoy,

What filtering do you do in these Subreports?

Are those filters in the Subreports' Record Selection Formula or are they passed directly to the subreport (via a prompt)?

-Abhilash

Share
10 |10000 characters needed characters left characters exceeded
Sanjoy Jana Feb 08, 2017 at 12:53 PM
0

Abhilash,

The stored procedure (SP) we are using is all dynamic in terms of selection criteria - the WHERE clause we prepare from front end and pass to the SP, the query get created inside the SP with WHERE clause and then get executed. The same thing is happening for the sub report - the WHERE clause is generated in the main report and that is being passed to sub report through link parameter and the process is working fine. The only issue is for each group in the main report, the sub report is getting called from main report and the sub report is again calling the same SP with more filters. It is like for N groups in main report, the SP is getting called (5 * N) + 1 times. There it is chocking.

Is the any way I can pass sub set of main report's data set to sub report as its data set?

-Sanjoy Jana

Share
10 |10000 characters needed characters left characters exceeded
Abhilash Kumar
Feb 08, 2017 at 01:34 PM
0

If you've placed the Subreport on a Group Section, the Subreport will run as many times as a Group section appears in the Main Report.

Could you please elaborate on

Is the any way I can pass sub set of main report's data set to sub report as its data set?

Could you give an example of what you're trying to pass to the Subreport?

-Abhilash

Share
10 |10000 characters needed characters left characters exceeded
Sanjoy Jana Feb 08, 2017 at 02:11 PM
0

Name Age Gender State Salary Address

AAA 50 M WB 1500000 hasjha jakFHK

BBB 40 F AP 1000000 hasjha jakFHK

CCC 35 M KA 1000000 hasjha jakFHK

XXX 35 M MP 800000 hasjha jakFHK

YYY 40 F KA 1000000 hasjha jakFHK

ZZZ 25 M AP 400000 hasjha jakFHK

Suppose the above data set (from SP after lot of processing) is the main report data set, now I have to pass All those records which has 'State' = 'AP' to sub report; so eventually 2 records will be selected. I need to pass these two record only to sub report.

In current scenario, you have to call same SP from sub report with WHERE clause as " WHERE State = 'AP'". Instead of that, I want to pass those 2 records to sub report from main report.

-Sanjoy

Share
10 |10000 characters needed characters left characters exceeded
Sanjoy Jana Feb 08, 2017 at 02:15 PM
0

following is the screenshot of data set...


untitled.jpg (115.9 kB)
Share
10 |10000 characters needed characters left characters exceeded
Abhilash Kumar
Feb 08, 2017 at 02:19 PM
0

You can't pass 'rows' from a Main Report to a Subreport.

Anyway, what is the purpose of this Subreport? What do you wish to further do with this Subset of data?

-Abhilash

Share
10 |10000 characters needed characters left characters exceeded