I am trying to create a custom Quote SSRS document. We have added a UD table as a child to QuoteDtl and I need to pull that information into our quote.
I have read many times on this forum to not use the “Sync Dataset” button on Report Style. Without hitting the “Sync Dataset” button, the fields (that are not excluded on the RDD) from the added table do not show up in the Datsets list on Report Builder. I suspect I am missing a step (or I need to log off and back on at some point), but here is what I have done (without logging off and on at any point):
Opened Report Data Definition, opened QuotForm (not a typo on my part, the system report is spelled this way), Actions - Duplicate Report. Added New Table (my UD01). Clicked on my new table (in the tree view) then went to Data Sources, Report Table, Exclusion, and unchecked the fields I wanted to use on my form (about 10 of them).
I am not sure how report calculated fields work, or if they are necessary (I am just pulling in data fields and do not need to make any calculations from the information). Perhaps this was a step I missed.
Opened Report Style and found the standard QuotForm. Clicked on Standard SSRS in tree view, then Action - Copy Report Style. On my new report Style, I change the name, and select my customized Report Data Definition as the “Data Definition” on the Report Style - Styles, Style Detail - Details tab.
Then in Report Style, went to Action - Download SSRS Report. When I open the QuotForm.rdl file, I could not see the fields I want from my UD01 so that I can update my SSRS Quote Form.
Did I miss some steps? If I click on “Sync Data” I can then see the fields, but odd things happen to the reports (like -Quantity Breaks - show multiple lines of repeated data) and my UD table repeats the data also (it seems like it repeats all line of UD01 data the same number of line items on the quote). Any hints or tips would be great. The SSRS embedded education course did not seem very detailed.
Thank you @josecgomez. I have started over many times with this process. So what is the trick to get the datasets from my added table to appear in the available options on Report Builder?
Since you are adding a child to QuoteDtl is the relationship there one to one (one child per line) or one to many?
If it is one to many you will have to probably create a subreport and embed that to get the data it will require you to create a new sub report with a new dataset and pass in the correct filters to narrow down the data to just the related records.
The relationship from QuoteDtl to UD01 is one to many. I had this working partially before, but I forgot what I did. I may have created Calculated Fields (but I don’t remember). I know I did not create a subreport. I just used a table in Report Builder and it brought in my data. When I do the relationship in the RDD, I am selecting the Relationship Fields to only bring in the correct data from UD01.
When you say Start Over, does that mean to create a new RDD or just a new Report Style?
I actually have two custom report styles for Quote (one for Parts and one for Equipment). In my deleting and copying of multiple Report Styles, I get a Modified Custom Reports Popup. It seems that if I select Overwrite, it will overwrite for both of my custom styles (I think public cloud forces their report locations). I have not modified any of the subreports (not intentionally anyway).
In the Report Builder, right click the datasource and select properties. There you have access to the entire query. You’ll have to add fields to the select portion, and possibly add a join clause to include the new table(s).
Then you’ll need to add the Query fields so they show up in the tree on the left. This is also done in the datasource properties, under the fields tab (I think?)
Brad, because the “Sync Dataset” button is a bad option (because it breaks things), I did what Calvin recommended. I spent 12 hours on a Saturday to figure this out.
Watch this video by @DaveOlender . I found some helpful hints here:
Initially I did make the mistake of hitting the “Sync Dataset” button. I saved a copy of the .RDL file from the “Sync Dataset” version and It was helpful for the query formula and Fields (I was able to copy out, then edit and paste the items I needed into my NON-Sync Dataset version). I did this with Notepad (as shown in Dave’s video) rather than in Report Builder because I had a large number of UD fields from existing tables and I added another UD table to the Report Data Definition (so the copy, edit, and paste option was better for me). To make the Notepad format easier to read pay attention to Dave’s video at 31 minutes in.
@josecgomez recommended that I start over if I had hit the Sync Dataset button, so I made a new Report Data Definition (I am not sure that was necessary) and a new Report Style.
The process @ckrusen explained is absolutely correct (and it is much more manual that I had expected). I was expecting that making a custom Report Data Definition (adding tables, un-excluding fields, creating relationships) would have made the fields available in the Report Styles that use that RDD, but they do not. The sync dataset button probably does that, but I guess it breaks other things.
One other tip for you. The Report Location on the Report Style is both for the Cloud location and the path on your local PC (when you download the SSRS Report). Once that path is created, you cannot change it (you have to delete that style and copy another one to use a different location). If you are making multiple versions of a report style (in my case I made 1 SSRS for Parts quotes and one for Machine quotes), make sure you change the Report Folder Name, or you will be writing over the other versions on the cloud when you Upload SSRS Report.
If I had known how to edit the .RDL file in Notepad, that I had to change the default Report Folder Name for multiple report styles, and that I could use the Sync Dataset (but only to copy of the bits of detail I needed), I would have saved about 10 hours out of the 12 hours I spent learning this.
As we all initially expected it to do.
I guess the original intent of “Synch Dataset” was to do exactly that. But as you probably saw in the RDL(after pressing Sync Dataset), that process is unable to simple add the new additions to the RDD’s output. It has to breakdown the RDD into individual tables(which appear as separate Datasets in the RDL), renaming fields where conflicts exist. And often fails at this.
