Solved: Dynamic pick list values missing in web viewer

We have a report that uses a dynamic pick list to allow the user to select values from a list loaded from the database. When I run the report in the designer, I get prompted with a pick list. We run this report in a web UI and on one server we get the list and on another where the list is supposed to be we get a free-form text entry field that allows me to type in anything at all.

There is zero feedback from CR about what, if anything, went wrong. No event log entries, no dialog boxes, nothing. I have absolutely no idea where to even begin looking for a solution.

The non-working server is using the 64 bit runtime engine for .Net 4, 13.0.3.612 and the working one is running 13.0.2.469. I don't see anything in the release notes suggesting that the was anything "fixed" in the newer version that would break dynamic pick lists.

SAP Managed Tags:
SAP Crystal Reports, version for Visual Studio

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Accepted Solutions (1)

Hello Colin

Picture being worth a thousand words, could you send a screenshot of what you are seeing?

- Ludek

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

What I expect to see (from the working server):

What I actually see (from the non-working server):

It's either incorrect version of the viewer or the viewer is not configured correctly.

Use Process Monitor on both the working and non working computer. In the logs look for the crystalreportsviewer13. The non working computer should show you the same "stuff" (path, name, etc.)as the working one.

- Ludek

There has been no "configuration" of the viewer. The example I provided was just a test with my local dev installation and one of our staging servers. A better example is we have 2 staging servers both running the same OS and both having had our application and CR runtime installed from the same installer binaries and both have identical versions of the CR runtime. One server is able to properly display the dynamic pick list, the other is not. In both cases all other parts of CR are working, and on the one that cannot display the dynamic pick lists, it gives me a free-form text entry field and if I enter something in there and use the button to send it to the selected values list, it will run the report with the typed values.

Monitoring with procmon results in 1864 entries on the non-working server and 2788 entries on the working server, but they are all registry access. The only other CR entries I've noticed are for CSS files.

Hello Colin

Re; but they are all registry access

Please have a look at the Help Files for Process Monitor. ProcMon will show you all files as well as reg entries - if used correctly.

- Ludek

It turns out that I was missing few events earlier (I cleared the log before launching the report, but I think IIS may have cached the CR runtime). I ran iisreset and started logging, then logged into the site and navigated to the report view. Now I see a few more entries of type QueryDirectory (that mostly result in "no such file") in addition to the thousands of registry access entries when I filter by path contains "crystal" or details contains "crystal." The final 2 QueryDirectory entries succeed, and both (working and non-working) are looking for:

C:\Windows\assembly\GAC_MSIL\CrystalDecisions.Shared\13.0.2000.0__692fbea5521e1304\CrystalDecisions.Shared.dll

Note that I have different versions of crystal installed on both machines, 13.0.3 on the non-working and 13.0.2 on the working. It appears as though the wrong file is being looked for, although it is succeeding in both cases.

I have opened a tech support incident with SAP. We've created a reproduction using a RPT file with Excel data sources. I'd attach it here, but that doesn't appear to be possible.

If I host in the Visual Studio web server, I get a pick list filled in with values from the Excel source.
If I host in IIS (64 bit, Network Service account, full control on directory) I get a text box where I can type in values and add them to the parameter value list (i.e. the same as the original complaint).

So, the issue appears to be only when the website is hosted in IIS, and the CR runtime components were installed correctly (or at least the 32 bit ones were). Unfortunately CR is not logging any messages I am aware of that would provide any clues about where or why things are going wrong.

Colin, are you using any CR viewer events?

Have a look at this post: http://scn.sap.com/thread/3325885

What's the incident ID?

- Ludek

No events being used. The incident is 1148125/2013.

This is the entire code in my sample:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            ConfigureCrystalReports();
        }
    }
    private void ConfigureCrystalReports()
    {

        var connectionInfo1 = new ConnectionInfo()
                {
                    DatabaseName = Server.MapPath("~/AccountID.xls")
                };

        var connectionInfo2 = new ConnectionInfo()
                {
                    DatabaseName = Server.MapPath("~/AccountType.xls")
                };

        var reportDoc = new ReportDocument();
        reportDoc.Load(Server.MapPath("~/DynamicPickListFromExcelFile.rpt"));

        SetDBLogonForReport(connectionInfo1, connectionInfo2, reportDoc);
        CrystalReportViewer1.ReportSource = reportDoc;

    }



    private void SetDBLogonForReport(ConnectionInfo connectionInfo1, ConnectionInfo connectionInfo2, ReportDocument reportDocument)
    {
        Tables tables = reportDocument.Database.Tables;
        foreach (CrystalDecisions.CrystalReports.Engine.Table table in tables)
        {
            TableLogOnInfo tableLogonInfo = table.LogOnInfo;

            switch (table.Name)
            {
                case "Sheet1_":
                    tableLogonInfo.ConnectionInfo = connectionInfo2;
                    break;
                case "ID_":
                    tableLogonInfo.ConnectionInfo = connectionInfo1;
                    break;
            }

            table.ApplyLogOnInfo(tableLogonInfo);
        }
    }

