1 Reply Latest reply on Sep 10, 2015 1:39 PM by erolst

    Summarizing Data in Dashboard Layout

    sccardais

      I am working on a dashboard to summarize Company Payroll information.

       

      I’d like to display a comparison of payroll by department for the current year to date versus the previous for the same period.

       

      YTD Payroll Comparison.png

      Data for this report will come from table:PAYCHECKS with the fields shown below:

       

      • fk_ID Dept
      • Name_Dept
      • Date_ Payroll
      • Amount_Pay

       

      What is the best way to approach this? e.g. Separate portals containing summary data for Current Year to Date and Previous YTD based on different TO's or an Execute SQL query or ???

        • 1. Re: Summarizing Data in Dashboard Layout
          erolst

          You could create a new table with fields for Dept and Year; after the year has concluded, summarize into this table: have a script to go to the summary table, find the set of records for that year and Replace Field Contents with

           

          Sum ( Paychecks_forSummary_byDeptAndYear::Amount_pay )

           

          or use an equivalent SQL query to save on TOs and calc fields, e.g. Year ( Date_payroll )


          then create a new set of fields for the new year.

           

          If you want to see the latest comparison, you won't have to re-calculate the previous year's figures (since they won't change anymore), only the current one's.

           

          Then your dashboard'd consist of one portal into a Cartesian relationship to Dept, and two portals into the Summary table, filtered by year and sorted by Dept.

           

          As you may have guessed, that's only one of several different approaches, but it strikes me as the neatest one; especially since you can use that Summary table for other purposes.