Skip to Content
0

Dynamic LOV in Command Line Query based Crystal Report

Mar 17, 2017 at 05:45 PM

137

avatar image

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.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

Abhilash Kumar
Mar 20, 2017 at 09:44 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Dell Stinnett-Christy Mar 20, 2017 at 02:52 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Remi Parauha Mar 22, 2017 at 03:21 PM
0

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?

Share
10 |10000 characters needed characters left characters exceeded
Abhilash Kumar
Mar 22, 2017 at 03:53 PM
0

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

-Abhilash

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Hi Abhilash,

I have given Order by clause at the end of query and put like Order By Name asc but it is not working. It is not giving me LOV in sorted order. also I created Name prompt in the command line and using this prompt to run the report. and when run the report, it is not giving me LOV in sorted order.

0

Also it is giving me only 4 LOVs.

0
Abhilash Kumar
Mar 23, 2017 at 11:36 AM
0

Please attach the report with saved data.

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

-Abhilash

Show 6 Share
10 |10000 characters needed characters left characters exceeded

Hi Abhilash,

Can you pls share you email id so that I can send my report. I do not want to put it here due to some security reasons.

0

Hi Abhilash,

I thought of attaching my report here as I need answer asap as my client delivery is being impacted because of sorting of LOV issue. I tried to attach my report with saved data here but it said the below constraints-

"Your attachment must be one of: txt,xml,gif,png,jpeg,jpg,jpe. Your attachment must be 1MB or smaller."

My report with saved data is ~8.5 MB and it is in .rpt format. could you please guide me how to attach it?

0

Hi Abhilash,

Could you please reply to my questions. I am not able to attach my report with saved data here as it is in .rpt format and is big size 8.5 MB. Could you please guide me how to attach it?

0

Hi Remi,

Please attach it to your Google Drive and paste the URL here.

-Abhilash

0

Hi Abhilash,

I have given google drive link to you yesterday but not able to see my yesterday's post now. Did you get it? If not, here pasting it again. Please acknowledge once you receive it.

https://drive.google.com/file/d/0B4DMC7pIOwpqZW1GeDFZZmlQWUU/view?usp=sharing

Also, could you please let me know if we can put search button in parameter to search item from list of values in crystal so that user can search items from Launchpad, if yes, how to do it?
0

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.

0