Colin, please tell Vinit that you are also working on the issue here - just so he knows.

Two things:

1) Process Monitor logs should show you that the app is accessing the folder; crystalreportsviewer13

Do you see that folder being accessed?

2) Both servers are using hte same OS and IIS?

3) I'd like you to "dum" things down a bit:

1) Run the report first and ensure it has "saved data" as this is what we will be working with

2) New app

3) Viewer on a form

4) One line of code: CrystalReportsviewer1.ReportSource = <path to the "saved data report from (1)">

5) Deploy this app to the problem server - default app pool

6) Run

7) Hit the refresh button - this should give you a screen asking if you want to use current of new parameter(s). Choose 'new'. What does this parameter screen look like?

- Ludek

I previously emailed Vinit a link to this discussion. He has been able to reproduce it apparently. I tried your steps, but I do not get prompted about using current or new parameters. I ran the report in designer and saved it with data.

I will check procmon tomorrow, but IIRC there was access to that folder. The OS is not the same in all cases:

Windows 7/64 bit: not working

Windows 2008 R2: working

Windows 2008 R2: not working

Ummm:

Windows 7/64 bit: not working

Windows 2008 R2: working

Windows 2008 R2: not working

Windows 2008 R2, working or not working

- Ludek

That's kind of the point. We've got one 2008 R2 server that is working and one that isn't. Both were installed using the same binary installer package which uses a bootstrap to download the same CR runtime version on each installation. Both are reporting the same CR runtime versions. Both are VMs. The only difference is one is hosted in our production environment and one in our test/staging environment.

Procmon is not showing anything with "CRYSTALREPORTSVIEWER" in it (using the "Find" action).

I did:

iisreset
start procmon.exe
load the demo page
see the crystal ui prompting me for parameters but no dynamic pick list values
pause procmon and search for "CRYSTALREPORTSVIEWER"

Interesting. I asked Vinit for the same app he tested with and created a brand new image; VS2010, no CR. Installed the 32 bit MSI for CRVS. Installed Vinit's app and the parameter came up the same as it did on a development computer.

I have just sent an email to Vinit to share his image with me so I can take a look at possible differences, run a few utilities, etc.

- Ludek

"Inside" Visual Studio (i.e. using the 32 bit Cassini web server) it runs fine. Outside, using IIS in a 64 bit app pool it fails. I think it's an issue with the 64 bit code. Two releases ago was 32 bit, and was extensively tested and we did not see any issues. The last release we converted to 64 bit, and the dynamic parameters were probably not tested as extensively (just that they worked, probably on a QA server) and since the problem appears to be somewhat sporadic, we did not notice until recently.

Answers (2)

I don't know if this helps, but in my case, I was doing this --

.SelectionFormula = string.Empty;

this was causing the dynamic pick list params not to show up.

I had to change this to

.SelectionFormula = null;

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

I tried that, but it has not affected anything (I wasn't setting it in any case). I've literally got one line of code:

protected void Page_Load(object sender, EventArgs e)

{

CrystalReportViewer1.ReportSource = Server.MapPath("~/DynamicPickListWithData.rpt");

}

Hello Colin

Reproduced. 32 bit app pool works just fine, 64 bit not so good...

I'll be escalating this for a fix and I'll update this thread with a tracking ID once I have it. Submissions for SP 6 for CRVS are closed now, so we'll have to see about SP 7 (ETA; 3rd quarter 2013).

Oh, I've also taken over on that case. I notice that this is a single purchase case. I'll put in a note that this can / should be refunded. I really am not familiar with the refund process, but I suspect sales should be able to help; 866-681-3435

- Ludek

Thanks for all your help on this issue. It's a bit disappointing that we won't be seeing a fix in the near future. At least now I'll know that I'm not wasting time figuring out a work-around until then.

Hello Colin

I was not in the office yesterday, but over the weekend I thought about the issue a bit more (yeah, nothing better to do... )

Anyhow, it occured to me that the issue could happen if when using the 64 bit app, there is no 64 bit datasource defined. Taking a closer look at the app provided by Vinit this proved to be the case. His connection was to and XLS file, using the MS DAO engine. As there is no 64 bit DAO engine, the connection failed, but only after the parameter prompt popped up. E.g.; the the full test should have been:

1) Load the report

