AnsweredAssumed Answered

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

Question asked by liquidcooled on Aug 5, 2010

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

Outcomes