Report Request

[Guest Jhagan]

We are a trust and have the need to report on all the plans we administer as a whole. I have tried to customize a report in Report Writer that will count the number of participants in each plan. Sounds easy, but I have had problems with alphabetizing and other. Support said it must be grouped by plan ID then sorted by aplha. Anyone have a report of this type they could share? email: jhagan@flcities.com Thanks!

[Guest Bill Pearce]

Just a clarification... if you're looking for a count of participants by plan, why do you care about order?... Or do you want a listing of all participants by plan?

[Guest Jhagan]

Need count by plan. This report is used for reporting to our Trustees and for marketing so aplha order is necessary for presentation purposes. It seems like it would be easy to do.

[Guest Bill Pearce]

Use the following SQL statement in your report:

SELECT Planstat.planid,

Planstat.plannam,

count(planee.ssnum)

FROM Planstat, Planee

WHERE Planstat.planid = Planee.planid

AND Planee.Yrenddt = [date]

GROUP BY Planstat.planid,

Planstat.plannam

...and then use Crystal's grouping/sorting option to sort the report by plan name.

This will get you a list for a particular plan year end. If you want all plan years, simply remove the yrenddt from the WHERE clause and add it to the SELECT list and the GROUP BY list.

If you want all plan year ends within a range of dates, use yrenddate >= [start date] AND yrenddate <= [end date] in the WHERE clause.

Hope this helps.

[Guest ClovisJayc]

Off topic - Bill, does planstat carry eligible participants? I'm wondering what the linking characteristics would be, for instance, in the case of an eligible participant that didn't have an account balance in a deferral only 401(k). Or would one have to point to status code?

[Dawn Hafner]

On a related matter, does anyone have a library of Crystal Reports that they would be willing to share? Some of the Quantech reports are lacking. For example, they just came out with a report that shows shares and sorts by fund, but even the cash accounts come up with shares, and then all shares are added up and shown as total shares, which is meaningless. Anyone willing to share any formats that work well for certain reports or certificates?

[Guest Bill Pearce]

Hmmmm... Planstat is strictly plan level info. You'd have to join to a participant level table (such as planee) to get participant level data. I don't know how to answer your question about eligiblity... not that I don't understand the question, I just don't know the answer.

[Guest JohnB10]

Clovis and Bill:

If you want to include only certain categories on your report, you would use something like "Where planee.categcd = '1' " or "Where rptee.categcd = '1' ", depending on which table you're using. If you want to include only category '1's with account activity, don't check the "Zero account activity" box on the report screen. Hope this helps!

------------------

[Guest JohnB10]

Dawn:

Corbel has a library of some "unofficial" reports. You also might want to check with the various user groups (found at Corbel.com) to see if they have libraries. I'm not sure, though, if there are any good libraries out there.

So, as moderator of this Quantech group, I propose...

Let's create our own library. I know there are regular contributors to this forum who have some great reports (Tom P..how about that Crystal ADP/ACP report?) I have a few good reports to contribute as well.

Ground rules:

(1) E-mail any report you'd like to add to the library to me at (John.Bronikowski@Emjay.com). With it, inlude a message stating how to run the report, what the report does, your current Quantech level, and your database (Oracle vs Gupta). Also indicate whether you want to be notified whenever new reports are added to the library.

(2) If you don't have a report to donate yet, just send an e-mail and I'll add you to the new report notification list.

(3) All reports in the library are considered "buyer beware" and should be tested/reviewed thoroughly by any person using them.

Let's make this thing work!

------------------

[Tom Poje]

I am all for sharing and think it's a great idea, but first things first. I have to see the reaction at the users group meeting. there may be ways of improving the ADP report I have.

[Guest ClovisJayc]

Administrators at our company (a third party administrator) would like very much to make ongoing contributions (as well as an occasional withdrawal) to this type of library. Group-ware and the fresh ideas that stem from it is a good thing, as Martha Stewart would say.