SAP Knowledge Base Article - Public

1202074 - How to create a dynamic parameter displaying only the values starting with the first letter of a field in Crystal Reports?

Symptom

  • First parameter prompt you to type a letter, and the second prompt shows a dynamic list of values that starts with that letter.
      
  • 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?
      
  • 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 we create this type of parameter in Crystal Reports?

Environment

  • SAP Crystal Reports 2008
  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016

Resolution

  • There is no option in Crystal Reports to directly create this type of dynamic cascading prompt, but it is possible to create it using the Business View Manager.
          
    1. In the Business View Manager, create the following objects, based on the data source and tables you need for the parameter, if you do not already have it created.
      • Data Connection
      • Data Foundation
      • Business Element
      • Business View
            
    2. In the Data Foundation, add a SQL Expression Field for the database field you want to create a parameter on.
          
      1. Edit the Data Foundation.  
      2. 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.
              
    3. Add the SQL Expression Field to the Business Element.
      1. Edit the Business Element. 
      2. In the Object Explorer, right-click on Fields, then click on Insert Business Fields. 
      3. Choose the SQL Expression you just created, click Add, then click Close. 
      4. Then save the Business Element.
               
    4. Create a new List of Values based on the Business View that contains the new Expression Field.
      1. Create a new List of Values
      2. Choose the appropriate Business View.  Name the List of Values. 
      3. 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. 
      4. Then select and move the data field (e.g. "Customer Name"). 
      5. Click OK to create and save the List of Values.
             
    5. In Crystal Reports, add the new List of Values to the report parameter.
      1. In Crystal Reports, open the report in which you want to create the parameter. 
      2. In the Field Explorer, right-click on Parameter Fields and click New.... 
      3. 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". 
      4. Click OK to save the new parameter.
             
    6. In Crystal Reports, add the parameter to the Record Selection Formula.
      1. 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.

Keywords

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

Product

Crystal Reports 2008 V0 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016