Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1304
  • Last Modified:

SSRS / Report Builder Question

Hello all,

I have to build a report where I will have an incoming parameter CustomerGroupId.   The CustomerGroup has a one to many to Customers and then Customers has multiple Ancillary tables associated to a CustomerId for example.   I need to create a rollup type report where I get the CustomerGroupId and then I will have several pages for each Customer I will need to pull together so for example each ancillary table will have a page.  

I have worked with the Tablix before but at the end of the day I need to loop through and for each Customer in the Customer group create a multi page type report right after each other for each customer in the group.    Is this possible in SSRS as far as looping though a main dataset and creating a multi page type report for this?
0
sbornstein2
Asked:
sbornstein2
  • 3
  • 2
1 Solution
 
ValentinoVBI ConsultantCommented:
Looping inside SSRS is not possible but I don't think you need that?  I'm not sure I fully understand what you've described here, especially the part about "multiple Ancillary tables".  So you've got several different tables in the database that represent "ancillaries" of a customer?

In general I always try to fetch all data within one dataset.  This allows you to use a tablix with some grouping to display the data as needed.  Page breaks can be set on group level so Customer would be a group then each customer's data can start on a new page.

In case it's impossible to get all that data through just one query, another option could be to use a subreport.  Subreports can be nested inside a tablix and this actually opens the door to combining data from another dataset (the one inside the subreport).  In your case it could be an option to build a subreport with one parameter: CustomerID.  Your main tablix would be grouped on CustomerID and the subreport would be located on that level.

If the above doesn't help you out, please provide a clear data example and mock-up of expected outcome so we can help you further.
0
 
sbornstein2Author Commented:
okay got ya.  So I could have for example a parameter coming in as CustomerGroupId.   Get all the customer id's on this param.   My tablix would be on the customer id dataset multiple records.   Then within the tablix I could have multiple sub reports for each customer?  Sound right?  So here is my example:

CustomerGroupID = 1
Select CustomerId from Customers Where CustomerGroupId = 1

Let's say this has 50 records.
The main report tablix would be the dataset for the 50 records.  So first record CustomerId = 111 for example:

I could then have subreports where I can do the following:

Sub Report 1:

Dataset

Select * from CustomerDocuments Where CustomerId = 111
This would be a report for the customer 111 documents in a tablix etc.

Sub Report 2:

Dataset

Select * from CustomerLoans Where CustomerId = 111

This would be a report for the customer loans

etc.

Then I assume my main tablix would repeat like for example old Access detail section where it repeats?

Hoping this makes sense sort of :)
0
 
sbornstein2Author Commented:
I think I understand what to do.  So think of this as excel sheets.  I have an excel workbook multiple worksheets for a customer.   In this case I would create actual reports for each tab that will become sub reports for the main report of grouped customers?

Correct?  I think I see what I need to do for this 'roll up' report.   Create all the separate sub reports and then use the parameter of the CustomerId
0
 
ValentinoVBI ConsultantCommented:
You only need to create one subreport.  With one parameter: CustomerID.  That subreport is located in your main tablix by using the Subreport object, and it receives the CustomerID parameter.  The parameter is actually the "magic" part through which you just need to create one subreport.

So:

Main report has a parameter, CustomerGroupID and fetches the CustomerIDs.  The tablix is grouped on CustomerID and contains a Subreport control.

The subreport control calls your subreport which had a CustomerID parameter.  And of course the dataset inside the subreport uses the CustomerID parameter to fetch the data for that customer.

Once you've got the hang of that, it'll become clear what I mean and how it works.  Think of the main grouped tablix as a loop (even though it isn't one)...
0
 
sbornstein2Author Commented:
perfect awesome help thanks so much
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now