Print Page | Close Window

Returning results based on parameters

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2016
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22649
Printed Date: 20 Oct 2018 at 5:16pm


Topic: Returning results based on parameters
Posted By: shabbaranks
Subject: Returning results based on parameters
Date Posted: 24 Jul 2018 at 5:23am
Hi,
Im just getting back into writing Crystal Reports after probably 5 years plus. I cant remember how you create a report based on a parameter? So I have created a parameter which points to a company name but the report returns all values instead of just those relating tot he company entered in the parameter.

Any help would be appreciated - thanks :)



Replies:
Posted By: kevlray
Date Posted: 25 Jul 2018 at 4:24am
In the select expert.  Did you filter a field on that parameter?


Posted By: shabbaranks
Date Posted: 25 Jul 2018 at 4:27am
Hi Kevlray,

I managed to overcome this (the problem was due to my unfamiliarity) I remember in Crystal Reports 2008 you had a select expert, in this version you use interactive filtering which looks like the same thing :) Thanks!!


Posted By: kevlray
Date Posted: 25 Jul 2018 at 9:49am
What version of Crystal Reports are you using?  I know that Crystal Reports for Enterprise has interactive filtering, but that is a bit different than the 'regular' filtering.


Posted By: shabbaranks
Date Posted: 25 Jul 2018 at 10:18am
Its Crystal Reporting built into SAP Business which is what your referring to by the sounds of it. I couldn't find the select expert filtering, I don't suppose you know where it is?


Posted By: kevlray
Date Posted: 25 Jul 2018 at 12:55pm
The main filtering is done in Edit Query screen in Crystal Reports for Enterprise.  In the Query Filters section.  I wish I could attach a screen shot.


Posted By: shabbaranks
Date Posted: 25 Jul 2018 at 10:00pm
I found it (I think)its just called record filter?
One thing I am struggling with, it would seem the query needs to be in uppercase to work. I have converted the SQL view to uppercase but this has no impact on the parameter? If I use uppercase I get results and if I use lowercase it returns nothing?


Posted By: kevlray
Date Posted: 26 Jul 2018 at 4:14am
I have never heard of that issue.  I know that databases can be case aware, but that only applies to the data.


Posted By: shabbaranks
Date Posted: 26 Jul 2018 at 5:42am
Well - I'm making progress. I found that if I manually create a parameter using the formula process I can get results which work. Below is my code:

IF {?Select Supplier} = "Select All"
THEN ({SS_Supplier_View.Company Name}) like '*'
ELSE ({SS_Supplier_View.Company Name}) like "*" & uppercase({?Select Supplier}) & "*"
AND
{@RecordDate} >= {?Date From} and {@RecordDate} <= {?Date To}

But it seems to ignore the from and to criteria if you leave the search as "Select All"


Posted By: kevlray
Date Posted: 26 Jul 2018 at 6:44am
I would wrap everything in the Else clause in parenthesis.  Crystal is bad about ignoring multiple statement in a if else statement

ELSE (({SS_Supplier_View.Company Name}) like "*" & uppercase({?Select Supplier}) & "*"
AND
{@RecordDate} >= {?Date From} and {@RecordDate} <= {?Date To})


Posted By: shabbaranks
Date Posted: 26 Jul 2018 at 10:00am
Annoyingly its still retuning all date values even if with the parenthesis :(


Posted By: DBlank
Date Posted: 26 Jul 2018 at 12:05pm
maybe

({?Select Supplier} = "Select All"
OR
{SS_Supplier_View.Company Name} like "*" & {?Select Supplier} & "*")
AND
{@RecordDate} in {?Date From} to {?Date To}


Posted By: shabbaranks
Date Posted: 26 Jul 2018 at 9:20pm
Seems to have done the trick I think :) Thanks!!

I always get paranoid creating reports as you never can be 100% sure the correct data is there. This was what I went with in the end to catch the uppercase:

(uppercase({?Select Supplier}) = "All"
OR
{SS_Supplier_View.Company Name} like "*" & uppercase({?Select Supplier}) & "*")
AND
{@RecordDate} in {?Date From} to {?Date To}


Posted By: DBlank
Date Posted: 27 Jul 2018 at 5:14am
the case should not be an issue but if you are worried about it you should match it entirely. You are applying the UPPER() function to the parameter but not to your data and the way you have set all you are definitively making the case not match on the 'all' option.
uppercase({?Select Supplier}) = "All" should be
uppercase({?Select Supplier}) = "ALL"

Just a heads up on it.



Print Page | Close Window