Skip to main content
Solved

Creating report groupings based on data variables

I have to build a report for our phased construction project.  We want a report that will show

phase  (TaskCodeID)

  Hard Cost/Soft Cost  (first two digits of CostCodeID <29=HardCosts, 30=Land, 31-98=Softcosts, 99=Contingency)

    Cost Code Category (first two digits of CostCodeID)

        detail lines based on CostCodeID

    Cost Code Category Total

  Hard Cost/Soft Cost total

Phase Total

I’ve been able to create variables in the data section of my report called $HardSoft & $CostCostCat and I can get that data to display in the details section.

But, I don’t seem to be able to use the variables to control my groups.

Each groups section seems to also have a VARIABLES property.  Do I have to redefine the variables in in section?

As you can see all of the data for these variables is already within the CostCodeID so creating ATTRIBUTES and having to input this data on every bill seems unnecessary.

Any help?

10 replies

aaghaei
Rank
Userlevel 7
Badge +9

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 Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
R
Rank
Userlevel 3
Badge

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

aaghaei
Rank
Userlevel 7
Badge +9

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

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
R
Rank
Userlevel 3
Badge

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.

 

aaghaei
Rank
Userlevel 7
Badge +9

@RHarrison glad it helped. Cheers

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
R
Rank
Userlevel 3
Badge

@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?

Chris Hackett
Rank
Userlevel 7
Badge

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!

Chris ~ Let us know how we're doing: https://forms.office.com/r/xmJUdZ1QE5 - A member helped you, please *Like* their post. A member provided an answer to your question, please mark it as *Best Answer*.
R
Rank
Userlevel 3
Badge

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.

R
Rank
Userlevel 3
Badge

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

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.

 

R
Rank
Userlevel 3
Badge

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

Reply


Terms & ConditionsCookie settings