2) Parameter screen pops up

3) Fill out the parameter screen

4) Click OK - at this point the report attempts to connect to to the datasource and fails (I only tested up to (2) above

So, that takes me back to your app. What database and what type of connection are you using for the report?

- Ludek

That demo from Vinit is the one I provided. I used Excel because it was easy and self-contained (and it's what my report author created for me).

In production we have Oracle 64-bit drivers installed. We also deploy against SQL server, but are using System DSNs, and my understanding is those will take care of the bitness for us. In any case, what we see is

1. load (success)

2. Parameter screen (fails to load dynamic parameter values)

3. Fill out parameters (success)

4. Click OK (report runs and displays data)

We have many reports (around 60) that all have their db connections set up the same way (in code), and all display data correctly. It appears that it is only the dynamic pick lists that are failing to load.

In any case, if there is an error making a data connection in step 2, there should be something logged, somewhere.

Hello Colin

So, that was not a valid test then(?). E.g.; we reproduced the incorrect parameter prompt, but this is due to an incorrect DB engine. Nevertheless the test may be pointing us to an issue with the database client install(?).

I can do a test with 32 and 64 bit Oracle here, but if that works then we're at the database level. I'll let you know how the test goes.

- Ludek

The Excel data source example I provided was because I obviously can't send a self-contained Oracle or Sql Server example. I did not realize the 64 bit runtime would be unable to open an Excel data source.

In any case, it exhibits the same behavior we see with the database connections. I have verified we see it with both Sql Server and Oracle connections. The reports are otherwise able to run and return data correctly.

Just wondering what the status of this issue is now? Is there a bug tracking ID? This issue has become a bit of a blocker for us now. We were forced to go back to the 32 bit runtime for one client, and now they are running into a different issue with that runtime (I'll be starting a new thread for that) which has essentially rendered our product unusable.

Hello Colin

I think we had a bit of a communications break down. On May 1st, I sent you the following in an email:

I finally got 32 bit and 64 bit Oracle going. Created a sample report with dynamic parameter prompts and ran the report against the 32 bit and 64 bit Oracle. The parameter came up identical in both cases. Which leads to the following:
1) Can you please let me know the version of Oracle used
2) Let me know the type of connection (ODBC, OLE DB, Native, etc.). A screenshot of the “Set Datasource Location” screen with the “Properties” node expanded will be good to see.
3) From the description of the issue, I believe you have one server that works and one that does not. I’d like you to run Process Monitor on both of these servers. ProcMon is similar to Modules, but will also run on 64 bit apps. Unfortunately it does not have a compare feature, but that’s where my work starts. Process Monitor can be downloaded from here:
http://technet.microsoft.com/en-us/sysinternals/bb896645
Please filter ProcMon for the w3wp process as otherwise the logs get too large to go through. I believe if the logs are zipped up, you will be able to attach them to this email and send it back to me. If not, let me know and I’ll create an ftp container for us.

I don't remeber getting a reply to that, thus assuming that the above led you to a solution. Also, looking at your post from April 24:

That's kind of the point. We've got one 2008 R2 server that is working and one that isn't. Both were installed using the same binary installer package which uses a bootstrap to download the same CR runtime version on each installation. Both are reporting the same CR runtime versions. Both are VMs. The only difference is one is hosted in our production environment and one in our test/staging environment.

I believe the two add up to the assertion that this is not a Crystal Reports issue. E.g.; if this works on one computer, it is not the code, nor a bug in the report engine. Thus my conclusion would be that this is related to the environment - specifically to the configuration and install of the Oracle Client.

- Ludek

