Solved

Group by multiple values

Posted on 2013-01-11
6
427 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 12

Assisted Solution

by:Jared_S
Jared_S earned 167 total points
ID: 38767020
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 101

Assisted Solution

by:mlmcc
mlmcc earned 167 total points
ID: 38768058
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 35

Assisted Solution

by:James0628
James0628 earned 166 total points
ID: 38769804
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
Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

 
LVL 85
ID: 38769818
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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 0 total points
ID: 38855174
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 85
ID: 38872728
I've split the points among all 3, since each provided me with great suggestions and insights into my solution.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

623 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