When I first started modifying SSRS reports I had a field that I could not for the life of me figure out how it was ending up in the report. I eventually stumbled on the Link Tables functionality and was impressed with this black magic functionality.
2 Likes
Tried this David but at the step add Relationship Fields, DcdUserID is not present
1 Like
You will need to add the table USERFILE or USERFILELIST to have that column available.
- New Table
- Schema Table Button
- Userf > Search >
- Erp.UserFile
- Save > Refresh
- Unexclude DcdUserId from table report field exclusions list
- Save & now you can create the relationship.
1 Like
Yeah, as @CSmith noted… make sure you try the ERP UserFile… not the ICE UserFile (which shows up first in the list when adding a new table).
1 Like
Sorry Clint, DCDUserID does exist in UserFile but does not exist in POHeader although there is a field Calc_Buyer which maybe will work?
Also Company does not exist in UserFile.
I am trying to do bits of this in between my usual Sys Admin job interruptions which is frustrating as I get logged out of all open windows, especially as some of you are giving me real good help!
Looking at the PO because we are not dealing with a real transaction on our “third” env I can see the Purchasing Manager shows up as the person who entered the PO (as he is on the Project team and is creating test data) but the Buyer whose phone number I want on the PO Report is a different person.
This being the case I will probably have to wait on the custom field which I think will be getting created this week or next.
Use my example above. I did exactly what you’re trying to do and its been working for years now.
Add the Erp.UserFile table (which gives you DcdUserID). Add a relationship using EntryPerson from PO Header to DcdUserID.
I’ve already done this for my PO’s. Every step is included up above.
If you want to include “Company” in your relationship… you can join POHeader.Company to UserFile.CurComp.
@Briser , I would do what @CSmith said, linked it below. If you need more information about how to do this, let us know.
@CSmith , found out that you can no longer type in the linked field in Kinetic. It only allows you to select from the dropdown.
1 Like
Just an update on this, I now have a better understanding of the process and have used the custom field we have developed to display the Buyers phone number on the PO.
I created a custom field in the PerCon table and I then created a relationship in RDD to POHeader, unhid BuyerID and Company fields and then edited the fx for POHeader in the SSRS and did a Left outer join between the 2 tables and declared our custom field and created the Query Fields BuyerID and PhoneNum.
I then dragged the PhoneNum field to the canvas, zipped and uploaded the SSRS and test printed a PO which displays the Buyers Number at last.
Success! but wait, it seems to use Arial font and everything else uses Arial Unicode MS, and Arial Unicode MS is not selectable for my new item in MS Report Builder.
This means when the PO is printed the Buyers Phone number doesn’t quite lineup between the other items.
So next steps, I am now trying to figure out how to use Labels without just creating a textbox and typing something in there.
But at least I have made progress thanks in no small part to the help given so again, many thanks.
One thing to look at in terms of having things lined up (besides the font), assuming your text box is lined up with the other items which SSRS does help with, is the padding on the text box. IIRC the default is like 2pts on all sides which will adjust where it is sitting, if the other fields around it are not set to the same thing it will be off.
This is on of their fields where the padding is set to 0:
For me at least, when I add a text box into SSRS it defaults those to 2 instead. I suspect that will be enough to make it not line up properly.
As for the font question, again, IIRC just should just need to install the specific missing font on your local machine, I believe that Report Builder shows you the fonts you have installed on your local machine in that drop down, then when the report is rendered the font must also exist on the report server as well.
1 Like
Thanks Leonard, that has fixed the mis-alignment (I should have noticed that!).
It would seem that Arial Unicode MS used to be part of MS Office but was discontinued with Office365 which is a minor annoyance when typing text into a text box in MRB