?
Solved

Group by multiple values

Posted on 2013-01-11
6
Medium Priority
?
428 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 668 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 668 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 664 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

718 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