14 Replies Latest reply on Aug 30, 2011 5:06 PM by Chris

    Reporting and Charting record history

    Chris

      Title

      Reporting and Charting record history

      Post

      I am currently designing an investment management database and there is one record for each account. I am trying to find the BEST SOLUTION to creating data history for charting and reporting purposes. My current solution has been creating a snapshot of the account at specific intervals (monthly) and creating a new record in a different table for the snapshot of each account. I tried this out with Version 1 and it seems to work great except I think the only downfall is the STATIC information. The snapshot is frozen and cannot be changed. Reports cannot be modified to view certain trends or data ranges; limited anyways.

      The account is based on different transactions that are all calculated. I think there may be a way to create a realtime charting system that would flow data from the child table to the parent table and generate the proper data for charting. My thought process has been OVERWHELMING to say the least. Would I be using loop scripting and data calcuations to create specific intervals? Would I generate snapshots of a specified account in a different table for every report I run and then delete upon exit?

      Any input on this matter is greatly appreciated.

        • 1. Re: Reporting and Charting record history
          philmodjunk

          We'd need to know a lot more about how you've set up your database before we could make any useful suggestions.

          • 2. Re: Reporting and Charting record history
            Chris

            Well I'll try to explain as much as possible and see if it makes sense. 

            PhilModJunk, you've help me with part of this solution months ago.

             

            Each account can have multiple investments, between 1 & 4. Think 'stock market trading'. I have a transaction table that handles depositing and withdrawing funds into an account. The transaction table will also manage buying and selling stock A, B, C, or D and allow input of monthly expenses for each account such as interest fees. Every transaction has a ctotal which will give me a positive or negative number. I then have on the account table a sAccountBalance based on the transaction table. The relationship for this is based on the account number's transaction of course. The other information that I will be drilling down to is the prices that the stocks are purchased, the cost averages calculated for each, sExpenses, sCommisisons, and most importantly, dates.

            On the account table, I calculate the cost averages, totals, and a series of other numbers for analysis.

            I guess this may or may not make sense. I'm pretty good at deconstructing ideas and applying them to what I need to do. What I'm not sure about is the ability to drill down through relationships for data and creating charts.

            PhilModJunk, I read a lot of your stuff on here and you've been very good at explaining a concept in an understandable manner. If my explanation doesn't make much sense, do you have a simple example of charting ideas? 

            • 3. Re: Reporting and Charting record history
              philmodjunk

              What kind of "chart"? Would this be a FileMaker chart object or a table? (I've had others refer to "charting" something here when they really wanted a tabular presentation of the data, so I'm making sure we're on the same page.)

              What data do you need to display in this chart?

              This all sounds "sort of familiar", but I've helped enough other folks, that I'm not able to clearly picture your specific database design from memory.

              • 4. Re: Reporting and Charting record history
                Chris

                I'm looking at making line, pie, bar charts, depending on the data. When I was creating monthly snapshots, I was able to have monthly intervals on the (x) axis to show a progress with how each account was doing on a month by month basis. I did this creating a snapshot every month.

                Is it possible to create a script that can calculate and populate these snapshots anytime by using date ranges? Every time this report would run, it would have to calculate the account based on the very first transaction up until the specific month, create the snapshot, then generate the next snapshot for the next month. And so on and so forth. It's all based on date ranges and generating table data to base the charts on. I guess the only difference from what I'm doing now, taking a snapshot and storing it in a table, is that I can generate the snapshots on the fly and delete them when I'm done looking at the chart. 

                The type of data I will be showing is market prices, account values, profits. They most likely will all have their own chart.

                • 5. Re: Reporting and Charting record history
                  philmodjunk

                  I really have no idea how you are currently creating a "snapshot" of your data nor why this would be necessary.

                  There are ways that a range of dates and other criteria can control what data is accessible via a relationship or is present in a found set--two options that may provide ways for you to chart this data without creating any "snapshots" of your data.

                  • 6. Re: Reporting and Charting record history
                    Chris

                    I guess I think the snapshots are important because of how the chart data is set. Let me try to explain.

                     

                    Account A, July.

                    Transaction 1, deposit funds $1000

                    Transaction 2, purchase stock X at price of $10

                    Transaction 3, sell stock X at price of $15

                    Month End, Account value is $1500

                    Account A, August.

                    Transaction 4, purchase stock X at price of $8

                    Month End, Account value is different than the last month.

                    I am using positive and negative totals for each transaction and using a Summary field for the account value. The summary will change, of course, with every transaction. I want to see every interval for the Account Value field on the X axis, so I've been creating this 'snapshot' of the field setting a date beside it and going on to the next month to see what changes have occurred to the account. I feel like I'm very close to a solution for this but missing one simple thing. I haven't worked with charts very much and I'm not sure about how to plot the data on the chart when it's based on summary and calculation functions. The most complex part is plotting the data based on ...end of month 1, end of month 2, end of month 3. Or if I want to change the report to show after every transaction. Instead of creating 'snapshots', would I just create a bunch of variables? A scripts for every chart I want to create?

                    The dynamics of this have me a little confused, obviously.

                    • 7. Re: Reporting and Charting record history
                      philmodjunk

                      Aren't Transactions 1, 2, 3, and 4 simply 4 records in the same table? 1 - 3 have transaction dates in the month of july and Transaction 4 has an August date?

                      When you said "snapshot", I was picturing that you were creating new records just for the snapshot, but that doesn't look to me like this is what you have now that you've given an example.

                      You can treat data points in a chart (for each bar, line point, pie wedge...) just like subsummary parts in a summary report. In both cases you specify the summary field for the charted value, but then you sort your records by a "break" field to group them before they will produce the chart you need. With this charting option, you select the Use Data From Found Set and "Show Data points from groups of records when sorted" options.

                      • 8. Re: Reporting and Charting record history
                        Chris

                        That makes some sense. I think I have a big learning curve ahead of me with using charts. I haven't had much experience with them. 

                        Would the "break" field be my dates?

                        Yes, I was creating actual records for a snapshot. I was setting the date and copying over the field to a new record so I could plot the charts with data from each record. Basically, if the account was open for 7 months, I had 7 records with all the data. It was quite a simple process actually and works. It just didn't seem like the best way to do it and it isn't dynamic.

                        • 9. Re: Reporting and Charting record history
                          philmodjunk

                          Any field that has the same value for all records in a group can be used as a "break" field if you then include that field in your sort order.

                          If you want all records from the same month, you might want to define this calculation field set to return "date" as its return data type:

                          DateField - day(DateField) + 1

                          This computes the date for the first day of the same month so it produces a common value that you can use as your break field when grouping records by month.

                          • 10. Re: Reporting and Charting record history
                            Chris

                            So I've learned a few things about charts and layout parts role in the data. Here's what I've found...

                            As far as I can tell, I am placing the chart in a subsummary part on a layout based on the transactions. I did this and used your above date formula, which I have no idea how that works but it does, and I ended up with charting data for all the accounts. I assumed that I would have to perform a find routine for the specific account and it's transactions but doing so gave me a flat line (line chart, not lack of a pulse. Although I think it might be easier to just shoot myself at this point. LOL) Here's what I based the chart on. 

                             

                            X axis: Transactions::Date - Day(Transactions::Date)+1

                            Y axis: Accounts::Accounts::cAccountValue = Sum(Transactions::Total)

                            Use Data From: Current Found Set

                            *Show data points for groups of records when sorted. Check!

                             

                            The subsummary is not computing the sAccountValue based on what it was at that time. Is this because it's from another table or because it's a calculation? My solution to this was to create a sAccountValue on the transactions table as well and use Running Total. I plotted that data instead and got my line chart. Is there a way to use the related account record or am I going about this the wrong way altogether?

                            I really appreciate all the help. 

                            • 11. Re: Reporting and Charting record history
                              philmodjunk

                              Don't put the chart in the sub summary part. Put it in the body and see what results you get.

                              Transactions::Date - Day(Transactions::Date)+1 is what you put into a calculation field, not just in a chart calculation (you might want to use MonthName( Transactions::Date in the chart), so that you can use this field when sorting records to group records by month. (and still get them in chronological order in your chart.)

                              • 12. Re: Reporting and Charting record history
                                Chris

                                I placed the chart in the Body and I get the same result. I'm not sure why I thought it would be different. 

                                The Date function you gave me works great.

                                I'm still very limited with the data I'm able to get. I based some of my calculation on the accounts table through relationships. Setting up a chart on one of these fields will only give me one value because there is only the one record. For example:

                                To Calculate Profit:

                                Accounts:Profit=Accounts:Equity + (Accounts:Withdrawls*-1) - Accounts:Deposits

                                    Accounts:Equity=Accounts:Total Account Value + Accounts:Account Balance

                                        Accounts:Total Account Value= Accounts:Stock A Market Value + Accounts:Stock B Market Value + Accounts:Stock C Market Value + Accounts:Stock D              Market Value

                                        Accounts:Account Balance=Sum(Transactions::cTotal)

                                    Accounts:Withdrawls=Sum(Transactions2::Amount)

                                    Accounts:Deposits=Sum(Transactions3::Amount)

                                The biggest problem is the Stock Market Values are calculated with global fields because I work with live pricing when viewing accounts. I update the price when I login. The only history of pricing is during transactions. If I want to chart Accounts:Profit, would I have to recreate fields on the transactions table to calculate everything? Or is there a way to use a Break field to chart this data?

                                 

                                 

                                 

                                 

                                • 13. Re: Reporting and Charting record history
                                  philmodjunk

                                  Don't get too hung up on "break" fields. That's just jargon for any field used when sorting to group the records into groups based on a common value (such as the date in cMonth).

                                  I don't have enought detail here to say if your calculations/relationships are set up correctly. The fact that you have three different table occurrences of transactions seems unecessary here as it looks to me like the relationships for each are identical, but that could just be a case of my not being able to see the complete picture here.

                                  Since some of your data is stored in a global field, your data from past transactions won't show accurate values if they are calcualtions that use data in that global field. You may want to set up non global fields that store the value(s) current in global fields at the time the record was created. There are auto-enter field settings that can do this. Then you can modify your calculations to refer to the data in these non-global fields and your past transaction records won't compute new values when you change the values in global fields--only new transactions records will use the new data in them.

                                  • 14. Re: Reporting and Charting record history
                                    Chris

                                    It's hard to paint this picture in full because it is a lot of calculations and there is specific reasons for my multiple table occurrences. 

                                    You've solved my biggest problem and I can work on the rest with what you've given me. Thanks for the help.