Skip to main content

How to group on a calculated field?

Chris Dunlop

Hello. I have a report that has a Year attribute that is either filled out with a numeric year, or is null. In my report I have a group header, and several attributes I want to summarize on. For the Year attribute, I've set up the expression as:

 

Iif(IsNullOrEmpty([Year]),'Uncoded','Coded')

 

Which converts the year to either "Uncoded" for nulls, or "Coded" if there is any legitimate value. I want to display separate counts of the Coded and Uncoded records. How do I set up the Group Fields grouping to distinguish between Coded and Uncoded? Any help is much appreciated.

 

Chris

0

Comments

2 comments

Date Votes
  • Permanently deleted user

    Hi Chris,

     

    I think this should do it:

    • add a Group Header band. Under Properties > Actions > Group Fields, click the + button and pick your calculated field out of the list
    • drag a label onto your group header (or into a group footer). Under Properties > Summary, set the "Running" value to "Group"
    • under Expressions > Text, open the editor for the label's expression. Enter something like: sumCount( [calculatedField1] )  . For reference, the summary functions are found under Functions > Summary, and are only available when you've already set the running value in the previous step

     

    I tested on my end and found that the process we'd normally use for regular fields seems to work fine for calculated fields as well.

    0
  • Chris Dunlop

    Hi Amanda. I Hmmm, I'm still not getting it to work. Just to make sure I've explained it correctly, I'll describe my process steps so far. First, here's what I've got BEFORE adding the calculated field.:

     

    • I have 3 fields that I want to summarize on (OWNER, TYPE and STATUS). They are not calculated.
    • In my Group Header, I have 3 Group Fields set up (OWNER, TYPE and STATUS).
    • I added 3 labels to the Group Header for OWNER, TYPE and STATUS. For each, in the Summary, I've set Running to Group
    • I added 2 additional labels to the Group Header for COUNT and LENGTH. COUNT's expression is sumCount([OWNER]). LENGTH's expression is sumSum([Shape_Length]) 
    • If I run this report, it works correctly, giving correct COUNTs and LENGTHs for each unique combination of OWNER, TYPE and STATUS.

     

    Now, I want to include my calculated field in the summary. The source attribute is YEAR. Only some records have this field populated, with values like 1980, 2014 etc. The rest are nulls. In my summary, I want to group all the valid YEAR values as "CODED", and all the nulls as "UNCODED".

     

    • Based on your instructions, in the Group Header I added an additional grouping field, YEAR
    • I added another label to the Group Header. I set its expression to Iif(IsNullOrEmpty([YEAR]),'Uncoded','Coded'). In its Summary, I set Running to Group

     

    This report doesn't work correctly. It's grouping on individual years, not the Coded/Uncoded value. I tried replacing the Group Header YEAR group field with Iif(IsNullOrEmpty([YEAR]),'Uncoded','Coded'), but this doesn't work. It just groups on OWNER, TYPE and STATUS if I do that.

     

    Hope this makes sense! Thanks for your help.

     

    Chris

    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post