I did not receive that email (and unfortunately yesterday I dumped all my deleted messages in preparation for an Outlook server move so I can't even see if it got swept up in a spam filter). I'm working on getting the Oracle versions, however in my message on May 1, 2013 6:11 PM I verified that we see the issue with both Oracle and SQL Server which makes it unlikely that it is an Oracle client install issue. Also, all other database operations in the report function correctly. That is, after CR is for some unknown reason unable to display the results of a simple SQL query when the user needs to select parameter values, it quite happily executes one or more different SQL queries and formats and displays those results.

In my message on Apr 30 I noted that we set up that database connections in code. We do not use the connection that is saved in the report file. This is how we set up the connection:

ConnectionInfo connectionInfo = new ConnectionInfo();

connectionInfo.ServerName = server;

connectionInfo.DatabaseName = database;

connectionInfo.UserID = user;

connectionInfo.Password = password;

foreach (Table table in report.Database.Tables)

{

var logonInfo = table.LogOnInfo.Clone() as TableLogOnInfo;

logonInfo.ConnectionInfo = connectionInfo;

table.ApplyLogOnInfo(logonInfo);

}

One thing that did occur to me is that maybe this connection setup is failing on the 64 bit engine for the dynamic parameters.

This issue is absolutely a problem with Crystal Reports. Even if there is some issue with my environment that is preventing part of the report from working, the software should not just carry on as if nothing happened and display an empty text box where the user can type in whatever they feel like and add it to the list of selected values. At the very least it needs to log an error somewhere. I should not be forced to resort to tools like procmon to figure out what's going on.

I will work on getting the procmon results, but it may takes some time. My priority right now is getting our customer running on the 32 bit engine which is crashing randomly after "heavy" use (where "heavy use" is defined as a single user paging through a single report).

Hello Colin

I'll do more investigating also. But to me the critical piece is that this does work on one machine, but not another. I hate to look like I'm banging on the same thing, but from April 24:

That's kind of the point. We've got one 2008 R2 server that is working and one that isn't. Both were installed using the same binary installer package which uses a bootstrap to download the same CR runtime version on each installation. Both are reporting the same CR runtime versions. Both are VMs. The only difference is one is hosted in our production environment and one in our test/staging environment.

So to me that just adds up to some system difference. Apologies, I know I'm getting irritating with this argument .

Re. text box that a user can enter anything into it. I do not think this is a bug. The report engine knows there is a parameter, it just is not getting enough info on what to ask for, so it throws a generic query. Nevertheless, I am pretty sure this is not the cause of the issue, rather it is an aftereffect of the issue(?).

Now, one thing that occurs to me, based on this:

" We do not use the connection that is saved in the report file."

is a simple test:

I would like to see the behavior of the report, if you do not give it a connection in code. E.g.; use the connection in the report. I'd like to runt he report in a new one line app:

1) Add a viewer to a form

2) On Init / new add this line of code:

CrystalReportViewer1.ReportSource = <path to the report>

The report will prompt for the password as this is the only piece of info it does not "remember". There is no way to change the connection using this approach, but it will be interesting to see what happens if we use the original connection.

- Ludek

I don't disagree that it may be some environmental issue, but:

everything else about our web app works
the CR installer didn't report any issues
some parts of the report are able to execute database commands against the same database using the same connection
all our other reports (which have their db connections set the same way) are able to run and retrieve data
the CR viewer doesn't indicate that there were any errors, or that it had trouble retrieving values from the DB

If the report engine knows it needs a parameter, and it knows that parameter was supposed to come from a pick list, and it know that it wasn't able to retrieve those values, giving the user a textbox where they can type in random values is at best a questionable choice. At the very least some component somewhere in the process needs to raise it's hand and say "excuse me, we've got a little problem connecting to the database here." Whether then allowing the user to type in anything they want is the correct course of action is a usability discussion, but failure to record the fact that something the report designer requested was not able to be fulfilled absolutely is a bug.

I've attempted the simple test, with what I can only describe as interesting results. When I load the page, this is what I get:

