7 Replies Latest reply on Jan 16, 2009 10:31 AM by sbuehler

    Creating a query with multiple criteria

    Rookie

      I am trying to create two new queries based on multiple criteria and I'm not sure how to go about it. Here is the criteria I need.

       

      I have created a query of All Incidents (open and Closed) by Category but I also need the following;

       

      Weekly view of all Incidents by Category for a specific set of Analysts. We aren't using Groups so I don't know how to pick up the correct people or specify the week variable. Essentially I need the Application/Team Manager to be able to run his own query for his group of Analysts based on the current week and another query for the Technical/Team Manager for his group based on the current week. They want to run these queries each week.

       

      Please help

       

      Thanks

        • 1. Re: Creating a query with multiple criteria
          Apprentice

          Do the managers want to see who created the ticket, or who last worked the ticket? If they want to see who created the ticket then you could use createdby.title (or whichever field you prefer) IsOneOf {prompt user}.

          If the manageres want to see who last worked the ticket, then do the same thing with lastupdatedby.title.

          The managers could then enter the analysts they want to see in the report.

           

          The analyst criteria should come after the category criteria.

          • 2. Re: Creating a query with multiple criteria
            Rookie

            The Managers want to see which Analyst resolved the ticket. Isn't there a way I can hard code the specific Analysts names so the Manager doesn't have to enter them everytime? The Apps Manager has 7 employees which I'm sure he won't want to key every week he runs the report.

            • 3. Re: Creating a query with multiple criteria
              Stu McNeill Employee

              Hi Alison,

               

              I'd strongly recommend you look at organising the users into groups, then you have the option to use the "current group" as a criteria so the query becomes more dynamic based on who runs it.

              • 4. Re: Creating a query with multiple criteria
                Rookie

                Hi Sara,

                 

                Actually the field (Is one of) works great, I just added the specific Analysts names and chose not to prompt so my query now shows all Incidents by Category only by users that I specified. Now I just need to figure out how to get the query to only pick up 1 weeks worth of data. Is there a way to promt the Managers for a date range?

                 

                P.S. We tried using Groups in the initital setup of the product but we ran into issues with it and decided that individual analysts would be best. We knew this may cause us difficulty in queries and reporting but it works great in every other way.  

                 

                 

                Thanks

                • 5. Re: Creating a query with multiple criteria
                  Stu McNeill Employee

                  Alison,

                   

                  Another option instead of using groups would be to have a dropdown on your analyst window to select who their manager is, we could then use that along with the "is current user" as the query criteria so a manager can check on all users who have them set as the manager.  If that sounds like it would be maintainable for you then I'll go into more detail on how to set up the relationship.

                  • 6. Re: Creating a query with multiple criteria
                    Rookie

                    Hi Sara,

                     

                    I figured out the date criteria.

                     

                    I chose Last.updated is within the previous 7 days and that shows them a week worth of Incidents.

                     

                    Thanks for pointing me in the right direction.

                    • 7. Re: Creating a query with multiple criteriaU
                      Apprentice

                      Use RaiseDate IsBetween {prompt user}. This should be placed before the other criteria.

                       

                      I'm curious about what issues you had with placing analysts into groups? Were there permission problems?

                       

                      Without groups, your reports will be very complicated. For instance, with this one you will need to remember to hard-code in anytime you get a new analyst. With 7 in one group, even with mild turnover, that could become a huge job once you start building up your query library.