Skip to Content
avatar image
Former Member

Dynamic LOV in Command Line Query based Crystal Report

Hi All,


I am creating dynamic LOV in my Crystal report which was built in old Crystal 2008 and got migrated to Crystal Report 2013 BO 4.1 last year. This Crystal report is built with command line query and many command line parameters and report is running fine.


Now I want to create one parameter called 'Title' which should pull list of values from database. I have created one new command line query for this which has 'Title' and then created report level parameter (?Title) with Dynamic LOV. I have also selected this Title field in record selection expert. But my report is not showing LOV. It creates only static parameter wherein I need to manually type Title name. But I want to see list of values so that I can select Title from there.


Is there any way to do that? I have not created any ODBC connection to connect to database. I am directly connected to database using Crystal Database expert.


Any help is highly appreciated.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Mar 20, 2017 at 09:44 AM

    Hi Remi,

    Please try this:

    1. Go to the Database Expert > Expand existing connection name > Double click 'Add Command' > type in the following SQL query:

    Select distinct table.Title from table

    2. Click OK and go to the Links tab. Delete any Joins that CR automatically adds to the other Command tables.

    3. Ignore the warning and click OK. Expand Field Explorer > Right-click the prompt > Edit > Choose 'dynamic' for List of Values.

    Under 'Values', select the Title field from the NEW Command Object you added in Step 1.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 20, 2017 at 02:52 PM

    Is it not showing the data in Crystal or after you publish it to BO? If you'll be publishing the report to BO, best practice will be to create the parameter in the Business View Manager. This will make it so that:

    1. You don't have to "update the repository" when you publish the report to BO.

    2. You can set the credentials for the connection the dynamic parameter uses before you publish the report to BO.

    3. You can reuse the connection for other parameters instead of having a bloated set of duplicated connections all going to the same database, each used for a different parameter.

    4. Multiple reports can use the same dynamic parameter definition, again preventing duplication.

    Also, see this blog post for more information about using commands: https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/

    -Dell

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 22, 2017 at 03:21 PM

    thanks Dell and abhilash.

    I have created project title parameter at command line but it's LOV is not in sorted way but LOV is sorted by project id. Is there any way, i can make project title sorted alphabetically at command line itself?

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 22, 2017 at 03:53 PM

    Add an "Order by" clause in the Command Object that generates the LOV.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 23, 2017 at 11:36 AM

    Please attach the report with saved data.

    I would like to see how you've setup these prompts.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Abhilash,

      Could you please reply if you got time to look into this. I just wanted to know how to sort values in LOV parameter. After putting 'order by Title' clause in query does not fetch Title values in sorted form and it is giving sorting in title id by default.

      Thanks in advance.