Design CRM Custom Report/ SSRS with Multiple Record - Multiple Pages

You might have requirement to show report in CRM and we know we can use filteredView + pre-filtering report in CRM. We can construct our dataset with CRMAF_FilteredEntity as the alias name. And we can place the report into the Report Area, List, or Form.

We know that the report can be saved in other format, such as PDF or Word and even you can print it.

We also know that by using the pre-filtering report in CRM, you can have report with reliable contextual data based on the selected records through your selection in the User Interface and based on the location you place the records.

*Form

We know when you enable this pre-filter report, we can run the report based on the record we opened to that form.

*See the ‘Run on Current Record’ context.

Yes, it has pre-filtered by default, by the current record you run.

*List Area

When you enable to display the record in the List Area for related record, then you can select records that you want to include in the report.

*Report Area

You run a report in Report Area, then you can call the ‘Filter function’ which you can select multiple records or all records at once.

So, it seems not too complicated if you know how to use this pre-filtering.

But, have you realized if you want to print out multiple records using pre-filtering report, you want to print those records into multiple split pages, it can be difficult.

Now the problem is coming when you want to show the selected records into different or multiple split pages, that is one record one page.

For example, you want to create a Quote from an Opportunity.

An Opportunity can have more than 1 Quote, let’s say 1 Opportunity 1 Quotes then you want to print all of the 3 quotes from the Opportunity and break them into 3 pages, one record one page. Not only 1 page then show those 3 records in one page.

And another example:

You want to run a report from Quote from the Report Area, which can have hundred records and you want to split them into hundred pages. So one quote, one page.

Everything is fine when you run the report, you can see page header with body split into different page.

Page 1 of 36...

Page 2 of 36

You can see the Header and The body look perfect, just after the header and repeat for each record.

And so on.. Until..the last.

Page 36 of 36

*You notice the first line after the header, It is always consistent shown after the header for each page.

But, what happened if you save to PDF and print it..

It is totally changed, it becomes mix in one page and will continue put next record just after the previous record.

See this picture:

The red block is showing the part belongs to previous record

The blue block is showing the part belongs to the current record which is defined before in the Page 2 of 36.

The first line after the page header now is in the middle of the page and not just after the Page Header part.

Now, you see the total page is 83, it is caused by the Report Page Size and you can see the report is becoming very unstructured.

*Remember, the page size will determine the space that can be used and how many pages will be splitted based on the page size + margin (make sure your Body size + Margin is not larger than the page size), or you might be shocked realizing where the empty whitespace was coming from...

It will show non-stop, no break, and continuously showing the record without any break, which is not what I expected.

Here, you can see the inconsistency when you run the report on the spot or when you save it to PDF. Don’t believe with what you can see before you save it to PDF or print it, because basically the customer will receive the paper with hardcopy and then sign it.

Therefore, you need to tweak your SSRS setting manually to break them into multiple pages based on the record..

Solution:

1. Back to your SSRS BIDS

2. Create a group

I am sure that you should have created the ‘Group’ before, if not then you might need to create a grouping, it can be using List or Tablix. Set the group based on your criteria, for example you want to print 1 Quote one page, then you might need QuoteId as the grouping expression, or you want to print 1 Account 3 Quotes, then you also need to group by QuoteId for each Account.

This group is so important.

*This example is to run report of multiple record of Quote and show the result, both in preview and in print mode as multiple split pages, each Quote will appear in one page, and so on.

Thus, I try to group by QuoteId because QuoteId is I believe it as the Unique Identifier to identify each record in order to split each identity to each page.

3. Group Properties

4. Turn 'the magic' on..

5. Here is how the magic works

Next record

Next record..

Until last record.

*You can see, your data is 'being forced' to split across multiple pages, both in Preview or in Printing Mode.

*This is what I expected..It does not enlarge the data inside and fit it into one page, instead, it is printed in multiple different pages.

If you converted to Excel you can also have different sheet nam (provided you have set the different page name in your SSRS Editor)

You can also reset the page number:

So, if you want to display the the Report in Form, list, and Report Area and you want to split them into different page for each record as advance report then can be printed to customers, you need to set your own Page Break, because after you upload the report to CRM, CRM will not split into different pages, it will show you in the report preview, but when you want to print it, you will not get what you wanted, you need to set your own ‘Page Break’.

*And another tip, I tried to create report with nested group and then I give page break to both of them, it was running well in the ‘Preview’ mode in the BIDS, but when I upload to CRM and run it, I got error, such as ‘Invalid Argument’. Then, I was ruined by this issue, and then actually that was caused by I turn ‘Yes’ the Page Break for each group, including child group resulted error on page when I run the report. Thus, I just use only one group, I choose the smallest group as my grouping, and the only one, no more nested, and ignore the big brother, then set the Page Break to this group and it worked.

Summary:

The key things are:
1. Create a Group, refer to the above solution number 2
2. Apply a Page Break for that Group.

*This post might be useful if you want to print out multiple record or hundred and more records in CRM but you want to display it into split or separate pages and repeated for each page, each record each page.

In the next post I will explain how to handle multiple split pages in SSRS with dynamic Page Header in line with current data/record based on the each record with same dataset using pre-filtering CRM Report.

Hope this helps!

Thanks.