Hey gang,
I got an idea for a subreport or similar type thingy for our packing slips. I have the basic idea of the data needed for my subreport type thingy. I am curious if there is any documentation out there to help me figure this stuff out or if I am more or less on my own. About my experience:
I know my way around SQL, maybe not the best Query designer but I can get the job done.
I have experience with both Crystal and Microsoft tools, but not specifically SSRS aside from the basic training provided by our friends at Epicor over a year ago. My specific situation:
We package Install Hardware and Templates along with our products when we ship them to the customer.
The hardware is on the BoM (as purchased materials) for the Job/Packing Slip Line item for CPQ based line items (which I estimate will be 80% or more).
Our sales team will hang me out to dry if I tell them install hardware will be a separate line on Quotes/Orders.
I have a BAQ that can find the hardware in the BoM from the Shipment Line Item to the JobMtl.
I was thinking of adding what I think will be a Subreport under the shipment line item that will list out the hardware from my BAQ.
I’ve gotten so far as creating the report style and downloading the zip file. I think I have to back track and do some Report Data definition stuff or something to get the data from my BAQ into the report and then I need to figure out how to start the subreport and feed it data. I do have a nice post on here bookmarked that is around step three for me, so maybe once I get this rolling I might be on my way. Mostly looking for some resource to get me down the right path.
Thanks in advance.
PS try to be descriptive when answering, I still get quickly lost in Epicor acronyms.
This post isn’t getting any love, let me try a different question.
I need to add some tables/fields to my report dataset. Is it best practice to Copy the Report Data Definition and modify a copy?
I am trying to use the newer screens instead of the classic versions, since I am a new learner and some day those classic version may no longer be supported I figure it best to look forward.
All that said, do I copy the Report Data Definition and then proceed to add the tables/fields?
Yes, the first step when modifying a system report would be to copy the Report Data Definition (RDD). You cannot modify the system one.
I would start by copying the RDD and then creating a new Report Style that will link to your modified report and connect that to the new RDD.
Just an aside/best practice IMO:
Even though virtually all of our customer facing forms have been customized and are the default when you want that form I always leave Epicor’s Standard SSRS report available and completely untouched, either in Report Style, SSRS or the RDD since if/when you have issues the first thing to test is if the report works with their base version - obviously this will not have all of your customizations but if you can show that it fails even with their base one you can usually get support a bit easier.
Tory, sorry we didn’t see this one, I know there are several people on here who love to answer SSRS questions too (me being one of them) and I don’t see the normal contributors on here.
If I can get some time tomorrow I’ll try and help you out! It’s been busy!
I cannot give any advice on modifying DD’s to create new sub reports but I have done this many times using my own SQL Queries.
I create a new shared connection on the SSRS server that has read only credentials to the database.
Then create a new report that uses this shared connection and go write your query in SSMS or text editor of choice, then copy it into the new report. Configure any parameters that will be needed to filter the data for the sub report and then in the original report add your new report as a sub report and wire up the parameters.
Not sure if this is an approved way of doing it, but it served me well so far.
We are SaaS cloud based. I am curious if connection to the database will be possible, but I think in my situation I need to work within the confines of Epicor for now.
Look at the Job Traveler. Download it and you’ll see the main report and all of the sub reports. If you examine the main report, you’ll see how the parameters are setup to pass information down to the sub report for various levels.
Separately, here’s a nice write up on how printing works in Epicor/Kinetic:
The Data Source should be the same but they each can have it own main SQL statement. In the Job Traveler, these are the parameters to pass information down to the Raw Materia Components sub report:
And these are the parameters passed to the report:
On the sub report, here’s where the parameters are received from the main report. You can also see the Data Source is the same but the sub report includes JobMtl where the main report does not.
Right that’s what i was thinking, but using @hkeric.wci 's post on RDDs created via BAQ data sources (Is there a way to add table or BAQ to a BAQ Report - #5 by hkeric.wci) , @ToryD could add something as simple as the company table to the RDD and then add both the BAQs to the report data definition and then join the BAQs to the company table which should return only one row (or whatever) and then they’ll have both BAQ datasets they need and they can reference them from within whatever subreports.
Wow thanks gang, this is what I was hoping to find. I recently discovered the Experts corner, that is a precious jewel.
Thanks @Mark_Wonsil for getting this thread on track for me!
@utaylor your response regarding BAQs is intriguing, not sure if I fully understand what you are posting. I have a BAQ already written to test my hypothesis for this extra data on the pack slip. It would definitely make my life easier if I could link up a BAQ, I am actually making a connection on a table that is not a strong internal connection. Maybe this option will work better for me. I connect JobMtl table on ShipDtl.JobNum = JobMtl.JobNum. Then I filter to only show specific Materials from the JobMtl.
Ah I see, I couldn’t understand whether you were starting with a base RDD or not.
@hkeric.wci , correct me if I’m wrong (and if you have the time ), but I don’t think you can add BAQs as data sources to native RDDs using tables, not sure though @ToryD, I’d have to try it out in my kinetic environment!
@ToryD , just know that you can put BAQs in an RDD should you ever need to do that, I just don’t know if you can join them to native schema tables like those in the packslip report or any other base report.
OK thanks for the clarification. Yes I am using the PackSlip as my starting point so I do have a table based RDD, which I did create a new copy and added the JobMtl table and I believe linked. I have to test it yet.
OK on the BAQ reports, will keep that in mind. I think I read your post and thought maybe it was only on BAQ native RDD, but thought I’d ask.
yeah, @ToryD , not yet… we can’t add a BAQ to a schema report yet. See picture below, I’m trying to add a custom BAQ as a dataset to the packslip report and it’s greyed out.
I updated the RDD to add the JobMtl and specifically made sure to include (or not exclude) the fields I want to see (at least for testing).
Now in the Report Builder I don’t see the new fields, do I have to add them manually? Is there any easy way to test to make sure I got the fields in my RDD correctly?
), but I don’t think you can add BAQs as data sources to native RDDs using tables, not sure though 
