0 Replies Latest reply on Aug 5, 2010 11:17 AM by liquidcooled

    Filtering the results from a sub-summary report, and using charts with sub subsummary reports

    liquidcooled

      Title

      Filtering the results from a sub-summary report, and using charts with sub subsummary reports

      Post

      Hi,

      I'm creating a database to manage a stock portfolio across multiple brokerage accounts.  What I have now is a Transactions table that has the imported data from the different brokerage accounts.

      I need the database to be able to calculate the total shares across all accounts as well as which shares are held in individual accounts.

      I also need to be able to see a pie chart that will show the distribution of shares in each account as well as the total distribution across all accounts.

      I have started on a database to accomplish this, and I'm getting some of the results that I want, but I'm having a hard time getting everything that I want out of it. So here is what I have so far that seems to be working. I can do a Subsummary report based on Account number and then Stock Symbol that will show me the total number of shares of each stock in each account.

      What I would like is in the top part of the subsummary where it is sorted by account is to have a pie chart that show the distribution of the stock shares. I would also like the report to exclude stock shares of 0 or less, so that the report only shows shares that are currently held and not shares that have been held in the past.

      Following are the tables that I currently have. If any one has any ideas on how I can get the results that I'm looking for I would appreciate it. 

      Thanks

      Transaction Table  
      _pk_transactionID  
      fk_accountID Connects to pk accountID in Account Table
      fk_stockID Connects to pk stock ID in stocktable
      fk_typeID

      Connects to pk type ID for type of trade, because some trades are option trade

      which do not effect number of shares.

      QTY Number of shares bought or sold
      Price  
      cStockTypeQtyHelper

      If(fk_typeID="stock"; Quantity;0)

      cTotalSTKBalance

      =Total of cStockTypeQTYHelper (running with Restart) when sorted by Stocks::StockSymbol

      Account Table  
      pk account ID connected to transactions table fk_accountID
      Stock Table  
      pk_stockID Connected to Transactions table fk_stockID
      StkSymbol Stock Symbol
      Type Table for different types of transactions stock, option, dividend 
      pk_typeID connected to fk_typeID in Transaction table