enter database password (okay, that's expected)
enter parameter values, with pick lists filled with the correct data (this is promising...) no pick list values are shown
enter database password (that's a bit odd, but whatever...)
enter parameter values (umm... what?)

Just for fun (and also because I assumed it would be the next troubleshooting step) I added in the connection info (hard-coded the values into my little test project) and now it works properly. The same report, deployed to the real application, continues to not work. I can make it work simply by switching to the 32 bit engine (but that has it's own issues, rendering it unusable for us). It fails exactly the same way.

Let me reiterate that it is only the dynamic pick lists that do not work. In this test report I have 2 dynamic pick lists, 1 static pick list and the data that gets shown is a separate query that ignores the parameters. Even in the case where the dynamic pick lists are not populated, the report that gets produced is able to query the database and display the resulting rows.

The only thing I can conclude at this point is that internally the CR engine is using different code paths to access the database depending on whether it is for report data or for dynamic pick lists, and that somebody has managed to stuff it up in the 64 bit engine.

I will need to find a test server that demonstrates the dynamic pick lists working in order to capture the procmon results. The server I previously used is not available to me at the moment.

UPDATE: I no longer have a server that demonstrates dynamic pick lists working. The server that was previously working is no longer (I can't even be sure at this point that it ever was -- I may have mixed up enabling 32 bit processes in the app pool).

UPDATE 2: Turns out in my simple test I forgot that I was running under the Visual Studio web server, which is 32 bit. When I switched to IIS/64 bit I get the same behavior. Corrections noted by strikethrough text.

Message was edited by: Colin Young Updated due to error during testing.

Colin, please stand by. I will do more testing on ideas I got from discussing the issue with coworkers here. I'll update this thread to day or tomorrow with my results.

- Ludek

Colin, could you attach one of your reports here? I do not need "saved data", just the rpt will be fine.

Oh, to attach, just rename the rpt to txt and attach. The system will zip it up on it's own.

- Ludek

Message was edited by: Ludek Uher

Here it is.

Hello Colin

I think I know what the problem is. The report has many questionable design approaches. Looking at the table links I see the following:

Crystal Reports is a relational database reporting tool. Unlinked tables are never a good idea as this often leads to unanticipated consequences. When a report such as this is created, there is actually a warning:

My recommendation would be to establish valid links between the tables. An addtional point. Playing with the report, I wanted to see if all tables are actually used in the report. An attempt to remove Acount List and Account Type List tables resulted in this message:

I believe that at least partly, the above message is due to this:

My suggestion would be to get some advise on how to re-construct this report (if possible), so that it conforms to best practices. The guys in the CR Design forums should be able to help.

- Ludek

So instead of messing around with getting my report developer to try and clean up that demo report, I just created one from scratch myself using the new report wizard. One table, one column. I added one dynamic picklist and one static picklist. So far, it appears to be working. That said, there are still a number of problems with CR itself:

The reports work in the 32 bit runtime and fail in the 64 bit runtime. I can't imagine you are maintaining 2 separate code bases, but clearly something is out of sync between the 2 runtimes.
Even if we have deviated from best practices, it's simply not acceptable to fail and not raise a meaningful exception. Exceptions in general I've found to be a major deficiency in CR. If I'm lucky enough to get one it usually has insufficient information to debug the issue, and in many cases actually presents completely incorrect information.

Is there a document or something that details best practices? Or a tool to validate the rpt file for issues? Clearly the runtime isn't doing a good job in alerting us to problems.

There is no one document for "best practices". I think a document like that would be interesting to see as it would have to have the contents of all the help files KBases, Articles, Documents, Articles, Tutorials and Wikis on how to. Certainly the message "Generally not supported" should have caused your report developer to pause and consider the message or perhaps even to ask about it in these forums(?). Some resources you may be interested in (I could have added many, many more):

Facts and Best Practice for List of Values(LOV)

Crystal Reports 2008 -> Performance Improvement Techniques

SAP Crystal Reports 2008 - Articles

Designing Custom Reports with Crystal Reports Tips and Tricks

Re. fail and not raise a meaningful exception

The report was not designed correctly. Your report developer was warned that what he was doing was not a good idea, yet he persisted anyhow. I discussed the issue with a co-worker who is responsible for escalating issues for a fix and he absolutely refuses to even hear of it, unless the report is fixed and the issue reproduced there.

Re. The reports work in the 32 bit runtime and fail in the 64 bit runtime.

Is this the new report, or the old report? If it is the old report, it's simply pure luck. By the way, do make sure that that report is bringing in correct data. Using four unlinked tables results in the report sending out four Select statements. This then results in Cartesian Product / Join. This may result in incorrect / unanticipated data, the same data irrespective of parameter chosen, etc., etc., etc.

If you have issues with the new report, we can work on that further.

- Ludek

So basically there is a bug in the CR 64 bit runtime. I'm attaching the tests that should prove it (all 10 of them). In summary, if you include a command in your rpt file (i.e. using a SQL statement instead of just picking a table), your dynamic picklists will not be populated when using the 64 bit runtime with the web viewer. You don't even need to make use of the command. Just the mere presence of it breaks things. I've also attached an Excel spreadsheet summarizing the test cases and results.

The other interesting thing I noticed during testing is that if I forgot to update my connection settings the parameter prompt would appear and only after I had entered a value for the parameter would I get an exception or error indicating the database connection had failed. That just reinforces my suspicion that the engine is choosing to ignore any errors that occur during the prompts (I also found in some CR documentation reference to a so-called "prompting engine" but I don't know if that was a documentation term or a reference to an actual code module). That in itself is a fairly obvious usability issue that merits its own bug.

I dispute that the previous report was "not designed correctly." The additional command objects that were not linked are only used as data sources for the dynamic picklist values and are not used in the report output at all, so there is no danger of a Cartesian join. I have found comments on the web suggesting that that is a correct setup (albeit not on "official" SAP discussion forums). That actual message states that it is not "generally supported" which implies that there are some situations in which it is supported and I believe that the way we are configuring them falls under those supported situations. I also must point out that the dialog title is "Visual Linking" while the far less severe warning about multiple data sources which I understand to be more of an advisory type message actually has a title of "Database Warning" giving the mistaken impression that it's much more important than the previous.

I hope this is sufficient to persuade you that there really is a bug in the 64 bit runtime. If we don't see some action on this issue soon, our client who is an SAP partner will escalate the issue.

Hi Colin,

"generally not supported" is too light of a term actually. What it means is if there are unfavorable results or if you find an issue we can't fix it, if you link the tables and get the same results then we can do something about it, find a work around or fix it as a bug.

The problem is when using unlinked tables Crystal generates a SQL statement with multiple SELECT's in it and those are unpredictable. What it can do do is sometimes only perform a single select or could be a delay depending on the selects themselves. Which is why we pop up a warning, it also fails to work if you use SQLExpressions, that cause an actual error.

If you show SQL you'll see this.

Can you enable CRLogger.dll and see if the DB drvier is even sending the SQL off to the server?

1553469 - How to enable Database logging in Crystal Reports for Visual Studio 2010

Having your customer log a case won't help unless he has access to your code. We don't fix issues for third party software, you need to log a case.

And Cases get priority obviously because they are paid for, if we are too busy we may not get back to this post...

Curious, if you cancel and then hit the refresh button does it populate the list?

Don

First of all, this is a paid support incident: 1148125 / 2013. On 26 April, Ludek stated that he had "taken over" on this case. Since I was not provided any information to the contrary I assumed that meant through this forum.

Did you even look at the sample reports I attached? There are 10 reports that systematically vary the use of table and command object as the data source for the report output and dynamic picklist values, both with and without the data sources linked, and I even included an Excel spreadsheet summarizing exactly how each report was configured to make it super easy for you to understand exactly what I did (I neglected to include the test database previously, it is attached here). It's really clear that simply putting a command object into the report breaks the dynamic parameters in the 64 bit web viewer, and that whether or not the tables are linked has no bearing at all on the results.

Following the directions in support note 1553469, I get logging in the CR 2011 designer, but not from the web viewer. The problem is with the web viewer in a 64 bit process, not with the 32 bit web viewer and not with the 32 bit designer client. We did do database tracing and determined that in all cases, the correct SQL is being executed. That is, linked or not, 32 bit or 64 bit, when displaying the dynamic parameter prompt, there is a single SQL statement executed that retrieves the correct values from the database. The only difference is that in the 64 bit web viewer it never shows those to the user, while the 32 bit web viewer and the designer client do show those values to the user.

Curious, if you cancel and then hit the refresh button does it populate the list?

That's the 32 bit client, and we established way back at the beginning of this thread that it is in fact working just fine. The web viewer does not display either a "cancel" or "refresh" button.

This issue is clearly a bug. We do have some options to work-around the issue but none are ideal: we can rewrite all our reports to use stored procedures and views (difficult to maintain in the future since database schema changes are strictly controlled), we can rewrite reports to only use stuff built in to CR (not really practical since the queries are using case statements and intelligently filtering data rather than allowing CR to just pull back the entire table when we know certain records are going to be filtered out) or we can use our own custom-built dynamic lookup UI controls (again less than ideal since they were built to solve a slightly different requirement).

Ah, not sure why you are using forums to pass all of the info then...

Ludek's case, if he can replicate then I'll escalate it to R&D.

Try SP 6, just came out.

Don

Colin I was on training when you posted this. I'll work on the samples you posted today and contact you re. results.

- Ludek

SP6 has no effect. I'm posting here because I wasn't given alternate contact information, and in general Ludek has been much more responsive than phone support was. I suspect he has a lot more experience than the people manning the phones.

Hello Colin

Looks like I've been able to duplicate the issue with MS SQL 2012, OLE DB connection.

I am looking at possible work-arounds, etc., and I will be creating an escalation to R&D to see if we can get this resolved in SP7.

I'll update this tomorrow re. the work arounds, escalation tracking ID and so on.

- Ludek

FWIW, we also tested with the SQL native driver with the same results. I haven't been able to test with Oracle since I don't have an Oracle test DB accessible to me at the moment.

Just and FYI from the Report Designer Resources, this is a known limitation at runtime when publishing these types of unlinked data source reports to BI/BOE Server, work around is to make sure if you are going to use unlinked tables or Commands to be sure to add the Command that queries for the parameter values first in the report. Then it's sure to be run first.

Please test, Ludek will also, if it works then no escalation and solution is to link your tables/commands or recreate your reports and add the Command to query for the parameters first.

Don

We've reproduced the problem with linked data sources. Please see the reports posted 5 Jun. From the SQL trace, we do see the SQL being executed. We did catch a COM exception after the prompt SQL was executed:

(optionally) check table exists in sys objects
execute SQL for dynamic picklist
throw COM exception
carry on with the rest of the report as if #3 never happened

Clearly you're able to make it work in the 32 bit engine.

Yeah - I reproduced it also. But I do have one possible way out. My testing shows that this is not an issue with actual database tables. Now, it is not possible to point a report based on command object to a table, but it is possible to point it at a view that precisely duplicates the command object. I tested this and it worked for me:

1) Create a view that matches the command object

2) Remove the command object that is feeding the Dynamic param from the report

3) Delete the selection formula that used the command object

4) Delete the command object from the report

5) Add the view

