4 Replies Latest reply on Feb 18, 2014 1:33 AM by NickLawrence

    Best way to create this?

    NickLawrence

      Title

      Best way to create this?

      Post

           Hi all,

            

           I need to convert an excel spreadsheet in to an FMPro file, (see Fig: 1 & Fig: 2) is it best to use a portal to show the expenses for the project as shown in Fig: 1, and how would I get it to summarize as in Fig: 2?

            

           Any help greatly appreciated

            

            

      Fig: 1

            

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
                          

      Client:

                     
                          

                               Joe Blogs

                     
                          

                                

                     
                          

                                

                     
                          

      Project:

                     
                          

                               Project 1

                     
                          

                                

                     
                          

                                

                     
                          

      Blitz Days

                     
                          

                               27-Feb

                     
                          

                                

                     
                          

                                

                     
                          

      Invoice:

                     
                          

                                

                     
                          

                                

                     
                          

                                

                     
                          

                                

                     
                          

                                

                     
                          

                                

                     
                          

                                

                     
                          

                                

                     
                          

                                

                     
                          

                                

                     
                          

                                

                     
                          

                                

                     
                          

      Role1

                     
                          

      Role2

                     
                          

      Role3

                     
                          

      Role4

                     
                          

      Role5

                     
                          

      Role8

                     
                          

      Total

                     
                          

                                

                     
                          

                               Staff1

                     
                          

                               Staff2

                     
                          

                               Staff3

                     
                          

                               Staff4

                     
                          

                               Staff5

                     
                          

                               Staff6

                     
                          

                                

                     
                          

                                

                     
                          

                                

                     
                          

                                

                     
                          

                                

                     
                          

                                

                     
                          

                                

                     
                          

                                

                     
                          

                                

                     
                          

      Train

                     
                          

                                £             10.00

                     
                          

                                

                     
                          

                                

                     
                          

                                

                     
                          

                                

                     
                          

                                

                     
                          

       £        10.00

                     
                          

      Bus/Tram

                     
                          

                                

                     
                          

                                £             15.00

                     
                          

                                

                     
                          

                                

                     
                          

                                

                     
                          

                                

                     
                          

       £        15.00

                     
                          

      Taxi

                     
                          

                                

                     
                          

                                

                     
                          

                                £        20.00

                     
                          

                                

                     
                          

                                

                     
                          

                                

                     
                          

       £        20.00

                     
                          

      Mileage

                     
                          

                                

                     
                          

                                £           114.00

                     
                          

                                

                     
                          

                                £        25.00

                     
                          

                                

                     
                          

                                

                     
                          

       £      139.00

                     
                          

      Parking

                     
                          

                                

                     
                          

                                

                     
                          

                                

                     
                          

                                

                     
                          

                                £        30.00

                     
                          

                                

                     
                          

       £        30.00

                     
                          

      Hotel

                     
                          

                                

                     
                          

                                £             70.00

                     
                          

                                

                     
                          

                                

                     
                          

                                

                     
                          

                                £        35.00

                     
                          

       £      105.00

                     
                          

      Food

                     
                          

                                

                     
                          

                                £             25.00

                     
                          

                                

                     
                          

                                

                     
                          

                                £        50.00

                     
                          

                                

                     
                          

       £        75.00

                     
                          

      Total

                     
                          

       £             10.00

                     
                          

       £           224.00

                     
                          

       £        20.00

                     
                          

       £        25.00

                     
                          

       £        80.00

                     
                          

       £        35.00

                     
                          

       £      394.00

                     

            

            

      Fig: 2

            

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
                          

      Client

                     
                                                

      Train

                     
                          

      Bus/Tram

                     
                          

      Taxi

                     
                          

      Mileage

                     
                          

      Parking

                     
                          

      Hotel

                     
                          

      Food

                     
                          

      To Invoice

                     
                          

      Inv No

                     
                          

      Paid or
                               Unpaid

                     
                          

       Contract Limit

                     
                          

                               Joe Blogs

                     
                          

                               Project 1

                     
                          

                               £10.00

                     
                          

                               £15.00

                     
                          

                               £20.00

                     
                          

                               £139.00

                     
                          

                               £30.00

                     
                          

                               £105.00

                     
                          

                               £75.00

                     
                          

      £394.00

                     
                                                                      

                               £500

                     

            

        • 1. Re: Best way to create this?
          philmodjunk

               do you want to reproduce the format shown here or can a different format be used so long as it computes the same totals?

               A summary report of this data would be much simpler to set up, but it won't have your data arranged in this "cross tab" type format.

               Cross tab type layouts showing such tables are also possible, but you are looking at a much more complex design to make it happen using subsummary layout parts and single row portals to produce the needed "grid" of a cross tab report.

          • 2. Re: Best way to create this?
            NickLawrence

                 Hi Phil,

                  

                 Many thanks for your very quick reply, it doesn't necessarily have to be in the same format as long as it is easy to follow, but I don't mind putting the effort in to get it up and running correctly, more about getting it working right than making my life easy. I did have an initial stab at it using a table but couldn't get the summary part to work correctly so any help is greatly appreciated.

                  

                 Regards

                  

                 N

            • 3. Re: Best way to create this?
              philmodjunk

                   Are you importing data from this spreadsheet or simply trying to replicate the resulting totals?

                   A Summary report of the above data might look like this:
                   Train
                       Staff 1 amount
                       Staff 2 amount
                       and so forth
                   Train Total

                   Bus/Tram
                       Staff 1 amount
                       Staff 2 amount
                       and so forth
                   Bus/Tram Total
                    

                   And So forth for each expense category

                   A summary recap at the bottom could list total expenses for each staff member

                   And the data shown in figure 2 (for either a summary report or a cross tab) should be stored as a set of records where each of the following rows is a record:

                   ClientID   | Project ID | Expense Category | Amount
                   3                      2           Train                       10.00
                   3                      2            Bus/Tram               15.00
                   And so forth....

                   This data should then be linked to a table of projects that should in turn be linked to a table of Clients. (and join tables might be needed.)

              • 4. Re: Best way to create this?
                NickLawrence

                     Thanks Phil,

                      

                     No data will be imported, just designed to and used going forward. I will look at your suggestions, I'm sure I'll be back with questions :) Again may thanks for all your help.

                      

                     Kind regards

                      

                     N