Solved

Crystall_ReportQuery

Posted on 2011-02-17
19
605 Views
Last Modified: 2012-05-11

I have a crystal report that selects data from a view (Query) in oracle database.

I have define a dynamic parameter (p_stock_no) and it looks up all the stock numbers from the DB.

I have added a selection formula {VW_STOCK_USAGE.STOCK_NO} = {?p_stock_no)

Is it possible to do the following:

1) Add a "*All Stock Items*" option to the dynamic list so when user selects that it will report on all stock items.

2) Have user type a wild card search himself so when he enters TOY% in the search field the report will lookup all stock numbers that start with TOY%.

I noticed with static parameter you can get a value entry field but not dynamic.


Also, is it better always to display stock description along the stock number in the dynamic menu or just display stock numbers.
0
Comment
Question by:sam15
  • 7
  • 6
  • 5
  • +1
19 Comments
 
LVL 8

Expert Comment

by:kingjely
ID: 34923212
I use static parameters in the same way you do, with views ect in add command.
and use the wildcard *, and also 'Starts With' in rtecord selection to append to the {?parameter}

So you can type in a word, or any thing begining with 'T"

Why do you need dynamic parameters?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34923244
I don't think it is possible.  Can you add a value to the VW_STOCK_USAGE table that could be used as the ALL value?

mlmcc
0
 

Author Comment

by:sam15
ID: 34923359
With Static parameters every time they add a new stock you have to add it manually to the report unless they type it in. Basically it wont show in the dropdown menu.

I can create a lookup table for stock number and *ALL* to that and base the dynamic field on it. Not sure how the CR record selection formula will be . I guess something like this
if (p_stock_no} = "ALL" do not filter
else
(p_stock_no} = {?p_stock_number}
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 34923458
2 ways

{?p_stock_number} = "ALL"
OR
{?p_stock_number} = {p_stock_no}


If {?p_stock_number} = "ALL"  then
    TRUE
Else
   {?p_stock_number} = {p_stock_no}


mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34923461
If you are going to build the table or add ALL to the table givve  it  a value so it collates to the top of the list like _ALL_

mlmcc
0
 

Author Comment

by:sam15
ID: 34923484
That shoud work for dynamic list. What about the wild card search. Cant i have dynamic field and let user enter his own criteria too if he desires like a static list.
0
 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 250 total points
ID: 34923869
Dynamic parameters don't allow you to append values to the pick list. Since a Dynamic parameter is based on a data source, you only get what's listed in the source.  Additionally, you cannot set a default value for a dynamic parameter, nor can an end user enter a custom value.

So, how do you account for * or "All"?  You have to modify the underlying data source to accommodate it.  Here's an example of a simple SQL Command that demonstrates the technique:

SELECT DISTINCT
  '*All Products' AS PROD_NAME
FROM
  Product
UNION ALL
SELECT DISTINCT
  [Product Name] AS PROD_NAME
FROM
  Product

If you base your Dynamic parameter off a simple command like this you'll have the ability to list both the "All" value and the actual database values.  You would then account for this in your report's record selection criteria like follows:

'{?ProductName} = 'All' OR {table.productname} = {?ProductName}'

If there's any possibility of your table having NULL values for the field you're trying to query ({table.productname} in my example) then I'd recommend you change the syntax of the selection criteria to:

(
If
  {?ProductName} <> 'All'
Then
  {table.productname} = {?ProductName}
Else If
  {?ProductName} = 'All'
Then
  True
)

~Kurt

p.s.  here's a sample report (Xtreme Sample DB 11.5) that uses this concept https://www.box.net/shared/vgrjqjn5m4 . If you need to download the Xtreme Sample Database,it can be found here:

https://smpdl.sap-ag.de/~sapidp/012002523100005852352008E/cr_xi_xtreme_rep_smpl_en.zip

0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 34923874
Pasted from two different examples - obviously, you'd want your 'All' value in your command to be the same as you use in your record selection criteria.  In my examples above, the values are different, but the concept should be clear.