6) Recreate the selection formula

7) Save and run

Please let me know if this work-around will work for you. I understand that this would only be a work-around and we still need a fix for the command objects.

- Ludek

Yeah, I figured that out also. A stored procedure also works. It doesn't work for our situation though. We have an Saas product, and we don't allow our clients to add views and stored procedures to the database, at least not without going through our support team. It's not really a scalable long-term strategy.

We've got our own client-side lookup elements that are simultaneously more and less powerful than what CR provides, so we are working on implementing those and adding additional coding to close any gaps between what this client needs and what we currently support.

We'd still need a fix for the command problem since we'd like to advertise that one could build reports using the full CR features.

Ok. Understood. I have a technical escalation number for us: TE5000490843

At this time it will be set for Service Pack 7. ETA: mid September +(?).

I've run through a number of ideas, but none have panned out so we're stuck with the fix escalation.

I suppose you'll probably have better ideas re. other work-arounds, but one that comes to mind is creating your own dynamic parameter screen, capturing the user choice and then programmatically passing that to the report. Obvious, I know but I really have no more to offer.

I have placed a note into the system to OK a refund on your case as this is a bug. You should be able to do this by calling 866-681-3435 (or at minimum they should be able to point you in the correct direction).

- Ludek

As a reference, this Discussion is in part duplicated here:

