Handle Page Header Data in CRM Custom Report/SSRS with Multiple Split Pages

In my previous post, I was talking about how to design CRM Report for Multiple Records appearing in Multiple Split Pages for each record. 

Now, how to make the header follow record for each page?

If you are using Pre-Filtering report, you can filter the data you want to display in to the report, but how to handle the Page Header (if you use Page Header instead of using Tablix Row Header).

As we know, in SSRS we have 3 parts: Report Header, Body, and Report Footer.

So let’s say you have pre-filtering report, you only display the report by record through record Form, it means that the record that you want to display is only one record.

Then, you notice the Page Header, Body, and Page Footer will display correctly.

*You have Quote Id + Revision in the Header.

Now, what if you select multiple record as filter?

For example you edit the Filter to show all Quotes from this Potential Customer

Or you select multiple Quote records.

Result:

Using my explanation in my previous post, you can have multiple separated page for each Quote record (not 1 report for all Quote records) as per your requirement.

First Page:

*You notice the Quote ID that will appears in the Report Header.

Second Page:

Last Page:



If you notice, the Page Header will display the same thing even though the records are in split page and in fact, in body it shows different record.

So what to do?

Basically, in the page header if you just use the field from DataSet and drag it to the field, it will give you only the first one.

This is the expression:

="Proposal Ref: " & 
First(Fields!quotenumber.Value, "DataSet1") & "-" &
First(Fields!revisionnumber.Value, "DataSet1") &

The problems are:

1. You cannot use DataSet1 anymore as your scope because as mentioned in my previous post, you need a Page Break set by Group. So that if you use DataSet as your scope, it will get the wrong data since your purpose is to break the multiple records to multiple page using Page Break by each Quote Id.

2. SSRS does not allow you to set the tablix group name as your scope, you can only use DataSet.

Using the pre-filtering record, your dataset can be grown if you select many records not a single record.
It is similar to IN Clause in SQL if you are familiar.

So example your Quote record is 23 records with multiple Quote ID:

No	Quote ID	Revision Number	Potential Customer
1	QUO-01104-H1R4G3	0	A. Datum Corporation (sample)
2	QUO-01105-A1S5U5	0	A. Datum Corporation (sample)
23	QUO-01135-C1B7U7	0	A. Datum Corporation (sample)

If you use ‘first’ in syntax expression in the header it will give you the first record only (QUO-01104-H1R4G3-0)

It will be safe if you only show report in the each record Form, but it won’t if you show the report in Report Area or List Area.

So, how to make the Header to show correct Quote ID for each record same as shown in the body?

You can do these following tips and tricks based on my experience.

1. You can use Tablix Header instead of using Page Header

Tablix Header has each Row Header that you can repeat, but sometimes you found it is not easy, because maintaining page header is easier and you can have 3 different parts of layout that will not affect each other if got any changes.

2. You can use Report Parameter

If you know the parameter sent from the CRM Report that you can use it, but you need to Query with Where Clause to your data set, which if you are using pre-filtering report (standard one), you cannot control the parameter sent by CRM, so this is cannot be used, unless you pass a custom parameter to the report or you use javascript to open the report or you use Report Parameter and let the users to choose when run the SSRS.

3. You can use a hidden textbox.

You can use a hidden textbox, placing it into the Report Body.





And use the Expression (but don’t use ‘FIRST’)



And Make sure your scope is not DataSet, but you should use the Group Name as scope, see this.



*Now, you notice the group name can be the scope, different from PageHeader’s textbox.

Please refer to my previous post to get more detail info about this.

Then, set this expression into your page header textbox.

=ReportItems!textBoxQuoteNumberBu.Value

Change to your textbox Name.

*But, this tip got weakness, if your data is too long shown in report body into one page each Quote record (if you convert to PDF or Word, etc), it will not give you the value in next page because in second page and so on, the hidden textbox does not exist to the second page of each Quote record, etc.

Example:



*First Page only fits until 10 lines for example:

Second Page:

(To show next item number 11 and 12)



*Because your hidden text only occupied one page each group, then in the second page if your data having more than 1 page, it will show blank value.

4. You can utilize the Page Name.

Go to your Row Group and go to Page Properties that you set the Group to have page break for each record.



Then use your PageName, set the expression for example:

=First(Fields!quotenumber.Value, "list1_quote") & "-" &
First(Fields!revisionnumber.Value, "list1_quote") 

*Now, you notice the group name can be the scope, different from PageHeader’s textbox.

Now, change your textbox in the Page Header and set this expression:



Set to:

=Globals!PageName

And see the result after you do those mentioned ways.

Page 1



Page 11



And so on…

I think it is the best idea, beside you can have your expected header, you can also have advantage once you converted the report to excel file, it will give you the different sheet name:



Hope this helps!

My blog is not only talking about CRM and its UI or features, you can see my other posts about SSRS here:

http://missdynamicscrm.blogspot.com/search/label/SSRS


Thanks.