Skip to Content

Array utilization in Crystal Reports with linked Subreport

There are numerous cases when you want your Main Report to communicate to the Sub Report. This is achieved by creating a linked Sub-Report where a Sub-Report is linked to the main Report using the link on at least one dimension (db field corresponding to StringVar datatype in Crystal Reports). The data that is returned by the Sub-Report is governed by the Main Reports’ controlling field in the link. There can be circumstances where the values for the dimensions used in the Main Report are dependant on parameter that is optional. This does not create any problem for a Sub-Report which is used inside a Group Header/Group Footer of the Group based on the dimension that has dependency on the optional parameter for its values. But when the same Sub-Report has to be presented as a single block within the Report i.e. by placing it in the Main Report’s Report Header/Report Footer, then there has to be a intelligent approach to this situation. One of the ways to handle this situation is to use Array. These are efficient storage objects that can read and retain the values of the database fields/dimensions during the processing of the Report and the same list of values for those dimensions can be passed to the Sub-Report using a formula. Let me illustrate this functionality with Crystal Reports XI R2, without letting any misconception enter in the readers mind about the version support, as this is entirely version independent.


Let us consider an illustration using ‘Suppliers Balance Report’, having the parameters ‘Supplier No’ as optional, ‘From Date’ and ‘To Date’ as mandatory, ‘Product’ and ‘Sub product’ as mandatory cascading parameter. The data that is populated into the Main Report is an outcome of the combination of parameters ‘Supplier No’, ‘From Date’, ‘To Date’, ‘Product’, ‘Sub product’. When no specific input is being supplied to the optional prompt ‘Supplier No’, we need to incorporate a series of formulae by use of Array, named as ‘Supplier Array Reset’, ‘Supplier Array Accumulate’, ‘Supplier Array Append’ in the illustration, to hold ‘Supplier No’ and subsequently use that to pass to the Sub-Report i.e named as ‘Balances’ in the illustration. Also it has to be ensured that the ‘Select data in subreport is based on field’ within the Subreport Links is kept unchecked as depicted below:



 Subreport Links


For a Sub-Report that has to be printed only once in the body of the Report, the placement of it can be either within a Report Header and Report Footer. In order to create a linked Sub-Report the values have to be passed by entities within the main Report. The layout of a Crystal Reports with different sections and the positioning of different formulae for Array processing are illustrated below: 



  Construction of Array Blocks in Crystal Reports


Supplier Balance Report


Neither can a Sub-Report be linked using the parameter – ‘Supplier No’ as it is optional nor there is any chance of creating a valid link to it using the dimension -‘Supplier No’ as the binding parameter has made it a variable object. Here you can utilize the Array and implement the link to Sub-Report by passing the accumulated values within an Array in the main Report. So, for the Array to be processed earlier, you have to place the Sub-Report in the Report Footer. Declare an Array with the size that is desired as shown below and using a sequence of formulae, ensure that every value that is populated for the dimension- ‘Supplier No’ is being read into it. All the steps of creation of an Array to populating database(db) values and further to concatenation of list of Array values are displayed below:



(1) Declaration of an Array in Crystal Reports (Formula Editor)


Supplier Array Reset



(2) Reading values from a db field in Crystal Reports (Formula Editor)


Supplier Array Accumulate


Once the Array has acculmulated all the values of dimension -‘Supplier No’, a new formula has to be created that combines all the values retained in the Array. This can be accomplished by using the Join Function along with Array and a variable.



(3) Appending values in Crystal Reports (Formula Editor)


Supplier Array Append



A db field has to be used in the Record Selection formula of the Sub-Report, having a datatype that corresponds to the db field in the main Report. This rule has to be followed especially in the data model shown, where the main Report and Sub-Report are built on separate datasources. Further, the values have be separated before assigning it to a db field in the Record Selection of the Sub-Report for further processing. This can be achieved by using the Split Function with the identical delimiter that was used in the Join Function and the same is depicted below:



 Record Selection Formula in Subreport of Crystal Reports (Formula Editor)


 Subreport Record Selection


Thus we can define all the links in the Sub-Report links dialog, that includes the variable and the mandatory parameters if these are going to influence the Sub-Report. Thus, the result of the Sub-Report would be regulated by the main Report as all necessary entities have been linked and all of them feed the same values to the Sub-Report.

Be the first to leave a comment
You must be Logged on to comment or reply to a post.