Report Checkbox Filter

I would like to add a checkbox to the Work in Process report that when unchecked will filter the results of the report based on JobType. I know I can customize the menu item using CallContextBpmData.CheckboxXX and pass that value to the RDL, but I am struggling with how I would filter the query based on a boolean value. Thoughts?

1 Like

An additional question. Today I was looking to make the RDD change to add the JobType field when I realized the WIPReport has a TWip data source that houses all the data and also seems to be created on the fly (no columns within the actual RDD and not a table in the DB). How can I join to it if there are no fields in the RDD to join on?

Why a checkbox and not a dropdown/list?

You can still join the table. Create a new relationship and when you use the dropdown, you will see the fields in the TWIP table.

I should have explained that originally. The checkbox will only be used to determine if JobType PRJ shows on the report, all other JobTypes will show regardless. I did figure out the RDD thing and I think I have a solution for the rest as well, just fiddling with the RDL dataset query because it seems to be unhappy at the moment.

Here is the query if anyone sees anything wrong with it before I do:

="SELECT T1.BurdenCOS,T1.BurdenCTI,T1.Company,T1.CompletedQty,T1.IUM,T1.JobNum,T1.JobType,T1.LaborCOS,T1.LaborCTI,T1.MaterialCOS,T1.MaterialCTI,T1.MtlBurCOS,T1.MtlBurCTI,T1.PartDescription,T1.PartNum,T1.Plant,T1.PlantDesc,T1.ProdCode,T1.ProdDesc,T1.ProdQty,T1.QtyShipped,T1.QtyToInventory, T1.QtyToJob,T1.RevisionNum,T1.SubContractCOS,T1.SubcontractCTI,T1.TDBurdenCost,T1.TDLaborCost,T1.TDMaterialCost,T1.TDMtlBurCost,T1.TDSubcontractCost,T1.RptLanguageID,T2.JobType
FROM TWip_" + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN JobHead_" + Parameters!TableGuid.Value + " T2 
ON T1.Company = T2.Company AND T1.JobNum = T2.JobNum"

Probably something like

="SELECT T1.BurdenCOS,T1.BurdenCTI,T1.Company,T1.CompletedQty,T1.IUM,T1.JobNum,T1.JobType,T1.LaborCOS,T1.LaborCTI,T1.MaterialCOS,T1.MaterialCTI,T1.MtlBurCOS,T1.MtlBurCTI,T1.PartDescription,T1.PartNum,T1.Plant,T1.PlantDesc,T1.ProdCode,T1.ProdDesc,T1.ProdQty,T1.QtyShipped,T1.QtyToInventory, T1.QtyToJob,T1.RevisionNum,T1.SubContractCOS,T1.SubcontractCTI,T1.TDBurdenCost,T1.TDLaborCost,T1.TDMaterialCost,T1.TDMtlBurCost,T1.TDSubcontractCost,T1.RptLanguageID,T2.JobType
FROM TWip_" + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN JobHead_" + Parameters!TableGuid.Value + " T2 
ON T1.Company = T2.Company AND T1.JobNum = T2.JobNum AND T1.JobType = IIF(T1.MyCheckBox = 1, "Value1", "Value2")"

Too early to be correct :smiley: its a start. I think if you sometimes need one or the other you use the Parameters on the SSRS Report, instead of the Query itself.

Take a look at a report under JobTraveler called Time Traveler which has a checkbox customized in your environment :wink:

We actually have something similar on the Job Pick List report that I was going to try once I can get the dataset query to be happy.