Checkbox display on Time Phase SSRS

Epicor ERP 10
, , , ,
1

This is probably a simple syntax issue but I am having some issues with a customization on the Time Phased Material Requirements report and am seeking help.

Background is we added a checkbox (Checkbox01.Part) to Part Maintenance for customer kanban parts and mapped it to our Time Phased Inquiry.

image

I then am trying to add a box on the TimePhase.rdl report that will display a simple Y/N on the report for each part depending on if the box is checked. I am have tried a number of different variations of the IIF expression and usually get an error about aggregate like “The Value expression for the textrun ‘Textbox38.Paragraphs[0].TextRuns[2]’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset.”

My hope is to have something to the effect of =IIF(Fields!Checkbox01.Value, “Part”) = True Then “Yes” Else “No”) but I am not sure what I am getting wrong to get it to display the correct state of the checkbox on the TimePhase.rdl

Any ideas would be greatly appreciated.

Thanks

2

Can you post some screen shots of your modified report and what you’ve done so far?

The syntax for the textbox expression would be =iif(Fields!Checkbox01.Value = True, “Yes”, “No”) if you haven’t tried that already (also depending on what you named the field).

3

The statement in SSRS would be written as

=IIF(Fields!Checkbox01.Value = TRUE , ”Yes, ”No”)

The first value “Yes” is return when the checkbox is true and “No” when it is false.

Scott

1 Like
4

Hi Scott,

When I try that statement, I get the following error:

The Value expression for the text box ‘Textbox38’ refers to the field ‘Checkbox01’. 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.

Everything else on the tablix pulls from TPhase so I was trying to reference the Part dataset in the statement, otherwise I get this error.

Thank you,
Liam

5

All I have done is taken the standard TimePhase SSRS and added one text box it pulling from Part

image

The Kanban: is plain text and the [Y/N] should be the statement referencing whether or not the checkbox (Checkbox01.Part) is checked or not.

Thanks,
Liam

6

I think we need to go back and first know if you added CheckBox01 to the RDD (I assume you are using an RDD) and then to the query in the SSRS report.

The Value expression for the text box ‘Textbox38’ refers to the field ‘Checkbox01’.
Report item expressions can only refer to fields within the current dataset scope

This leads me to believe that possibly it is not in the data being sent over to the report server from the RDD or that your query was not updated to pull in from
the report server. There are a number of posts on how to go through that process on this forum.

Scott

1 Like
7

Thanks again Scott, I will look into updating the query to pull from the report server.

I am using an RDD, which I added Part to and excluded columns other than Company, Checkbox01,
PartNum, RptLanguageID and SysRowID. I added a relationship with TPhse as the parent and Part as the child - relationship type Output. I have my custom SSRS report set to use this RDD. I am guessing I am missing the last step of updating the query.

8

Here’s a post that may help verify you’ve gone through all the required steps:

I added many screenshots outlining adding a new field to an SSRS report. There are many other posts on this site outlining it too.

9

Adam,

Thanks for providing this… I definitely didn’t add the field to the dataset expression and I think that is the part I am missing here, I did create the RDD and Report Style as described in that post.

I tried adding T2.Checkbox01 FROM Part_ to the end of the query as you have for LotNum in your posted example, but I get the error [BC30451] ‘Checkbox01’ is not declared. It may be inaccessible due to its protection level. I am wondering if the T2 is incorrect as I am not sure where that comes from. I will continue to search the forum for more examples of this issue.

Thanks

10

T2 refers to one of the tables in the query expression on that Packing Slip’s .rdl, so it won’t work in your case (until you modify the query a little further at least). Since you added the Part table to your RDD, you can now add it to your query expression (via a table join to T1 which is that TPhse table).

I haven’t made a new RDD to test this with, but a query similar this should work:

image
image973×208 15.1 KB

I highlighted what I added to the base report’s query expression.

="SELECT T1.BalanceQty,T1.DueDate,T1.ExceptionReason,T1.PartDescription,T1.PartNum,T1.ReceiptQty,T1.RequiredQty,T1.SourceName,T1.SugOrderDate,T1.Calc_DspJobStatus,T1.Calc_DspLeadTime,T1.Calc_DspMaximumQty,T1.Calc_DspMinimumQty,T1.Calc_DspMinOrderQty,T1.Calc_DspPartClass,T1.Calc_DspPartType,T1.Calc_DspSafetyQty,T1.Calc_DspContractID,T1.IUM,T1.ContractID,T1.SortByDate,T1.BuyForJob, T1.RequirementFlag, T1.SourceFile, T2.Checkbox01
 FROM Tphse_" + Parameters!TableGuid.Value + " T1 LEFT OUTER JOIN Part_" +  Parameters!TableGuid.Value + " T2 ON T1.Company = T2.Company AND T1.PartNum = T2.PartNum"

Part_" + Parameters!TableGuid.Value + " T2 declares your T2 table, which refers to the temporary Part table that gets created when the report is run. Notice the base report expression specifies T1 as the temporary TPhse table.

Then you’ll just need to add that new query field to store T2.Checkbox01 in:

image
image599×585 14.6 KB

THEN you should be able to use that field in that expression posted previously.

11

Thank you for your help… the issue was in the RDD. We had downloaded and uploaded the SSRS report, copying it and syncing the dataset after re-adding table relationships in the RDD and it works.

1 Like