1 Reply Latest reply: Oct 6, 2011 7:49 AM by Marc Livingston RSS

    First document, budget vs actual

      For my first document in QV I'm making a financial report. The goal is to show the acutal spendings vs the budget for each department.

      I'm using the personal edition.

       

      The data I'm using comes from a Microsoft sql server 2005.

      I already know sql so I try to make query's that only get the data that I want to use. This is more easy for me than creating associations in QV.

       

      In my document I now have 4 tables: GLBE (budgets), GLE (acutal spending), Bedrijf (company) and Costcenter.

       

      The first thing I need to do is associate the ''BedrijfCode'' and ''CostcenterCode''.

      Renaming the fields to ''BedrijfCode'' and ''CostcenterCode'' in the GLBE and GLE table doesn't work, it creates a synthetic key.

      I tried to make a link table (Don't know if this is the right thing to do.) but I failed.

      How can I associate ''BedrijfCode'' and ''CostcenterCode'' from the GLBE and GLE table to the Bedrijf and Costcenter table?

       

       

      My second question has to do with the dates: GLBE_datum and GLE_PostingDate.

      The date in the GLBE table is on the fist day of the month for every month. The date in the GLE table is on a (few) random day(s) in the month.

      Somehow I need to tell QV that those dates are to be associated, e.g. money spend on 14-08-2006 and 16-8-2006 should fall under the budget for 1-08-2006. Is there a way I can make an association with rules?  (The dates are in the day-month-year format.)

       

       

      Tables1 is a pictue of the tables.

        • Re: First document, budget vs actual

          as far as associating dates, try formatting them to month, then linking them. IE: Month(GLBE_datum) as Month

          Month(GLE_PostingDate) as Month.

           

          To avoid synthetic keys, you can concat the data to create the link, and put this in both tables. Then use only the field from one table and rename them in the other. Then you can link the Bedrijf to GLBE by naming the fields the same, and link CostCenter to GLBE naming the fields the same.

          This of course would match your field names to work.

          BedrijfCode' & '-' & 'CostcenterCode' & '-' & Month(GLBE_datum) as mainlink

          Untitled.png