1202074 - Create a dynamic parameter using the first letter of a field

SAP Knowledge Base Articles - public

1202074 - Create a dynamic parameter using the first letter of a field


When creating a parameter to choose a value from a list of values, it is often helpful to break up the list; for example, using the first letter of the value.


How can you create a dynamic cascading parameter which will let you first choose the first letter, and then choose from the shorter list of only those values beginning with that letter?


Create a list of values in the Business View Manager:
  1. Create a Data Connection, Data Foundation, Business Element, and Business View using the Business View Manager, if you do not already have appropriate ones created.
  2. Edit the Data Foundation.  Create a new SQL Expression Field by right-clicking on SQL Expressions under the Object Explorer, and choosing Insert SQL Expression.
  3. In the function creation window, expand Functions, and then String.  Double-click on the function you want to use; to use the first letter, double-click on LEFT( , ).
  4. With the cursor to the left of the comma, expand Data Foundation, then expand the table with the field you want to use, then double-click the field.
  5. Move the cursor to the right of the comma, and enter 1 (to use the first letter of the field).
  6. Click the X+2 button to check for errors, then click Apply.
  7. In the Property Browser, click on the ... beside Rights and ensure that you grant rights to the appropriate users and groups.
  8. Save the Data Foundation.
  9. Open the Business Element.  In the Object Explorer, right-click on Fields, then click on Insert Business Fields.  Choose the SQL Expression you just created, click Add, then click Close.  Then save the Business Element.
  10. Create a new List of Values.  Choose the appropriate Business View.  Name the List of Values.  Under Available Fields, select first the SQL Expression field you created (e.g. "Customer First Letter") and move it over to List of Value Fields.  Then select and move the data field (e.g. "Customer Name").  Click OK to create and save the List of Values.
  11. In Crystal Reports, open the report in which you want to create the parameter.  In the Field Explorer, right-click on Parameter Fields and click New....  Choose Dynamic list of values.  Under Choose a Data Source, choose Existing, then choose the List of Values you just created.  Where necessary, click under Parameters where it says "Click to create parameter".  Click OK to save the new parameter.
  12. Edit your record selection formula in Crystal Reports to ensure the data is filtered by the field or the SQL Expression you created.

Now, when you run the report, you should have a prompt in which you first choose from the SQL Expression field (e.g. the first letter of the customer name from A to Z), and then choose from a reduced list of values showing only the data fields (e.g. customer names) that begin with that first letter.


dynamic cascading parameter dcp first letter break down breakdown subdivide sub divide choose field , 5914533 , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , How To


Crystal Reports 2008 V0 ; SAP Crystal Reports XI ; SAP Crystal Reports XI R2