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?
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.
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.
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).
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.
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:
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?
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…
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.