Hi everyone,
Just wondering if somebody can help on this. We are trying to add Ship To information on Quote PDF printout.
The field does not reside in the QuoteHed Table, so was wondering how to call it to the form.
In SO form, it is there out of the box but not in Quotes.
Thanks
This is all from memory, so forgive me if I miss a few steps.
You will need to copy the report style for the Quote and reproduce a new style.
Report Style > Actions > Copy Report Style. From there, you have to update a few things under Styles > Style Detail > Detail. You can usually use the previous styles as a guide for how to fill in the fields. The important part here is that you need to give the Report Location something in a CustomReports folder. For me, the quote form custom folder report location is:
reports/CustomReports/QuoteForm/QuotForm
Once your new style is created, you have to update the dataset to include the fields you want. Go to Report Style > Actions > Download SSRS Report. Look for the downloaded SSRS (*.rdl)
Here you need to edit the dataset for Quote. On the left side look for the folder called Datasets, and inside the dataset called Quote. Right click and go to Dataset Properties. Go to the Fields category on the left side. Click Add and choose Query Field. The QuoteHed table includes the field ShipToNum. So, in the Field name and field source type in “ShipToNum” (without quotes).
Now the field is available to place on your report form. Insert a new field and choose ShipToNum as the source.
Save the SSRS and go back to Report Style. Make sure you have selected the style you want to update from the list on the left side. Then click Actions > Upload SSRS Report. Choose the folder where you saved your custom report. If this step uploads successfully, without errors, then you can go and run your quote form again. This time, make sure you update the style to choose the new style you just created.
Good luck!
Hi Nate,
Thanks for the detailed explanation… That helps a lot and worked.
Now I am trying to add the Ship to Adress and I can’t find the fields where they are stored.
Chose Help, Field Help, Technical Details and click on the field.
It’s the ShipTo table btw.
The DB field is empty.
If you don’t have a “One Time ShipTo” then it’s QuoteHed_ShipToNum linked to ShipTo Table
One Time ShipTo is
QuoteHed_UseOTS Use OTS yes/no
QuoteHed_OTSName Name x(50)
QuoteHed_OTSAddress1 Address x(50)
QuoteHed_OTSAddress2 OTSAddress2 x(50)
QuoteHed_OTSAddress3 OTSAddress3 x(50)
QuoteHed_OTSCity City x(50)
QuoteHed_OTSState State/Prov x(50)
QuoteHed_OTSZIP Postal Code x(10)
QuoteHed_OTSResaleID Tax ID x(20)
QuoteHed_OTSTaxRegionCode Tax Region x(4)
QuoteHed_OTSContact Contact x(50)
QuoteHed_OTSFaxNum Fax x(20)
QuoteHed_OTSPhoneNum Phone x(20)
QuoteHed_OTSCountryNum Country Number >>>>>>9
QuoteHed_OTSCustSaved OTSCustSaved yes/no
QuoteHed_OTSSaveAs OTSSaveAs x(2)
QuoteHed_OTSSaveCustID OTSSaveCustID x(10)
Hi Kevin,
I see the OTS but this case is not a One Time ship to but a solid ShipTo
See above
Hi Kevin,
Sorry I am a total newbie on the SSRS.
How do I call the Quote Ship to Address using ShiptoNum?
Thanks
Unfortunately I don’t do reports. My brain refuses to cooperate with the mess that is SSRS.
I believe most of the information was in the first post however. You’ll need to set up a relationship in the report definition between quoteHed.ShipToNum and the appropriate field in the ShipToTable.
I kinda gave you the easy version. If the field is already part of the tables included in the report, then it is pretty trivial to bring them into the report as you found out with ShipToNum. What you really want is to link the ShipTo table to the QuoteHed, using that ShipToNum. For this you need to edit the report data definitions (RDD).
Honestly, I haven’t had a lot of luck with this in the past, but I believe this is the process.
Go to Report Data Definition. Lookup the QuotForm report. This should show the entire dataset responsible for feeding that SSRS report. On the left you can see all the tables, fields and relationships.
I think you need to add a relationship here that relates QuoteHed to ShipTo using ShipToNum. I think if you get that setup, you should be able to add fields from ShipTo, like the actual ship to address you need.
Once you add those fields to the report data definition, you have to relink it to the SSRS. In Report Style, resynchronize your dataset, then download the report to your computer to edit it to plop in those new ship to address fields.
Someone else may point out a few mistakes in these instructions. As I said I haven’t had much luck doing this in the past. I think you need to duplicate the report data definitions to even work on them. Good luck!
To summarize what Nate said: You’ll need to edit the “QuotForm” RDD to add the ShipTo table and relationships (Company, CustNum, ShipToNum) and then edit the RDL (SSRS Report) query to include the ShipTo fields you want displayed on the report then finally add the fields to the report.
Thanks everyone …
Got it now.
Have you got any guidance on how you amended the SSRS data source by any chance? I’ve built the relationships for this exact scenario that I’m also trying to do, but can’t quite work out how to amend the dataset in Report Builder to populate correctly.
If you’ve added a Relationship in the RDD (joining two tables)… you need to also create that relationship join in SSRS (Report Builder).
I’ll try to walk through it. Others can jump in if I miss anything…
In Report Builder, we need to recreate this new relationship.
The Expression window may look pretty messy, but you can add some carriage returns to clean things up and make it more readable. (See second image below)
Anything after “FROM” is where the tables are joined together for the query.
So… in this case you would want to add a new join… put your cursor just before the quotation mark at the very end… add a SPACE… then click “Enter” to start a new line. If you don’t add that space, your query may fail as it may treat it like a big run-on sentence. I’ve lost a lot of time thinking my query was wrong and finally realizing that I didn’t have a space there.
Here is the relationship join in the RDD:
We need to add this to the Report Builder query… Copy and Paste one of the existing joins… and then we will alter… So I copy/pasted the last join… and now have this:
Below is the altered query (changes highlighted in red). In English…
We’re adding the ShipTo table… and we’re going to call it “T4”
“T1” is already the designation for the QuoteHed table… so, just like we did in the RDD, we want to build the relationship between “T1” (QuoteHed), and our new “T4” (ShipTo)
We’re joining this table “ON” T1.Company = T4.Company AND T1.CustNum = T4.CustNum AND T1.ShipToNum = T4.ShipToNum
… again, these are the same field relationships we used in the RDD.
This now completes joining the new table to your query (in Report Builder).
The next thing you need to do is add whatever fields you want to use in your report.
In the RDD (sorry for all the bouncing around)… make sure you include whatever “columns” you want to be able to use in your report. Highlight the SHIPTO table you added as a data source… go to the “Exclusions” tab and make sure you UNCHECK fields you want to add to your report. I will generally use the Actions menu and “Include all Columns”. I don’t know if this is true or not… but I was told adding all columns will not greatly impact speed of the report… but including all LABELS will. Again, not sure if that’s true or not, but that’s how I roll.
So… if you want to include the ShipTo “Name” and “PhoneNum” for example, make sure those are UNCHECKED in the RDD… and then add those to your Report Builder Query.
Back to Report Builder… I generally do this right at the top of the query so all my “added” columns are easy to find later:
Again, “T4” is the designation for the ShipTo table, and we’re adding “Name” and “PhoneNum”. I will generally use an “AS” statement for these types of fields because “Name” and “PhoneNum” can be used on multiple tables within Epicor… so in this case, I’m adding T4.Name AS ShipToName.
That should do it for your query set up… but we still need to add the “Fields” so you can use them in your report.
Click “OK” to close the query expression.
With the Dataset Properties window still open… click on “Fields” on the left hand side.
We want to “Add” and “Query Field”…
Add the fields here that you just added to the query expression. If you used as “AS” statement… that’s what you’ll call them:
Click OK
SAVE
Now you can add those fields in your SSRS report… save… upload… test.
Perfect, thank you so much. That’s exactly the part I was struggling to do. Will give that a shot and do some testing 
Ok finally got it to a stage where it will generate a pdf, but the added fields don’t show up on the report. Zero errors, just blank fields.
I’ve overcome RDD issues, formatting issues, and am now stuck on this tiny bit! Any pointers on where I need to be looking to troubleshoot?
Really appreciate the advice so far, gotten me so much further than I was before!
Have you checked the data that Epicor is exporting to the SSRS GUID? If the selected fields are blank in the raw data then it could be an error in setting up the RDD.
This gets tough to diagnose without seeing “everything”.
Can you paste in a screen shot of your SSRS report with the fields you’ve added?
