Trying to use a filter in an SSRS report

Epicor ERP 10
, ,
1

I have copied a built-in report style and am trying to filter records on a boolean field but getting an empty report.

The report is Advanced Billing/Deposit Balance and I have been asked to filter out records with DeferredRevenue not ticked so I have added InvcHead to a copy of the RDD and linked it with a relationship to OrderDtl on Company and OrderNum.
I have un-Excluded the fields.

I have then edited the Dataset in the SSRS and added a join for the two tables, declared the DeferredRevenue field and then added a Bollean filter for Deferred Revenue = True.

Can anyone see anything I have missed? cheers.

image
image487Ă—161 3.5 KB

2

Really quickly, if you remove the filter, does the report run as expected, that will rule out an issue with the RDD/RDL.

Edit to add:
Also, if you add that field into the report, do you see records true/false as expected?

1 Like
3

Thanks Leonard, I removed the filter and the report ran as expected.

Tried adding it into the report but it does not appear

4

If the field isn’t showing then that would point to either an RDD or RDL issue, possibly with a join on one or both.

Also, just to double check, you un-excluded all of the fields in the Join (presumably Company, OrderNum, OrderLine, OrderRel on both sides)?

5

All fields are un_excluded on both tables (OrderLine not present on InvcHead)

If I add a filter I got no report body at all, just the header and “End of Report”

I have shaded the box on the report which I placed the field and I just get a shaded box

6

Below is the Dataset: My addition is missing it seems, must have forgotten to save it

=“SELECT T1.Company,T1.IUM,T1.RptLanguageID,T1.LineDesc,T1.OpenLine,T1.OrderLine,T1.OrderNum,T1.OrderQty,T1.PartNum,T1.UnitPrice,T1.Calc_AdvDepBal,T1.Calc_Alloc,T1.Calc_DocAdvDepBal,T1.Calc_DspPricePerCode,T1.Calc_CustNum,T1.Calc_ExtPrice,T1.Calc_HeadNum,T1.Calc_LineType,T1.Calc_LegalNumber,T1.Calc_InvoiceNum,T1.Calc_DispAllocDeposits, T2.CustID,T2.Name,T2.Calc_CustTot,
T3.AllocAmt,T3.AllocBal,T3.AllocTaxBal,T3.DepHeadNum,T3.LegalNumber,T3.DepInvoiceNum,T3.PrePayType,T3.TaxAmt
FROM OrderDtl_” + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN Customer_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.Calc_CustNum = T2.CustNum
LEFT OUTER JOIN TempAllocDeposit_" + Parameters!TableGuid.Value + " T3
ON T1.Company = T3.Company AND T1.OrderNum = T3.OrderNum
"

7

OK working now, note to self - remember to save the pasted dataset after editing in Notepad!!!

2 Likes