http://scn.sap.com/thread/3372006

- Ludek

Creating our own dynamic parameter screen is exactly what we've done. We actually already have the components for doing lookups for various database objects and the normal way to use our product is to configure the UI with our UI elements, but we have a customer that is looking for something more sophisticated than what we currently offer so we are working now to create code to cover those gaps.

Also, we aren't in the business of creating a general purpose reporting tool, so there are always going to be cases where somebody will want to do something that CR can do and we haven't implemented, so we'd like the option to let those customers use the native CR functionality.

Thanks.

Understood. I'll push this as much as I can.

- Ludek

The issue has been assigned to SP 8. ETA; 1st Q 2014

- Ludek

Thanks. I'm no longer working with CR, but my former colleagues will be looking forward to that release. I'll alert them to this post.

Hi Ludek,

Was this issue ever fixed in SP7 or SP8?
We are faced with the same problem. I've installed SP7 and that did not seem to resolve the issue.

I also had a look at the fix list for SP8 and this issue does not seem to be one of the ones that were fixed there either.

Do we have a new ETA for this yet?

Thanks

Things got a bit confused with the SPs.

The target date for this fix was 1st Q 2014 (SP 8), meaning about March 2014 (see my post on Oct 4, 2013).

In November, it was decided to release SP 8 in December due to some backward compatibility issues introduced in SP 7. But that is all that SP 8 had as far as fixes. So, the fix for this issue is still due in March 2014, but in SP 9.

- Ludek

