Our version of the report (we changed the format of some of the fields and added a calculated field) does not display anything for parts where there is none on hand. We would it to display 0 and then the calculated field would be able to do its calculation of how many are needed.
We may have created this problem when we changed the format to display only whole numbers:
Based on the solution to that issue, we changed the expression of the QOH field to =Fields!OnHandQty.Value.
To solve the current problem, I have tried the following, without results:
iif function to put in 0 if the field is null
sum function to add the value of the field + 0.
The most recent error message is this: The Value expression for the textrun ‘OnHandQty.Paragraphs.TextRuns’ contains an error: [BC30451] Name ‘isnull’ is not declared.
In an ideal world, I would l like to have 0 displayed if there are none on hand and would like to round everything to a whole number.
Thanks for any advice you may have.
If there is a place I can go to look up these sorts of things, point me toward it.
Maybe do the check isnull in the dataset query instead.
="SELECT T1.PartNum,T1.MtlSeq,T1.ComponentType,T1.Level,T1.ParentPart,T1.ParentMtlSeq,T1.[Description],T1.PartType,T1.RequiredQty,T1.UOM,T1.Revision,T1.Indentor,T1.DisplayOrder,T1.IsSubstitute, T2.ParentPartNum,T2.ParentCompType,T2.Whse,ISNULL(T2.OnHandQty,0) as OnHandQty ,T2.WhsUOM,T2.AvailableQty,T2.UOM as PartWhseBOMResAv_UOM
FROM PartBOMResAv_" + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN PartWhseBOMResAv_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.ParentPart = T2.ParentPartNum AND T1.ParentMtlSeq = T2.ParentMtlSeq AND T1.ComponentType = T2.ParentCompType AND T1.MtlSeq = T2.MtlSeq"
Hi Sam, if your OnHandQty field is available through dataset fields that you can find on the left side of SSRS report designer. You might just want to do a simple check. Something like iif(OnHandQty <=0, “0”, write a function to Round your number here). I am not sure if you need <= or = is enough for you. You can also refer to this to look up how to implement a Round function.
The issue you have is that when the qty on hand is 0, technically there is no record to look at, because there is no record in the PartBin table. Most formulas here do not work because it is not just a null, but it does not exist. I’m assuming this is where you are getting your QOH.
I created a calculated field in SSRS form with the following formula and it worked:
=iif(isnothing(Fields!PartBin_OnhandQty.Value), 0, Fields!PartBin_OnhandQty.Value)
Thanks Paul, this is the closest solution to what I want to do (basically, put in a 0 if there is no record). I did finally figure out (before seeing your note and Calvin’s) that problem was not 0, but that there was no record.
Epicor did not like your expression. Got the following message:
The Value expression for the text box ‘OnHandQty’ refers to the field ‘PartBin_OnhandQty’. 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.
Also tried PartBin_OnHandQty (with capital “H”), but got the same message.
It did accept OnHandQty, but still did not populate the field with a 0.
This whole exercise is for the user’s convenience; we change the report just a little, to show the amount of the shortage in the far right. So our users typically scan down the right side, but can miss a part that is listed on the left with no values on the right. They might just have to live with it.
Or I might have to figure out a BAQ report as suggested by Calvin.
The error you received was not because of the formula, but because the field location. SSRS and any other reporting software is pretty sensitive to “field context” or the data scope. Try placing the field where you currently have the on hand qty.
(I dont recall this report, but can take a look later on as to where the field should be)
I made changes to the standard BOMRes report and got it to work. The default report suppresses the rows when the qty is 0, so the first thing to do is to remove supression. The table it is pulling the total qty from is the PartWhse, so my formula had to change. I created a calc field and it worked. I then added a sum of that calc field to the totals row. See attached rdl. I’ve color coded the fields. Normally I don’t dig this far on a help site , but I was intrigued.BOMResAv.rdl (103.1 KB)