i have designed a report in crystal XI that produces a sales report for particular products for last week.

i have done this using the filter fields and select the "between to" in dates and the "is one of" for product.

 

i have to put this report on the bosses computer but he wants to be able to select the dates and the products he wants to report on.

 

is there a way to set this up?

 

i have been looking at prompts but cant really figure out how to do it.

 

any help would be greatly appreciated thanks in advance.

 

Katie.

 

You will need to set up 3.

2 will be dates (begin and end) and 1 will proabably be a string (match your product field type).

Once you create the 3 parameters you use them in your select statement.

Datefield in {?BeginDate} to {?EndDate}

and product={?Product Selection}

 

i created the parameters, i think.

 

new parameter

type = date

list of values = dynamic (im assuming dynamic because there will be new valus coming in over the weeks)

choose datasource = date (date is the name of the field in the table)

 

i have done the same for the other 2 except my list of values for product type had to be static because it wouldnt let me use "number" in type when i left it as dynamic.

 

the next part i am unsure of i looked at the select expert but it will just let me refine my report and i need somethin that will pop up as such or can be clicked on to allow him to refine the report, monkey proof!

 

is this possible? or have i just not followed your previos instructions correctly? i am new to this and i have tried all day so any more help would be great thanks.

 

Katie

 

 

For your date parameters use static not dynamic. This lets your end user/boss type in dates. Easier process for dates.

 

For the product use the dynamic function. I assume you have two fields in your DB, one is the numeric value and the other is a description for that numeric value. Use the description field not the numeric one (or your boss will hate you because they will have to memorize the entire translation table). Select the DB field that uses the Description in the Value box. If your boss should always only pick one product this is fine as is. If he/she needs the option to pick more than one product change your options (at the bottom of the parameter box) for 'allow multiple values' to True.

IN your selet statement I gave you a statement to use although you have to replace it with real field and parameter names.

Once you add any parameter to your select stamement (and save and close the statement) it gives you a pop up asking for you to enter the parameter(s). Same as your boss will get which is ultimately what you wanted.

Only thing to be aware of is that different report deployment methods can ask for parameters differently so make sure to test the report on your bosses machine to make sure it works as you need it to.

 

where i am getting lost is where do i go to create this select statement?

i know this is probably straight forward but thanks again, you have been a great help and mayb i wont get fired lol

 

Katie

In your original post you stated

"i have done this using the filter fields and select the "between to" in dates and the "is one of" for product".

I assumed you had done this using the crystal select expert but you may have done this in a view that is feeding into crystal. I mention this becasue if you are using a view that pre-filters the data you need to remove those filters to give your boss the full scope of data to choose from.

OK. In Crystal there is a Select Expert. It is teh button of the hand grabbing a red ball.

Click on it.

Assuming you do not have any existing select statement at all it will open blank.

Click on New.

Select the Date field from the fields list and hit OK

Change criteria to 'Is between'.

Click on the first seelct option (this takes a little while to load as it is reading all of your data). Select the {?Begin Date} (this will be the name of the parameter you created). This will only appear here if the parameter data type matches the data type of the field you are filtering on (e.g. text to text, date to date, datetime to datetime, etc.). Seect the second option as {?Date End}.

Click OK.

Repeat the process but use the field of the product name.

Set it as "is equal to" and select the {?Product Selection}. Do not use in the list here. The Data pulled from the multiple selection process is an array that will be applied to the is equal to for each array option.

Hit OK.

Click back on the Select Expert Button.

Click on SHow Formula.

THis is where crystal used the GUI process I just walked you through to build the actual statement. It will be something like what I originally posted...

 

If you click on the formula Editor button this is where you can just write (or edit) the select statement instead of using the GUI process from above.

If you did your original "between dates" and "product is one of" statement will be showing up in the select expert formula. Delete it all before adding in the new parameter based statement. Otherwise it will have both parts of the select statement and try to apply all of them which you do not want.

Hope this helps clear it up a bit.

 

Katie.

I am getting the following error after i set up the new parameter and go into select expert to set it up, after crystal refreshes the data

 

list of values failure: failed to get values[cause of error: unknown Database Connector Error

failed to retrieve data from the database

error in file UNKNOWN:RPT

Unknown Database Connector Error].

 

error source: prompt.dll error code: 0x8004380D

 

i have tried using only one table and pulling some fields from it and it works fine but when i try to set up the "product select prompt" i am getting this

 

i set the prompt up like:

 

type = string

list of values = dynamic

choose a data source = existing

in the value box = DESC (our field for product description which is a string, well im assuming it is it has spaces and numbers and letters)

 

 

if anyone could help it would be greatly appreciated, thank you.