Question Re: Cascading Parameter list pulled from server incomplete in Technology Q&A

Cascading Parameter list pulled from server incomplete

Privete_member__1338745 — Tue, 24 Jan 2023 21:19:38 GMT

I have a problem with a Cascading Dynamic parameter.

I set up a stand alone dynamic parameter, pointed it to a specific value (METHOD) on a table (RUNS) on my data server. When set up like this, it correctly pulls a full list to populate the Dynamic Parameter. (See the left side of the screenshot.)

However, if I set it up as a Cascading Dynamic parameter, using the same source, it only pulls a partial list. (See the right side of the screenshot.)

The single dynamic parameter setup:

and here is the Cascading Setup:

Both setups have the same setup in the formula section. The only real difference is the parameter names. This one shown below is from the independent Dynamic parameter setup.

I tried changing settings for the "Join" between tables, but that had no effect.

I am using Crystal Reports 2016, Support pack 9 patch 4 (version number 14.2.9.4008.)

**Edit**

An odd thing happened. I updated the second of the parameters from RUNS.RUNNO to RESULTS.RUNNO and the list for the first parameter, RUNS.METHOD, increased by 9 lines. The list is still not as big as if I did the first parameter without a second parameter, but it did grow a little. See below.

Re: Cascading Parameter list pulled from server incomplete

DellSC — Tue, 24 Jan 2023 22:42:50 GMT

Are you filling the data for the parameters from the same query that contains data in your report? Or are you using separate tables for the parameters?

-Dell

Re: Cascading Parameter list pulled from server incomplete

Privete_member__1338745 — Tue, 24 Jan 2023 23:10:36 GMT

I'm fairly new to Crystal reports, but according to my knowledge, I believe it's from the query that contains the data in the report.

I set up the tables, parameters, and formulas, and then pressed the refresh button and parameter pane comes up asking for a value in the parameters. (this is where the problem happens)

If it helps, here's the SQL query after I've filled in the parameters, where I set the METHOD parameter to 23.014 and RUNNO parameter to 920:

SELECT DISTINCT "FOLDERS"."BATCHNO", "RESULTS"."ORDNO", "RESULTS"."ANALYTE", "RESULTS"."FINAL", "RESULTS"."UNITS", "RUNS"."METHOD", "RUNS"."RUNNO", "RESULTS"."TESTNO"

FROM "QM_STARLIMS_DATA_PROD"."dbo"."FOLDERS" "FOLDERS" INNER JOIN ("QM_STARLIMS_DATA_PROD"."dbo"."RUNS" "RUNS" INNER JOIN "QM_STARLIMS_DATA_PROD"."dbo"."RESULTS" "RESULTS" ON "RUNS"."RUNNO"="RESULTS"."RUNNO") ON "FOLDERS"."FOLDERNO"="RESULTS"."FOLDERNO"

WHERE "RUNS"."METHOD"=N'23.014' AND "RUNS"."RUNNO"=920

ORDER BY "RESULTS"."ORDNO"

Re: Cascading Parameter list pulled from server incomplete

raghavendra_hullur — Wed, 25 Jan 2023 14:51:37 GMT

Hi,

From what you have shared, I think the cascading parameter set is being created using List Of Values(LOV's) as part of using a Business View manager (a client tool which needs to be installed separately).

If that is the case, by default, there is a restriction of only 1000 values getting displayed when we check the values at report.

There needs to be a registry update (or an additional option as well) for this and you can check the steps mentioned in the link shared below:

https://userapps.support.sap.com/sap/support/knowledge/en/1218588#:~:text=Create%20a%20dynamic%20LOV%20parameter%20containing%20over%201%2C000,it%20only%20display%20a%20maximum%20of%201%2C000%20values

Hope that helps!!!

Thanks,

Raghavendra

Re: Cascading Parameter list pulled from server incomplete

Privete_member__1338745 — Wed, 25 Jan 2023 18:04:28 GMT

Not effective.

I had the LOV updated to 5000 and 10000, but nothing happened.

It was updated at

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\DatabaseOptions\LOV using the string MaxRowsetRecords and the value of 10000

The problem still persists.

Re: Cascading Parameter list pulled from server incomplete

Privete_member__1338745 — Thu, 26 Jan 2023 17:49:32 GMT

Edited the original question to add new information and screenshot.

Re: Cascading Parameter list pulled from server incomplete

Privete_member__1338745 — Thu, 26 Jan 2023 21:29:34 GMT

@raghavendra.hullur

It seems you were correct about the problem, but the article you referenced gave the wrong place for the correction.

After poking around more, I found the right place to put the LOV reference. There are 3 places in my registry that are all very similar:

(Not the correct place)

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\SAP BusinessObjects\Crystal Reports for .NET Framework 4.0\Crystal Reports\DatabaseOptions \LOV

(Also not the correct place)

HKEY_CURRENT_USER\SOFTWARE\SAP BusinessObjects\Crystal Reports for .NET Framework 4.0\Crystal Reports\DatabaseOptions\LOV

(Finally, this one worked!)

HKEY_CURRENT_USER\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\DatabaseOptions\LOV

Note: The final part of the address \LOV had to be created by right click and selecting "New Key." After the new key is made, then I added a new string value (again right click and select "String Value") and named the string "MaxRowsetRecrods" and gave it the value 10000.

Also note: My way to tell it was the correct registry address was the folder "DatabaseOptions" was filled with many different entries, (approximately 40+ in the folder). At the incorrect registry addresses, "DatabaseOptions" only had one or two entries.

Re: Cascading Parameter list pulled from server incomplete

raghavendra_hullur — Fri, 27 Jan 2023 08:01:06 GMT

Hi,

Probably the link I shared is not for the Crystal version you are using. We had this issue with Crystal Reports 2020 and one of our Admin guys resolved the issue using the link shared without much info on the steps they followed.

Anyways, glad that the link shared, gave some inputs to you to explore further and resolve your issue and I am sure the options you explored will help someone with similar issue in future.

Have a good weekend 🙂

Thanks,

Raghavendra