Filtering Crystal Reports with Transact-SQL in Asp.NET MVC

[Former Member]

I have more than two thousand Reports with Crystal Reports XI in my legacy system. I and all my Clients use SQL Server 2008 R2 database (different servers and database names)

So, I have my new application in ASP.NET MVC 5 that I would like to use my Crystal Reports. In my ASP.NET MVC application I have Grids with filters, and these filters I use to filter the Grid view and the Reports, my filters were developed using Linq to Sql.

My Problem is:

If the Report Select Command is:

select
Cart.Description, Cart.Value, Cart.Date,
CartItems.Description, CartItems.Value, CartItems.Quantity
from Cart inner join CartItems on Cart.codCart = CartItems.codCart

So, the Cart and CartItems tables have columns with the same name, which are: Description and Value. And when viewing the report, the result considers the Description and Value columns from the first table.

For example, I have these records on Sql Server database:

Cart.Description   Cart.Value     Cart.Date   |   CartItems.Description   CartItems.Value
Sale One            $100          01/01/2015  |   Product One               $60
Sale One            $100          01/01/2015  |   Product Two               $40
Big Sale            $8000         02/01/2015  |   Product Three             $8000

But the Crystal Reports data results is (see that the Description and Value columns show the data of first table -> Cart):

Cart.Description   Cart.Value      Cart.Date   |   CartItems.Description   CartItems.Value
Sale One            $100           01/01/2015  |   Sale One                 $100
Sale One            $100           01/01/2015  |   Sale One                 $100
Big Sale            $8000          02/01/2015  |   Big Sale                 $8000

I used this tutorial to open Crystal Reports in ASP.NET MVC Crystal report as a PDF ActionResult in ASP.Net MVC

But, instead of use the reportDocument.SetDataSource(dataSet) method, I did it in a different way:

1.Change the connection string in Report to connection string of the Client

pReportDocument.SetDatabaseLogon("user", "Password", "server", "database");

PropertyBag connectionAttributes = new PropertyBag();
connectionAttributes.Add("Auto Translate", "-1");
connectionAttributes.Add("Connect Timeout", "45");
connectionAttributes.Add("Data Source", "server");
connectionAttributes.Add("General Timeout", "0");
connectionAttributes.Add("Initial Catalog", "database");
connectionAttributes.Add("Integrated Security", false);
connectionAttributes.Add("Locale Identifier", "1040");
connectionAttributes.Add("OLE DB Services", "-5");
connectionAttributes.Add("Provider", "SQLOLEDB");
connectionAttributes.Add("Tag with column collation when possible", "0");
connectionAttributes.Add("Use DSN Default Properties", false);
connectionAttributes.Add("Use Encryption for Data", "0");

PropertyBag attributes = new PropertyBag();
attributes.Add("Database DLL", "crdb_ado.dll");
attributes.Add("QE_DatabaseName", "database");
attributes.Add("QE_DatabaseType", "OLE DB (ADO)");
attributes.Add("QE_LogonProperties", connectionAttributes);
attributes.Add("QE_ServerDescription", "server");
attributes.Add("QESQLDB", true);
attributes.Add("SSO Enabled", false);

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo ci = new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();
ci.Attributes = attributes;
ci.Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;
ci.UserName = "user";
ci.Password = "password";

//Update Conn on Tables
foreach (CrystalDecisions.ReportAppServer.DataDefModel.Table table in pReportDocument.ReportClientDocument.DatabaseController.Database.Tables)
{
  table.QualifiedName = "database" + ".dbo." + table.Name;
  table.ConnectionInfo = ci;

  pReportDocument.ReportClientDocument.DatabaseController.SetTableLocation(table, table);
}

//Update Conn on Tables of SubReports
foreach (ReportDocument subreport in pReportDocument.Subreports)
{
   foreach (CrystalDecisions.ReportAppServer.DataDefModel.Table table in pReportDocument.ReportClientDocument.SubreportController.GetSubreportDatabase(subreport.Name).Tables)
   {
  table.QualifiedName = "database" + ".dbo." + table.Name;
  table.ConnectionInfo = ci;

  pReportDocument.ReportClientDocument.SubreportController.SetTableLocation(subreport.Name, table, table);
   }
}

2. Get the complete T-SQL from Report, with Columns, Tables, Where, Group By and Order By

CrystalDecisions.ReportAppServer.Controllers.RowsetController objRowsetController = pReportDocument.ReportClientDocument.RowsetController;

//ISCRGroupPath iscrGroupPath;
CrystalDecisions.ReportAppServer.DataDefModel.GroupPath groupPath = new GroupPath();

string strSQL;
string strSQLGroup;

strSQL = objRowsetController.GetSQLStatement(groupPath, out strSQLGroup);

strSQL = strSQL.Replace("\r\n", "");  

3. Merge the T-SQL from Report with the Where Clause of the Filters

I have a parser of SQL to do this

strSQL = strSQL.Replace("\"", "");

//Parser Query of Report
SqlParser.SqlParser objSqlParserReport = new SqlParser.SqlParser();
objSqlParserReport.Parse(strSQL);

//Parts of Query
string strSelectReport;
string strFromReport;
string strWhereReport;
string strGroupByReport;
string strOrderByReport;

