Link to home
Start Free TrialLog in
Avatar of ElrondCT
ElrondCTFlag for United States of America

asked on

Subreport is blank

Using Crystal Reports with Visual Studio 2008 (using Visual Basic, FWIW, but that's probably not relevant), I'm passing a dataset with a parent table and several child tables. I've created a subreport within the report (that is, it's not a separate report, but was created with Insert / Subreport / Create a subreport with the Report Wizard). The subreport is in a footer section, and is intended to provide a summation of values in a table that is not displayed on the main report.

All the value fields (from the table) in the subreport are coming up blank. The text objects display fine. I've tried putting fields in detail, groupfooter (Sum), and reportfooter (Count and Max) sections, and everything is blank. I know that there are records in the table, but the output is identical to what I get if I create a report with no records in the subreport's table. I have no links specified between the main report and the subreport, so I don't see any way that records would be filtered out.

Any ideas on how to make my data appear?
Avatar of Ido Millet
Ido Millet
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I always have to cycle thru all datasources using code below for subreports in VB...

Sample ConnectionInfo SUB offered...

   Private Sub CR_ConnectionInfo_Runtime()
        Dim crtableLogoninfos As New TableLogOnInfos()
        Dim crtableLogoninfo As New TableLogOnInfo()
        Dim crConnectionInfo As New ConnectionInfo()
        Dim CrTables As Tables
        Dim CrTable As Table

        With crConnectionInfo
            .ServerName = "XXXXXXXXX"
            .DatabaseName = "Yourdatabase"
            .UserID = "<userid>"
            .Password = "<password>”
        End With

        CrTables = rptNewReport.Database.Tables

        For Each CrTable In CrTables
            crtableLogoninfo = CrTable.LogOnInfo
            crtableLogoninfo.ConnectionInfo = crConnectionInfo

        CrTables = Nothing
        crtableLogoninfos = Nothing
        crtableLogoninfo = Nothing
        crConnectionInfo = Nothing

    End Sub
Avatar of Mike McCracken
Mike McCracken

Are you passing the recordset to the subreports also or just to the main report?

Subreports even if created as you did have their own data source and need to be fed data just as the main report does.

Avatar of ElrondCT


Thanks, all, for your thoughts. Mlmcc, I've never had to separately specify the datasource (using .SetDataSource) for a subreport. All the tables (for both main and subreport) are in the same dataset, so my experience is that the data flows through.

IdoMillet, I was trying to keep things simpler by having everything in one report (not least because I've found that whenever I make a change to a subreport that's completely separate, I have to completely delete and replace the subreport, resetting all the links, which is a pain in the neck). But I decided it makes sense in this situation to follow your advice.

In doing so, I realized I really didn't provide the whole story. The subreport that's not displaying is actually a second-level subreport--it's a subreport of a subreport. I now have it both in its original position, and as a first-level subreport. The second-level subreport is blank, while the first-level subreport has the information I expect.

So it looks like the issue may be that I'm nesting too deeply. The problem is that this is a report totals subreport, and I need to group the results. So what I have overall is:

Individual detail, by month--primary table
   Subreport: individual detail, by month--secondary table
Report footer:
     Primary table summary by month
         Subreport: secondary table summary by month

I want the subreport for a particular month to immediately follow that month's data for the primary table, not have all the secondary information segregated. So if I run it for Jan-March:

Individual 1 Jan - Primary
Individual 1 Jan - Secondary
Individual 1 Feb - Primary
Individual 1 Feb - Secondary
Individual 1 Mar - Primary
Individual 1 Mar - Secondary
Individual 2 Jan - Primary
Individual 2 Jan - Secondary
Individual 2 Feb - Primary
Individual 2 Feb - Secondary
Individual 2 Mar - Primary
Individual 2 Mar - Secondary
Totals Jan - Primary
Totals Jan - Secondary
Totals Feb - Primary
Totals Feb - Secondary
Totals Mar - Primary
Totals Mar - Secondary

The two tables aren't necessarily related to each other except that they contain results for the same dates. So I can't think of a way to use grouping to output the summations for both tables in the totals without a subreport.

Any ideas, or am I forced to list all the months' totals for Primary, then all of them for Secondary?
Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Have you tried using a crosstab to get the totals in the footer?

Mlmcc, see my previous post for what I want the report to look like. I'm using a subreport to display information from the secondary table. I'm also using a subreport to display the totals, because the totals need a grouping operation (to show each month separately).

If necessary, I'll do the totals as:

Totals Jan - Primary
Totals Feb - Primary
Totals Mar - Primary
Totals Jan - Secondary
Totals Feb - Secondary
Totals Mar - Secondary

which I can do with two separate subreports, not nested, but that's less desirable because it means you have to go back and forth for the full set of information for a single month.
When you say the tables aren't related, the desired output implies there is a relationship since you have totals for individuals and totals for months.

Have you considered using a UNION query to get the information from each table.  You can add a field to indicate which table it is from then group on tha value to separate the data into primary and secondary groups.

To be precise, I said they aren't necessarily related. What it amounts to is that I've got two types of information that I'm reporting per month: Any individual may have one type, the other, or both.

I'll have to look at UNION; it's not something I've used before, but perhaps that will fill the bill.
Basic idea beind the union.  

SELECT personname, date, field1, field2, "" as field3, "1" as type
FROM Primary

SELECT personname, date, "", field2,  field3, "2" as type
FROM Secondary

Each selection must have the same number of fields.
Each selection must have fields in the same order  by type.

It is easiest if you list the common fields first then the remaining fields from the primary table
You need to add fields for the unique fields of the second table and a field to identify which table it is from.  I generally add 0 or some known value for numeric fields, a null string "" for string types and a known date like 1 Jan 1899 for date fields.

You can then process the records with 2 detail sections or with formulas based on type of record.

Thanks for the primer. I don't think it's going to be worth it to put that together; I've got about 40 fields in one table and over 100 in the other. If that's what it takes, my clients can live with having all totals for the primary table first, then all totals for the secondary table. It may be worth keeping in mind for other situations, though.
You are correct, that would be a bit of an issue with that disparate a field count.

What database are you using?

This report is using a dataset internal to the application; the tables are calculated values, not stored in a database. (There are other tables in the dataset coming from a database, usually Access but possibly SQL Server, but those tables aren't at issue in this question.)

I'm going to consider this resolved, displaying each set of totals separately; not ideal, but the best available. It's not worth complexifying the report further to figure out a way to get around the CR limitation; I already have over 50 sections in the main report and 25 in the first totals subreport. Thanks for your help as always.