BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

 
Lead By Knowing (Opens a new window)  

General Notice: No events within the next 45 days.

Crystal Report - Get SQL for report and Subreport(s)


 
Search this topic... | Search SDK (VBA/ASP/JSP)... | Search Box
Register or Login to Post    Forum Index -> SDK (VBA/ASP/JSP)  Previous TopicPrint TopicNext Topic
Author Message
chris465
Senior Member
Senior Member



Joined: 29 Apr 2008

Posts: 33



PostPosted: Fri Apr 15, 2011 2:05 pm 
Post subject: Crystal Report - Get SQL for report and Subreport(s)

I have been able to use the code provided by SAP to get the SQL code for the main report. But I am not sure how to have it generate the code for the Sub-reports.

The eventual goal would be to write in the code to have it traverse the reports in InfoView and retreive the SQL code used, to identify all of the tables and columns utilized in the reports.

Thought I would ask if anyone has something developed that works. Thanks.

Edit: Should note that there is many SQL Commands in these reports.

Code:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

        // Declarations
        CrystalDecisions.CrystalReports.Engine.ReportDocument boReportDocument = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
        CrystalDecisions.ReportAppServer.ClientDoc.ISCDReportClientDocument boReportClientDocument;
        CrystalDecisions.ReportAppServer.Controllers.RowsetController boRowsetController;
        CrystalDecisions.ReportAppServer.DataDefModel.ISCRGroupPath boGroupPath;

        // Load the report from the application directory
        boReportDocument.Load(Server.MapPath("Report.rpt"));
                     
        // Access the ReportClientDocument in the ReportDocument (EROM bridge)
        // Note this is available without a dedicated RAS with SP2 for XI R2
        boReportClientDocument = boReportDocument.ReportClientDocument;
       
        boReportDocument.DataSourceConnections[0].SetLogon("user", "pass");
       
     
        // use the RowsetController to get the SQL query
        // Note: If a report has parameters they must be supplied before getting the
        // SQL query.
        boRowsetController = boReportClientDocument.RowsetController;
        boGroupPath = new CrystalDecisions.ReportAppServer.DataDefModel.GroupPath();       
        Label_SQL.Text = boRowsetController.GetSQLStatement(boGroupPath, out temp);

        // Clean up by closing and disposing of the ReportDocument object
        boReportDocument.Close();
        boReportDocument.Dispose();      
    }
}
Back to top
chris465
Senior Member
Senior Member



Joined: 29 Apr 2008

Posts: 33



PostPosted: Tue Apr 19, 2011 8:47 am 
Post subject: Re: Crystal Report - Get SQL for report and Subreport(s)

Changed up the method of how I was getting the SQL. The code is still rough, as I need to clean it up for easier reading, but this is the start. Gets the tables in use and the code from the commands.

Code:

        private string getSQL(InfoObject iObject, EnterpriseSession eSession)
        {
            CrystalDecisions.CrystalReports.Engine.ReportDocument boReportDocument = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
            CrystalDecisions.ReportAppServer.ClientDoc.ISCDReportClientDocument boReportClientDocument;
            CrystalDecisions.ReportAppServer.Controllers.DataDefController boDataDefController;
            CrystalDecisions.ReportAppServer.DataDefModel.Database boDatabase;
            CrystalDecisions.ReportAppServer.DataDefModel.CommandTable boCommandTable;

            // Load the report using the CR .NET SDK and get a handle on the ReportClientDocument
            boReportDocument.Load(iObject,eSession);
            boReportClientDocument = boReportDocument.ReportClientDocument;

            // Use the DataDefController to access the database and the command table.
            // Then display the current command table SQL in the textbox.
            boDataDefController = boReportClientDocument.DataDefController;
            boDatabase = boDataDefController.Database;

            string sql;
            sql = "";

            for (int i = 0; i < boDatabase.Tables.Count; i++)
            {
                CrystalDecisions.ReportAppServer.DataDefModel.ISCRTable tableObject = boDatabase.Tables[i];

                if (tableObject.ClassName == "CrystalReports.Table")
                {
                    sql = sql + "Table " + i + ": " + tableObject.Name;
                }
                else
                {
                    boCommandTable = (CrystalDecisions.ReportAppServer.DataDefModel.CommandTable)boDatabase.Tables[i];
                    sql = sql + "Query " + i + ": " + boCommandTable.CommandText;
                }
                sql += Environment.NewLine;

            }

            foreach (string subName in boReportClientDocument.SubreportController.GetSubreportNames())
            {
                CrystalDecisions.ReportAppServer.Controllers.SubreportClientDocument subRCD = boReportClientDocument.SubreportController.GetSubreport(subName);

                for (int i = 0; i < boDatabase.Tables.Count; i++)
                {
                    CrystalDecisions.ReportAppServer.DataDefModel.ISCRTable tableObject = boDatabase.Tables[i];

                    if (tableObject.ClassName == "CrystalReports.Table")
                    {
                        sql = sql + "Table " + i + ": " + tableObject.Name;
                    }
                    else
                    {
                        boCommandTable = (CrystalDecisions.ReportAppServer.DataDefModel.CommandTable)subRCD.DatabaseController.Database.Tables[i];
                        sql = sql + "Subreport " + subName + " - Query " + i + ": " + boCommandTable.CommandText;
                    }
                    sql += Environment.NewLine;
                }

            }


            // Clean up
            return sql;

        }
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> SDK (VBA/ASP/JSP)  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0454 seconds using 17 queries. (SQL 0.0028 Parse 0.0340 Other 0.0085)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo