Edit RDD or use Sub-reports with SSRS?

(Calvin Krusen) #1

What are the pro’s and cons of using a sub-report (with an embedded query) over editing the RDD to get all your data into a single dataset?

For example, I want the Receipt Labels that are printed from Receipt entry, to include information from the sales order, when a link exists (aka BTO purchases). The ReceiptLabels report uses RDD “RcvLabel”, which does have OrderNum, Line & RelNum info to tie it back to to the Order.

So options are:

  1. Edit the RDD to include the OrderHed table
  2. Make one or more sub-report(s) (with embedded query) of the OrderHed, OrderDtl tables, that accepts Company, OrderNum, OrderLine, and OrderRel as parameters.

Things I’m most interested in are: speed, maintainability, load on DB server

One thing I’ve found, is that you cannot change the RDD on a report with a defined break routing. So if that report style needed an update to the RDD, you’d have to make a new style, and build the break routing from scratch.

(Bruce Ordway) #2

related data… edit RDD vs adding a SubReport

And have you already tried editing the query expressions for datasets in your existing reports? Depending on tables/fields needed, I might use this approach too.

Screen shots below from Traveler_SubComponents.rdl where I pulled some data from the JobOper table.

image

image
image.png497×540 50 KB

(Calvin Krusen) #3

Maybe I don’t quite follow how RDL reports …

I thought that when the report is run a GUID is generated, and temp tables - based on the RDD - with names like < RDD_Referenced_Table >_< GUID > .

For example: “JobAsmbl_60e83362557149b6b592e3b6981638ae”

And that only tables from the RDD would would be used to make the temp tables.

In your example, you added the JobOper table to the reports query expression. Is that table in the original RDD? Or did you have to make a new RDD, add, and link the JobOper table?

I can’t imagine that just adding things to the report’s query, would cause the data set to be generated for them.

(Bruce Ordway) #4

Yes, that might be a bad example
I was just reusing the JobOper table that was already referenced in the JobTrav RDD

  • pulled in into an existing subreport rather than adding a new subreport.
(Calvin Krusen) #5

Editing RDD’s is far from straightforward, and whose nuances are easily forgotten when months (or years) go by between editing one.

Creating a sub-report is straight forward. But I was concerned that this unnecessarily taxes the DB server.

(Bruce Ordway) #6

taxing server
Yes, I’ve run into a few issues with modified RDD’s… big & slow datasets.

I suspect a lot of case by case decisions are made as to which approach you’ll prefer to use.

The hardest part of the process for me is getting fully defined requirements up front.
Instead of an “evolving” report design… where users keep adding/changing.

(Bart Elia) #7

SubReports can be a HUGE perf problem. If you see a subreport inside of a ‘loop’ - one per line item - you can grind to a halt waiting on the report.

Think how a large query occurring inside of a foreach loop in a bpm and you see the corollary.