Report Design
 Crystal Reports Forum : Crystal Reports 9, X, XI, 2008, 2011 : Report Design
Message Icon Topic: report for my boss! help plz Post Reply Post New Topic
Author Message
katie1200
Newbie
Newbie


Joined: 29 May 2009
Online Status: Offline
Posts: 23
Quote katie1200 Replybullet Topic: report for my boss! help plz
    Posted: 03 Jun 2009 at 6:42am
hi all
 
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.
 
IP IP Logged
DBlank
Senior Member
Senior Member


Joined: 19 Dec 2008
Online Status: Offline
Posts: 7843
Quote DBlank Replybullet Posted: 03 Jun 2009 at 6:47am
Use parameters.
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}
IP IP Logged
katie1200
Newbie
Newbie


Joined: 29 May 2009
Online Status: Offline
Posts: 23
Quote katie1200 Replybullet Posted: 03 Jun 2009 at 7:26am
thanks DBlank
 
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
 
 
IP IP Logged
DBlank
Senior Member
Senior Member


Joined: 19 Dec 2008
Online Status: Offline
Posts: 7843
Quote DBlank Replybullet Posted: 03 Jun 2009 at 7:42am
No problems.
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.


Edited by DBlank - 03 Jun 2009 at 7:44am
IP IP Logged
katie1200
Newbie
Newbie


Joined: 29 May 2009
Online Status: Offline
Posts: 23
Quote katie1200 Replybullet Posted: 03 Jun 2009 at 7:59am
i have made them changes. the allow multiple values was one i was wondering about!
 
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
IP IP Logged
DBlank
Senior Member
Senior Member


Joined: 19 Dec 2008
Online Status: Offline
Posts: 7843
Quote DBlank Replybullet Posted: 03 Jun 2009 at 8:40am
Sorry for not being clear.
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...
{table.Datefield} in {?BeginDate} to {?EndDate}
and {table.product_description_field}={?Product Selection}
 
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.
IP IP Logged
DBlank
Senior Member
Senior Member


Joined: 19 Dec 2008
Online Status: Offline
Posts: 7843
Quote DBlank Replybullet Posted: 03 Jun 2009 at 8:49am
Also you may have done the "i have done this using the filter fields and select the "between to" in dates and the "is one of" for product." via the report designer wizard. I forgot about the wizard as I generally do not use it.
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.
IP IP Logged
katie1200
Newbie
Newbie


Joined: 29 May 2009
Online Status: Offline
Posts: 23
Quote katie1200 Replybullet Posted: 04 Jun 2009 at 3:01am
thanks so much for that detailed description your very good. Im just back in work now and i will try it.
 
Katie.
IP IP Logged
katie1200
Newbie
Newbie


Joined: 29 May 2009
Online Status: Offline
Posts: 23
Quote katie1200 Replybullet Posted: 16 Jun 2009 at 4:13am

hi again

i was away on hols hence the late reply.

i have gotten the date prompts working but i am having problems with the product selection.

i have followed DBlanks second post exactly and the database is set up like that, one numeric field and one descriptive field.

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.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.061 seconds.