I have created a custom SSRS report (Main). It has a BAQ and RDD. I created a custom subreport for the main report. The subreport also has a BAQ (Different tables) and RDD. I am having issues getting the subreport to show in the main report. I have tried utilizing the parameters to get the subreport to show.
What would be the correct steps to take to get the subreport to show in the main report?
One Report Style can only have one Report Data Definition. So the data of the Subreport should come from the same RDD as the main report.
Mine sharing what report are you creating?
For the sub report, make it in SSRS report builder, with its query connected directly to the DB. Add parameters that will be passed from the main report.
A couple words of caution when making subreports connect right to the DB, as opposed to the dataset (with a GUID) created by E10.
The sub-reportâs datasource will be âhard codedâ with the server and DB name. If you either move the SQL server to a new box, or change the DB name, then the sub report will have to be updated with new data source connection info.
When E10 makes a dataset (with the GUID), the data is fixed and never changes. Where as a sub-report connected right to the DB, will us the current value. This really only matters if you archive the report in E10.
For example, a report has a sub-report with its datasource connected directly to the DB. Say the only function if the sub-report is to return the Site name (Plant.Name). If the report is run - and set to archive for 1 year - it shows the current plant name. If you change the plant name a month later, and then print the archived report (from in Sys Monitor), the report would show the new name.
Granted thatâs just a name difference. But if your sub did some sort of totaling of inventory, any changes to inventory after the initial run of the report would then show in the âarchivedâ report.
ckrusen, This worked perfectly.
Thank You
I will actually be presenting this very topic at Insight this year. We will be creating an RDD composed of multiple BAQâs which should allow you to create subreports. All the result sets will be tagged with the same GUID.
Iâm curious to know if the environment discussed here is a cloud environment. If so, then during development within Report Writer, how is the direct connect to the database being made? I have a need to create a subreport using a different BAQ (with different tables) but Iâm having no success at all getting this subreport to execute and display. Iâm really questioning if subreports can be done in an E10 cloud environment.
I am a bit new to Epicor and this cloud environment. Most my experience has been having that direct connection to the database when creating SSRS reports. I donât really understand this TableGuid that is being passed from the main report to the subreport, but what it looks like is a table id that the system uses to report off of the same table from the main reportâŚwhich doesnât work for what I need to do.
Iâm not finding really anything that explains how to do this in a cloud environment so any links, documentation, or videos related to what I need to do would be greatly appreciated.
Many standard reports use sub-reports and do so in SaaS without issue. Look at Job Traveler as an example and youâll see many sub-reports. Pay close attention to the DataSet section and the query expression.
I have been looking at the Packing Slip report, but I think Iâve been following everything that looked relevant. Iâll checkout this Job Traveler youâve mentioned.
My technique of making the subreport connect directly to the DBâs ERP tables (and not the Report tables), probably only works for on-premise folks.
When When a report is submitted to E10 the App takes the RDD (or BAQ in the case of a BAQ report), and generates temp tables contaiing just the info needed for the report. These are suffixed with a GUID to keep them separate form other reports using similar named tables.
The GUID is passed to SSRS and SSRS uses that for selecting the tables to use from the Report DB.
When you make a BAQ you get exactly one dataset out. All the info for all the records is in that. When a RDD runs, it creates multiple datasets, and then SSRS joins them as needed. Some of the datasets might only be used by a subreport. SSRS will pass the GUID of the main report to the subreort. The subreport then pulls its data from the datasets created by RDD.
For example, a BAQ that included the OrderNum, OrderLine, PartNum, OrdQty, ReleaseNum and Rel Qty, would make a single dataset (named BAQReportResults_GUID) that has a column for each of those records.
An RDD may could create multiple datasets (Order_GUID, OrderDetails_GUID, Releases_GUID).
Calvin,
Thank you for spending some time with my issue. I really appreciate it.
Using he cloud environment, I donât have access to a direct connection to the database. I can create âBAQâsâ and then download the .rdl file from the BAQ report designer ⌠which youâre probably more familiar to this process than I am. Personally, I would rather create my own SELECT statement and use that as my recordset source for all SSRS reports, but I canât directly test my statements since I donât have that direct DB connection. So, youâre saying the TableGuid isnât required for EPICOR SSRS reports? But, if it is used, is it required to be used in the FROM section, as a table, for the data source query, because the BAQ needed for the subreport is using different tables.
Whatâs confusing me a little also is that the field dropdown lists in the EPICOR out the box SSRS reports are populated with what looks like to be normal field names. The report Iâm creating from the downloaded .rdl, the field dropdowns arenât populated and when using the Expression Builder wizard, the field is like First(ShipTo_Company) or SUM(ShipHead_PackNum), not [Company] or [PackNum]. Itâs a little confusing of why my scratch report is more cumbersome to create.
I really believe Iâm passing the necessary values to the subreport, because Iâm displaying them right above my subreport, but the âqueryâ for the subreport dataset just isnât working.
Are tablix controls required? It seems they are becuase I canât group anything unless one is on the form.
I feel like Iâm close.
From what I have seen the important parts are the Dataset Query used in the report and ensuring the RDD contains the relevant dataset needed for the Query to have the appropriate information. If you view the expression for the standard reports that work you should find as Calvin says a table name suffixed by a GUID common to all items within the RDD so each Query expression within uses that GUID suffix to point to temp tables generated on report run and unless the Archive is set deleted/removed almost instantly.
So if you were using [Erp].[OrderHed] in a direct SQL statement in an direct access of the data tables on the Epicor DB server in your report you would use/see something like:
="SELECT T1.OrderNum, T1.OrderDate, T1.ShipViaCode FROM OrderHed_" + Parameters!TableGuid.Value + " T1"
In the report to gather the dataset from the temp tables which should only hold the data (record set) for/appropriate to the parameters you specified in the popup dialog to generate it. If you download the Cloud version reports and the contain the same type of expression(s) then logic would hold with a correct RDD you can access the data you desire.
Here is a SSRS Report Dataset I am using to list the data in a comma separated list of the values stored in a UD field from the Configuration of a Part. This is being done in a custom sub-report section within the Standard Job Traveler report (slightly modified to provide the appropriate JobNum list of components and their PartMaster description vs the one the salespeople and sales order generated output to display on the shop floor. I am also passing the appropriate JobNum to provide MassPrint functionality as well.
="SELECT PartNum,PartDescription FROM PART_" + Parameters!TableGuid.Value + " WHERE PartNum In (Select * FROM STRING_SPLIT( (SELECT PartsList_c FROM JobHead_" + Parameters!TableGuid.Value +" WHERE JobNum = '" + Parameters!JobNum.Value+"') , ',' ))"
When the subreport is connected directly to the DB, when you deploy it to another Test environment, how do you change the connection? Do you have to manually do this every time you deploy it to a different environment or what is the best way to handle this?
Our Production and Test environments used the same SSRS settings in the EAC setup.
That has some dangers, but with limited persons using TEST (just me), it worked out fine. But when we moved the SQL DB to a different Server, then I had to update the datasource in the subreports RDL.
Makes sense you have to manually updated the datasource in the subreport rdl each time you deploy it to a different environment.
Do you know if the datasource works the same way with external BAQ or sql views in how it connects to views?
No I do not.
Mark can you help in pointing me to any instructions to add a BAQ to a existing Job Traveller
So I can add Info appertaining to user who is printing the report
Epicor Support So far have been unable to help.
The field marked in the current rdl is not producing a user name
and support tell me easiest way is to add a BAQ
Epicor Cloud 2022.1 about to upgrade to 2022.2
The System Monitor displays the info youâre looking for, along with the GUID for the instance of the report. Since your report is passed the GUID, you could make a direct query of the SysMon table (donât recall the exact table name), for the record with the matching GUID. That record would then have the username of the person that ran it, the start time, etcâŚ
(I replied to this thread in another topic: Add BAQ to existing RDL)
However, if Alan is staying on Single Tenant, then this is an excellent solution. If they are going public cloud, Iâm pretty sure you cannot reach back into your ERP database from the SSRS servers. IIRC, I was only able to report on what was extracted. This prevents a security concern you brought up awhile ago.