Skip to Content
avatar image
Former Member

Show Specific Groups Based on Record Criteria

SAP 14 / 2010

I am trying to make a report that will only return the Group if its records meet some sort of criteria.

So using my data below, and if the Job requires a variable called Criteria (this variable can be static and does not have to be a parameter), how do I get my Report to only return the Approriate Groups.

Example 1

Grouping = Job

Criteria: Saw, Tape Measure.

Returned Groups:

Job 1

Job 4

Example 2

Grouping = Job

Criteria: Screw Driver, Hammer

Returned Groups:

Job 1

Job 3

Example 2

Grouping = Job

Criteria: Pliers, Wrench

Returned Groups:

Job 2

Sample Database Table Looks Like This:

JOB_NUMBER TOOLS_REQUIRED Job 1 Hammer Job 1 Saw Job 1 Tape Measure Job 1 Screw Driver Job 2 Pliers Job 2 Wrench Job 2 Hammer Job 3 Screw Driver Job 3 Tape Measure Job 3 Hammer Job 4 Pliers Job 4 Saw Job 4 Tape Measure Job 4 Hammer
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Sep 19, 2014 at 02:30 AM

    Hi,

    So you have a group on job and want to filter the groups based on values of field TOOLS_REQUIRED, is it?

    What will be your criteria or static option for this (apart from it being a parameter)?

    I suppose this has to be done via a report level parameter with "Allow Maulti Values" set to true.

    Do you have a parameter for filtering TOOLS_REQUIRED column values?

    If so, (hoping that parameter an multi value parameter), create a record selection by navigating to Reports --> Selection Formulas --> Record and include below content:

    <TOOLS_REQUIRED> in {Your Multi value parameter}

    This will filter the groups for values included in parameter.

    Hope this is what you are looking for.

    Thanks,

    Raghavendra

    Add comment
    10|10000 characters needed characters exceeded

    • Cool.  That will make this MUCH easier!

      I think the easiest way to handle this will be the following:

      1.  Add two additional copies of the table to your report.  When you do this, Crystal will throw a warning stating that the table is already in the report and then ask if you want to "Alias" it.  It will then add the table to the report with "_x" on the report where "x" is a number.  So, if the table is named "MyTable", you'll have MyTable, MyTable_1, and MyTable_2 in the list of tables in the Database Expert.

      2.  Link from the Job Id in MyTable  to the JobID in MyTable_1 and then from MyTable to MyTable_2.  These need to be inner joins, which is the default.

      3.  Create two optional parameters that have the list of tools - I'll call these Tools1 and Tools2.  These need to be single-value parameters.

      4.  In the Select Expert, edit the formula and add something like the following:

      (not HasValue{?Tools1} or {MyTable_1.TOOLS_REQUIRED} = {?Tools1})

      AND

      (not HasValue(?Tools2} or {MyTable_2.TOOLS_REQUIRED} = {?Tools2})

      NOTE:  If either or both parameters are required, don't make them optional and take out the "not HasValue()" part of the selection criteria for that parameter.

      5.  On the Database menu, turn on "Select Distinct Records".

      6.  On your report, only use the fields from MyTable.

      The data will be filtered based on the joins.  Because the parameters are optional, they don't have to be selected in order for the report to work.

      -Dell

  • Sep 19, 2014 at 01:05 PM

    So, if I job includes the correct criteria, do you want to show all of the records for that job or do you just want to show the records that meet the criteria?

    If you just want to show the records that meet the criteria, then Raghavendra's response is all that you'll need.  If you need to show all of the records for the jobs that meet the criteria, let us know and I'll provide a solution for that.

    -Dell

    Add comment
    10|10000 characters needed characters exceeded