Good Morning,
I am new to editing SSRS reports. I have a minor modification to make to the BOM resource availability report (i.e., just called Availability under engrg/reports). I want to add the warehouse description, as the current standard report shows the warehouse code and that does not provide the detail we desire.
SO… I revised the RDD to add the warehouse table and tied it to the PartWhseBOMResAV data source per below. The relationship works because i have sent data out using warehouse address1 field and see it. Where my problem occurs is that I need the Description of the warehouse from the warehouse table.
No matter what I do, I cannot get this field into the downloaded rdl. I see the description field for the parts, but it will not give me the description for the warehouse. It is as if the RDD will not send out a field that is called the same thing as one in there for another data source.
Has anyone seen anything like this and have any “tricks” for getting the fields out from the RDD into the modified SSRS report as needed?
This is a copy of a system RDD modified to add another table/field. Is there a way to see a query of this? I’m not sure how to do that. If there is, would you please let me know how to do so?
For the semi-helpful online Help I referred to, search the help for “Add a Table Field to the Sales Order Pick List Copy”
The following is for their example of adding to the SOPickList report
In Report Builder, click the Report Builder button, click Open, and then navigate to the SalesOrderPickListNew folder.
Select SOPickL and click Open.
In the Report Data pane, expand Datasets, right-click the OrderHed dataset and select Dataset Properties.
In the Dataset Properties dialog box, verify that Query is selected on the left, and then click the fx button under Query on the right. The Expression dialog box opens.
Add the OrderDate field to the expression:
5a. Resize the dialog box to make the expression (SQL query) in Set expression for:
Query.Command.Text easier for you to read.
5b. In the expression, find T1.Calc_HeadComment, and immediately following that field type T1.OrderDate, (be sure to include the ending comma).
In this expression, T1 is the alias for OrderHed table.
5c. Click OK to save your change and close the Expression dialog box.
Back at the Dataset Properties dialog box, add OrderDate to the dataset:
6a. Select Fields on the left.
6b. On the right, click Add to create a new row at the bottom of the field list.
6c. In the new row, type OrderDate for both Field Name and Field Source.
6d. Click OK to add the new field and close the Dataset Properties dialog box.
In the Report Data pane, OrderDate is added to the list of fields under the OrderHed dataset. The field, by default, has been added as a user defined type. This detail of the field definition is not accessible from this location. Next, you will edit SOPickL.rdl to change the field definition to a string type.
Is this the manual process for updating the SSRS dataset? What version are you using? I will keep this in mind. I haven’t had it not work yet…but I am guessing my time will be running short.
Oh, look at that. This is a good thing to know how to do, thanks Brad. I do not see the T3.[Description] which is warehouse desc in there but do see the T1.[Description], which is part desc. That’s so strange because it is definitely in the RDD unchecked from excluded IDK why the heck Epicor dropped it in the interim
=“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,T2.OnHandQty,T2.WhsUOM,T2.AvailableQty,T2.UOM as PartWhseBOMResAv_UOM
, T1.[AltMethod], T1.[Company], T3.[Plant], T3.[WarehouseCode]
, T3.[Address1]
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
LEFT OUTER JOIN Warehse_" + Parameters!TableGuid.Value + " T3 ON T2.[Whse] = T3.[WarehouseCode]
"
So… if I change this in the RDL file then maybe upload via Report Style, think it’ll fix it up?
As per @ckrusen and the documentation he copy/pasted, when you make a change to the RDD to show additional fields, you need to manually make the change in the .rdl file in Report Builder. This involves adding the field to the query and then adding it to the fields.
Yes (thank you too @ckrusen :)), I am trying to follow the instructions but the addition of the field chokes because it is the same name “description” as one in there already for the part, T1.
So I tried to give it an alias in the SQL T3.[Description] AS WHDesc. I see it in the list to add now and added it to the report. However, during print preview on the uploaded RDL, it doesn’t give me any data in the WHDesc field.
At first the addition of Add1 did not work, but I had seen it work playing with it yesterday, so looking further into that, I realized when I messed with the SQL I had gotten an extra space in here Parameters!TableGuid.Value + " T3 ON T2.[Whse] = T3.[WarehouseCode] before the T2. Yikes!
Wow having to go in and directly edit this is pretty nasty especially since there’s not much intellisense working in there. No wonder I’ve dragged my feet on getting these little SSRS tweaks fixed
Thanks very much for your patient help! Without the E10 help group I’d be slogging for many more days with this sort of thing.
The fact that the query expression is built by combining text strings and variables, makes editing them a nightmare.
For example: the line:
LEFT OUTER JOIN JobBin_" + Parameters!TableGuid.Value + " T3
looks like it contains the string " + Parameters!TableGuid.Value + ", but actually contains:
The second half of a string ...LEFT OUTER JOIN JobBin_"
The variable Parameters!TableGuid.Value
The first half of a string "T3 ...
If the Expression editor had syntax highlighting look how much more readable it would be:
="SELECT T1.AssemblySeq,T1.[Description],T1.DueDate,T1.IssuedQty,T1.IUM,T1.JobNum, <snipped for brevity>
FROM JobAsmbl_" + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN JobMtl_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.JobNum = T2.JobNum AND T1.AssemblySeq = T2.Calc_AsmSeq
LEFT OUTER JOIN JobBin_" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company AND T2.JobNum = T3.JobNum AND T2.Calc_AsmSeq = T3.AsmSeq AND T2.Calc_MtlSeq = T3.MtlSeq AND T2.PartNum = T3.PartNum"
