How to Insert a Subreport within a Subreport

[ccding]

CR XI; SQL Server Db

Is there an easy way to insert a subreport within a subreport?

 

[AndyMc]

ccding,

You can have as many subreports on your main report as you'd like, but subreports within subreports are not supported.

Andy

 

[Madawc]

Using Shared Variables, it is possible to pass a value from the subreport back to the main report. It could then be passed to another sureport using Edit > Subreport Links. Note that the shared variable will not have the value from the subreport until the next section.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[ccding]

That sounds good; I will give that a try. I am a bit confused about the editing the subreport links though,,, Wouldn't that upset the links to the main report?

 

[lbass]

You don't need to use links to pass a shared variable. You can just reference the variable in a subsequent subreport.

-LB

 

[ccding]

Thanks ! Would you be able to provide me more information on 'Passing Shared Variables'...
(Thank you for your time and help !.)

 

[lbass]

If you provide the specifics of what you are trying to do, where the sub is located, what you want shared, where you want it displayed, etc., someone could help, but we're not here to provide general instruction.

-LB

 

[Madawc]

Try SEARCH, there are some examples on older threads.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[goesnowhere]

There is a way to create a subreport with in subreport but it very limited and you need to know a bit about SQL. Actually you are limited to one field (but often this is enough). Use a SQL Expression to create a select statement linking back to one of the database fields in the tables that you have selected in your subreport. It is similar to a SQL Nested SubQuery. Remember to enclose the sql expression in parentheses. Use the coalesce function to return a default value if a null value is encountered in your query (this is optional).

SQL Expression Example

(select count(table.field) from table where table.linking_field = subreporttable.linking_field)

with coalesce
i.e.
coalesce(
(select count(table.field) from table where table.linking_field = subreporttable.linking_field),0)

Some things to be aware of

Only one value can be returned. You will get an error if multiple values are returned in your expression. You may need to use the min, max, avg, sum, count aggregrate functions to limit your query to 1 value.

Using a SQL Expression with multiple tables can get complicated with Crystal XI. Use aliasing of table names so that Crystal XI doesn't find fields 'amibiguous).

If you find that Crystal XI cannot handle your SQL Expression, review your subreport and see if you can rewrite both the subreport and the sub-subreport as a view or stored procedure (Again this would require some knowledge of SQL)

Hope this helps.

David

 

[ccding]

Thank you All for your help. Here is what I am working with:

Main Report:
This report has the CompanyId and CompanyName. I have these fields in GH1.

Subreport1:
This report has information on the Contact related to the CompanyrID.
It contains the
ContactName, ContactLevel (Level1, Level2, Level3, Other). The example below is the Company's Contacts grouped by CompanyId, then by ContactLevel:

ContactLevel1
ContactJohn
ContactJane
ContactPeter

ContactLevel2
ContactSam
ContactSmith

­ContactLevel3
ContactHenry
ContactKathy
ContactLarry

I can easily insert this subreport to the main report linked on the CompanyID.

Subreport2:
This report has information on the Contact’s data like multiple phone numbers.
It contains the ContactName, and PhoneNumber (Cell, Fax, Home, Business, Assistant’s Phone.)
I want to insert these PhoneNumber(s) next to (or underneath) the ContactName in Subreport1.

I have attempted a SharedVar for Subreport1 and Subreport2…but am not having success.

Thank you for your time. I hope I have provided enough information

 

[lbass]

Why are you using subreports for this? Are there other parts to the report you are not describing? If not, I would eliminate the first sub and use the contacts table linked to the company table.

I would also try eliminating the second sub. If you don't get repeating data, you can just group on contact name and put the contact data in the detail section.

-LB

 

[goesnowhere]

Why don't you add the contacts data table into your 1st subreport and link by the Contact ID (or something similar) and group by contact name and add the particulars in the detail section of the subreport....
Each contact should have a ContactId (look at the primary key in the contacts table) that is unique and the related data in the contact data table should have the Contact id as a foreign key which you can link to (most likely a left outer join on this table) so that the contact name is displayed whether or not there is any contact information.

David

 

[ccding]

yes, there are many more fields on each subreport or main report. I wanted to keep the forum as easy as I could. I wish I could add the contacts to the 1st subreport. However, the 1st subreport can have several different types of levels. Being that it is grouped by level - then my records repeat with multiple phone numbers. I have tried several types of data pivoting, but noticed that a subreport was the most direct answer. Now if only I could take this combination and insert it into the main report.
Thanks for all of your comments !!

 

[Madawc]

To pass data back from a subreport, use a shared variable. For a date, put a formula field in the subreport like

Shared dateVar 
V_Today := {LoadStatus.LastDLoad}

To access it in the main report, create another formula field with

Shared dateVar 
V_Today := V_Today

If it was a currency value, you'd do it differently, e.g.

whileprintingrecords;
shared currencyvar SumSaved;
SumSaved:={#TotSaved};
SumSaved

And to access it in the main report, create another formula field with

whileprintingrecords;
shared currencyvar SumSaved;
SumSaved

Note that the shared variable is only available in the section after the section which contains the subreport.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Okay, so you need a sub for the contact name, but why do the contact details need to be in a sub? Aren't they in the same table?

-LB

 

[ccding]

Thank you so much gentlemen !! Well, This one is really bugging me, as I am sure it is the programmer and not the program. I have used the code above like this:

whileprintingrecords;
shared stringvar Phones_Title;
Phones_Title:={@PhoneNumbersAndRole};
Phones_Title

I put this formula in the Subreport2.

Now in the main report, I used the following formula:
Whileprintingrecords;
shared stringvar Phones_Title;
Phones_Title

-----
Question 1.. This phone data (above)I need should be in the Subreport1 and not the main report. So, I am confused on this point.

Question 2..How do I pass the data in the Main report? I've tried the edit/Subreport links and do not understand where to access this shared variable ??
---
Well, I understand that this thread is too long already. Thanks for your help thus far. I am going to really study up on Shared Variables this weekend.

Thank you Lbass, Madawc, and David !

 

[lbass]

I don't really see how shared variables helps with this issue. I wish you would explain more about how the contacts name and contacts data relate to each other and the main report. Maybe you could show what the results look like if you add them all into one report, with an inner group (Group #3?) on contact name and the contact details in the detail section.

-LB