I work for an ISP and I'm trying to generate a report each month that shows me our users' data usage, so we can see who our heaviest users are and how much data they're consuming each month.

The database is storing each user's username, session start/end times, and the data transferred in and data transferred out for that particular session. The problem is, each user has several sessions per day that need aggregated. The end result I'm trying to see is how much total data each user transferred over the course of the month.

The data SQL is giving me looks like this:

[user1] [call start date/time] [call end date/time] [data in] [data out]
[user1] [call start date/time] [call end date/time] [data in] [data out]
[user2] [call start date/time] [call end date/time] [data in] [data out]
[user2] [call start date/time] [call end date/time] [data in] [data out]
[user3] [call start date/time] [call end date/time] [data in] [data out]
[user3] [call start date/time] [call end date/time] [data in] [data out]

customer.username - radius.callstart - radius.callend - radius.data_in - radius.data_out are the actual database column names.

and so on...

Typically in SQL, I would do a GROUP BY on the user field and a SUM on each data field, which would give me something like this:

[user1] [total data in] [total data out]
[user2] [total data in] [total data out]
[user3] [total data in] [total data out]

I got this far in my report in Crystal Reports 2008. The problem is, I need to see the grand totals per user. So I would ideally only see this on my report:

[user1] [grand total data]
[user2] [grand total data]
[user3] [grand total data]

I can't figure the best way to go about doing this. I'm somewhat novice with Crystal Reports in general, so any advice or assistance would be appreciated. I had tried making a formula field that added radius.data_in and radius.data_out, but the totals didn't match the users (it was adding up the SQL fields before it handled the grouping).

Thank you for taking the time to read my question :)


Edited by dephekt - 13 Apr 2010 at 8:13am

Well, part of the problem is, I need to be sorting users by their total data transferred, not just the sum of data_in or data_out.

Basically, I need to sum data_in and data_out for each call per user, then I need to sum data_in + data_out, which would tell me the total amount of data they transferred, and sort based on that.

If I order the report strictly based just on data_out or just on data_in, the report will be entirely too inaccurate, because some users have massive amounts of upload data that wouldn't have been taken into consideration on the report. So, for example, if a user had uploaded 30 GB of data over the month because they run some high traffic server at their office, they actually wouldn't show up on my report unless the sum of both sums was taken into consideration.

I hope that makes sense, it's kinda difficult to explain and thank you very much for your help thus far.

That worked perfectly. I did a report with one group using the username field and told it to suppress the details. Then I added a formula field for the total transfer amount, then added it as an insert summary and was able to sort against it. Thank you VERY much. I've learned a ton from doing this now.