3 Replies Latest reply on Jun 10, 2010 1:33 PM by philmodjunk

    Dynamic Report Issue

    d.justins

      Title

      Dynamic Report Issue

      Post

      Hi there.

      I'm having a small issue with a relationship filter.


      What i am trying to accomplish is a dynamic report being published when my user chooses a date. The point of the report is to sum the hours on an Attendence table based on a month. For this i have a relationship created with a userID and a calculation field. I have this already working with another instance showing the hours based on a calculation returning the current month. The problem is, i get blocked when i add global fields in the calculation.

       

      My calculation that works is this:

       

      Case( Month(Activity_Date)=Month(Get(CurrentDate)) and Year(Activity_Date)=Year(Get(CurrentDate)); 968 ; 0)

       

      The relationship instance is based off of a field holding the userID and a field holding 968. This works. Then i have a calculation on the user table summing total hours based on this relationship instance.

       

      My problem calculation is one that has global fields holding a month value instead of the current month. This is where the relationship goes sour. The calculation is returning proper results, but i get a little "Stop Sign" block on the relationship.

       

      So why would it not like my calculation then? and Is there another way around this?

        • 1. Re: Dynamic Report Issue
          philmodjunk

          Global fields and the unstored calculations you get when you include them in the expression of a calculation can only be used on the "parent" side of a relationship. I think of these as "one way" relationships as you can get to related records moving from the table with the unstored/global field to the table with stored/indexed match fields but you can't do the reverse.

           

          Here's a work around:

           

          I don't know the significance of the value 968 so you'll have to adapt the basic idea.

           

          Define a date calculation field, cMonth, as Activity_Date - Day ( Actitivity_Date ) + 1  /* returns the date for the first of the month for all dates of the same month */

           

          Now define an unstored date calculation field, cCurrentMonth, in your parent table as: Get ( CurrentDate ) - Day ( GetCurrentDate ) + 1

           

          Now a relationship like this:

          ParentRecord::cCurrentMonth = DetailRecord::cMonth

           

          will work.

          • 2. Re: Dynamic Report Issue
            d.justins

            Thanks for the reply.

             

            The issue is not getting the relationship with values in the current month, i already have that working (which is the example i have there). 968 is an arbitrary number that i needed a value for.

             

            The problem comes with the user input.

             

            Here's the whole picture:

             

            I have a table with records of Attendance of many students. Each one has a date, start time, stop time, total time and studentID

             

            What my boss wants is a report of all students for a single month of the total hours of activity in a month.

             

            I set up a manager where the users can choose a month and year and click a button to give that report.

             

            I'm using the relationship instance to filter out all dates that are not what the user has selected, and creating a calc field that sum(totaltime).

            This should give a nice list of each student with total hours for just the month and year the user selected.

             

            The calculation i'm trying to do is an unstored checking the month and year on the record to the month and year the user selected (global fields) and returning a 1 or 0. Then, if it is a 1, the relationship validates on a field on the students table, then only the correct records are allowed to be sumed in the calc field.

             

            However, this is not working with the globals, and i can't figure out another way other than to make a script that changes two fields on every attendance record (takes forever).

            • 3. Re: Dynamic Report Issue
              philmodjunk

              Apologies for mis-reading your original post.

               

              I take it you have a portal filter that isn't working?

               

              If so, please post the filter expression so we can see what you are trying to use.