Date parameter "forumula as default:" does not get applied to variant

ReportServer (EN)
1

I’ve created a Dynamic List report. It has a “Date” parameter called “report_month”, which is set to “${today.firstDay().addDays(-1).setDay(1)}”

I then created a variant so I could schedule it. I assumed that the parameter would be evaluated every time that the scheduler runs, but it seems to run with a constant value.

When are parameters evaluated?

2

Hi,
I have created a test, but with a bit different set of parameters (as I didn’t want to wait till the end of the month :-)):

startDate: ${today.addDay(-1)}
endDate: ${today.add(0)}

and a query using

BETWEEN ${startDate} AND ${endDate}

Then I created a variant, saved it and scheduled it to be executed before and after midnight.

RS evaluated all the parameters while executing jobs, returning different data before and after midnight.

Could you create a similar test that could be done now?

Karolina

3

OK, I think I know what the issue is. I think when you open a variant, the parameter selection gets filled in using the formula. If you then change the report, i.e. add columns, etc and then save, I think the parameter which was filled in by the formula gets saved with the variant. Then all subsequent running of the variant will have the parameter stored, not calculated.

You have to make sure that before you click “Save” on a variant that has a parameters which are set by a formula, you clear out any values.

To reproduce:

Create the variant you as you did, but then open the variant via Administration->Reports, and double click your variant. The parameters will be filled using the formulas. If you then click “Save”, those values get stored with the variant and won’t get updated on subsequent runs of the report. Can you check and see if I’m right? I tried on our system and I believe that is what is happening.

4

Just to clarify: when exactly do you set report scheduling? do you click ‘save’ more than one time (i.e. before and after schedulling settings?)

Did you manually changed some parameters after setting the scheduler (then the exclamation mark next to the date field disappears)?

Anyway, I prepared some test, I’ll see the results tomorrow.

Karolina

5

Here are the steps.

  1. Create a report with a parameter report_date, query is:
    SELECT ${report_date} AS report_date
  2. Double click to edit
  3. Parameter seems to be filled into form
  4. Add column
  5. Save as variant
  6. Schedule

I’m going to run a test now, which will run this simple report every hour. I’ll see if the date changes after midnight.

6

Hello,

we are looking at this issue and will keep you informed here.

Cheers,
Eduardo

7

Hi Eduardo, hi bpeikes,

Here are the details & results of my tests:

The report is based on the internal ReportServer database and should return reports executed in the set date & time range.
The report query:

SELECT E.DATE_FIELD, P.VALUE, R.NAME_FIELD
FROM RS_AUDIT_LOG_ENTRY E,
RS_AUDIT_LOG_PROPERTY P,
RS_REPORT R
WHERE
E.ENTITY_ID = P.LOG_ENTRY_ID
AND
P.VALUE IS NOT NULL
AND
P.VALUE = CAST(R.ID AS VARCHAR)
AND
E.ACTION = 'REPORT_EXECUTION'
AND
P.KEY_FIELD = 'report_id'
AND
E.DATE_FIELD BETWEEN ${startDate} AND ${endDate}

Formulas for the parameters:

startDate: ${today.addDay(-1)}
endDate: ${today}

In all cases the scheduler was set to send e-mail with CSV output. On 04.01 I executed all jobs manually, then on 5th and 6th they were executed automatically.
I created the following cases:

Case 1
I created a variant, saved it, leaving the parameters untouched (i.e. they were calculated by the formulas) and scheduled.
Initially the parameters were automatically set to 03.01.2016 and 04.01.2016.

Expected result: the report should contain data about reports executed on and only on the day prior the date of the job execution.
Result: as expected on all days

Case 2
I created a variant, saved it, leaving the parameters untouched (i.e. they were calculated by the formulas) and scheduled. Then I saved the variant again.
Initially the parameters were automatically set to 03.01.2016 and 04.01.2016.

Expected result: the report should contain data about reports executed on and only on the day prior the date of the job execution.
Result: as expected on all days

Case 3.
I created a variant, saved it, and scheduled, then I left the second parameter untouched and I manually set the first parameter to 02.01.2017 . Then I saved the variant again.
Expected result: ?
Result: reports executed by the job on all days included data about reports executed from 02.01.2017 till the date prior to the job execution date.

Case 4.
Similar to case 3. The difference: the first parameter was changed after first job execution. Then the variant was saved.
Expected result: ?
Result:
During the first job execution the report contained data only from the day prior to the job execution.
After the change of the parameter, all jobs returned reports with data about reports executed from 02.01 to the day prior of the date of the job execution.

For me it means that RS recalculates the parameters according to the default formulas, unless a user changes the parameter(s) manually. If the parameters are changed for the report variant attached to the existing job, further job executions reflect the parameters’ change.

Note, that in case of manual change of the parameter(s), the info mark next to the date field disappears.

I’d say we don’t have any bugs here.

Cheers,
Karolina