Ok, so I missed something. I created a new RDD for a copy and added a table. I have noticed when I add the table the fields for the added table do not show in the Exclusions. I do have my report relationship.
What did I miss??
I think you have to close it out and open it back up. Not 100% sure though.
1 Like
it is always those simple things that will bit usâŚ
1 Like
I wasnât seeing the fields I needed.
1 Like
if these are linked tables how do they show up in SSRS? I didnât see the field I needed form VendorCnt.
Sorry for the late response.
You will need to manually add the fields to the SSRS report in both the Query and the Field list.
The query field, since linked through the POHeader should be T1.VendorCnt_TheFieldNameYouWant.
2 Likes
If I add a table that was not linked; do I add it the same way in the query? I am working on my AR Form and added the OrderDtl and created a relationship with InvDtl. How do I see this in my data set?
Typically those links in the RDD are combined into a single dataset, with the field name being the tablename_field name
in the example above T1.VendorCnt_FaxNum
is from the FaxNum field of the linked VendorCnt table.
But the table I added is not in a linked table. so how do you get them in the data set? So then I looked at the InvcDtl and I see OrderLine under linked tables⌠OrderLine is not a table?? Ok so I will roll with it and I put T2.OrderLine_Character02 fails on dataset error. T2 is InvcDtl and I am guessing that the OrderLine is suppose to linked to OrderDtl�?
That is what I added. the table OrderDtl to the RDD (I had to do that in E9) and created a relationship InvcDtl to OrderDtl. I didnât know how to get the Order Dtl into the dataset.
Do I have to add the ORderDtl as Primary Table??
I canât see anyting in my dataset for OrderDtl tableâŚI am missing a step.
Yout sent me some great instructions before and it worked for the other items but when I add a new table I donât know hot to get if into the data set.
Most of those twenty some tables get lumped into a single dataset (named âARFormâ I believe). Try adding to the query expression, and query fields, using
T1.OrderDtl_ReqQty
(or whatever field youâre using from OrderDtl, in place of ReqQty)
edit:
Prior to changing the SSRS RDL, make sure the report runs. This will check to make sure your RDD isnât broke.
Ok I added it a couple ways and got error messages.
1> Runs fine before changes to query
Added the following:
I get this errorâŚ
If i take it out of the Query it runs fine but of course I donât get my dataâŚLOL
I think this may be a rare case when using âSyncDatasetâ (in Report Style Maintenance) may be in order. BUT BEFORE YOU DO!!! make a copy of the SSRS RDL âfileâ specified in the Repost Style Maint.
I made an RDD âARForm-tstâ As:
- A scopy of the built-in RDD ARForm,
- Added table OrderDtl
- Added Table Relationship of InvcDtl to OrderDtl
- Relationship Type: Output
- Related fields: Company, OrderNum, OrderLine
- Excluded all columns and labels on table OrderDtl, except: Company, OrderNum, OrderLine, and OrderComment
So I should be able to pull in the OrderDtl.OrderComment field into the ARForm.
The Sync Dataset will totally change the data structure of the report.
Here is RDL ARForm-Test ( a copy of the built-in ARForm). Notice just 3 tables in the dataset
Here it is after Data Sync. Notice all the new tables in the dataset.
The query expression (for âtableâ ARForm in the dataset after the Sync), now references 12 tables and ends with
LEFT OUTER JOIN OrderDtl_" + Parameters!TableGuid.Value + " T12 ON T2.Company = T12.Company AND T2.OrderNum = T12.OrderNum AND T2.OrderLine = T12.OrderLine
Up in the SELECT part of the query expression, you find
..., T11.MiscCode_Description, T12.OrderComment, ...
I usually get bad results when I use Sync Data, but looks like you may have to go that route here.
Or⌠Figure out how to edit the un-synced RDLâs query expression and added fields.
edit:
The Sync Data did create the Query field too:
I didnât expect it to pull in the UD fields from OrderDtl_UD (the four fields by the â?â )
How did you get OrderLine to be in the Pick Fields for this link dorpdown? Mine only shows:
Never mind⌠I had the wrong table selected
I should have had the InvcDtl table selected, the chose OrderLine from the dropdown, then
I tried that too but still no luck. SO I do see the TrackingNumber field listed in the view and table under my SSRS folder in SQL.