~Kurt
0
 

Author Comment

by:sam15
ID: 34926661
I am in catch 22 situation.

Dynamic parameter is nice because everytime they add a new part to database it shows up in the list.
With static list i have to refresh the report and publish it again. It can get tedious.

But static parameters are more fleixible in that they allow user to use wild card searches. based on above it does nto seem dynamic parameter support this.

Am i correct here?

Any ideas on the best thing to do. If the dynamic list is like USA States then it does not change and static would be no problem.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 34926694
As stated above, Dynamic parameters are based on whatever values are in the data source the  parameter queries. So, if you want an explicit value that stands for "All" then you have to add that record to the data source.  That's what the sample command does.

~Kurt
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34926727
What types of wild cards are they using?

How are you running the reports?
If it is a home-grown application you could add your own parameter screen and get the ability to do what you want.

mlmcc
0
 

Author Comment

by:sam15
ID: 34926788
yes, i can add separate data source with *All* option for dynamic parameter. But i was confirming that if  I use a dynamic parameter I wont be able to search by "TX01*" to get all parts that start with TX01. right?

This is oracle pl/sql web HTML application . The report is defined in Crystal. If i use an HTML for to capture parameters then I have to append it to the Crystal report link. It can be done but it requries redesign and some work. I am using Crystal parameter form now.
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 34926879
If you use a dynamic parameter then your user CANNOT enter custom field values. So, unless you want to customize your data source to have explicit values that equal "TX01*", then no.  If your actual data value is "TX012345565" then that's what would normally show up in the Dynamic parameter list.

Then again, if you're going to let your users do a wildcard search against values, why do you want a dynamic parameter anyway?

~Kurt
0
 

Author Comment

by:sam15
ID: 34927079
I am trying to give them both options. Right now, I have a static list that shows all stock numbers with another field below for any value they key in. Whenever a new stock number is added it does not show in the list unless i add it manually to the report which is tedious.

is there a standard or best practice for choosing what type of parameter to use?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34927279
What versioin of Crystal do you have?
Obviously it is either CR XI or CR2008

If it is CR2008 you have the ability to use OPTIONAL parameters.
You could add a dynamic and static to do the same thing and make them optional.  That way the user could enter the criteria using either one

The select criteria becomes
(
If HasValue({?DynamicParam}) then
   {MyField} = {?DynamicParam}
Else
    TRUE
)
AND
If HasValue({?StaticParam}) then
   {MyField} LIKE {?StaticParam}
Else
    TRUE
)

With that they can only enter one or the other.  It might be possible to handle the using of both.

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34927377
A far as a standard, there really isn't an official one that I know about.

If I were to use the Crystal prompting window I would consider how difficult it is to enter specific values or to even just remember them.  Given your scenario where they seem to be long strings like TX012345565 and there are probably many that start with TX01... I would use dynamic if the user generally selected a few individual ones.  If the user tended to choose TX01*, XT03* then I might use static since it allows them to do what you are trying to do.

Where I have been involved in the project beyond just the report wrinting, I generally provided my own parameter screen thus I had full control over what was presented.  I had "dynamic" selection lists long before Crystal added the capability.  It does come with a price in that the application is now a bit more complex.

I have been on several projects where by definition all parameters were static because the application developers didn't want to deal with dynamic parameters and handled the parameters in the application.

mlmcc
0
 

Author Comment

by:sam15
ID: 34929230
I use CR XI Developer on the client. The server is Business objects XI.
I did not see a place to make the parameter optional unless I missed it.

On static, i see an option "Allow custom values" but on dynamic It does not show up. I see "Allow range values" though>
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 34929556
There are no native optional parameters in your version of Crystal Reports.  You can only make them optional in the type of example I provided with my sample reports.

~Kurt
0
 

Author Closing Comment

by:sam15
ID: 34935202
Excellent!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now