Specifying Date Array in Main report for use in subreports

[jcrawford08]

Hi All,

Running CR XI R2 in a SQL environment...

I have a main report that is essentially just a display interface for several summary style sub-reports contained on it.

Two of the subreports have a grouping formulas that are dependent upon a series of dates.

I'd like to have it where I specify those dates on the main report and then those dates are passed through a shared variable to the Subreports. Then moving forward, we only have one location to update the dates as opposed to two.

I would then simply reference the shared variable in the subreport and it would use it for grouping the records.

Any ideas on structure for creating an array-style shared variable with dates that I could reference in the subreports?

-jcrawford-

Not in word only, but in deed also... 1Jn3:18

 

[lbass]

You should use date parameters instead. Create the same parameters in both container and subreports and then link the subs to the main report by linking the parameters to each other, so you are prompted only once. Be sure to use the dropdown in the linking screen in the lower left to choose the parameter {?parameter} instead of the default (?pm-?parameter}. This way you will only be prompted once for the date.

-LB

 

[jcrawford08]

Unfortunately, I have it as a scheduled report off of our server, so that it will run automatically. Since it is scheduled, parameters are a no-go.

My thought was to simply have an array style formula in the main report that the user simply edits from the container and then it links to the two sub-reports...

I know it may sound more difficult, but I think the stability of having it run as scheduled trumps the simplicity of parameters in this case.

So, granted, the simplest way is to have them as a multiple value date parameter that is then passed to the sub-reports; however, for this specific application, I need to have it hard-coded into the report, yet still variable, and as simple as possible.

So, although it doesn't save MUCH work, what would be the most effective way to set up a hard-coded set of dates that would then be passed from the container report to the subreports?

-jcrawford-

Not in word only, but in deed also... 1Jn3:18

 

[lbass]

The problem is that you can't use shared variables in selection formulas, and you can't group on them. You could potentially limit the dates in a container report using a record selection formula and then linking on the date field would limit the dates in the report, but I can't tell whether this is a scenario that would achieve what you are trying to do.

-LB

 

[jcrawford08]

The main container report actually has NO tables in it, so I was thinking of just having a formula in the container report that would specify the dates needed, and then use that formula to link to the two subreports...



I know it's an odd set up, but essentially the other subreports that pull into the container report are just dashboard summaries of various reports that are only date dependent, and referencing currentdate in various capacities in the reports themselves allows for them to operate independently without having to be linked to the main report.

-jcrawford-

Not in word only, but in deed also... 1Jn3:18

 

[lbass]

Not that odd. What is the nature of the dates you want to pass to the subs? If you are trying to automate this without reference to parameters, then I guess there is some consistent logic to the dates you want to pass.

-LB

 

[jcrawford08]

It is actually the second Tuesday of every odd month; unfortunately, although this is the standard, scheduled time, committees are subject to change.

For now I have the dates specified out using dateserial, then using if/then logic to establish the grouping of the subreport i.e.:

if {Date.Field} in [dateserial(2011,1,1),dateserial(2011,3,1)] then groupingA else groupingB

only I list out the specific dates the committees happened.

What I would hopefully do, is create a list of dates using this format in a formula of the container report and then pass it to the subreports, where that information would be used to set up groups (i.e. GroupingA if the date is in the specified dates or GropuingB if it is not in the specified dates)

Thoughts?

-jcrawford-

Not in word only, but in deed also... 1Jn3:18

 

[lbass]

You want one group for committees that met on the second Tuesday of odd months, and a separate group if they didn't? Do you have one date field that holds the meeting dates? You should add that table to the main report, add criteria that limit it (if necessary) to the container report. But what is the content of the subreports? I'm having trouble seeing what the end result would be. Can you show a mock example of how the report would look once implemented? An array might be possible, but I don't know why you are distinguishing Tuesday meetings from others--so not sure how you could build in criteria for meetings that are not on expected dates.

-LB

 

[jcrawford08]

Essentially the majority of our workflows are not dependent on approval date, these are the only two workflows we have that are - hence why I need the information to only affect them and also why I am bringing all this information together in sub-report form.

There are two ways that files are approved, with a group consensus at committee - for files that have more in-depth information and need a more comprehensive review, and by single sign off. Single sign-off files are signed off on a weekly basis.

The approval date of the file is the only data we have to indicate whether it was a single sign off file, or whether it was in need of committee review. Hence, that is why we need to group based on whether the approval date was within X set of dates.

Committee meetings are SCHEDULED for the second Tuesday of every other month, but as with any group of people or standing meeting there are often cancellations and reschedules that do not fit this norm, so that's kind of what I'm trying to engineer into the report, is the flexibility it needs yet still be as simple as possible...

-jcrawford-

Not in word only, but in deed also... 1Jn3:18

 

[lbass]

So what determines the dates use in the "X set of dates"--that also have to be predictable if you are automating this.

-LB