Field error

Will79 · April 5, 2018, 7:58pm

When trying to print job traveler, I am getting an field error for Calc_Comment field.
The expression references the field ‘Calc_comment’, which does not exist in the Fields collection. 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. —> Microsoft.ReportingServices.Diagnostics.Utilities.RSException:

So I went to look for this field and found this query in the SSRS.
“SELECT T1.JobID_c,T1.JobCode,T1.OverUnder_c,T1.CreateDate,T1.CreatedBy,T1.JobCSR,T1.Calc_CustName,T1.DueDate,T1.IUM,T1.ProdQty,T1.ReqDueDate,T1.StartDate,T1.Calc_MultiJobParts,T1.Calc_OrderQty,
T1.Calc_StockQty,T2.AssemblySeq,T2.BomSequence,T2.Company,T2.[Description],T2.DrawNum,T2.IUM as JobAsmbl_IUM,T2.JobNum,T2.OverRunQty,T2.PartNum,T2.PullQty,T2.RequiredQty,T2.RevisionNum,T2.Calc_BCAsmSeq,
T2.Calc_BCJobNum,T2.Calc_BCPartNum,T2.Calc_BCRevNum,T2.Calc_comment,T3.RevShortDesc,T4.CommentText,T4.DaysOut,T4.DueDate as JobOper_DueDate,T4.EstProdHours,T4.EstSetHours,T4.Machines,T4.OpCode,T4.OpDesc,
T4.OprSeq,T4.PrimaryProdOpDtl,T4.PrimarySetupOpDtl,T4.ProdStandard,T4.RunQty,T4.StartDate as JobOper_StartDate,T4.StdFormat,T4.Calc_BCOpCode,T4.Calc_BCOprSeq,T4.Calc_DispStatus,T4.Calc_OPText,
T4.Calc_OPType,T4.Calc_PurPoint,T4.Calc_VendorId,T4.Calc_VendorName,T4.OpCode_OpDesc,T5.CapabilityID,T5.ConcurrentCapacity,T5.DailyProdRate,T5.OpDtlSeq,T5.ProdCrewSize,T5.ResourceGrpID,T5.ResourceID,
T5.SetUpCrewSize,T5.SetupOrProd,T5.Calc_BCCapabilityID,T5.Calc_BCResGrpID,T5.Calc_BCResourceID,T5.Calc_CapbltyDesc,T5.Calc_ResDesc,T5.Calc_ResGrpDesc,T5.Calc_SchedResDesc,T5.Calc_SchedResGrpDesc,T5.Calc_SchedResGrpID,
T5.Calc_SchedResID,T6.ResourceGrpID as ResourceTimeUsed_ResourceGrpID,T6.ResourceID as ResourceTimeUsed_ResourceID,T6.WhatIf FROM
JobHead_” + Parameters!TableGuid.Value + " T1 LEFT OUTER JOIN JobAsmbl_" + Parameters!TableGuid.Value + " T2 ON T1.Company = T2.Company AND T1.JobNum = T2.JobNum LEFT OUTER JOIN PartRev_" + Parameters!TableGuid.Value + " T3 ON T2.Company = T3.Company AND T2.PartNum = T3.PartNum AND T2.RevisionNum = T3.RevisionNum LEFT OUTER JOIN JobOper_" + Parameters!TableGuid.Value + " T4 ON T2.Company = T4.Company AND T2.JobNum = T4.JobNum AND T2.AssemblySeq = T4.AssemblySeq LEFT OUTER JOIN JobOpDtl_" + Parameters!TableGuid.Value + " T5 ON T4.Company = T5.Company AND T4.JobNum = T5.JobNum AND T4.AssemblySeq = T5.AssemblySeq AND T4.OprSeq = T5.OprSeq LEFT OUTER JOIN ResourceTimeUsed_" + Parameters!TableGuid.Value + " T6 ON T5.Company = T6.Company AND T5.JobNum = T6.JobNum AND T5.AssemblySeq = T6.AssemblySeq AND T5.OprSeq = T6.OprSeq AND T5.OpDtlSeq = T6.OpDtlSeq"

I figured out that Calc_Comment is T2, but I can not figure out what T2 is or how to find the culprit for this issue.

Aaron_Moreng · April 5, 2018, 8:01pm

T2 is JobAsmbl.

Will79 · April 5, 2018, 8:03pm

So waht do you think the error is referring to? I have hardly any comments and not sure what case it would need to be.

Aaron_Moreng · April 5, 2018, 8:04pm

See where it says T2.[Description]? That’s where I think the issue is.

Will79 · April 5, 2018, 8:07pm

I see it. Not follwoing. Sorry my skills iin this area needs a lot of work. Working on it but still not up to speed. So this would have nothing to do with it?

Aaron_Moreng · April 5, 2018, 8:08pm

I see the thing now, let me form a response

So you added the field to the report data definition. You might want to use the database field name as the name, not just “comment”. I don’t know if this is required, but it sure makes it easier to track. Make sure that the Report Exclusions on that table include the column you are trying to grab. I’m not sure why this needs to be a calculated field, but again, I’m still learning this stuff so bear with me.

Once it’s added, you will save and then go to modify the report.

In the report, select the data set. In the data set, there is a query expression editor. Click that.

In there, there will be a SQL statement that runs against your reporting database. That is where the select statement lives, and where you will want to select the field you are trying to grab. I don’t know if this is needed for a calculated field though…You might also try clicking the “Refresh Fields” button. Careful with this one, especially if you aren’t comfortable with SQL.

Also, you probably need to map your Data Set fields by right clicking on the data set and selecting “Add Calculated Field”. I noticed this is where you go to create the field objects in the report. Pay attention to the two options when you hold down the Add button. One is for Calculated field, in which you define the formula for the field OR a query field, wherein it should match the field pulled back from the query.

Will79 · April 5, 2018, 8:27pm

Well, i found the hidden tablix calc_Comment. It was a row visibility in SSRS set to Fields!Calc_comment.Value="". So, If I am reading the erro right it is because of that field not existing?

Expression references the field ‘Calc_comment’, which does not exist in the Fields collection. 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.

Aaron_Moreng · April 5, 2018, 8:29pm

The field collection is the results set from the dataset query. I think it needs to be referenced like ‘T2.Calc_comment’ instead of ‘T2.[Description]’ in order to work.

Will79 · April 5, 2018, 8:38pm

Gotcha. FYI, this is not an report I wrote, this is the job traveler that was created before I started. And the report is base. But I do see your point and will check it out.