Solved

SSRS / Report Builder Question

Posted on 2013-11-14
5
1,127 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
perfect awesome help thanks so much
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
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…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

12 Experts available now in Live!

Get 1:1 Help Now