cancel
Showing results for 
Search instead for 
Did you mean: 

CR Designer - Web Service data driver - Need clarification on how Link tables work

daltmann
Explorer
0 Kudos

Hi everyone,

I created a report using the Web Service data driver, it's connected to two different web services and then they are linked by their IDs using Inner Join (Database Expert > Links). Everything is working fine, the data is displayed correctly on the report, the data of both tables (Web Service output/response) is linked. In terms of web services calls, there are 2, one for web service #1 and another for web service #2.

However, when the web service #2 returns more than 5000 items/records the number of web service calls are increased drastically, one for the web service #1 and one for each item/record the web service #1 returns, to the web service #2. Meaning, if web service #1 returns 500 items and web service #2 6000 items, I'll have 501 web service calls.

Can someone please explain/clarify this behavior?

Thanks, Diego

Accepted Solutions (1)

Accepted Solutions (1)

DellSC
Active Contributor
0 Kudos

When you join data from two different data sources in Crystal, the software will pull all of the data into memory and do the join (and any filtering) there. I suspect there is an inner limit of 5000 records where Crystal can easily do this in memory. If this is the case, when you go over 5000 records, Crystal will separate the data from the first query into separate records and query the second web service for each so that it can join them.

-Dell

daltmann
Explorer
0 Kudos

Hi Dell, thanks for the answer, Is that limit configurable?

DellSC
Active Contributor
0 Kudos

Not that I'm aware of. I looked at SAP KB 2165260, which has the Crystal registry settings that can be tweaked for various things, but there's nothing there that's specific to data from a web service.

-Dell

daltmann
Explorer
0 Kudos

Thanks Dell, one more question, how Crystal pass to the web service the record selection criteria that is defined at the report level? I'm asking this to "filter" the data at the web service to reduce the output and not return all records.

DellSC
Active Contributor
0 Kudos

If the web service has parameters defined that can be used to filter the data, then you need to use those. Otherwise, all of the data is pulled into memory and filtered there.

-Dell

daltmann
Explorer
0 Kudos

Thanks Dell again for the response, one more question, hopefully the last one :), is there any limit on how many records returned by the web service Crystal can handles?

DellSC
Active Contributor
0 Kudos

Not that I'm aware of, but that doesn't mean there isn't one. You also need to think about speed issues when you're loading lots of data - both in getting data from the web service and from report processing perspectives. Because you can't push processing to the database - joins, filters, etc. - when working with a web service, Crystal will handle all of that in memory.

-Dell

daltmann
Explorer
0 Kudos

That makes sense, thanks Dell.

Answers (1)

Answers (1)

JWiseman
Active Contributor
0 Kudos

hi Diego, given what Dell has mentioned about how the web service data is processed and the performance implications, is there a possibility of reworking your current report so that each web service is called only once?

e.g. Consider the following:

  1. The main report runs off of your secondary web service.
  2. Data from the main report is then stored in sets of arrays or in string running totals.
  3. All sections in the main report are hidden.
  4. In the main report's Report Footer a SubReport is inserted.
  5. This SubReport runs off of your primary web service.
  6. The data that was rolled or put into arrays in the main report is then shared with the SubReport, either via SubReport Links, or via Shared Variables, depending on the data.
  7. In the SubReport, the shared data is then parsed out.

It's a bit of report development work, but there could be a significant performance gain.

daltmann
Explorer
0 Kudos

Thank you Jamie for the recommendations.

JWiseman
Active Contributor
0 Kudos

Here is a sample report that demonstrates this technique. Change the .txt extension to .rpt.mainreportdataparsedinsubreport.txt

Note that the subreport is where all of the data is displayed. This is done so that things like charts and crosstabs can be used given the evaluation times. i.e. By passing the rolled up data to a subreport via a link, this allows the data to be available whilereadingrecords. This also allows this method to act like a left outer join as the subreport's data acts like the left side table and the parsed data acts like the right side side table.

The main report sections can be suppressed...they're only visible to show the data that is being passed to the subreport. Right now the report handles up to 2000 groups in the main report...this can be expanded but you'd need to change the formulae in the subreport as well as create additional string running totals on the main report.