Solved

SSRS / Report Builder Question

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS report 5 50
SQL Server 2014 - Report Server - Stored Procs 8 56
Data source in SSRS 2 48
How to get sum of group by column and sum of total values 4 55
In this short article I will be talking about two functions in the SQL Server Reporting Services (SSRS) function stack.  Those functions are IIF() and Switch().  And I'll be showing you how easy it is to add an Else part to the Switch function. T…
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…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…

911 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now