Solved

Group by multiple values

Posted on 2013-01-11
6
419 Views
Last Modified: 2013-02-10
I need help with grouping and summing in a Crystal Reports (2008 version).

I've been asked to build a condensed version of a GL income report. Basically, the full version of the report shows all transactions grouped by a Parent code, and then further grouped by a subcode.

The client needs to SUM groups of SubCodes to provide a simple income statement, showing them an overall picture. They have provided me with a listing of each Subcode and it's "parent" group.

If you refer to the attached Excel file, the "Parent" code would be in the A column, and the "Subcode" would be in the B column. I need to SUM on all Revenue Items, Labor Items, Overhead, etc based on the values in Column B.  If you refer to the "Goal" section of the sheet (columns M and N), you'll see basically what the client is after.

The client has given me a list of all Revenue Codes, all Overhead Codes, etc etc. In SQL, I'd do this:

SELECT SUM(MyValue) As Revenue FROM MyTable WHERE AccountCode IN ('7045', 7025',7055','7065','7090')

Can I do something like that in Crystal, using a Formula or some other method?

Essentially, I need to create a Summed section based on the data's Code - if the data line is associated with any of the Revenue codes (('7045', 7025',7055','7065','7090') then SUM those lines in that Group. If the line is associated with a Labor code (5025, 5020,5030, 5040, 5035, 5045, 5065, etc) then I need to show the SUM of those values in a Group section (or detail section).

I tried a Formula like this:

{INCSTMT_ttx.ACCT} IN '7045', 7025',7055','7065','7090']

And using that as my Group field, but that doesn't seem to work.

Any thoughts? Note too these reports are built using TTX files (I know, I know) so I'm constrained with those as well.
EE-Q.xls
0
Comment
6 Comments
 
LVL 12

Assisted Solution

by:Jared_S
Jared_S earned 167 total points
Comment Utility
I don't know  thing about TTX files, but if this were just from a normal table, you could

create a formula that would declare a global variable for each group,

then while reading the records evaluate the group ID being read and add the transaction

value to the appropriate variable.

Then you just bind the variable to a text field on your report.

If you needed the transactions to appear within the same area on the report, then you might

be able to evaluate the ID while reading records and assign a generic value to a "group"

variable. Then add that variable to the detail level, hide it, and group by it.

There may be more elegant solutions come forward, but I would start there if it was my

project.
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 167 total points
Comment Utility
You can add a formula like

IF  {INCSTMT_ttx.ACCT} IN '7045', 7025',7055','7065','7090']

['7045', 7025',7055','7065','7090'] THEN
    "Revenue"
Else
    "COGS"

You then need a second formula to handle the COGS subgrouping.

If {INCSTMT_ttx.ACCT} IN ['5025', '5020', '5030', '5040'] THEN
    'LABOR'
Else If {INCSTMT_ttx.ACCT} IN ['2020', '2030', 2040'] then
    'OVERHEAD'
Else If {INCSTMT_ttx.ACCT} IN ['6050', '6070', '6080', '6090', '6020'] then
    'MATERIAL'

You can then use this as a second group.

If you know
    7000-7999 is REVENUE
    2000 - 2999 is OVERHEAD
    5000 - 5999 is LABOR
    6000 - 6999 is MATERIAL

You can simplify this and it will handle new subcategories
Group1
If Val({INCSTMT_ttx.ACCT}) IN [7000 .. 7999] then
    'REVENUE'
Else
    'COGS'

Similarly for the other ranges for the second grouping.

Simply suppress group 2 when group1 = REVENUE

mlmcc
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 166 total points
Comment Utility
FWIW, near the end of mlmcc's post, he has:

If Val({INCSTMT_ttx.ACCT}) IN [7000 .. 7999] then


 I'm not sure where "[7000 .. 7999]" came from, but it won't work (unless something has changed in the more recent versions of CR).  There are different ways that you could express that.  I would use:

If Val({INCSTMT_ttx.ACCT}) in 7000 to 7999 then


 You could also use Select-Case.  If there are more than a few "groups", I find Select-Case easier to read than a long series of if-else statements.


 How those groups are sorted may be an issue.  They will default to alphabetical sorting by the group name.  If you need them in a different order, using the "Specified Order" option for the group would be one option, but I found it to be a hassle.  I've handled this kind of thing before by just adding some spaces to the front of the group names.  If you put those names on the report, they'll have those spaces in front of them, but if you're using a proportional font, a few spaces probably won't even be noticeable.  And if they are, you can use a formula to remove any leading spaces and put that formula on the report instead.


 Also, FWIW, you said that you tried to group on a formula like the following and it didn't work:

{INCSTMT_ttx.ACCT} IN ['7045', 7025',7055','7065','7090']


 That's just going to produce True if the account is one of those numbers and False for everything else, so you just get 2 groups on the report, one for those numbers (True) and one for everything else (False).  What you need to do is produce a different value for each set of account numbers, which is what mlmcc is doing in his formulas.

 James
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 84
Comment Utility
thanks for all the suggestions. I've working on this today, and will let you know my results. I'll be onsite with a customer next week, however, and may have a lot of time to keep up with this
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 0 total points
Comment Utility
I'm sorry for the delay in addressing this matter. My one week onsite turned into quite an extended visit, and I've been swamped since then.

I ended up modifying the TTX files to include several new fields to represent AcctTypes and ParentAcctTypes, and grouped by those values in Crystal. I also had to modify the SQL to provide that information to the report. Once I did that I was able to get this working.

I did later try out the Formulas and Groupings, and found they would work too - but I'd already delivered to the customer, and all seems to work well.

Thanks again, and sorry (again) for my tardiness.
0
 
LVL 84

Author Closing Comment

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Comment Utility
I've split the points among all 3, since each provided me with great suggestions and insights into my solution.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now