Skip to Content
avatar image
Former Member

Regarding sub report data source

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • Feb 08, 2017 at 08:01 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 08, 2017 at 12:53 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 08, 2017 at 01:34 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 08, 2017 at 02:11 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 08, 2017 at 02:15 PM

    following is the screenshot of data set...

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 08, 2017 at 02:19 PM

    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

    Add comment
    10|10000 characters needed characters exceeded