Dynamic dropdown parameter lists 1

[DairyAire]

We have quite a few dropdown lists in our parameters that we would like to have built dynamically as the data changes occasionally.  We've been told that we have to go in and manually make it update and then save the report again each time the data changes.  Is there any easy way to get these to change dynamically?  Thanks!

 

[MalcolmW]

This isn't possible in SCR (any version), although calling a report from Seagate Info will do this.&nbsp;&nbsp;It would be nice to have this as a feature though... <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>

 

[MalcolmW]

I stumbled on something today that made me realize it was possible to do this - here is some code for v7, it is different for version 8.<br><br>Stored Procedure Parameters and Crystal Parameters are both set through the ParameterFieldDefinition Object. The following sample code passes four parameters to a report. The Main Report and the Subreport have a Stored Procedure and a Crystal Parameter . <br><br>Private Sub Command1_Click()<br>Dim crpParamDefs As CRAXDRT.ParameterFieldDefinitions<br>Dim crpParamDef As CRAXDRT.ParameterFieldDefinition<br>Dim crpSubreport As CRAXDRT.Report<br>Set crpParamDefs = Report.ParameterFields<br><br>For Each crpParamDef In crpParamDefs<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;With crpParamDef<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Select Case .ParameterFieldName<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'This is the Crystal Parameter<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case &quot;MainParam&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.SetCurrentValue &quot;Main Report Parameter&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'This is the Stored Procedure Parameter<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case &quot;[CustomerID]&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.SetCurrentValue &quot;Alfki&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End Select<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End With<br>Next<br><br>Set crpSubreport = Report.OpenSubreport(&quot;sub1&quot<br>Set crpParamDefs = crpSubreport.ParameterFields<br>For Each crpParamDef In crpParamDefs<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;With crpParamDef<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MsgBox .ParameterFieldName<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Select Case .ParameterFieldName<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'This is the Crystal Parameter for the subreport<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case &quot;SubParam&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.SetCurrentValue &quot;Subreport Parameter&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'This is the Stored Procedure Parameter for the Subreport<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case &quot;[CustomerID]&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.SetCurrentValue &quot;Anton&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End Select<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End With<br>Next<br><br>Report.EnableParameterPrompting = False<br>CRViewer1.ReportSource = Report<br>CRViewer1.ViewReport<br>End Sub<br><br> <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>

 

[DairyAire]

Forgive me if I'm not understanding, but it seems like this code just sets the parameter to a specific value.&nbsp;&nbsp;I know one of the guys here does that already for some of the reports.&nbsp;&nbsp;What we need is to have Crystal dynamically build the list of values for the parameter when it is a dropdown list parameter.&nbsp;&nbsp;For example, if the parameter is allowing them to choose a list of activity types, we want the user to always see the up-to-date list of activity types from the domain table in the database.&nbsp;&nbsp;Right now if we refresh the report structure, it will store the current list in the report.&nbsp;&nbsp;If the users then add two more activity types later, that report will not show those two activity types as options in that parameter without us manually going in and updating that parameter again.&nbsp;&nbsp;Am I explaining this properly?

 

[MalcolmW]

Sorry for the confusion.<br>The parameter list that CR stores with the report is static, so I know of only two ways to have a dynamic list.<br>1) Use Crystal Info to call the report, and use the parameter list in Crystal Info (which can be dynamic) to pass values to the report parameters.<br>2) Get a list of values for a parameter with your own code, get your user to choose a value from that list, and then pass it to CR.&nbsp;&nbsp;I'm not sure whether you can set values for the parameter list in v7, but the ability to do this has limited value, as you can write your own code for the user to choose the value desired from the select statement that you must write in any case.<br>Neither v7 or v8 allow you to simply refresh the parameter list, which is what any reasonable person (such as yourself) would want. <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>

 

[yenyen]

How do you use the parameter field on a text data type column? Can that be done in CR 8.5?