Balance per Participant by Source

[TPA Bob]

Does anyone know how to export into an excel format a report that reports employee name and SSN, and ending balance per participant per source of money? We want the sources in columns and then each row represents a participant.

thanks.

[Tom Poje]

instead of screen viewing or printing a report writer report you can always send the report to excel and open there

(e.g. print to file, and then choose excel for the format) this can be done with any crystal report. your headers might not be lined up, and you might have to re-align some of the data, but that works well if you have many items you want.

for pulling just the elements you want (you get no headers)

the possibly top secret method known only to spies and espionage agents of foreign countries is to go to

transactions, then click on

File

Import/Export

File

Import/Export setup

Category and then

Employee/Plan Data (to get name and soc sec number)

Account Detail (Export Only to get ending balance)

then Export (Summary and the date range)

shhhhhhhhhhhhhhhhhhhhhh. no fair telling. It works just like DER, but you only have a limited selection of elements

[Bill Presson]

Account Detail (Export Only to get ending balance)

Tom,

This only allows you to choose each source by fund. So if you have 3 sources and 15 funds, you have to do this 45 times, then try to add everything in the spreadsheet.

I think that is what TPABob was trying to avoid.

[Tom Poje]

Bill- you are correct of course, it was unclear to me what the exact situation was. the method would still work. if you really only wanted to pull one source, you could run a transaction to transfer 100% from all investments to one investment. then run the DER out of transactions to obtain your numbers, then reverse the transaction. sounds like more work than its worth.

I have a participant statement that prints portrait, so I guess if I was really interested in doing something like that, I could tweak the report so it only shows end balance, and export out of report writer to excel but no time now to even think about a project like that.

[TPA Bob]

Yes, I wanted a report that would have each participant as a row, then then sources reading left to right, 401(k) pre-tax deferral, Roth 401(k), SH match, etc.

Seems odd that the one item that cannot be extracted into a spreadsheet is ending balance (without going through a lot of cut and paste). This is a Plan that is going to a new investment provider and I need to send beginning balances.

So I did the old fashioned way expect did not use a typewriter.

Thanks.

[Fredman]

Sorry, I'm a little late to the dance, but there are two ways that I've done this...

1. See attached. This can be run from the Summary of Accounts area of Report Writer and exported to Excel. I think it is setup to pull the name and source type description field, but it could easily be modified to pull SSN and some other field. It is about as simple as a report can get. One cross tab with a few fields. The usual disclaimers apply (no guarantees, you get what you paid for, etc.).

2. Transfer all assets to a dummy investment (we like to use an investment called TAKEOVER). Once that is done create a new plan year and you'll now have all money in one investment sitting in the beginning balance field. Run a DER to export beginning balances for the dummy investment.

Obviously #1 is easier, but #2 works well as long as you understand what you're doing.

balance_by_source_crosstab.rpt