CrystalReportsViewer: Error in formula Record Selection

[Former Member]

I'm building a windows application in C# that allows a user to navigate to an .rpt file and display the report.

I've copied the database down to my local machine and the application works fine.  When I attempt to run the app at the client site, I get the following:

It is complaining about a commented out piece of code in the .rpt file.  If I remove it, it simply complains about the next line (commeneted or not).  It looks like it can't parse.

There are some differences between my environment and our client's:

In my environment I'm running directly against the database.  At the client site I take a snapshot and run against the snapshot.

My environment is running SqlServer 2008; the client is running 2005.

They are running Crystal Reports 2008 I have CR 2010.

I've installed the latest runtimes on their server.

Any help would be much appreciated.

JP
 
 
 
 
 
 
 
 
 

[former_member183750 · Accepted solution]

This sounds pretty weird:

It is complaining about a commented out piece of code in the .rpt file.  If I remove it, it simply complains about the next line (commeneted or not).

In any case see if the following will helps; 

Install the MS SQL Server Client. You'll see the driver name is "MS SQL Server Native client 10". With CRVS2010, this is the one you need to use. Alternatively, use ODBC, it seems to resolve all issues, but the client must be installed.

Also, enable the "Verify on 1st Refresh" option.

Trying to run the report in CR2011 may not be a bad idea either.

- Ludek

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member · Accepted solution]

Ludek -

Thank-you for your response.  I tried installing and using the MS SQL Server Native clent 10 and, unless I didn't so it correctly, it gave me the same results.

Here's where I tried to point to the driver in the code; perhaps I did something incorrectly:

private void applyLoginInfo( ReportDocument document, string serverName, string dbName, bool useTrustedConnection, string userName = "", string password = "" )

        {

            TableLogOnInfo info = null;

            try

            {

                #region Credentials

                //

                // Define credentials

                //

                info = new TableLogOnInfo();

                info.ConnectionInfo.AllowCustomConnection = true;

                info.ConnectionInfo.ServerName = serverName;

                info.ConnectionInfo.DatabaseName = dbName;               

                //

                // Set the userid/password for the report if we are not using integrated security

                //

                if ( useTrustedConnection )

                {

                    info.ConnectionInfo.IntegratedSecurity = true;

                }

                else

                {

                    info.ConnectionInfo.Password = password;

                    info.ConnectionInfo.UserID = userName;

                }

                #endregion

                #region Apply to connections, tables and sub-reports

                //

                // Main connection?

                //

                document.SetDatabaseLogon( info.ConnectionInfo.UserID,

                    info.ConnectionInfo.Password,

                    info.ConnectionInfo.ServerName,

                    info.ConnectionInfo.DatabaseName,

                    false );

                //

                // Other connections?

                //

                foreach ( CrystalDecisions.Shared.IConnectionInfo connection in document.DataSourceConnections )

                {

                    connection.SetConnection( serverName, dbName, useTrustedConnection );

                    connection.SetLogon( userName, password );

                    connection.LogonProperties.Set( "Data Source", serverName );

                    connection.LogonProperties.Set( "Initial Catalog", dbName );

                    // Point to the SQL Server Native Client 10.0 driver

                    connection.LogonProperties.Set( "Driver", "{SQL Server Native Client 10.0}" );

                }

                //

                // Only do this to the main report (can't do it to sub reports)

                //

                if ( ! document.IsSubreport )

                {

                    //

                    // Apply to subreports

                    //               

                    foreach ( ReportDocument rd in document.Subreports )

                    {

                        applyLoginInfo( rd, serverName, dbName, useTrustedConnection, userName, password );                       

                    }

                }

                //

                // Apply to tables

                //

                foreach ( CrystalDecisions.CrystalReports.Engine.Table table in document.Database.Tables )

                {

                    TableLogOnInfo tableLogOnInfo = table.LogOnInfo;

                    tableLogOnInfo.ConnectionInfo = info.ConnectionInfo;                   

                    table.ApplyLogOnInfo( tableLogOnInfo );

                    if ( ! table.TestConnectivity( ) )

                    {

                        Debug.WriteLine( "Failed to apply log in info for Crystal Report" );

                    }

                }

                #endregion

                try

                {

                    //

                    // Break it all down

                    //

                    document.VerifyDatabase( );

                }

                catch ( LogOnException excLogon )

                {

                    Debug.WriteLine( excLogon.Message );

                }

            }

            catch ( Exception ex )

            {

                throw new ApplicationException( "Failed to apply login information to the report - " +

                    ex.Message );

            }

        }