Hi Ludek,

Colin was my colleague, as a company we are looking into the update on this proposed fix. Can you give us a update on this please? If this has not been fixed yet, can you give me a date when this will be available? What is the process to get this fix?

Thanks

MRoy

Thanks Ludek for the update. This issue has been on our radar for some time now, and would appreciate some solution on this from SAP. Please do update us whenever you have some information on this.

Thanks

MRoy

Yeah - understood. I knew the issue was scheduled into SP 8, which then turned into SP 9. But had no idea it got lowered to an Enhancement status. Let's see what R&D and Program management come back with.

- Ludek

Hi All,

Ludek mentioned this to me also so I changed the status to defect rather than Enhancement.

We know why it doesn't work, there is no 64 bit crdb_query.dll.

Dev is building one and should be included in SP 9, then Dynamic Param's should work.

ADAPT01692433 - 64 bit: Dynamic parameter screen is empty in WEB app if report has a Command Object in it

Thanks again

Don

Hi Don et al,

Is there any update on this issue? We have also been experiencing some intermittent issues with dynamic parameters.

Thanks,

:: Tyler

This is fixed in SP 9:

I'm just today compiling a wiki of all fixed issue - hope to have it published this week...

- Ludek

Unfortunately I have another scenario where even SP 9 does not solve this problem.

Here is the run down:

Existing report upgraded form CR XI. Update DB driver everything works fine. If I modify the report by adding columns from existing tables still everything is fine. If however I add a table with a single key join and add a column from that table I have the above problem. It will work on dev machine with CR for VS installed. However when I deploy to server with SP 9 no lists for dynamic parameters.

Two other notes the only way to reproduce on the development machine is to set the driver to Sql Server Native Client 10 or 11. If I use OLE DB for SQL Server it always works on the dev machine. Other then that the development database is SQL Server 2012 while server to deploy to is SQL Server 2005. Shouldn't make a difference in my mind but thought I would mention it, because if feels like something is forcing the driver back to a native sql server version.

Does this work in a 32 bit environment?

The environment I am deploying to is 32 bit. My DEV environment is 64 bit and it works there in the developer designer. The only way I have been able to reproduce the failure on the DEV machine is by using 1 of the native SQL drivers. Previously I was told those were preferred over the OLEDB for SQL Server.

I'm still confused where / how this works.

Any CR designer is 32 bit. Be it a stand-alone crw32.exe, or the designer that is used inside the .NET IDE. So, the question is; Do these reports work in any 32 bit environment. E.g.; crw32.exe, or in the CR designer inside the .NET IDE?

Or; how (in what version of CR ) were these reports created?

- Ludek

So then I guess it works in one 32 bit and fails in another.

Let's try this

Reports Originally created in Crystal XI
Upgraded to Visual Studio .NET 13.x version (currently SP 9).
All the reports work if I just upgrade them.
They begin to fail with the scenarios outline below when I add a table from SQL and try to add a column from that table (multiple different RPT files that work when upgraded exhibit this behavior)

Failure Scenarios

.NET IDE works with OLE DB driver Fails with any native SQL driver.
32 Bit ASP.NET Deployment machine always fails

Hopefully that is clearer.

My suspicion is that there is something weird going on with either the report(s), the client(s) or both...

I often find that using the stand-alone version of CR gives either a clue or a better error. And an eval of CR 2013 can be downloaded here:

Crystal Reports | Business Intelligence for Small Business | SAP

It would be interesting to see what the report does in CR 2013 when it's set to use native SQL .

- Ludek

I can confirm that I am seeing the very same issue on Windows 7 64-bit.

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Can you post the version of the CR runtime you are using? Also, have you tested on any other servers (e.g. 2008 R2)?

Sort of happy to hear it's not just me. On the other hand, if it was me it would be easier to fix

Thanks.

OK. I tested this as a 32 bit app pool, so later today I'll do a 64 bit.

Version of runtime is CRVS SP 5 (I'll post the exact version once I have the image running)

Oh, WIN 2008 only for now.

- Ludek

Ask a Question

Top Q&A Solution Author

User

Count

Dynamic pick list values missing in web viewer

Accepted Solutions (1)

Accepted Solutions (1)

Answers (2)

Answers (2)

Re: How can we use a odata connection of dataspher...

Re: Scheduled Report shows successful status; but ...

Re: Blending Tables (Import Models)

Re: Object store secret mys-object-store-secret no...

Re: CF Deployment Error: Error getting tenant t0