cancel
Showing results for 
Search instead for 
Did you mean: 

Modifying SQL from Crytal Report within V.S. 2012

Former Member
0 Kudos

How do you modify the SQL statement that is generated within a Crystal Reports file (.RPT)?  I would like to alter the WHERE clause that is generated inside the Crystal Report which was loaded via the CrystalDecisions.CrystalReports.Engine.ReportDocument object. 

I realize that the RecordSelectionFormula property can be used, BUT the string that is generated from the original SQL WHERE subclause within the Crystal Report becomes formatted in a non T-SQL format. In order to modify this property effectively, one would have to create a string that adheres to the that property's recognizable format.

I was able to obtain the SQL statement generated from the Crystal Report (ReportDocument object) using the ISCDReportClientDocument and RowsetController objects and GetSQLStatement() method.

What are the objects and methods that can be used to update (modify) and refresh the SQL within the ReportDocument object for the Crystal Report?

Thanks,

Eric

Accepted Solutions (1)

Accepted Solutions (1)

DellSC
Active Contributor
0 Kudos

This can't be done through the ReportDocument class.  I'm not super familiar with the Report Appication Server (RAS), but you might be able to do it through the Inproc RAS functionality that is part of the SDK.

You can find sample code here:  http://scn.sap.com/docs/DOC-50373

-Dell

Former Member
0 Kudos

Thank you for replying.

Yes, I thought that perhaps the SQL could not be modified using the ReportDocument object.  I also figured that the RAS objects within the SDK can be used, but which ones?

In my original post, I mentioned that I used the ISCDReportClientDocument and RowsetController object to obtain the original SQL for the report.  These objects are part of the RAS SDK.

I did manage to find a few examples on modifiying the SQL using the SetSQLCommandTable() of the ReportDocument object, but that didn't seem to work.  Do you know which specific objects (and namespaces) within the RAS assemblies can be used to accomplish this?  I can not seem to find working examples online or within the API guides provided by SAP.

Thanks,

Eric

former_member183750
Active Contributor
0 Kudos

Hi Eric

See KBA 1281732 - How to pass a SQL query to a Crystal Reports in a Visual Studio .NET application?

It's not pretty, but it may work for you. If not, then the only other option are ADO .NET datasets. In this way you construct the dataset based on the SQL and then pass that to the report. Let me know if that is an option for you and I'll add in links some resources.

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow us on Twitter

former_member188030
Active Contributor
0 Kudos

Hi Eric,

In CR designer once the report is designed the SQL could not be edited unless you are using a command object or you add / remove the DB fields from report and refresh it.

Same is with SDK, editing report sql is not possible directly.

The two options are available as accessing the command object or use of datasets to gather the data into a dataset by setting the SQL beforehand in code and then pass the resulting data to the report.

Thanks,

Bhushan

Former Member
0 Kudos

Hello Ludek,

Using ADO datasets and passing them to the Crystal Reports .RTP is not an option because that would entail changing all of our RPT files and that is not at all feasible.

There should be objects and methods within the CrystalDecisions.ReportAppServer namespace that should do what I need regarding the modification of the SQL according to what I was told by a Crystal Reports customer service rep.

I need an example or set of examples on how to do this.   Like I mentioned before,  I have seen an example using ISCDReportClientDocument object, but it didn't work.  Are you familiar with this object and do you know of any examples?

Thanks,

Eric

Former Member
0 Kudos

Thank you Bhushan.

Can you elaborate more on using the command object?  What are the methods used?  Examples?

Using a dataset to pass back to the Crystal Report is not an option because there are too many reports that would need modification.

Thanks,

Eric

Former Member
0 Kudos

Hi Eric,

This is my first time out with CR10. I am converting CR4.5 reports written for VB6. Some years ago I tried CR8 that came with VS2005.Net, but it didn't have the ability to give it an SQL statement, and who wants a million rows returned to the report viewer if you only want one or two?

I use a dataset that was loaded based on my sql where clauses and load it to the report document. In other words, the report is built around a SQL statement that returns all records, but I build a dataset based on parameter entry, and pass it to the report.  There are two problems I have noticed; if no records are returned, ReportViewer is unhappy, displaying an error message about groupings; and if the user clicks on the Group Tree panel to navigate to a specific group, the report display is refreshed from the internal query, displaying ALL rows! I haven't figured out how to deal with that yet. I'm hoping to learn that there is an event I can trap to reload from my query, rather than the full query...If I hide the group panel, the user can still open it, unfortunately.   I also use multiple .RPT files with one viewer.

private void loadReport(int reportId)

