BAQ Report Option to Filter SSRS

[NateS (Nate Schoonover)]

Hi everyone,
I have been struggling with what seems like a simple problem. I want to put an Option checkbox on my BAQ Report. Then I want to filter my SSRS based on whether or not that box is checked.

More specifically I am listing my inventory in all warehouses and bins. I want the checkbox to only show bins that are not numeric. So, if checked, I would get bins “A, AA, AB, and AB1” but not “1, 22, or 115”

My approach was to insert an option in the BAQ Report. I used ReportParam_Check01 for this.
Then inside my SSRS, I attempted to filter the results using the following code. I first applied this code to the dataset filter, then to the group filter, and then to the tablix filter. All returned similar errors when I try to upload the report.

=if(Fields!Option01.Value=true,IsNumeric(Fields!PartBin_BinNum.Value),false)

I have also tried to point directly to the check field instead of the report option field.

=if(Fields!Check01.Value=true,IsNumeric(Fields!PartBin_BinNum.Value),false)

Can someone point me to how this could be done? I have reviewed a handful of links that all came up a bit short.

Thanks for your time!
Nate

[Alexandre_Pothier]

Hi Nate,

What is the exact error message you get? I see that you are working with IF, while you need an IIF.

[NateS (Nate Schoonover)]

I have switched back and forth between if and iif. I am not sure when to use either, they both seem to work.

Here is the error generated when I use this code in the tablix filter.

=iif(First(Fields!Check01.Value, "BAQReportParameter"),IsNumeric(Fields!PartBin_BinNum.Value),false)

Application Error

Exception caught in: Epicor.ServiceModel

Error Detail

Message: A FilterExpression for the tablix ‘Tablix1’ includes an aggregate function. Aggregate functions cannot be used in dataset filters or data region filters.
Program: Epicor.ServiceModel.dll
Method: ShouldRethrowNonRetryableException

Client Stack Trace

at Epicor.ServiceModel.Channels.ImplBase1.ShouldRethrowNonRetryableException(Exception ex, DataSet[] dataSets) at Ice.Proxy.BO.ReportImpl.ImportSsrsReports(DataSet reports) at Ice.Lib.Report.SsrsTransfer.UploadReports(String clientDirectory, String reportLocations) at Ice.Lib.Report.UploadSsrsDialog.UploadReports(Session session, String selectedPath, String printProgram) at Ice.Lib.Report.SsrsDialogBase.TransferReports(Session session, Action1 showReportsTransfered)
at Ice.Lib.Report.UploadSsrsDialog.UploadReports(Form parentForm, Session session)
at Ice.UI.App.DynamicReportDesigner.BAQRDesignerForm.UploadSsrsReport()
at Ice.UI.App.DynamicReportDesigner.BAQRDesignerForm.OnClickMiscTool(String ToolKey)
at Ice.Lib.Framework.EpiBaseForm.handleToolClickInternal(String tKey, ToolClickEventArgs ea)
at Ice.Lib.Framework.EpiBaseForm.handleToolClick(String tKey, ToolClickEventArgs ea)

So I pulled out the aggregate function like this, and got this error:

=iif(Fields!Check01.Value,IsNumeric(Fields!PartBin_BinNum.Value),false)

Application Error

Exception caught in: Epicor.ServiceModel

Error Detail

Message: The FilterExpression expression for the tablix ‘Tablix1’ refers to the field ‘Check01’. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case.
Program: Epicor.ServiceModel.dll
Method: ShouldRethrowNonRetryableException

Client Stack Trace

at Epicor.ServiceModel.Channels.ImplBase1.ShouldRethrowNonRetryableException(Exception ex, DataSet[] dataSets) at Ice.Proxy.BO.ReportImpl.ImportSsrsReports(DataSet reports) at Ice.Lib.Report.SsrsTransfer.UploadReports(String clientDirectory, String reportLocations) at Ice.Lib.Report.UploadSsrsDialog.UploadReports(Session session, String selectedPath, String printProgram) at Ice.Lib.Report.SsrsDialogBase.TransferReports(Session session, Action1 showReportsTransfered)
at Ice.Lib.Report.UploadSsrsDialog.UploadReports(Form parentForm, Session session)
at Ice.UI.App.DynamicReportDesigner.BAQRDesignerForm.UploadSsrsReport()
at Ice.UI.App.DynamicReportDesigner.BAQRDesignerForm.OnClickMiscTool(String ToolKey)
at Ice.Lib.Framework.EpiBaseForm.handleToolClickInternal(String tKey, ToolClickEventArgs ea)
at Ice.Lib.Framework.EpiBaseForm.handleToolClick(String tKey, ToolClickEventArgs ea)

[tsmith (Tyler Smith)]

The way I do this is by adding a parameter in the RDL and setting its default value to pull the field from BAQReportParameter dataset. Then you can reference the new parameter in your BAQReportResult or Tablix filter conditions.

[Alexandre_Pothier]

Try with the “Hidden” property of the detail section of your tablix sections

[NateS (Nate Schoonover)]

The way I do this is by adding a parameter in the RDL and setting its default value to pull the field from BAQReportParameter dataset. Then you can reference the new parameter in your BAQReportResult or Tablix filter conditions.

I got this error when I tried this approach:

Application Error

Exception caught in: Epicor.ServiceModel

Error Detail

Message: A Value expression used for the report parameter ‘IsAlpha’ refers to a field. Fields cannot be used in report parameter expressions.
Program: Epicor.ServiceModel.dll
Method: ShouldRethrowNonRetryableException

Client Stack Trace

at Epicor.ServiceModel.Channels.ImplBase1.ShouldRethrowNonRetryableException(Exception ex, DataSet[] dataSets) at Ice.Proxy.BO.ReportImpl.ImportSsrsReports(DataSet reports) at Ice.Lib.Report.SsrsTransfer.UploadReports(String clientDirectory, String reportLocations) at Ice.Lib.Report.UploadSsrsDialog.UploadReports(Session session, String selectedPath, String printProgram) at Ice.Lib.Report.SsrsDialogBase.TransferReports(Session session, Action1 showReportsTransfered)
at Ice.Lib.Report.UploadSsrsDialog.UploadReports(Form parentForm, Session session)
at Ice.UI.App.DynamicReportDesigner.BAQRDesignerForm.UploadSsrsReport()
at Ice.UI.App.DynamicReportDesigner.BAQRDesignerForm.OnClickMiscTool(String ToolKey)
at Ice.Lib.Framework.EpiBaseForm.handleToolClickInternal(String tKey, ToolClickEventArgs ea)
at Ice.Lib.Framework.EpiBaseForm.handleToolClick(String tKey, ToolClickEventArgs ea)

[NateS (Nate Schoonover)]

This method actually worked! Kind of… It left huge blank gaps in my report where the rows were hidden. I turned on CanShrink but still have big blank sections where the rows are hidden.

[Alexandre_Pothier]

I’m not sure to understand, as the section should be hidden. You’ve put the condition on the tablix section or your fields?

[NateS (Nate Schoonover)]

First, I applied this criteria to the Hidden expression of the detail row in the tablix. This gave the big blank sections.
Then, I applied the same code to the Hidden expression of the tablix itself. This didn’t seem to filter at all.

=iif(First(Fields!Check01.Value, "BAQReportParameter"),IsNumeric(Fields!PartBin_BinNum.Value),false)

[NateS (Nate Schoonover)]

Aa ha!!! I had to add the criteria to the detail group Hidden expression. Just like you said! LOL!
That did the trick! Thank you all so much!