Linked subreports - LawsonGuru.com - LawsonGuru.com Forums -

Name	Points
Greg Moeller	4184
David Williams	3349
JonA	3291
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1369
Roger French	1315
mark.cook	1244

ChrisM

Basic Member

Posts: 9

12/17/2007 11:03 AM

Hello

Crystal XI and SQL Server 2000

I've created a linked subreport, so that the main report can collect two parameters, company group and fiscal year, from lists rather than the user typing them in. The linked subreport picks up these two parameters and then collects another three. This works a treat in Crystal and means I get the best of both worlds - server-side filtering and parameters from picklists. However, when I publish the report and attempt to run it, I get two parameter sheets - the first is Lawson and is for company group and fiscal year, the second is bare Crystal and is for the final three parameters. Then the report doesn't run, it errors [Error Failed to open the connection. Failed to open the connection. C:\Temp\{CF6F6ABA-BC27-424C-9014-F5F87FB7D46B}.rpt]. The connection is the same for the main and the subreport but the data sources are different. Both reports reside in the same directory.

Can I get around this? Will Lawson work with subreports?

Many thanks

ChrisM

John Henley

Posts: 3353

12/17/2007 3:20 PM

Which data provider are you using for the main report, and for the subreport?

Chris Martin

Veteran Member

Posts: 277

12/17/2007 4:59 PM

What version of LBI are you on? I believe that there were a number of related fixes with 9.0.1.6.

ChrisM

Basic Member

Posts: 9

12/17/2007 6:15 PM

John, the main report connection type is ODBC(RDO), and the subreport is the same connection type. They are also using the same connection. The connections are not to PROD, they're to a reporting db on the same server.

Cheers

ChrisM

ChrisM

Basic Member

Posts: 9

12/17/2007 6:16 PM

Posted By Chris Martin on 12/17/2007 11:59 AM

What version of LBI are you on? I believe that there were a number of related fixes with 9.0.1.6.

How do I find this out, Chris?

John Henley

Posts: 3353

12/17/2007 6:28 PM

In left-hand menu in Reporting Services Report Administration, under the grouping "Server Administration" there is an option called "About Reporting Services" which will display the LBI version number.

John Henley

Posts: 3353

12/17/2007 6:31 PM

Chris,
I did a simple report using latest version of LBI (9.0.2.2) and confirm what you are seeing (two separate parameter pages -- one for the main report and one for the subreport).
What you would need to do is to prompt for ALL the parameters in the MAIN report, and then add those to the subreport links, then in your subreport use the parameters passed from the main report instead of the parameters you defined in the subreport. Then delete the parameters you have defined in the subreport. This will force one parameter page--the one for the main report.

ChrisM

Basic Member

Posts: 9

12/17/2007 6:46 PM

Posted By John Henley on 12/17/2007 1:28 PM

In left-hand menu in Reporting Services Report Administration, under the grouping "Server Administration" there is an option called "About Reporting Services" which will display the LBI version number.

Product: Reporting Services
Version: 9.0.1.8.45
Support: http://support.lawson.com

Maximum Memory: 1024 MB
Total Memory: 129 MB
Free Memory: 66 MB

U.S. Patent #6,651,055 and other patents pending.
Copyright © 2006 Lawson Software, Inc., All rights reserved.

ChrisM

Basic Member

Posts: 9

12/17/2007 6:48 PM

Many thanks John. It's 19:50 here in Reading, UK, unfortunately it will now have to wait until early tomorrow. I'll let you know how it goes with this version.

Cheers

ChrisM

ChrisM

Basic Member

Posts: 9

12/18/2007 1:16 PM

Hello

One step further today.
I've changed the two connections in the report back to the connection which was originally defined for PROD, which just happens to be the name of the server (not the SQL Server) The new connection pointed to my reporting db on the same server / SQL Server. The linked report now works, all of the parameters are on the same sheet, CompanyGroup and Fiscal_Year are both selected as discrete values from lists. The last remaining problem is this - how do I set up CompanyGroup and Fiscal_Year to be dynamic prompts in Lawson? Making them dynamic prompts in Crystal is straightforward, but once published they don't work, the prompt is looking for a discrete value. For Crystal, I've set up views (on my reporting server) for both so that the list can be managed outside of the report. I'm guessing that if Lawson is unable to correctly map to a dynamic list from Crystal, then there will be an alternative means within LBI for accomplishing this?

Cheers

ChrisM

Chris Martin

Veteran Member

Posts: 277

12/18/2007 1:41 PM

LBI does not support Crystal Reports XI's dynamic parameters. This would require a customization to the LBI code to accomplish.

ChrisM

Basic Member

Posts: 9

12/18/2007 1:46 PM

Thanks Chris.

I noticed when I tried to set up parameter lists in LBI that there appears to be provision within it for retrieving and using live lists. Perhaps this would be functionally equivalent - setting up a list in LBI from which to select a single value to be passed to Crystal?

Chris Martin

Veteran Member

Posts: 277

12/18/2007 2:34 PM

There is no delivered way of "setting up a ist in LBI from which to select a single value to be passed to Crystal". Accomplishing dynamic parameters in LBI would require (at minimum) modifications to existing RS jsp and xsl files as well as server/web.xml modifications to establish connectivity to the database that contains your dynamic values.

John Henley

Posts: 3353

12/18/2007 6:23 PM

Based on the latest LBI version 9.0.2.2 (I'm not sure if this is a new feature or not), you can remove the LBI parameter page, and LBI will pass control to the Crystal Parameter page. I found this so interesting, I posted a tip on how to do it: (see https://www.lawsonguru.co...ameters-in-LBI.aspx)

Chris Martin

Veteran Member

Posts: 277

12/18/2007 6:49 PM

I should've stated that there is no way to do this (without customization) using the LBI parameter page. Thanks for clarifying John.

ChrisM

Basic Member

Posts: 9

12/18/2007 7:09 PM

We're on Version: 9.0.1.8.45 but this is so interesting that I'll have to have a chat with my manager about an upgrade! Many thanks, John & Chris!

Chris Martin

Veteran Member

Posts: 277

12/18/2007 7:13 PM

Chris,

You'll want to test it out to make sure (I don't currently have access to your specific version), but what John is suggesting should work with your version also. Check out this KB article:

http://kmcollections2.law...&tab=4&productid=REP CRYSTAL&release=9.x 9.0.x&platform=&database=&docid=555892&title=Does Reporting Services support use of Dynamic or Cascading parameter prompts in Crystal?&author=4202&audience=all&ct=&type=QuestionAnswer&qQuery=dynamic parameters&clientId=6051

If you have trouble accessing that link, search for Article ID 555892 on the Lawson support site.

ChrisM

Basic Member

Posts: 9

12/19/2007 5:41 AM

It works fine on our versions of LBI and Crystal.
The modified report I’ve been testing on in Crystal (Summary P&L Report - Consolidated (NH707)) works perfectly now:
1) All parameters are picked up on one sheet
2) It returns a restricted result set from the server of a few hundred rows
3) It opens quickly
4) Company Group and Fiscal year are selected from lists
5) These lists are dynamic – the list content comes from the server, it’s not hard-coded in the report
6) The data is retrieved using a parameterised stored procedure located in the reporting db

Exactly what we were looking for. Many thanks guys!