Creating report groupings based on data variables

You can go around this by using your formula in the section group header grouping formula but I think where you will run into issue is the sorting part of it. The Sorting doesn’t allow to use either variable or formula.

Do you have segmentation in your cost code? If yes, as an alternative for grouping in report designer you can access each individual segment of the cost code and utilize segments to group on them.

Reza, 

Do I create the variable in both the data area & the header area & can they the same name?  Or, do I only create the variable in the header where it is needed? 

I don't think I'm worried about the sorting since I can sort on just the CostcodeID.  

Thx

Grouping on header level with suffice. You will need to sort by CostCodeCD. CostCodeID is an internal key and you may end-up seeing multiple groups for your sections

You were correct about sorting on CostCodeID.  It’s a mess.  I had to join in the CostCode table and sort on CostCodeCD.  Thanks for that tip.

@RHarrison glad it helped. Cheers

I’m hoping you can keep helping.

I’m working on a report based entirely off of PMTRAN (with PMCOSTCODE linked in to give me access to CostCodeCD).  I’m starting with parameters for project, startperiod & endperiod.  I’m then trying to filter so that I only select records from PMTRANS where PMTRAN.ProjectID = @project and where PMTRAN.FINPERIODID is between @startperiod and @endperiod.

I can’t seem to get my date parameters into a format that is consistent with PMTRAN.FINERIODID.  I’ve tried

1. creating the parameters as ‘string’, ‘date’, and ‘integers’.
2. creating a variable based on the parameters to put them in yyyymm format.
3. creating variables to put all of the dates into yyyymm format.

nothing seems to work.

This is how I’m defining the parameters

view name
=Report.GetFieldSchema('OrganizationBranchReportParameters.FinPeriodID')

default value
=Report.GetDefExt('OrganizationBranchReportParameters.FinPeriodID')

But I’ve also tried

view name
=Report.GetFieldSchema('PMTran.FinPeriodID')

default value
=Report.GetDefExt('OrganizationBranchReportParameters.FinPeriodID')

I’ve looked at a bunch of other reports where period ranges are used and can’t seem to find anythign that works with PMTRAN.FINPERIODID

Any suggestions?

Hi @RHarrison - If your original question was answered by @aaghaei, then it’s best to mark it as such and create a new post for this question. That helps keep things sorted and gives the person who helped proper credit. If this is not a new question, carry on  Thank you!

Done.

Reza,

With your help I’ve managed to get my costcode categories to work.  All of the grouping based on fields (CostCostCD_Segment1, and then CostCodeID). 

But I’ve still not been able to get a grouping to work off of a variable (which was my original question).  I’ve tried creating a third grouping and added a variable called $HardSoft  in that grouping.  I then went into the parameters for the entire report and selected Groups-Collection.  In the Group Selection Editor I then selected Grouping-Collection and selected the variable I created in the DataField property. 

When I run the report I can an error that says

    A Field with the name $HardSoft cannot be found.

I’ve also tried creating the variable in the data section AND in each group header.  All produce the same error.

After a tonne more trial & error I finally figured it out.

I wasn’t able to group or sort based on a variable but I could use the same formula from the variable as the basis of the group.  

If I later change the variable I’ll need to remember to edit it in two places but IT WORKS!!!!!!

Thanks for all of the help