locked
Conversion of crystal command table into CommandTable RRS feed

  • Question

  • User-1456881142 posted

    I've create a SAP crystal report to be based on ASP.NET and this table was build out by command table. I need to setting and getting the command query of report at run time, but it just happened to gave me error as "Unable to cast COM object of type 'System.__ComObject' to interface type 'CrystalDecisions.ReportAppServer.DataDefModel.CommandTable'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{EC2F9A25-E94D-40C0-BF21-3C9D748CC983}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE))".

    CrystalDecisions.ReportAppServer.ClientDoc.ISCDReportClientDocument rptinnerfile = rptfile.ReportClientDocument;
    CrystalDecisions.ReportAppServer.Controllers.DataDefController rptdatadefcontroller = rptinnerfile.DataDefController;
    CrystalDecisions.ReportAppServer.DataDefModel.Database rptinnerdatabase = rptdatadefcontroller.Database;
    CrystalDecisions.ReportAppServer.DataDefModel.CommandTable rptcmdtable = new CrystalDecisions.ReportAppServer.DataDefModel.CommandTable();
    
    foreach(CrystalDecisions.ReportAppServer.DataDefModel.Table tbl in rptinnerdatabase.Tables)
     {    
          rptcmdtable = (CrystalDecisions.ReportAppServer.DataDefModel.CommandTable) tbl;
     }

    I've been struggling for this problem in few days, i'm hoping someone could help me out on this problem.Thanks

    Saturday, April 22, 2017 7:26 AM

All replies

  • User-1123947812 posted

    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

    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;

            }

    Friday, November 10, 2017 6:40 AM