Show Specific Groups Based on Record Criteria

[Former Member]

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

[raghavendra_hullur · Accepted solution]

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

[Former Member · Accepted solution]

Your solution gets me very close to my Answer. I've actually gotten this far. The hold up is this.

When passing a multiparamter called ?GetToolsRequired to the Record Selection {database.TOOLS_REQUIRED} = {?GetToolsRequired} based off of any two variables/parameters, it will return the Group as long as at least One of the Criteria is met and not both/All.

So the using Mr. Hullur previous solution provides these returned results while using my Table of Values and MultiValue Paramter of Saw, Tape Measure:

Job 1 : (Because Saw and Tape Measure is met)

Job 3 : (Because Tape Measure is met)

Job 4 : (Because Saw and Tape Measure is met)

I need it so that only Job 1 and Job 4 is returned because ALL criteria is met, not just One of the criteria.

[DellSC · Accepted solution]

Is there a limit on the number of tools that can be selected?  Whether there's a limit or not, this is going to be a bit of a challenge.  If there is no limit, then you'll have to do this using a stored procedure in the database.  If there is a limit, then I can think of two possible ways to do this, which I'll outline if that's the case.

-Dell

[Former Member · Accepted solution]

There is a limit as there will never be more than two at a time.

[DellSC · Accepted solution]

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

[DellSC]

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

[ReportingMil]

Yes please https://community.sap.com/t5/technology-q-a/filter-on-multiple-criteria/qaq-p/13662938