Ad Hoc Report - Missing items

[Former Member]

Hello,

I'm working on an ad hoc report and when I select all of the items for selection an output I then filter out the selection values to generate more specific items I want to see.  One of the items I have for selection when I select certain values it excludes everyone who doesn't have that value.  How can I get the report to show all line items that have that value plus those who may not have it at all?

I have tried exluding certain values or including certain values but it seems to still only give me a few line items.  When I don't use that selection value at all everyone shows up on the report.  Some show up multiple times because they may have other items listed but I only want them to show up if they have lets say 0-2 of the 10 values I could choose from.

Any help with this would be greatly appreciated.

Elizabeth

[Former Member · Accepted solution]

Paul,

Seems like it's almost there but still won't give me the correct population.  As soon as I remove the wage type field and hit output I get everyone regardless of the subtype I select.

I don't know if you can see in this screenshot but personnel # 711227 has two lines, one for NY Supp and one for Transit.  I only wan to show the NY Supp line.  I also want the report to include those that don't have the NY Supp such as personnel # 711256.  All jobs listed as Interns & administrators don't receive the NY Supp so I still need them to show on the report.

[paul_davidson · Accepted solution]

Hi Elizabeth,

Although the output now contains the other valid payment wage types, you can filter them out.  Use the filter icon in the ALV output and enter 0001-1457 and 1459-9999 in exclude ranges.  This will remove the other wage types leaving only "blank" and "1458".

The selection now gives the correct group of people, filtering the output gives the correct wage types.

Paul

[Former Member · Accepted solution]

Paul,

That worked!  Thank you so much for your help you have made my day.

Is there a way I can connect with you?  I'd like to stay in touch.

Elizabeth

[paul_davidson]

Hi Elizabeth,

Ad Hoc Query will produce an output line for each valid record it encounters.  So if a person has two of the selected wage types that are currently valid, the person would appear twice in the report.

As to selecting people without any of the wage types, you should use Set Operations.  Activate in Ad Hoc  via Extras --> Show Set Operations.  Here you can define different groupings.  "A", "B", A+B", "A-B" and "B-A".  First, on the Selection tab, enter your selection values except for wage type.  Select Hit List and enter that number just to the right in Set A.  Then add wage type and enter your desired wage types.  Select Hit List - this will give you all people with those wage types - enter into Set B.
Then on the Set Operations tab, you can define the "A-B" or all people without the wage types selected.  You then output that list or select Transfer Resulting Set "to object set A".  Then select "Union" to give you all persons with no wage types and those with your selected wage types.  Transfer that to "reporting set" and "to Hit List".  Return to the first tab to run your output.

Paul

[Former Member]

Hi Paul,

Your information is very helpful, I never learned "Set Operations" but it seems to be exactly what I need.

I got the results I needed which is 30 - 13 = 17 but when I select output nothing happens.  It looks like it's loading but then just stops.  At the bottom of the screen it says "no data was selected" but it shows my hit list has 17 persons.

[paul_davidson]

Hi Elizabeth,

Glad I could help.  Set Operations is a great way to find "negatives" - those who do not have a certain infotype or entry.

I can't see your Ad Hoc Query screen, but you probably still have the selection criteria for your wage types showing and these 17 do not have that, so no output.  Once you put the 17 both in the Reporting Set and the Hit List from the Set Operations tab, you can delete the selections as only these 17 will be in your output. 

Paul

[Former Member]

Paul,

That helped to get the output.

I selected specific subtypes within Wage type but my report still shows all of the wage types that each individual employee has. 

For example I get the following:

Employee Name     Wage Type     Wage Type Text

J. Smith                    1458               NY Supp

J. Smith                    2296               Transit

J. Smith                    2049               Add. Transit

E. Smith              

L. Smith                    1458               NY Supp

A. Smith                   

P. Smith                   

P. Smith                   2296               Transit

How do I show just those that have "blank" wage type and wage type 1458.  I tried just selecting the subtype in wage type 1458 and excluded 2296, 2049, etc. The output shows the exact number of people on the report but they have multiple line items because it shows all of the wage types the employee has vs. just the blank and the one for 1458.

Elizabeth

[paul_davidson]

Hi Elizabeth,

Let's think about this a bit.  First, in Set Operations you identified those with Wage Type 1458 and those with no wage type.  You can add A+B and put that list into the Reporting Set and Hit List. 

Now for the output - you want only the field for wage type filled if it is blank or 1458.  If you remove wage type from the selection field you are getting all wage types.  You just want "blank" or "1448", so we want to leave the wage type field in the selection area, but use the "Exclude Ranges" tab to exclude "0001-1457" and "1459-9999".  Thus, we would only have valid records if wage type = "1458" or "blank".

Give it a try.

Paul

[Former Member]

Which is the selection item in question?

[Former Member]

Hi Tania,

The selection item is called Wage Type.  It may be specific to my organization but it includes items from an employee's pay such as: benefit deductions, 401k deductions, additional payments like an auto allowance etc.