Another variable in this equation as if there weren't enough already) is that my development machine is 32 bit (so my exe is 32-bit).  I've installed the 32-bit runtimes on the target machine which is a 64-bit OS.  Could that be the problem?

JP

[former_member11696 · Accepted solution]

Hi John,

That is very strange because CR .NET will not return commented out text from the selection formula. Actually it's something we are trying to get back in. It was available in the RDC.

I don't think it is the DB client causing the problem...

I think it may be some hidden character in the formula somewhere, likely before that comment.

[It is complaining about a commented out piece of code in the .rpt file. If I remove it, it simply complains about the next line (commeneted or not).  It looks like it can't parse.]

Can you delete all of the comments, copy them into the summary info Comment area if you need them or in a hidden text box. See if that works and if not try retyping in the formula, do not copy and paste. highlight everything and hit the delete button and then hold the shift key down and press the down arrow key, this will highlight any spaces or other so it too can be deleted.

Can you attache the report to this post so I can have a look also? I don't need data. rename the report to .txt and use the Advanced editor to attach the file. I would like to get hold of a report that this is happening in again so I can send to R&D to see what the parser is picking up.

I have seen this happen once before but after trying it again the next day I could not reproduce the problem...

Thanks

Don

[Former Member · Accepted solution]

Don -

Thanks for your response.

I'll try the steps you suggest, but would like to point out that this application works flawlessly from my test machine.  If there is an issue with the .rpt file, I would expect that I'd be haveing a problem no matter where it runs.

I'm working on obtaining authorization to post the .rpt file; it is the property of one of our customers and I need permission before I can post.

I look forward to the day I cannot reproduce the problem ...

JP

[Former Member · Accepted solution]

Hi Don -

I was cleared to upload the file so see attached.

Thanks!

JP

Buy Guide Items-ADO.txt.zip

[former_member11696 · Accepted solution]

Hi John,

Have them rename the parameter "BuyGuide#", remove the "#" and change it to "BuyGuideNo". All shifted top row keyboard charaters are typically reserved for other "special" controls. The c++ interpreter is likely trying to do a redirect with that character.

Let me know if that resolves the problem?

Thanks again

Don

[former_member11696 · Accepted solution]

Hi John,

I've been doing some testing and somethign I can't explain....

In my test app if I get the selection formula it always inserts a carraige return after the braken it inserts:

" (

  {regency_view_buy_guides_with_corp_id.order_no} = {?BuyGuide#}  ) "

CR is doing this to qualify the SQL

I can't get the SQL because I don't have their DB but can you get them to click on Database, Show SQL and copy the SQL statement and paste it in? I would like to see what CR is generating...

Also, as much as I hate it when Ludek is right... lmao, it may be due to the DB drvier being used.

CR 2011 likes SQL Server 2008 Native 10 client and others have reported that when connecting to SQL 2005 in CR 2011 use the SQL Native 10 client anyways, it works better than the MDAC version of the OLE DB Provider. It could be that CR is trying to format the SQL according to MDAC Native driver and somwthing is not quite right....

Also, ask them if it's possible to get the Store Procedure SQL statement, I would like to see how they are defining the Parameter.

Thanks again

Don

[Former Member · Accepted solution]

Don -

Sorry for the delay in getting back to you.

I've forwarded your requests onto the customer and we are waiting for responses.

In the mean time, I have a couple more questions.

First, if the driver is suspect again, the code I posted was my attempt to point this at the correct driver.  Did I not implement that correctly?

Second, my boss really needs to get this done at this point.  It looks to me as though both you and Ludek are SAP employees, yes?

Is there some sort of paid support or paid services we can purchase to expedite this?  I'm not at all implying by this that you are a bottleneck; you've been very responsive.  My boss is of the mind that if we can just pay to have this resolved it's a win for everyone.

Is that an option?

Thanks,

JP

[Former Member · Accepted solution]

Don -

Here's the SQL you asked for:

SELECT
"p21_view_contacts"."first_name",
"p21_view_contacts"."last_name", "p21_view_oe_hdr_salesrep"."salesrep_id",
"regency_view_buy_guides_with_corp_id"."order_no",
"regency_view_buy_guides_with_corp_id"."user_line_no",
"regency_view_buy_guides_with_corp_id"."unit_price",
"regency_view_buy_guides_with_corp_id"."item_id",
"regency_view_buy_guides_with_corp_id"."qty_ordered",
"regency_view_buy_guides_with_corp_id"."extended_price",
"regency_view_buy_guides_with_corp_id"."short_code"

FROM 
("P21prod"."dbo"."regency_view_buy_guides_with_corp_id"
"regency_view_buy_guides_with_corp_id" INNER JOIN
"P21prod"."dbo"."p21_view_oe_hdr_salesrep"
"p21_view_oe_hdr_salesrep" ON
"regency_view_buy_guides_with_corp_id"."order_no"="p21_view_oe_hdr_salesrep"."order_number")
INNER JOIN "P21prod"."dbo"."p21_view_contacts"
"p21_view_contacts" ON "p21_view_oe_hdr_salesrep"."salesrep_id"="p21_view_contacts"."id"

WHERE
"regency_view_buy_guides_with_corp_id"."order_no"='2269881'

ORDER BY
"regency_view_buy_guides_with_corp_id"."order_no",
"regency_view_buy_guides_with_corp_id"."user_line_no",
"regency_view_buy_guides_with_corp_id"."item_id"

[former_member11696 · Accepted solution]

Hi John,

Thank you... So the record selection formula does generate the correct syntax in the SQL Statement...

mmmmm very strange...

Yes you can purchase a single case:

http://store.businessobjects.com/store/bobjamer/en_US/pd/productID.98078100?resid=S6I@hgoHAkEAAGsiyV...

Do you have the same database structure as your client for testing?

And to clarify what they/you are using?

You have:

CR for VS 2010

Using the SQL NAtive 10 driver

They have:

Your app and which DB Server/Client?

Don

[Former Member · Accepted solution]

Don -

>> Do you have the same database structure as your client for testing?

Yes, I've copied the database.  The only difference between how I run in test vs. how I run in production is that in production I run against a snapshot (we run on the mirror).  I don't have a version od SQL Server installed on my workstation that supports snapshots, so I run directly against the DB in test.

I have:

>> CR for VS 2010

Yes

>> Using the SQL NAtive 10 driver

Yes

They have:

>> Your app and which DB Server/Client?

SQL Server 2005 SP3 and teh native driver for that by default.  We've instllaed SQL Native 10 on that machine and my code attempts to use it.

I appreciate the link to support.  What my boss is actually lookinig for at this time is more along the lines of consulting services.  We'd like someone to pick up from where I am with the app and complete it.  Does SAP offer anything like that?

[former_member11696 · Accepted solution]

Hi John,

I think we do but checking, I passed on your request to the people who know how far we can go and recommend who can develop for you.

From Support we can only assist debugging/suggesting how to, we can't debug/develop your app for you. Others on this site may have suggestions also.

Thanks again

Don

[former_member11696 · Accepted solution]

Best palce to start is with Sales: 1-800-877-2340

There is also an Outsourcing partner guide here that is another option:

http://www.sap.com/community/ebook/2012_Partner_Guide/index.html

Don

[Former Member · Accepted solution]

Don,

Thanks so much for your responses; as always, they are much appreciated.

I'm currently out of town on a family matter.  I've forwarded your suggestions to my management, as this issue is attracting a lot of attention.

If you have any other thoughts please stay engaged ; I'm checking email while on the road.

Thanks,

JP