Hi, I have a QuotForm in Crystal which I’m trying to customize to SSRS. I have made a copy of the RDD dataset and saved same in SSRS format. I also made a copy of the standard SSRS report in Report Style Maintenance. I have synced the copy style report to the RDD dataset. The problem is that when I download and open the report in report builder, some tables in the RDD dataset are missing. Like the QuoteDtl table is in the RDD dataset but it is not in the report builder dataset. Someone please advise before this drives me crazy. Thanks in advance.
Sync Dataset is not 100% functional yet. It works great for BAQReports, but not built in Reports. The issue you have is likely that the QuoteDtl table was not linked with a Relationship in the RDD.
I would highly recommend that you start over and get things going from the original logic.
- In Report Style click Copy Report Style and start with a clean RDL file and the original RDD.
- In Report Data Definition, Click Duplicate Report and add your tables,exclude/show your fields, and make the Relationships.
- In the Report Style, change the RDD to your new RDD
- Download the RDL files from Report Style by clicking Actions > Download SSRS Report
- Modify your SSRS Report by updating the query (this is the hard part)
- Upload the RDL files to Report Style by clicking Actions > Upload SSRS Report
Hi Jason, Can you expand on what you mean by “Updating the query?”
I’m a newb to the pain that is building an SSRS report, but I don’t see anywhere that a “Query” can be updated. Do you mean the individual fields?
The mappings from table.field to just field in SSRS report builder are about as clear as mud. If anyone has any tips on how to correlate them I’d love to hear it.
What Jason means by “updating the query” is that you have to manually add the missing tables and relationships in the dataset query after downloading the form from Report Style. If you provide your dataset query I could show you a sample.
1 Like
Another way you could solve this is to create two report styles using the same RDD. Name one as “Test” and let the other one be your real working Style which for easy identification here I will call “MainStyle”. Use the sync button on the “Test” style and avoid using the sync button on MainStyle. Create and name folders for both styles in your system and download both accordingly. Open both forms in two separate instances in report builder, study the query in both forms and copy from the synced “Test” style only the fields and relationships you desire to your “MainStyle” query. You can also add any missing table and relationship to your query. Hope this helps.
We’ve recently transitioned from V6 to E10 and we’re in the process of updating all our CR’s (~600) to SSRS. I’m working on the QuotForm to bring it in line with our standard and having difficulty because of the way the RDL displays the RDD fields. It lops off the source table, so you sort of have to shoot from the hip to know if you’re grabbing the right field. I find myself, bringing in a field, saving, uploading, run a print preview, and view the results. This is very tedious. I was hoping there was a magical way that I could map the table.field to the Fields!{field name}.Value.
I use magical because I’ve been working the same report for days and most of my time is spent trying to identify the field name as it translates from v6 to E10, then from the live tables, to the RDL.
I’m reaching the point where I just want to start from scratch and build our quote form from a BAQ.
Unfortunately there is not easy way that I know of. I faced somewhat similar challenges when we upgraded to E10. Once you get to understand the dataset queries and how they are structured by the sync button, you’ll find it easier to add the fields to the report. And you don’t have to upload to EPICOR before you can preview your report. Copy the GUID value from the System Monitor, expand the Parameters tree in reports builder, click to open the TableGuid properties dialog, in the Default Values tab check Specify values, click Add and paste the GUID value. Click Ok to close the window and Run the report from Reports Builder.
Double clicking the DataSet in report builder will bring up the DataSet Properties. Click the “fx” next to the query window and you will see the SQL query. The fields are labeled as T#.QuoteNum and at the bottom is the corresponding table aliases(T#) where the joins are. That’s how you know what table the field is pulling from.
What he means by modifying the query is, if you add a table or field to the RDD then you have to modify the SQL Query, same as mentioned above. Either add the new join to the query and/or add the field if the table already existed in the original query. You will also have to click on the DataSet Properties “Fields” and add the new field there.
edit: wow, this is an old post…
Unfortunately with a RDD based report this doesn’t happen. I completely see what you’re talking about when building with a BAQ based report. When you work from an RDD it rolls many of the fields up into a summarized dataset and drops the table identifier. Sometimes, if you’re lucky, it will include the original table in the format (table_datafield). I cannot find the mapping for that roll-up anywhere. It’s either in the code, on the server where I don’t have access (cloud), or some black magic that Epicor has cast. I’m convinced of the latter.
You probably tried using the “Sync Dataset” button on Report Style Maint.
This is almost always a very idea. (there are even threads her on how people hide that button to keep from accidentily clicking it).
Doing this will TOTALLY reconstruct your dataset, changing it from a single main table (with support tables like RptLabels, RptParams, etc…) to individual tables for each table (or view) from the RDD. It is also supposed to redo the queries to add any new tables and fields to the dataset.
If you have a copy of the RDL from prior to clicking the Sync button, go back to that one, then follow the directions on the online help. Search for:
“Developing a Modified Version of an Epicor Shipped SSRS Report”
Correction, I see what you mean now. This is a mess… I’m going to be alot more acquainted with NotePad++ in the foreseeable future.
As an alternative. Can stock RDD based reports be replaced with BAQ based reports? Since I have to rebuild the quote form from scratch again to undo the damage here, I’d rather do it in a BAQ so we don’t have to worry about someone mistakenly clicking that button in the future and blowing it up.
Along the same lines, does using the sync DataSet button in a BAQ Report Definition have the same effect?
Here’s an example of an RDL (for the OrderAck report) before and after using Sync Dataset
Before:
After:
Looking at the Query Expression for each shows how different they are.
Before using Sync, the query expression referenced 4 tables. And after the Sync, there were 5.
Yes. And this is actually where you might want to use Sync. A BAQ Report uses the BAQ as it’s dataset, and a BAQ’s query is already in the form that the Synch button would “convert” it to.
But just so I don’t develop bad habits, I never use the Sync button. I’ll manually edit the query expression and add new fields, even on BAQ report updates.
Normally your report dataset and RDD tables won’t look the same. Not sure what sync dataset is but that report doesn’t look right. Usually most fields are contained within the quote dataset on the report even if they aren’t from the quote table and looking that dataset’s query will show where fields are coming from.
Here’s what my working custom quote form report looks like.
I was speaking of SSRS reports not BAQ reports. This is how I always handle modifying SSRS. Considering you have the SSRS report in front of you, you should be able to access this.
Thanks Dan. I’ve started cleaning up the dataset in the xml and I’ll circle back around to add the fields manually that I’ve pulled in from the QuotHed_UD table.
One thing I’m still not quite connecting is the “T#” mapping. I don’t see where a table name is being assigned to T1 vs T2 for example. Is this just a SQL shorthand for the first table in an array? If so how is that order determined?
I’ve always thought of RDD’s as being like a BAQ, but where all the displayed columns appear in just one “table”.
In your example, The QuotForm RDD references 9 DB tables (some might actually be views, like QuoteLnMsc). But when it runs, it puts the resulting data into one “table” named Quote, and adds the support “tables” (RptLabels, RptParamster, etc…)
I listed the instructions above. Here are some pictures to go along with that.
Double Click the quote dataset.
Click the fx button
View the query (maybe paste it in SSMS or notepad to view it better)
At the bottom of the query are tables and their alias (T1, T2, T3)
above that is the fields listed as T1.QuoteNum etc…
T1 being “QuoteHed_” + Parameters!TableGuid.Value + " T1"
Proves I did search before posting a new question! 