Select ‘OrderLine’ in the ‘Pick Fields from …’ dropdown
Select the fields I want in the ‘Available’ pane, and add them to the ‘Picked’ pane
Aside from updating the RDL’s query to include this/these added fields, does anything else need to be done?
Is Adding a table only a last ditch effort when a Picked Link can’t be found?
Back to the need to update the RDL… what is the syntax of the new field(s)? If I added OrderComment and ProdLabelCount_c, would they be: ..., T1.OrderComment, T1.ProdLabelCount_c, ...
How do you know which table number (T1, T2, T3, etc …) when the RDL’s dataset property’s includes multiple?
I have used the pick links all the time with no ill effects that I know of. It is easier than adding tables and creating the relationships.
On the ARForm report, I found an example of how to name the pick link to access it.
T1.CurrencyCode_CurrencyID
T1 points to table that has the pick link (InvcHead for this example)
CurrencyCode = the pick link tab selection from RDD
CurrencyID = Pick field on the Description fields tab from RDD.
Note the underscore that connects the pick link to the pick field.
If you have any other questions, please reply and I will answer to the best of my ability.
On how to tell which t1, t2, etc to use. Below id at excerpt form the ARForm .
FROM InvcHead" + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN InvcDtl" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.InvoiceNum = T2.InvoiceNum
LEFT OUTER JOIN FSCallDt" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company AND T2.InvoiceLine = T3.Calc_InvoiceLine AND T2.InvoiceNum = T3.Calc_InvoiceNum
LEFT OUTER JOIN FSCallMt" + Parameters!TableGuid.Value + " T4
ON T3.Company = T4.Company AND T3.CallNum = T4.CallNum AND T3.Calc_fCallLine = T4.Calc_fCallLine AND T3.Calc_InvoiceLine = T4.Calc_InvoiceLine AND T3.Calc_InvoiceNum = T4.Calc_InvoiceNum
I put the T1, T2, etc in bold. Notice in each line that the T desgniations show, there is a table name listed.
I also put those table names in bold.
What the T1 does is give the SELECT statement a nickname for the table being referenced.
If you didn’t do that, then you would have to type out the table name before each field.
For the above example, you could have changed
T1.CurrencyCode_CurrencyID
to be
InvcHead.CurrencyCode_CurrencyID
and it would work the same.
In my version of ARForm, the table InvcDtl is labelled with the T2.
So if yours matched, you would do: T2.OrderLine_OrderComment, T2.OrderLine_ProdLabelCount_c
in the Dataset ARForm on the report.
And don’t forget to create the fields in the report also so the report can see your new additions.
*** TIP ****
To make your new fields a little more readable in the report you can give them an alias.
Example: T2.OrderLine_OrderComment AS OrderComment, T2.OrderLine_ProdLabelCount_c AS ProdLabelCount
Notice the AS - that tells it you are creating an alias name for the field.
Now in the fields creation, you reference the alias instead of the full field as the fields source.
You would then use OrderComment and ProdLabelCount as the Field sources.
Just curious … can you change the field name in the Fields tab of the dataset properties?
Does (2) below need to match the query expression, while (1) is what shows in the report builder?
Such that I could change (1) to OrderComment?
Number 1 can be anything you want. Number 2 has to match what you have it as in the dataset.
If you look at my previous post that has the tip in it, you will notice you can give the field an alias that you reference instead. If you do that, then both fields can be OrderComment instead of number 1 being: OrderComment and number 2 being: OrderLine_OrderComment
For what you are doing, either way works fine.
When you start creating complicated fields though, the alias is a must.
My example being, I have a long expression in the select statement that modifies the AddressList to something I like better. No way to put all of that in the field source so you give it a short alias like AddressList02 and that is what you reference.