strSelectReport = objSqlParserReport.SelectClause;
strFromReport = objSqlParserReport.FromClause;
strWhereReport = objSqlParserReport.WhereClause;
strGroupByReport = objSqlParserReport.GroupByClause;
strOrderByReport = objSqlParserReport.OrderByClause;

string strQueryReportComplete;
string strWhereComplete = "";

//If exist filter in Report
if (string.IsNullOrEmpty(strWhereReport) == false)
{
   if (string.IsNullOrEmpty(strWhereComplete) == false)
   {
  strWhereComplete = strWhereComplete + " and ";
   }

  strWhereComplete = strWhereComplete + " " + strWhereReport;
}

//If exist filter in Grid
if (string.IsNullOrEmpty(strWhereFilterGrid) == false)
{
   if (string.IsNullOrEmpty(strWhereComplete) == false)
   {
  strWhereComplete = strWhereComplete + " and ";
   }

  strWhereComplete = strWhereComplete + " " + strWhereFilterGrid;
}

//Build complete query
strQueryReportComplete = "SELECT " + strSelectReport;

strQueryReportComplete = strQueryReportComplete + " FROM " + strFromReport;

if (string.IsNullOrEmpty(strWhereComplete.Trim()) == false)
{
  strQueryReportComplete = strQueryReportComplete + " WHERE " + strWhereComplete;
}

if (string.IsNullOrEmpty(strGroupByReport.Trim()) == false)
{
  strQueryReportComplete = strQueryReportComplete + " GROUP BY " + strGroupByReport;
}

if (string.IsNullOrEmpty(strOrderByReport.Trim()) == false)
{
  strQueryReportComplete = strQueryReportComplete + " ORDER BY " + strOrderByReport;
}

4. And finally, I call the SetSQLCommandTable method to update the T-SQL in the report

CrystalDecisions.Shared.ConnectionInfo objConnectionInfo = pReportDocument.Database.Tables[0].LogOnInfo.ConnectionInfo;

pReportDocument.SetSQLCommandTable(objConnectionInfo, "Command", strQueryReportComplete);

So how can I filter the report, using the T-SQL filter?

Note:

- All My reports are developed using the Report Designer, where the database connection is to the Development Database, so I need to change the connection to my Clients database.

- I Could use reportDocument.RecordSelectionFormula method, but I have not found a way to convert the syntax of T-SQL to Crystal syntax.

- Instead of use the solution adopted by me, I could have used the option to reportDocument.SetDataSource(dataSet), but I'd have to do the steps 1 and 2 to take the Query Report, and call the reportDocument.SetDataSource(dataSet) and _contentBytes = StreamToBytes(reportDocument.ExportToStream(ExportFormatType.PortableDocFormat)) methods occurs a overflow memory exception.

This same question I did in the StackOverflow (http://stackoverflow.com/questions/32103173/filtering-crystal-reports-with-transact-sql-in-asp-net-m...) without result.

[former_member11696 · Accepted solution]

Hello,

Cool code... but the problem is you cannot update/set a reports data source based on tables to a Command object. Actually you can but only if there are 2 or less tables.

Create the report off the command and then you can update the command SQL with the new filters.

Or use a second report based on the first reports table info and copy the SQL from it into the second reports Command object.

How did you get this to work in the previous version, or did you? Were you using .NET assemblies or were you using the RDC?

Don

[Former Member · Accepted solution]

Thanks Don, but update my all 2k reports to use Command is not a good solution for me, because it is hard work and my old system will stop working using Command instead of tables.

Jan Tovgaard in this post https://scn.sap.com/thread/3566834 has the same problem as me, I can not use DataSet because the results are greater than 5k and the solution to use XML was confused and there is no practical example with two or more tables.

In my old system in VB6 we use:

Dim crystalReport As CRAXDRT.Report

crystalReport.SQLQueryString = "CartItems.Value < 5000"

crystalReport.SQLQueryString = filter in SQL language

And works fine.

I can not understand how such an important feature stoped to exist in a more current version of Crystal.

My current problem is columns with the same name in different tables, my current solution is to create Computed fields in the database and then rename the field names, example: Cart.Value continues with that name, but for the field CartItems.Value I created the following in the database:

ALTER TABLE ADD CartItemsValue as (Value)

And then I update the report to use the new field called CartItems.CartItemsValue.

[former_member11696 · Accepted solution]

Yes the history as to why is in the RDC we allow editing the SQL directly but in CR 9 we completely rewrote the database drivers, Query engine and QueryBuilder. This change removed the ability to alter the SQL directly so we add the Command Object to the Designer and SDK which allows you to edit the SQL again.

We did this because we were spending so much time on "fixing" client issues we basically said "if the DB client does not follow the Standards ask the DB client maker to fix the problem"

And this was documented in CR 9 so you were made aware of these changes... assuming you were following the info of course, if not well now you know why...

And yes, you cannot have the same name in more than one table now except if the table is fully qualified, which they should be now, CR will actually Alias the table/field name now, you can see this working in CR Designer.

Unfortunately I don't have any other options for you, I tried asking DEV to add the ability to convert reports from Tables to SQL multiple times and the problem is the logic to be able to handle the field remapping is quite extensive to be able to support all databases so it's been rejected each time.

All I can suggest now is to have 2 Report Designers open, copy the SQL statement and use it as the data source for the new report. Then copy each object from one to the other and fix accordingly.

Once converted then you'll have the full ability to alter the SQL again...

Don