Skip to Content
author's profile photo Former Member
0
Former Member

Ad Hoc Report - Missing items

Posted on Aug 05, 2016 at 07:40 PM 128 Views

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Aug 09, 2016 at 07:44 PM
    2

    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.


    Screenshot.jpg (584.9 kB)
    Add a comment
    10|10000 characters needed characters exceeded

    Comment on This Answer
  • author's profile photo Paul Davidson
    Paul Davidson
    Posted on Aug 08, 2016 at 02:57 PM
    3

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Paul Davidson Former Member
      Aug 09, 2016 at 05:49 PM

      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

    Comment on This Answer
  • author's profile photo Former Member
    Former Member
    Posted on Aug 08, 2016 at 05:29 AM
    0

    Which is the selection item in question?

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member
      Aug 08, 2016 at 11:54 AM

      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.

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.