Creating Parameters in SQL Command

[Former Member]

I've created a report via the Add Command option rather than pulling directly from the tables and that works great.  However, I need to create a dynamic parameter for user to select records from the many thousands of records on the report.

I created a parameter in the Parameter area of the Add Command screen & integrated that in the WHERE clause of my SQL commands and the parameter seems to work okay.

However, I just discover that the parameter was "STATIC" instead of dynamic as I intended it to be.  Is it possible to make it dynamic?  Any help would be greatly appreciated.

Thank you.

[abhilash_kumar · Accepted solution]

Hi Fione,

1) Create a new command object and type in the SQL that would return just the List of Values. E.g:

Select distinct table.field from table

2) DO NOT Join this command object with the other command object

3) Ignore the warning.

4) Edit the prompt from the Field Explorer > Set it to 'Dynamic' > From the 'Value' options choose the field from the Command Object you created in Step 1

-Abhilash

[Former Member · Accepted solution]

Thank you, Abhilash, for your prompt response.

I'm completely new to SQL Command lines; all this time I've been using tables to create my Crystal reports.  So bear with me on my ignorance.

I'm not sure I understand Step 2 in your reply:  DO NOT join this command object with other command object.  How do I do that?  Can you please provide some guidance on this?

The report that I created on my original post was pulling data from 3 different tables so my FROM clause has 2 joins.  Do you mean this join here?

[abhilash_kumar · Accepted solution]

OK. So the report right now has ONE command object, correct?

You'd create another Command Object based on Step 1.

If you've created reports directly against multiple tables, you should have seen the 'links' tab that lets you JOIN the tables you've added.

Just like that, when you add the second command object a links tab shows up where CR tries to setup Joins. You'd need to delete any Joins going from this Command Object or coming into it.

Hope that makes sense.

-Abhilash

[Former Member · Accepted solution]

Got it, thanks for the clear instruction.

Now that I've gotten through Step 3, I'm not sure I understand Step 4.  Which prompt am I supposed to edit?  Please advice.  Thank you.

[abhilash_kumar · Accepted solution]

The parameter you created in the 'Parameter' area of the Command Object should appear in the 'Field Explorer > Parameters.

Edit this parameter.

-Abhilash

[Former Member · Accepted solution]

Can I clarify something with you before I proceed to Step 4?

Do I create my parameter in my original Command object or in the newly created Command object in Step 1 of your instructions above?  Please advice.  Many thanks to you.

[abhilash_kumar · Accepted solution]

Let's re-visit.

1) You have a report with a Command Object as described in your original post. This command sql also has a prompt created in the command object and added to its where clause.

2) You need dynamic LOVs. So, you'll need to create another command object to return the list of values for the dynamic prompt. This would be a simple select distinct query with no prompt as the prompt is already created in the first command object. And, remember, do not join this command object with the first command.

3) The prompt you created in the first Command should show up under the Field Explorer on the report.

4) Edit this prompt > set it to dynamic > and point its 'Value' option to the field from the second Command Object.

In Short, the prompt is sourced from the first Command which is the Main SQL with the where clause.

The second command object only loads the list of values for this prompt.

-Abhilash

[Former Member · Accepted solution]

Thank you for the detailed explanation.  However, after doing all that you've instructed, I got a text box when I refresh the report to prompt for a parameter instead of a drop down list.  That means my report is still "STATIC", correct?

I went back and check my parameter field under Field Explorer and the "List of Values" still said "Dynamic".  So I'm not sure why I'm getting a text box for user input instead of a drop down list.  How can I be sure that my parameter is "Dynamic"?  The database that I'm querying from is changing every hour so I cannot have this report be Static.  Please advice.  Thank you

[abhilash_kumar · Accepted solution]

Could you attach the report (or send it to my e-mail)?

-Abhilash

[DellSC · Accepted solution]

Also, for more information about working with commands, see this blog post: 

-Dell

[Former Member · Accepted solution]

Please check your email.  Thanks.

[abhilash_kumar · Accepted solution]

Hi Fione,

The second command object, which is the source for the list of value, should be:

SELECT Distinct POLINE."PO_NUMBER" PO#

FROM POLINE POLINE

LEFT OUTER JOIN MMDIST MMDIST

ON ((POLINE."PO_NUMBER" = MMDIST."DOC_NUMBER") AND

         (POLINE."PO_CODE" = MMDIST."PO_CODE")) AND (POLINE."LINE_NBR" = MMDIST."LINE_NBR")        

WHERE POLINE."PO_CODE" = 'CAP' AND

             MMDIST."DOC_TYPE" = 'PT' AND

             MMDIST."SYSTEM_CD"='PO' AND

The dynamic prompt's 'Value' should then point to this PO Number field and Not the PO Number field in the First Command Object.

I aliased the PO_NUMBER field as PO# to easliy identify it.

-Abhilash

[Former Member · Accepted solution]

OMG, it worked!

I wouldn't have done it without your guidance, Abhilash.  You're a big life saver.

Thank you very much for all your help.