A loop in QPR Add-In for Microsoft Office is a construct very similar to for-each loop that can be found in some programming languages: It is used to repeat some content for all the members of some set, for example all the scorecards in a set of scorecards.

 

A loop is delimited by start and end tags. These tags are not substituted by any content while the report is generated. Instead, the content between them is repeated for every member of the loop’s source set.

 

The query and loop variable of the loop are defined in the start tag. The query is used to define the loop’s source set. The query is then made to QPR Web Service Foundation and the result is used as loop’s source set. The loop variable of the loop on the other hand is a name that can be used within the loop to refer to the element in the source set that is currently being looped through. When the document is being generated, it is replaced with the ID of the current element in every tag that is between the start and end the tag of the loop, after which the substitution is performed for that tag.

 

As queries are written in the query language of QPR Web Service Foundation. Knowledge of the query language is needed in order to gain complete control over the loops. Most commonly needed loops are made ready available in the QPR Add-In for Microsoft Office.

 

 

Creating a loop

 

To create a loop, use the Insert Loop button in the ribbon. Note that there are four alternative loop types: scorecards, measures, process steps, and periods. You can select the loop type either from the ribbon or change it later using the Loop Type drop-down box on the Insert Repeat Loop Tags pane that is opened. The type of the loop determines the type of items which are being looped through.

 

Use the Select Model drop-down box to pick the correct model. Depending on type of the loop, use Select Process Level or Select Scorecard and/or Select Measure drop-down boxes to select the root element of the loop. By default, the root and all is children (recursively) are looped through. For Period type loops, you can select  the period level to be used. The Period level from model option allows you to select period level from the period levels available in the selected model. With the Period level of measure option you can set the period level to be fetched from a specific measure identified by a FullID or by a loop variable or a parameter that can be typed in or selected from the drop-down menu. In addition to selecting the period level, use the controls available to define the start date and the end date by using the drop-down calendar and/or by defining a period offset from the current date (i.e. "today", not the date defined by 'Period by Fixed Date'). These two can be mixed. For example, it's possible to define a fixed start date when the end date is current date - 1 periods. To define a loop more freely, use a Selected Objects loop or loop with a custom query.

 

A Selected Objects loop allows to pick a set of freely selectable items. When a Selected Objects loop is selected, use the search functionalities to locate the items you want to loop through and click Add to add the selected items to the list. The items are looped through in the order they appear in the list, and you can use the arrow buttons next to the list to alter the order. Similarly as in other loops, items are placed in loop variables when a Selected Objects loop is looped through.

 

For other loops than a Selected Objects loop you can define also the sort order with the Sort Order drop-down menu with the exception of periods, where the order is  defined in the Sort by Date radio button group. Scorecards and measures can be sorted by name, symbol, or hierarchy (in a depth-first order, i.e. one hierarchy level processed at a time). Process levels can be sorted by name and symbol, and periods by either ascending or descending date.

 

After the start and end tags are in place, you can create the content to be looped between them. It can contain both data tags and other content. Check the "Using the loop variable" section for information on how you can refer to the item that is currently being looped through in order to display content related to it.

 

Alternatively you first create the content which you wish to loop. Select everything you want to have inside the loop and insert loop as previously explained. Loop start and end tags are placed at the beginning and end of the selection and all ID's used within the loop are replaced by loop’s loop variable. This is useful if you wish to for instance first test the content with just one item before placing it into the loop.

 

 

Loops with custom query

 

Use a custom loop when you want more control over the items which are looped through. In custom loop you can define loop’s query freely using the query language of QPR Web Services Foundation.

 

Custom loop can be created using the Add Custom Tag button. Select Repeat Loop as the tag type in the corresponding drop-down box. Now write the wanted query into the Query textbox. Refer to QPR – Developer’s Guide for the syntax to use. In the Variable Name textbox define the name of the loop variable that is later used in the other tags inside the loop. See the "Using the loop variable" section for more details.

 

The Insert Tag button creates start and end tags for the loop, just like when using one of the default loop types.

 

 

Using the loop variable

 

Inside the loop tags, a loop variable can be used everywhere where an ID would be used. During report generation it will be replaced by the ID of the current element in a loop.

 

To use a loop variable, insert a tag as you would normally do. Loop variables defined in the document can be found in the search results with a special type “Loop variable” in the Type column. Select the loop variable you wish to use.  You can also use the Search Only drop-down box to include only variables in your search.

 

All properties are available for the loop variable, as QPR Add-In for Microsoft Office does not know the type of the object the variable represents. Thus it is your responsibility to use only those properties that the looped element actually has.

 

Loop variables can also be used in custom tags. In that case use variable’s name in curly brackets ({ and }) as custom tag’s Source.

 

 

Nested loops

 

Loops can also be nested. This is useful for example if you wish to loop through scorecards and then through elements of each scorecard. In these cases the inner loops need to be defined using custom loop and loop variable of the outer loop.

 

To create a nested loop you need to first create the outer loop normally. After creating the outer loop, add a custom loop inside the outer one. In the inner loop’s query, you can use the loop variable of the outer loop (in curly brackets). For example the following query: [{scorecard}].TopElement.ChildObjects(Recursive=1,KeepOriginals=1) would loop through all the elements of the scorecard defined by loop variable scorecard.

 

See QPR – Developer’s Guide for more information.