SSRS display 0 value

Epicor ERP 10
1

Report: BOMResAv (BOM Resource Availability).

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[0].TextRuns[0]’ 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.

Thanks again.

—sam

2

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"
3

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.

Let me know if you have any questions.

Alex

1 Like
4

Sam,

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)

5

Are you saying the the original version of the report DOES show info for parts with zero QOH?

And like @Paul_Mayevsky said, there won’t be a record if there are no part bin records (where the QOH is stored.

Note that you might see an item with zero QOH, when it is actually a net zero QOH. Like if Bin A has 10, and Bin B has -10, that part would show, with a QOH of 0.

6

No, the original version also does not display anything; I checked that after I had written the first post.

7

Then you’d need to tweak the RDD. Probably better off making a new BAQ report - unless there is magic in that other one that you don’t understand.

8

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.

Thanks again.

9

Have not yet dabbled in BAQ reports. This might be a good time to start. Was hoping the solution to this would be easier.

Thanks.

10

This is a helpful site. Thanks for the reference.

11

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)

12

Sam,

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 :slight_smile: , but I was intrigued.BOMResAv.rdl (103.1 KB)