We have a client that has created a custom Income Statement in designer and they filter departments within the account code segments using trees. They need to have their departmental managers run these reports on their own, however they dont want them to be able to look at other departments. We cant use the distribution function, because they want to be able to drill down and protecting the cells so the department cant be changed also affects the drill down function and they still have the ability to unhide the raw data and see the other department numbers.
The best solution would be to filter the sub reports of the designer to only run that department, and create a new report for each department with its own filter. The problem I am facing is that the filter option doesnt seem to work for the designer report. When I try to add a filter, there are no fields available. Attached is a print shot of the filter with no available fields to select. The parameter in the sub reports to select the year is set up as a pass through variable, I tried to set this up with the department filter, but it doesnt work either. Does anyone have any recommendations on how to filter the data in a designer report when running it, and not once it has run?
If what you are referring to is filtering on the Account segment values in Sage 300 to bring in only certain departments (seg vals) then have successfully accomplished what you are attempting. Please could you contact Craig Juta at our Canadian office email@example.com
We can then provide a solution for you
As you know, the segments are not available in this reports to be filtered on. One way to solve this is to include the segments in each report and then apply a single filter to all.
It would be a long response if I were to list every step required to do this but you probably know how to do this and if not then reach out to us and we will gladly help you.
Regards, Craig Juta
Yes, I tried that and it worked in the Budget, Quantities, Balance Forwards but the Actuals sub report couldn't find the expression I added, said it wasn't "bound". I looked in the container for that sub report and it has a complicated SQL join that I could not figure out. I did find a way to add the segment to the account sub report and then filtered that. This seems to bring in details only for the specified segments, but I noticed the Actuals/Budget/Quantities tabs still have all the data in it (all segments). The designer reports I created only bring in values for that segment, so I think this will work for now.
Thank you so much for the response!
Thank you - that was part of my problem!!
Were you able to add the filter eventually? I am facing the same situation here. The actual reports does not have segment code as a field. I added the segment code as a data field, but when I use it as a filter, the SQL query that the Actuals container has does not seem to support filter. Wondering what I can do in this situation
Did you ever get to the bottom of this?
I added the segment code as an SQL Expression instead of a data field. The SQL statement I use is: SELECT DISTINCT [GLAMF].[ACSEGVAL02] FROM [GLAMF] ORDER BY [GLAMF].[ACSEGVAL02]
It seems to work fine for now!
Good to hear