Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Parmeter value to suppres record

Posted on 2013-05-24
9
Medium Priority
?
214 Views
Last Modified: 2013-06-25
In CR XI R2 I have next fields:

Field1      Field2      Field3      Field4
John            AAA      2013/01/01
Eric      1A      BBB      2013/01/14
Peter      2B      CCC      2013/02/11
Susan            DDD      2013/02/20
Don      1B      FFF      2013/02/27
Simon      1A      KKK      2013/03/01


In report I  have parameters against next filed filed1 Par1, on filed3 Par3, on Fied4 Par4.

What I need is parameterX that will show me only names from Field1 that have  values in Field2.
Then, when user select one of those names in parameterX that record will be suppressed where paramterX value or values match value in filed1.

It means that parameter with option for multiple selection will show list as :
 
1.Eric
2.Peter
3.Don
4.Simon
Any idea how to do this.
0
Comment
Question by:Taras
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 39195074
Please post the code you currently have in your record selection rule.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 39195079
SO you want to limit the list of values for the parameter.

What database are you running against?

mlmcc
0
 

Author Comment

by:Taras
ID: 39195234
Database is SQL Server and is used for Raiser’ Edge application that report is in, however I am working on the report outside that environment.
What they do, they are making export database file in MS access mdb format.
Then I am using this database file to create report.
After I finish they upload report back and reconnect with SQL Server database, It means I have to use what mdb file provide.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 101

Expert Comment

by:mlmcc
ID: 39195250
You should be able to create a command against the database

SELECT Field1 FROM YourTable WHERE Field Is Null

You can then base the parameter on the command

mlmcc
0
 

Author Comment

by:Taras
ID: 39195322
Command. Did  you mean?

Something as:
SELECT Field1 FROM YourTable1
WHERE YourTable1.JoinField = YourTable2.JoinField
And Not (Is Null(YourTable2.Field2)

Not sure about sintax

Mdb file do not support command.
Does it mean, there is no way around, and it is not possible to be done somehow in report when data are already in.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 39195367
What version of Crystal?  CR XI and CR2008 allow commands with an MDB data source

SO field1 and field2 are in separate tables?

If you have to join tables I would put the filter in the join (ON) clause.

SELECT Field1 FROM YourTable1 INNER JOIN YourTable2
ON  YourTable1.JoinField = YourTable2.JoinField
And Not (Is Null(YourTable2.Field2)

mlmcc
0
 

Author Comment

by:Taras
ID: 39195556
I apologize I checked report data source in report. HereI have three tables that are involved with those fields.

Table1
Table2
Table3
Data for parameter will be from table2
I will put it in SQL not sure how it should go in access,help.

Select
b.Field1
From table1 a
Left Outer Join table2 b
On a.JoinFieldM = b.JoinFieldM
Left Outer Join table3 c
On a.JoinFieldN = c.JoinFieldN
Where IsNull( c.field2,’’)<>’’
0
 
LVL 14

Accepted Solution

by:
LinInDenver earned 1500 total points
ID: 39205420
I would build ParameterX as a string with Yes or No choices. The question it asks is "do you want to include records only with Field2 populated?"

In your Record Selection,

(
IF {?ParameterX} = 'Yes' then length(trim({table.field2})) >0
else 1=1
)
0
 

Author Closing Comment

by:Taras
ID: 39275571
Thanks a lot
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

722 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