Solved

SSRS / Report Builder Question

Posted on 2013-11-14
5
1,169 Views
Last Modified: 2013-11-21
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
Comment
Question by:sbornstein2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39650377
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
 

Author Comment

by:sbornstein2
ID: 39652371
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
 

Author Comment

by:sbornstein2
ID: 39652398
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
 
LVL 37

Accepted Solution

by:
ValentinoV earned 250 total points
ID: 39654523
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
 

Author Closing Comment

by:sbornstein2
ID: 39666950
perfect awesome help thanks so much
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question