{

     ReportDocument rptDoc = new ReportDocument();

     DataTable dt = new DataTable();

     dt.TableName = "Company Report";

     dt = getReportRecords(reportId).Tables[0];

     string fileName = GetReportFileName(reportId);

          rptDoc.Load(fileName);

          rptDoc.SetDataSource(dt);

          CrystalReportViewer1.ToolPanelView = ToolPanelViewType.None;

          CrystalReportViewer1.ReportSource = rptDoc;

}

Mark





former_member188030
Active Contributor
0 Kudos

we can only modify the command table of the reports which are designed using command object. I couldnt find a code example but you could check the CommandTableClass in the RAS SDK dev guide and API reference.

http://scn.sap.com/docs/DOC-27465

-Bhushan

0 Kudos

Hi Eric,

You cannot change the SQL now, no option and will not be.

You can convert a report with only 1 or 2 Table to a Command at runtime but that is it.

Only option now if you must edit the SQL is to use a Command and you'll likely have to recreate all of your reports.

Once you do that though here's how to, you simply have to update the SQL Statement but be sure not to alter the fields used. If CR can't find the fields it Auto Maps and deletes them from the report. Typically causing it to fail...

Second to last line is how you update the SQL statement.

private void CommandTable_Click(object sender, EventArgs e)

{

    //CrystalDecisions.CrystalReports.Engine.ReportDocument rpt = new CrystalDecisions.CrystalReports.Engine.ReportDocument();

    ISCDReportClientDocument rcd;

    rcd = rpt.ReportClientDocument;

    String newTableName = "Command";

    CrystalDecisions.Shared.ConnectionInfo connectionInfo = new CrystalDecisions.Shared.ConnectionInfo();

    DbConnectionAttributes dbconn = new DbConnectionAttributes();

    NameValuePairs2 propertyBag = new NameValuePairs2();

    propertyBag.Set("QE_DatabaseDLL", "crdb_ado.dll");

    propertyBag.Set("QE_Servertype", "OLE DB (ADO)");

    propertyBag.Set("QE_ConnectionString", "Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;User ID=" + ";Initial Catalog=YourDatabaseName;Data Source=Server;");

    propertyBag.Set("QE_Servername", "Server or DSN");

    dbconn.Collection = propertyBag;

    connectionInfo.Attributes = dbconn;

    connectionInfo.UserID = "UserName";

    connectionInfo.Password = "YourPassword";

    connectionInfo.ServerName = "Server";

    connectionInfo.DatabaseName = "YourDatabaseName";

    //// Just to get the SQL

    //rptClientDoc.DatabaseController.logon("User", "YourPassword");

    //GroupPath gp = new GroupPath();

    //string tmp = String.Empty;

    //try

    //{

    //    rcd.RowsetController.GetSQLStatement(gp, out tmp);

    //    btnSQLStatement.Text = tmp;

    //}

    //catch (Exception ex)

    //{

    //    MessageBox.Show("ERROR: " + ex.Message);

    //    return;

    //}

    //String sqlQueryString = tmp.ToString();

String sqlQueryString = @"'XTREME'.'Orders Detail' 'Orders_Detail' INNER JOIN 'XTREME'.'Orders' 'Orders' ON 'Orders_Detail'.'Order ID'='Orders'.'Order ID'";

    rpt.SetSQLCommandTable(connectionInfo, newTableName, sqlQueryString.ToString());

}

Note also, you don't need to use RAS to set the log on info but do need it to get the SQL statement before and after.

Don

Former Member
0 Kudos

Don,

After doing some research and some investigating on leads from other reply's, it seems like it can't be done from VS 2012.  Years ago, we have developed a program written in C++ from libraries dating back over 10 years and were able to open up a Crystal Report (.rpt) with SQL modifications by our program before displaying the report.  This application of course was using the old libraries from Crystal XI Business Objects.  It seems that the objects in these libraries can't be used in an application written in VS 2012 and compiled / targeted for 64 bit mode.  It also seems like the RAS .NET objects do not interface with these old libraries, which means the SQL can not be changed.

Our current goal in modifying the SQL for all of our Crystal Reports was really to append extra filters to the existing WHERE clause that is embedded in the Crystal .rpt file.  I guess we will have to just translate the strings that we would normally append to the SQL WHERE clause and append them to the RecordSelectionFormula  property of the ReportDocument object.

Modifying all of our existing reports by either changing the tables to be command tables or changing the entire report to accept a dataset populated by our VS 2012 application is not a feasible process since we have many reports. Again, modifying the RecordSelectionFormula property will have to suffice for now.

Thanks,

Eric

0 Kudos

Hi Eric,

Yes, there are no 64 bit libraries back then but now there are and you can use CR. NET assemblies in a C++ app. There is one sample in the sample WIKI:

Crystal Reports for .NET SDK Samples - Business Intelligence (BusinessObjects) - SCN Wiki

Another way that has been suggested also. As long as the data is not that big you can still use a Dataset and point your existing reports to the DS.

Thanks again

Don

Answers (0)