By using this website, you agree to our Terms of Use (click here)
In order to allow me to focus on Consulting (click here) and Courses (click here), I continue to review all posts here in the Forums, but, in general, I'm only personally responding to posts that I mark as #AcumaticaTnT (click here). For Questions (click here), others may respond, or you can post over at Community.Acumatica.com (click here).
Sincerely,
Tim Rodman
I am attempting to hide/view sections in a sub report if the dataset in the set report is empty. Am attempting to use =COUNT(<field in dataset>) to determine the number of records and if 0, hide the section.
I have tried several approaches including the following:
- Setting the VisibleExpr setting on the sections in the SubReport
- Setting the VisibleExpr setting on the report object in the SubReport
When looking at values in the SubReport, I have tried adding textboxes to see the values being generated by the system. The following formulas provided the indicated values (with records, without records):
Formula #1
- Formula: =COUNT([ARAdjust2.AdjgDocType])
- With Records: Number of Records
- Without Records: Blank
Formula #2
- Formula: =ISNULL(COUNT([ARAdjust2.AdjgDocType]), 0) > 0
- With Records: Number of Records
- Without Records: Blank
Formula #3
- =CInt(COUNT([ARAdjust2.AdjgDocType]) )
- With Records: Number of Records
- Without Records: Blank
I have tried several variations of these formulas without any different results. I think my core issue is not understanding how the COUNT function works with a blank record set, I can't properly use it in the VisibleExpr expression.
Can anyone help me better characterize the COUNT function or guide me in how best to hide a section in a SubReport?
Note, I have done this successfully in a main report so not sure why things seem to be working differently in a sub report.
I have attached the main report and sub report as well.
Any help would be greatly appreciated!
Found that the sub report variable was not accessible by a textbox on the main form in a panel. Have a tech support incident with Acumatica about this and they haven't been able to explain it.
Also Acumatica tech support explained that the count function will not return anything if there are no records (i.e. will never return 0). I think this is a significant limitation in the function and had to revamp the approach. Not terribly happy with the final implementation but it's working.
Have you tried wrapping it in the IsNull function?
I Have the same problem.
Tried wrapping it in the IsNull function, but still doesn't work.
There are also some parameters for Groups
Here in the collection definition
And also in the group's property pane :
But it doesn't change anything either.
I just want to reactivate this old post still unresolved.
Thanks.
This is working for me.
On the sub report section that you do not want to show:
- Set visible = false
- visibleexpr = NullIf( Count([table.field]), 0 ) > 0
On the main form if the subreport is in a section you do not want to show if there are no rows then:
- In the sub report add a report header
- Set the height to 0
- Add a variable called 'rows' and set the expression to NullIf( Count([table.field]), 0 )
- In the main report name the subreport appropriately, e.g. subInvoices.
- In the section the subreport exists set the Visible = false and visible Expr = NullIf( $subInvoices_rows, 0 ) > 0
Many thanks Mark, it works for me too.
It is even easier, it's enougth to :
- Set visible = false
- visibleexpr = NullIf( Count([table.field]), 0 ) > 0
in one of these 2 places :
- in the subreport section of the main report
- or in the differents sections of the subreport
The second choice is the only one possible if the subreport filters the element list (my case)
Solved!