So what’s the deal with the Synch Dataset button in the Report Style Maintenance program?
If I make copies of Epicor supplied RDD and SSRS report, use them as a new style, and then use Synch Dataset, it FUBAR’s the report. Dataset structure changes, field names get changed, dataset queries break, etc …
Here’s my test…
Launch Report Data Definition, and load RDD “PackSlip”
Use Actions\Duplicate Report, naming the new one “PackSlip_copy”
Close Report Data Definition window
Open SSRS report “reports/PackingSlip/PackSlip”, and save as “reports/CustomReports/PackingSlip/PackSlip_copy”
Launch Report Style Maint, and load form PackSlip
Create a new Style identical to the built-in on but with PackSlip_copy for the RDD, and PackSlipCopy as the RDL
Test the new style. it Previews A-OK.
Back in SQL Server Report builder, examine the Datasets for PackSlip_copy. The Datasets consists of 4 “tables”
Close SQL Server Report builder
In Report Styles, with the new style selected, click Synch Dataset
Test PackSlip style Copy. It fails. The error details show:
Query execution failed for dataset 'ShipCOO'. ---> System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'Primary'.
Because ‘Primary’ is reserved word in SQL.
Open RDL PackSlip_copy with SQL Server Report Builder, and examine the dataset. You’ll see a much different structure now. Everything below the red line is new.
Examine the properties of Dataset ShipCOO, and edit the query to fix the conflicting field name ‘Primary’, by enclosing it in brackets. “T1.Primary” → “T1.[Primary]”
Save report, test again, find a new conflicting field name (ShipDtlTax.Percent), fix that in the Report’s dataset query for ShipDtlTax. Fix is the same, enclose name in brackets.
Then it finally works. And I haven’t even modified the RDD yet!
I add a table and relationships to RDD “PackSlip_copy”, and Synch Dataset (with the report closed in SQL Report Builder).
I open the report in SQL Report Builder, and examine the Datasets. The added table does not appear.
Should Synch Datasets only be used for User created BAQ Reports?
That has been my experience as well. Works good with BAQ Reports (So long as you do not have any Union Sub-Queries…), but causes more grief than manually modifying the Query and DataSet fields after making changes to the RDD.
I’m convinced the Synch Dataset feature was built as a way for Epicor to internally weed out the good reporting developers from the bad…train the new staff to use synch dataset and only keep the ones that can figure out what it did and how to fix it. You are clearly passing the test Calvin.
Adding a table (and its relations) adds that table’s fields to the “main” table’s underlying query used in the dataset (“ShipHeadShipDtl” in the PackSlip RDD).
I only need to add the fields to the SELECT clause in the dataset’s query. No need to add any joins in the query, as the pseudo table “ShipHeadShipDtl” includes the ability to reference the added tables. (Is this considered a view?)
One question …
The fields of added tables are usually added to the SELECT clause by using “T1.fieldname”
If I add (2) UD tables to the RDD, how do I specify which table the field is from? As in, how does it know which UD table T1.ShortChar01 would be slected from?
But when I add a table and DON’T use the Synch Dataset, I thought it just adds the added table to the “view” that is typically represented as T1 in the report’s dataset query def…
This is why you can just add the fields from the newly added table by including "T1.fieldFromAddedTable, to the SELECT clause of the query definition in the report.
JD - Wasn’t ignoring you. Just trying to figure out how to phrase my response…
My real need is to understand how the RDD works with the RDL in SSRS. Let’s forget about “Synch Data” and pretend that doesn’t exist.
The PackSlip RDD has 25 tables, and 7 Relationships. (one of the tables is OrderHed)
The PackSlip RDL dataset shows 4 tables (ShipHeadShipDtl, Company, RptParameter, and MiscCharges).
The FROM clauses in the PackSlip RDL ShipHedShipDtl query’s expression is:
FROM ShipHead_123456789abcdef T1
LEFT OUTER JOIN ShipDtl_123456789abcdef T2
ON T1.Company = T2.Company AND T1.PackNum = T2.PackNum
LEFT OUTER JOIN RptLabels_123456789abcdef T3
ON T1.RptLanguageID = T3.RptLanguageID
If I want to add the field OrderHed.ProjMananger_c to the RDL, Do I just tack on ", T1.ProjManager_c " to the Select clause?
Or do I have to add A JOIN for the OrderHed table by adding
, T4.ProjManager_c
to the SELECT clause and
LEFT OUTER JOIN OrderHed_123456789abcdef T4
ON T2.OrderNum = T4.OrderNum
to the FROM clause
Then add ProjManager_c to the dataset’s Query Fields. (Doing this regardless of which method above i do.)
As you stated above. Define the Join to the OrderHed table referenced as T4, add T4.ProjManager_c to the select statement add OrderNum_ProjManager_c to the Field List.
or
Use Linked Tables to pull the OrderHed fields you want from a table that is already defined in the Query statement in the report. For OrderNum, this would be the ShipDtl table (T2).
Since the ShipDtl join is already defined as T2 in the Query statement of the RDL file, add the Link Tables fields to the Select statement… T2.OrderNum_ProjManager_c
And then add OrderNum_ProjManager_c to the Field List.
The 2nd way seems to be what Epicor intended. I feel that just adding fields in the RDL is “cleaner” than adding tables via joins too.
The PackSlip RDD already includes the table OrderHed (although it appears to have no table relationships). And the OrderNum was already in the Picked Links. So I all have to do in the RDD is make sure the fields are in the Description Fields.
Just curious …
Since the OrderHed table was already included and the linked to the ShipDtl, why can’t I just make sure the OrderHed fields I want are not marked as Excluded?
Or am I misinterpreting what the Report Table Exclusion list means?
You are correct. Unmark the Excluded checkbox for any additional field you want, add the field to the Select statement in the Query and add to the Field List in the RDL.
Edit…
This works for table/fields that are already defined in the RDL Query, in this case the ShipHead and ShipDtl tables. For Linked Tables, you will need to place the fields you want into the Picked column, then add to the Select statement and Field List in the RDL.
