Solved

Subreport is blank

Posted on 2013-01-05
13
577 Views
Last Modified: 2013-01-09
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?
0
Comment
Question by:ElrondCT
13 Comments
 
LVL 22

Assisted Solution

by:Ido Millet
Ido Millet earned 150 total points
ID: 38747919
Consider creating the desired subreport first as a main report.  Then, once you confiorm it works as expected, insert it into the main report as a subreport.  If it doesn't work as a main report, at least you'd know the problem is not related to the subreport context.
0
 
LVL 8

Expert Comment

by:5teveo
ID: 38747923
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
            CrTable.ApplyLogOnInfo(crtableLogoninfo)
        Next


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

    End Sub
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 38747939
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.

http://www.emoreau.com/Entries/Articles/2006/09/Feeding-Crystal-Reports-from-your-application.aspx

mlmcc
0
 
LVL 20

Author Comment

by:ElrondCT
ID: 38753317
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?
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 350 total points
ID: 38753498
Crystal does NOT support a second level of subreports.  Thus what you are trying to do can NOT be done in Crystal in that manner.

Why do you think you need a sub within a sub?

Perhaps we can figure out how to make it work.

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 38753501
Have you tried using a crosstab to get the totals in the footer?

mlmcc
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 20

Author Comment

by:ElrondCT
ID: 38753525
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.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 38757330
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.

mlmcc
0
 
LVL 20

Author Comment

by:ElrondCT
ID: 38757640
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.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 38757683
Basic idea beind the union.  

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

UNION
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.

mlmcc
0
 
LVL 20

Author Comment

by:ElrondCT
ID: 38757702
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.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 38761484
You are correct, that would be a bit of an issue with that disparate a field count.

What database are you using?

mlmcc
0
 
LVL 20

Author Comment

by:ElrondCT
ID: 38761630
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.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now