DRC produces different results in Design Mode than it does Output Mode :
-
In Spread Design Mode, DRC returns 1 (unless min or start_date optional parameters are specified).
-
In Report Output Mode, DRC returns a numeric offset of the current row from the beginning of a particular data repeater. In this mode, DRC also causes a “side-effect”. It inserts a specific number of rows into the report and subsequently copies all cell content, formulas, and formatting from the DRC grow(s) into the newly inserted rows.
The unique feature of this formula is that it computes how many rows to insert at runtime. This is very handy when designing reports that “grow” based on a runtime criterion (such as user selected report date range or number of records returned by a query).
The Number_of_rows_to_repeat parameter tells the DRC how many rows to copy when expanding report. Use 1 when you'd like it to copy the current row it resides on. Use 2 or more if you'd like it to copy multiple subsequent rows.
Multiple DRC instances can reside in the same column. They will be expanded in the top-to-bottom fashion. However, a DRC instance MUST never collide with another. An example of such improper use would be : A1=DRC(2,”DATE”,”D”) A2=DRC(1,”DATE”,”D”)
The DRC in A1 will try to expand rows 1 and two, and the DRC in A2 will try to expand row 2. There will be a collision in row 2 and your report will not come out correctly. Simple fix :
A1=DRC(2,”DATE”,”D”) A3=DRC(1,”DATE”,”D”)
DRC formulas can be nested within a report ONLY if they are positioned diagonally. For instance:
A1=DRC(3,”DATE”…. B2=DRC(2,”SQL”…. D3=DRC(1,”SQL”… is valid, but :
A1=DRC(3,”DATE”…. B2=DRC(2,”SQL”…. D2=DRC(1,”SQL”… is INVALID because there are 2 DRCs specified for row 2 (in columns B and D), which violates the diagonal positioning rule. Violating this rule produces very un-expecting results (such as an infinite loop execution).
The second parameter in the formula syntax specifies how a DRC is supposed to grow the report it resides in. It supports these constants:
“DATE”
the number of rows inserted will be determined from the runtime specified date range of the report and the specified stepping parameter. Read on for detailed explanation on the stepping parameter.
“SQL”
The number of rows inserted will be determined from the number of result rows returned at runtime by the SQLFIRST formula in cell referenced by source_query_cell parameter. Read on for examples on how to set up SQL driven DRC instances.
The stepping parameter is only taken into account if “DATE” was used for second parameter. The value of stepping has to be an upper case constant. It makes DRC chop its date range bounds into appropriate number of slots (offsets). For instance, lets run a report from 1/1/2003 to 1/31/2003. DRC(1,”DATE”,”D”) means that DRC will count from 1 to 31 and grow its report by 30 rows. The “D” signifies a stepping by 1 day. Since there are 31 days in the date range, 31 integer offsets (from 1 to 31) are returned.
The following constants are supported in the stepping parameter :
-
“YYYY” step by 1 year at a time
-
“D” step by 1 day at a time
-
“4H” step by 4 hours at a time
-
“H” step by 1 hour at a time
-
“30M” step by 30 minutes at a time
-
“15M” step by 15 minutes at a time
-
“5M” step by 5 minutes at a time
-
“N” step by 1 minute at a time.
-
"M" step by Monthly
-
"Q" step by Quarterly
-
"SA" step by Semiannual
-
"S" step by Season (Winter, Spring, Summer, Fall)
-
"
Wx" step by Week as defined by x IE "W7" is Sunday through Saturday.
Click here for a table defining weeks. Only returns results where the end date of the week is in the report date range.
-
"
WSx" step by Week as defined by x. IE "WS7" is Sunday through Saturday.
Click here for a table defining weeks. Only returns results where the start date of the week is less than the report end date and the end date is greater than the report start date.
-
"
WOx" step by Week as defined by x. IE "WO7" is Sunday through Saturday.
Click here for a table defining weeks. Returns results for any week regardless of the report dates. IE "WO7" with an offset of 53 would return Sunday thru Saturday for one year from the start date of the report even if the report date range is one month
Note: stepping (shown above) and grouping are two different concepts with different parameters. If you need to see 5 minute variables and use the GDATE function to set the date, the lowest granularity of grouping for GDATE is H for hourly. Set the grouping to H and the stepping to 5M. This will group the data by hours and step throught the data by 5 minutes (using 5 minute variables).
For examples sake, lets take “15M” and compare it with the “D”:
DRC(1,”DATE”,”15M”) on report ran from 1/1/2003 to 1/31/2003 would count from 1 to 2972 (as there are 31x96 = 2972 “15-minute periods” that span that daterange). Report would in this case end up with 2971 rows inserted.
Optional parameters start_date and end_date are needed when you want to expand report by a date range other than the report start date and report end date. In such case, you get to tell your DRC instance which date to start counting from and which to end. For instance :
DRC(1,”DATE”,”D”,”1/1/2003”,”1/3/2003”) would count from 1 to 3 and insert 2 rows into the report.
Cell references can be used for specifying start_date and end_date. For instance :
DRC(1,”DATE”,”D”,A1,B1) would count from 1 to 3 and insert 2 rows into the report when A1 had a value of 1/1/2003 and B1 had a value of 1/3/2003
The source_query_cell parameter is only taken into account if “SQL” was used for second parameter. It tells a DRC what query (identified by cell) will be used to figure out how many rows to expand reports by. A query in source_query_cell must be defined using SQLFIRST.
For example: lets say your facility database contains 80 variables.
A1=SQLFIRST(0,0,”select varnum, name from vardesc”,2,1000)
A2=DRC(1,”SQL”,A1)
In such case, DRC would look at the runtime count of records of query in A1 (which would be 80 for 80 variables), insert 79 rows into the report and subsequently count from 1 to 80.
Note that DRC looks at the true value of record count for any query, even if it was artificially upper bounded by person who designed report. This is a small but important detail, because you can end up running queries that return millions of records. You hardly ever need to see that many records on a spread report. The basic rule of thumb in this case is: think about who is going to use your report and how they are going to use it.
Also, DRC cells can and should be used as QueryID cells in SQLRESULT cells. DRC cells know where their source queries are in the report, and will act as middle man in SQLRESULT executions. To better understand this concept, consider the following setup :
A1=SQLFIRST(0,0,”select varnum, name from vardesc”,2,1000)
A2 =DRC(1,”SQL”,A1) B2=SQLRESULT(A2,1,A2) B3=SQLRESULT(A2,2,A2)
and notice cells B2 and B3 refer to cell A2 for queryid, not A1 (which really defined that query using SQLFIRST)
The ability to point to DRC formulas from SQLRESULT formulas enables one to create nested query driven DRCs. If DRC happened to expand a row with a DRC formula on it, this feature ensures that the nested DRC is properly “relinked” to its freshly created SQLFIRST formula. When nesting DRCs, do not hook your SQLRESULT query ids to one cell. B2=SQLRESULT($A$2,1,A2) means that this it was hooked to cell A2. Instead let DRC do the linking and hooking for you. B2=SQLRESULT(A2,1,A2) would be the correct way of writing that formula given that your DRC is located in cell A2.
Optional parameters min and max are needed when you want to specify which query result record to start counting from (by min) and/or which query result record to end counting at. Going back to the previous example:
A1=SQLFIRST(0,0,”select varnum, name from vardesc”,2,1000)
A2 =DRC(1,”SQL”,A1,20,30)
9 rows would be inserted into the report, while DRC would count from 20 to 30 even if the query in A1 returned 80 result rows.
Another example with the same setup :
A1=SQLFIRST(0,0,”select varnum, name from vardesc”,2,1000)
A2 =DRC(1,”SQL”,A1,20)
59 rows would be inserted into the report and DRC would count from 20 to 80 (since the upper bound is in this can determined during runtime)