Hello,
I am trying to add a few fields to the BOM Listing report:
PartPlant.LeadTime
Part.TypeCode
Vendor.Name
I tried adding PartPlant table to RDD and creating a relationship between it and PartMtl table.
Added to Report in Visual Studio and it is not working. All I get for LeadTime is a 0.
I can go into more detail on what I tried if needed.
Can anyone tell me where I am going wrong?
Thanks,
Shawn
Hey,
Anybody out there with any ideas? It has me stumped.
Thanks!
Shawn
Are they making it to the temp database?
Are they in your report query
Did you add them as query fields?
I added PartPlant table in RDD and made sure relevant fields were not excluded.
Created relationship to PartMtl:
I the visual Studio Report, I added
FULL OUTER JOIN PartPlant_" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company AND T2.Calc_PartNumIndentor = T3.PartNum"
and
SELECT T2.MtlPartNum,T3.VendorNum_Name AS VendorName,T3.Part_TypeCode AS PartTypeCode,T3.LeadTime,T2.PartNum_TypeCode AS TypeCode,
to the dataset PartRevPartMtl.
I created the Query fields to match and added to the report. Not getting the info like I expected.
Do you have access to a sql editor? When you create a report a temp table is created, which your report then calls.
ie PartPlant_" + Parameters!TableGuid.Value + "
Are you able to see those tables to confirm the field is there with the expected data?
I usually for get to add the field to the report with the add query option. The default is add (calculated). I should have mentioned this before.
I have access to SQL Management Studio.
I am not sure how to write the query. Can you give me a specific example?
Run the report archive for a day, this keeps the table there.
In Studio
Open the SSRS database find the table you are using in your report.
Sort the tables to find out the correct on with the GUID. Then do a basic query to select * from the table.
Hey,
Thank you for that tip! It showed me that I was only pulling in 1 row of data. I have to change the relationship fields it connected with from PartNum = PartNum to MtlPartNum = PartNum.
It then pulled in all the data I needed.
I really appreciate the time you spent educating me on this. It will definitely help in the future.
Thanks,
Shawn
You can also get the report GUID from the System Monitor. Do like Ken said, run the report with 1 day archive then go to the Reports tab and scroll all the way to the right. This tip is for those of you who can’t/won’t use SSMS.
Mark W.
I knew where to get the GUID in the monitor, just wasn’t sure where to find the temp tables and their info. Good to know that they are on the SSRS SQL server.
