7 Replies Latest reply on Oct 23, 2009 7:31 AM by JJJ

    CrossTab - Is it possible for each week in a month? - report

    Apprentice

      Hy;

       

      Consider October month

       

      it has 5 weeks

       

      week 1: 1 to 4

      week 2: 5 to 11

      week 3: 12 to 18

      week 4: 19 to 25

      week 5: 26 to 31

       

      Say for one support group which contains of 29 analysts i need report

       

                      week 1     week2     week3     week4     week5

      User 1     CNT          CNT          CNT          CNT     CNT

      User 2     CNT          CNT          CNT          CNT     CNT

       

       

      CNT = count of the incidents created by him/her in that week

       

      Is this possible?

       

      Now what I have did is - I created a cross tab

      row: tps_title (from tps_use)

      column: pm_creation_date (from pm_process)

      value: count(incident) (from im_incident)

       

      which is showing the data for all the dates available in the database, and for all the users.

       

      So I added one Select filter on the group to filter only those of a specific group.

       

      Here Data changed.

       

      Now I need to modify the dates and bring it to the particular month which is october -  for this I will create one more filter for getting the data from between dates.

       

      Here Data changed.

       

      Now How to group the data for each week? I want to see only those 5 weeks on the report, and CNT will be the count of incidents by each user for that week.

       

      Hope my explanition is clear. Please let me know if this can be done.

       

      Awaiting.

       

      /n RMudugal

        • 1. Re: CrossTab - Is it possible for each week in a month? - report
          Apprentice

          Hy;

           

          Just to addd few more things

           

          POINT#1

           

          In that Support group there are 29 users

           

          If in that week or in that month, say the user20 has not yet all created any incident/s then that user20 will not appear in the report. But I need to see this user20 with the value as 0 for his/her CNT

           

          so basically all 29 users needs to be listed and irrespective of  the incident creation and let it show the CNT if availble or 0 if not.

           


          POINT#2

           

          In the month we have overlapping weeks

           

          the report always starts from Monday to Sunday

           

          So in the October week 1 the Monday starts in the month of october, so it is half September and half october.

           

          How to fix in the report for populating in such data?

          • 2. Re: CrossTab - Is it possible for each week in a month? - report
            JJJ Employee

            Within the Crosstab, when you group by a date field it is possible to change what period of time to group by, the default is a single day.

             

            In Crosstab Expert select the field you are grouping by and press the Group Options button, this will give you the option to group the date field by Wekk, Month, Year, etc.

             

             

             

            To get all Users on a report regardless of whether they have logged an Incident, you will need to use an Outer Join on the link between pm_process and tps_user.

             

            The Left or Right setting should be the side tps_user is on, so you are selecting all values from the table on that side.  Because tps_user holds Analysts, End Users, etc, you will need to select the correct Users.

            • 3. Re: CrossTab - Is it possible for each week in a month? - report
              Apprentice

              Hi there!

               

              thank you for your reply.

               

              I checked the "Group Option" and that button enables itsself when the row value is selected and it is disabled for the column.

               

              Actually, the dates are placed on the column (horizontal data) and rows contains Analysts names (vertical data)

               

              This is the format we need.

               

              /n RMudugal

              • 4. Re: CrossTab - Is it possible for each week in a month? - report
                JJJ Employee

                There are two "Group Options" buttons, one under Row and one under Column.

                 

                Sounds like your looking at the wrong one.

                • 5. Re: CrossTab - Is it possible for each week in a month? - report
                  Apprentice

                  My mistake.

                   

                  I have seen that button after posting here.  Still the button was not enabling when i selected the column value, I deleted the Cross tab and re-added it, still not working...

                   

                  So I created new report, I can see that enabled.

                   

                  Thanks a lot for the hint. really helped.

                  • 6. Re: CrossTab - Is it possible for each week in a month? - report
                    Apprentice

                    About the all users displaying in the report irrespective of the creation of incident, showing total count or 0 (zero)...

                     

                    just changing the join type i dont think that will work...

                     

                    even I have tried writing the SQL query.. which did not work using the joings, i wrote it in different way to see the complete list which is showing as i need, but i cannot replicate this query like in the Crystal reports.

                     

                    Becuase, the entry of that Analyst/User comes in the list only if the entry is found in the DB, if not then the name is not selected using the join and grouping on them - to get the final total.

                     

                    any further thoughts?

                     

                    /n RMudugal

                     

                    (thanks lot for the inputs)

                    • 7. Re: CrossTab - Is it possible for each week in a month? - report
                      JJJ Employee

                      Attached is a very basic report showing all the Users from the tps_user table, with a count of the Incidents they have created (in this test database only SA and Email have created Incidents).

                       

                       

                      In the Table Links you can see tps_user is joined to pm_process with a LEFT OUTER JOIN to select all records from tps_user.  pm_process is joined to IM_INCIDENT with a LEFT OUTER JOIN also because pm_process is on the other side of an OUTER JOIN.  Any further tables joined to pm_process or im_incident should also be using OUTER JOIN because if a User doesn't have an Incident then it won't have a record on the additional tables.