Solved

SSRS / Report Builder Question

Posted on 2013-11-14
5
1,208 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

617 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