We have a report that he sends daily, but it relies on data in Adaptive being complete for the prior day before sending it. We have been manually sending it every day rather than allowing it to be scheduled, since the recipient list is long and we don’t want the email to go out to everyone with stale data. We were thinking that we could build a formula in Excel to record whether or not the data has been imported (i.e. if there is data in this cell refreshed by ReportWORQ through OfficeConnect, put a 1, otherwise put a 0). We would like to be able to schedule conditional reports i.e. the report only sends if the cell value after updating has a particular value. Or we could change the recipient list / body to only email me versus the whole team if the data is stale so we know to investigate the failed nightly import in Adaptive before trying to send again. Is there any way to do this in ReportWORQ now?
Jemiller,
Unless the ReportWorq team has an alternative, this may help in the interim. Hopefully I’m not insulting your intelligence . You may already know how to do what I am providing below.
We have a similar issue where I work. Ours is not related to ReportWorq (ie, needing to send out reports only when the data is available) but not begin certain processing unless we have a new data file from a source other than TM1 or if we need to move data from one TM1 model to another.
To do what you are asking, we have a process in the chore (usually the first one) that checks for the existence of the needed file. The code below does that but could be easily modified to check a value in a control cube that gets set on the update process’ epilog tab. As an FYI, I have a former co-worker who is testing some code that I helped her with that is checking for a file’s modified date. This would prevent having to delete the file (see reason for this below).
The code we have to check for a file is
Counter = 1;
# Initial check to determine if file exist (0 - file not available; 1 - file is available).
Check = FileExists('Server\Path\File_Name');
# If file is available, While loop is not performed.
While (Check = 0);
# Check every minute until file is available then continue.
Sleep(60000);
# Use 10 seconds (ie 10000) to test
Check = FileExists('Server\Path\File_Name');
Counter = Counter + 1;
If (Counter = 10);
# Send email; file has been missing for 10 minutes
From = 'From_email_address@example.com';
Subject = 'File missing - abc123.csv';
To = 'To_email_address@example.com';
SS = '<br>';
DS = '<br><br>';
# Multiple Body variables can be used then concatenate to a single variable
Body1 = 'Email from TI process <b>TI_Process_Name</b>' | DS;
Body2 = 'File cannot be found <b>File_Name.csv</b> in <a href=""""\\Server\FolderA\Folder%20B\"""">\\Server\FolderA\Folder B\</a>.' | DS;
Body3 = 'A sentence or two here to instruct what should be done to resolve the issue.' | SS;
Body4 = '';
Body = Body1 | Body2 | Body3 | Body4;
cmd = 'cmd /c f:\TM1_Maintenance\Email\SendMail_HTML.vbs Email_Server_Name 25 ' | From | ' ' | To | ' "' | Subject | '" "' | Body | '"';
ExecuteCommand(cmd, 0);
Counter = 1;
EndIf;
End;
Here is what it does
Initially checks for the existence of the file (can be changed to check a flag in a control cube)
If the initial check finds the file (or the flag in the control cube is positive), the While loop is skipped, and processing can continue. In your case, the process could call the ReportWORQ job.
If the initial check does not find the file (or the flag in the control cube is negative), processing drops into the While loop and sleeps for 60 seconds.
The check is performed again and a counter is incremented. If the counter has not reached 10, execution will return to the While statement for evaluation. If the file was found, it drops out. If not, it sleeps another 60 seconds.
If the counter has reached 10, it means the While loop has been executed 10 times (10 minutes; ie, ten 60-second sleeps) and the file is not available. At this point, the process sends an email to the appropriate TM1 Admin informing them. The counter gets reset and the file (or control cube flag) is checked again every 60 seconds. Another email is sent 10 minutes later if the file is still not available. For us, this continues until someone intervenes.
We have VBS script that we execute by calling it on the ExecuteCommand line. To make the VB code generic, we feed it the From, To, Subject, and Body of the email from variables in each process. Those along with the Outlook server name and port are part of the ExecuteCommand variable (cmd) and the VB code is looking for these inputs. The Outlook server name looks like a variable in my code above, but I replaced the actual server’s name.
Items to note:
The 60 second wait is arbitrary and should be set to your needs.
The email we send is HTML so it can be formatted. The single space and double space HTML tags are assigned to variables (SS and DS) to easily add line breaks to the email. The bold tag < b > will bold the process and file names (don’t use spaces before and after the b, I did that to get it to show correctly in this post ).
If you want to include the path to where the file is supposed to be located (or some other path) and it has spaces, you have to replace the spaces with %20 for the href tag or the link in the email will be broken. This is also the reason for the two-sets of 4 double quotes. The path shown after the href tag is what is displayed so it can have a space in it.
Remember for a successful execution, delete or rename the file being checked (or reset the flag in the control cube). This could be done systematically once the ReportWorq job is executed. If you don’t do this, the file (or flag) will give you a false positive the next time your chore runs.
Duane’s already provided some great suggestions on some of the options people use to manage schedules and triggers outside of of ReportWORQ, which is very helpful when you have more complex business processes & dependencies across multiple systems that impact whether or not a ReportWORQ job should be executed.
One other option that I wanted to mention is the Exception Check feature, which is new in ReportWORQ 5. This feature was designed to help support exception reporting (like an entity’s trial balance showing an out of balance), where you want people to get something from ReportWORQ when any exception is triggered, but it is also useful in other use cases like yours, potentially, too.
The feature works like this: When specifying a report in a job you can reference an “Exception Cell” (up to once per worksheet) and as long as one of those specified cells in a job evaluates to TRUE and the job completes successfully then any generated reports will be distributed. If all Exception Check Cells evaluate to FALSE then reports will NOT be distributed. Here’s a really crude example using our Tutorial sample files that will only evaluate to true if the day of the month is 29:
If my job runs on the 29th of a month, I get this result (Exception triggered, reports distributed!):
If it runs on any other day of the month, I get this result (NO reports distributed):
So, for a daily report like yours, you could create a job with one or more Exception Check cells which evaluate to TRUE, in your case, to indicate that the data is not stale and is OK to send. And if you schedule that job to run at the same time each day it either will / will not distribute reports depending on whether the data is stale-- since you can specify a notification email in a ReportWORQ schedule you’d be able to see if it distributed the reports or not. If they did not, you would just need to manually run the same job once the data from Adaptive has been fully-refreshed.
Hope this gives you another option to think about in ReportWORQ 5.
. You may already know how to do what I am providing below.
