Parmeter value to suppres record
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.
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.
peter57r
Please post the code you currently have in your record selection rule.
SO you want to limit the list of values for the parameter.
What database are you running against?
mlmcc
What database are you running against?
mlmcc
ASKER
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.
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.
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
SELECT Field1 FROM YourTable WHERE Field Is Null
You can then base the parameter on the command
mlmcc
ASKER
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.
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.
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
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
ASKER
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,’’)<>’’
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,’’)<>’’
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot