Like GetSQLStatement, is there any function to Set the SQL Query in the report?

[former_member730353]

Hi, Good Morning.

we are using CR13SP32 CR.NET, To get the SQL query from the report we use following function.

boReportClientDocument.RowsetController.GetSQLStatement(gp, out sqlQuery);

Defination: "string GetSQLStatement(ISCRGroupPath GroupPath, out string Reserved);"

Is there any equivalent function to set the query in the report?. Please provide the details.

Thanks,

Samson

[former_member11696 · Accepted solution]

No, Since CR 2009 we no longer allow edit/modifying the SQL used in the report.

Work around is to use a Command and write your own SQL, you can then edit it.

Note, converting reports from SQL to Commands doesn't work, you have to re-write the report using the Command data source.

Same for converting Commands to a Table...

[former_member730353 · Accepted solution]

Thanks for the information. But we are changing the report data by setting the datasource for the table of the record. Please refer the below code.

DataSet crSqlDs = new DataSet();
                using (OleDbConnection oleDbConn = new OleDbConnection(connString))
                {
                    using (OleDbDataAdapter oleDbAdapter = new OleDbDataAdapter())
                    {
                        oleDbAdapter.SelectCommand = new OleDbCommand(sqlQuery, oleDbConn);
                        oleDbAdapter.Fill(crSqlDs, "Command");
                    }
                }
                foreach (CrystalDecisions.CrystalReports.Engine.Table table in reportDoc.Database.Tables)
                {
                    table.SetDataSource(crSqlDs.Tables[0]);
                }

But i have questions:

1. If we use table.SetDataSource function, how the CR.NET works? will it first load all the data as per the report SQL in the report and then will it change the datasource and report data?

Setting the datasource using table.SetDataSource() ( with modified SQL query) is a right approach. Please suggest.

[former_member730353 · Accepted solution]

Hi, can you please check my earlier comment.

Is there any way to not to use the default SQL query at the time loading, instead use only SetSqlDataSource function to load the data, basically is there any option which disable the loading of report data using default SQL Query instead using only SetSqlDatasource function to load the data. Please provide your suggestion.

Currently we are facing loading issue because of the default SQL query as the database table contains large data which create huge delay in loading preview.

Thanks,

Samson

[former_member11696]

After setting the new log on info it will over write the existing one so there should only be 1 query sent to the DB.

Only reason it's running the report 2 times is either you have save data or you have one of the Refresh options enabled in the Report, open the report in CR Designer an Report Options and uncheck and verify options.