I have a command that lets user select 3 levels of grouping and there are 42 possible values to choose from. Here is the basic setup:
Grouping 1 - single value drop down selection from 42 possible values
Grouping 2 - single value drop down selection from 42 possible values. The value selected in grouping 1 will not repeat and a new value "None" is added.
Grouping 2 - single value drop down selection from 42 possible values. The values selected in grouping 1 and grouping 2 will not repeat and a new value "None" is added. If value "None" is selected in Grouping 2, Grouping 3 will only allow "None" value.
The code works perfectly however uses temporary tables. When I create this parameter on Business View Manager, the scheduling fails with the message Error Message: Query Engine Error. File ~tmp75748d21d1d8c72.rpt.
1 Does business view manager does not support temporary tables? Can I use CTE instead and rewrite the code?
2 I added the code with temporary tables to the command on the main report and created a dynamic cascading 3 level parameter. The query returns 77K + results. I have modified the registry to increase the LOV from 1000 to 5000. In my grouping 1, instead of seeing 42 different values, it lists only 3 values. I figured from excel output that column 1 (populating grouping 1 values) from the query results lists only 3 unique values up to first 5000 records.
I thought registry edit to increase LOV from 1000 to 5000 was for business view manager and not for dynamic prompts on crystal reports. The report has not been deployed on the repository but I am connected to the repository from CR since there are other parameters that I am consuming from the repository. Do I have to edit registry again? Any other workarounds?
Sorry for a lengthy post.