Solved

Calling a report from VB, which has multiple subreports independent of each other

Posted on 2004-10-27
2,500 Views
Last Modified: 2008-01-09
Hi ,

I have a main report which contains no  fields to display and not based on any query. It is derived from the base table on which other sub reports are based. This main report contains 4 sub reports. Now when I am calling the main report from VB passing the SqlQuery to it. Instead of displaying the reports based on the query it gives an error, can not open SQL Server. Error number 20599. My DB Is Oracle 9.

How do I achieve to display the reports.

Thanks,
0
Question by:swami_varahagir
    12 Comments
     
    LVL 100

    Expert Comment

    by:mlmcc
    How are you passing the query?

    You need to pass it to each subreport not just the main report.

    Look at

    http://support.businessobjects.com/communityCS/FilesAndUpdates/SCR8_VB_RDC_Subreports.exe.asp

    mlmcc
    0
     

    Author Comment

    by:swami_varahagir
    This is my Code

            rptTACTICS.Connect = GetConnectionString
            rptTACTICS.WindowState = crptMaximized
            rptTACTICS.Destination = crptToWindow
            rptTACTICS.ReportFileName = App.Path & "\Report\Attribute_Info_Report.rpt"
            rptTACTICS.DiscardSavedData = 1
            rptTACTICS.SQLQuery = strQuery & strDateCriteria
            MsgBox rptTACTICS.SQLQuery
            rptTACTICS.Action = 1


    Where as rptTactics is the ocx and Attribute_Info_Report.rpt is the main report.

    Now I hv to display the subreports based on the query passed to main report.

    Thanks,
    0
     
    LVL 13

    Accepted Solution

    by:
    To access the subreports, you need to use the SubreportToChange property, which is not very elegant, at all.

    'Change the SQLQuery for the first subreport
    rptTACTICS.SubreportToChange = "Subreport1"
    rptTACTICS.DiscardSavedData = 1
    rptTACTICS.SQLQuery = strQuery & strDateCriteria
    'Set the focus back to the Main report
    rptTACTICS.SubreportToChange = ""
    'Change the SQLQuery for the second subreport
    rptTACTICS.SubreportToChange = "Subreport2"
    rptTACTICS.DiscardSavedData = 1
    rptTACTICS.SQLQuery = strQuery & strDateCriteria

    From the ocx help files:

         SuberportToChange Property:

         Description

         Specifies whether changes to any of several properties (see list in Remarks below) affect the main report or a subreport.

         Usage

         [form.]Report.SubreportToChange(= SubreportName$)

         Code Samples

         With CrystalReport1
         
         .ReportFileName = "C:\rpts\Report.rpt"
               .SubreportToChange = "SubRpt1"
               .Action = 1
         
         End With
         
         «Any properties following the SubreportToChange will apply to the Subrpt1 subreport»
         
         Remarks
         
         When the SubreportToChange property is set to an empty string, focus returns back to the main report.
         
    -dave          
    0
     

    Author Comment

    by:swami_varahagir
    Thanks for the help but I am getting the error "Unknown Subreport Name".
    Plz let me know why and how do I resolve it.


    The subreports are independent reports and placed in the main report header as sub reports(Choose a file as Subreport option)

    Thanks,
    0
     
    LVL 13

    Expert Comment

    by:vidru
    Make sure you're including the '.rpt' extension in the subreport names.

    -dave
    0
     

    Author Comment

    by:swami_varahagir
    yes it is included
    0
     

    Author Comment

    by:swami_varahagir
    Yes I did. I was passing the report name with the complete path. I have changed it now and the cide is as follows.

            rptTACTICS.Connect = GetConnectionString
            rptTACTICS.WindowState = crptMaximized
            rptTACTICS.Destination = crptToWindow
            rptTACTICS.ReportFileName = App.Path & "\Report\Attribute_Info_Report.rpt"
            rptTACTICS.SubreportToChange = "Drug_Attribute_Info_Report.rpt"
            rptTACTICS.DiscardSavedData = 1
            rptTACTICS.SQLQuery = strQuery & strDateCriteria
            rptTACTICS.SubreportToChange = "Investigator_Attribute_Info_Report.rpt"
            rptTACTICS.DiscardSavedData = 1
            rptTACTICS.SQLQuery = strQuery & strDateCriteria
            rptTACTICS.SubreportToChange = "Country_Attribute_Info_Report.rpt"
            rptTACTICS.DiscardSavedData = 1
            rptTACTICS.SQLQuery = strQuery & strDateCriteria
            rptTACTICS.SubreportToChange = "CROPS_Attribute_Info_Report.rpt"
            rptTACTICS.DiscardSavedData = 1
                   
            rptTACTICS.SQLQuery = strQuery & strDateCriteria
            MsgBox rptTACTICS.SQLQuery
            rptTACTICS.Action = 1

    And I am getting an error in the last line (.Action = 1) .

    The error is "Can not open SQL Server". My DB is Oracle and I am using odbc for Oracle System DSN.

    Waiting for a quick solution.
    Thanks,

    0
     
    LVL 13

    Expert Comment

    by:vidru
    You need set the Connect property for each subreport as well.

    -dave
    0
     
    LVL 100

    Expert Comment

    by:mlmcc
    The subreport name is the name shown in Crystal designer which is not necessarily the file name.

    Also before rptTACTICS.Action = 1
     add a line
    rptTACTICS.SubreportToChange = ""

    This resets the report object to the main report.

    mlmcc
    0
     

    Author Comment

    by:swami_varahagir
    It is working fine for 2 subreports but when I am including more than that (I need to display 4), it gives Error detected By Database DLL.

    0
     
    LVL 100

    Expert Comment

    by:mlmcc
    Does it work for any 2 or does one of the reports cause a problem?

    mlmcc
    0
     

    Author Comment

    by:swami_varahagir
    it works for any 2 and when I add 1 more it gives the error Error detected by db dll

    Thanks,
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
    There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
    With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…
    This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

    933 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

    14 Experts available now in Live!

    Get 1:1 Help Now