2 Replies Latest reply on Jul 29, 2015 5:38 PM by pbedouk

    Cross Tab Guidance

    pbedouk

      Title

      Cross Tab Guidance

      Post

      Hi ... I wonder if someone would point me in the right direction for creating a cross tab style report for the following scenario:

      I have a single table with personal banking transactions. Each transaction (ie record) has a description, date, category and amount (among other things). For example I have categories like car-gas, car-insurance, car-maintenance, groceries, utilities, gifts, cash, house-insurance, house-taxes, house-upkeep ... etc etc.

      I'd like to create a report which has months across the top (plus total and monthly average) and categories down the side. The months across the top would be either year-to-date (Jan thru Dec, current year) or the last 12 months. The categories down the side ideally would have the sub-categories too (see car and house example above) with a row showing totals by month for the category. 

      How would I go about this?

      Here's a mockup of what I have in mind:

       

      Many thanks...

       

      Cross_Tab_Example.png

        • 1. Re: Cross Tab Guidance
          philmodjunk

          Do you have a table with Category in one field and sub-Category in the second--with only one record for each unique pairing of Category and Sub Category?

          If not, creating one would be helpful as you could set up a list view layout with Category values in a Sub Summary layout and the sub categories in the body. Portals to your transaction tables, but that filter by month/year could be set up to get your monthly sub totals as well as your totals and averages.

          • 2. Re: Cross Tab Guidance
            pbedouk

            Hi ... at the moment there is only 1 table with each row being a transaction record,  there is only 1 category field in each record, but that is easily split into 2 fields.  

            I'm not clear about what you mean by "with only one record for each unique pairing of Category and Sub Category?" ... 

            Are you suggesting tables like this:   Category ---< subcategory ---< transactions   ?

            Thanks for yor help!