Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     ReportCaster : dynamic parameters

Read-Only Read-Only Topic
Go
Search
Notify
Tools
ReportCaster : dynamic parameters
 Login/Join
 
Silver Member
posted
Hello again everybody

I've got a potentially interesting question about using dynamic parameters in a ReportCaster report.

I tried searching for topics on this question, but I couldn't find any.

*puts ball on penalty dot*
Here's the setup:

The users at our company can write their own reports (using MRE Domain Builder), these reports will be created as "My Reports", which they're able to share. They all need to start from a Reporting Object that's been pre-defined to make things easier.

There was a question from one user in particular, who was wondering if his monthly process of entering the date parameters for his "My Report" could be automated/scheduled to run each month. Because he's got to go through a lot of different reports, but he needs to fill in the date parameters. It should be run every first of each month, and the parameters would need to be the first of previous month until the last of previous month.

I told him such things should be possible with ReportCaster running his reports monthly.

*walks back to get a run-up to shoot the ball*

My idea was to create one pre-execution fex, which would populate a &FROM and &TO variable with the correct values. And then to use these values in the scheduled "My Report".

I already got the code to get the correct dates:
DEFINE FILE DIMENS
FROM2/I4YM=(&YYMD/100)-1;
FROM/YYMD=FROM2;
TO2/I4YM=(&YYMD/100);
TO/YYMD=TO2;
END

TABLE FILE DIMENS
PRINT
FROM
TO
BY DAYNUM
WHERE READLIMIT EQ '1';
WHERE RECORDLIMIT EQ '1';
ON TABLE HOLD AS monthly FORMAT ALPHA
END
The "DIMENS" table is just the table that makes the most sense in this case, but none of it's values are used at all. I would then use variables in the Parameters field of the ReportCaster Task.

*trips over his own feet*

But how do I pass these FROM and TO fields to the normal fex that is run after the pre-execution fex?

Also, a more general question about ReportCaster.
I've read about ReportCaster only being able to process one request at a time or something, which would mean the -INCLUDE balblabal.fex wouldn't work if one was to put it in a reportcaster fex.
But what I don't get is, why would they provide an option to execute "My Reports" then, because they _all_ have a -INCLUDE of the Reporting Object they're using.
Can someone explain to me why I'm comprehending this falsely?

*hopes for someone else to shoot the ball in the net*
Any help is greatly appreciated.

Tim.

This message has been edited. Last edited by: <Maryellen>,
 
Posts: 44 | Location: Belgium | Registered: January 10, 2005Report This Post
Guru
posted Hide Post
One area of confusion- an -INCLUDE is not another fex, it becomes part of the fex it is included in. A pre-execution fex is not an -INCLUDE, but a separate process. What you want to do would work in an -INCLUDE. It would be harder to do in the pre-execution fex (you would have to store the resulting file in a persistant place)
It would be easier to set the date range in pure Dialog Manager, using date arithmetic. You almost have to have a little utility fex (or several) to calculate commonly used date ranges to use with Report Caster. (and have a convention to always use the same variable names ie &STARTDATE and &ENDDATE in your fexes).
If you create them in a hold file, you will just need to -READ that file to put the values into &variables. The instructions for that are in the documentation. (Allocate the file, syntax of -READ, etc).
 
Posts: 391 | Location: California | Registered: April 14, 2003Report This Post
Platinum Member
posted Hide Post
Here's some sample DM logic so you don't have to use DEFINE with -READ:

-* Subtract one month from today's date
-SET &DTE_PREV1=DATECVT((DATEADD((DATECVT(&YYMD,'I8YYMD','YYMD')),
- 'M',-1)),'YYMD', 'I8YYMD');
-*
-* Change the day element of the dates to the 1st.
-SET &CURMONTH = EDIT(&YYMD,'999999') | '01';
-SET &PREVMONTH = EDIT(&DTE_PREV1,'999999') | '01';
-*****
-TYPE Today's Date = &YYMD
-TYPE Current Month beginning = &CURMONTH
-TYPE Previous Month beginning = &PREVMONTH
-*
-* The WHERE statement should look like this:
-* WHERE date_field GE &PREVMONTH and date_field LT &CURMONTH
-* This method of setting the date parameters was chosen to avoid tricky
-* calculations to determine the last day of a month -- 28, 29, 30, 31 --
-* and in case the report doesn't execute on the 1st of the month
-* but on the first business day of the month.
I think the Caster limitation you mention with regards to -INCLUDE refers to the fact that in RC you can't create report output that would result in more than one report to distribute -- Caster can only take a single answer set. This -INCLUDE shouldn't be a problem.
 
Posts: 118 | Location: DC | Registered: May 13, 2005Report This Post
Silver Member
posted Hide Post
Thanks for the great and fast replies you guys.

The idea was to use one single fex that would just calculate these dates and put them in variables, so I could use the same variables in every report that is run monthly.

What I have to do now to make this work is this:
1. I ask the user to share his "My Report" he wants to be run monthly.
2. I check what Reporting Object is used (It's just a simple join of a few tables most of the time)
3. I make a new fex and put it in a dir so the EDAServer can find it. I copy the code from the user's "My Report" along with the join I could find in the Reporting Object.
4. I add the code for the date-calculation to the fex.
5. Then, I schedule a job using this fex to run monthly.

I thought it would be really nice to just be able to use the user's "My Report", have them define variables for the dates, and make a pre-execution fex that calculates the dates and put them in variables, so the "My Report" could eventually use these variables.

I tried this strategy up til now:
Create a fex to run as a pre-execution fex which calculates the dates and puts them in &FROM and &TO.
And then in the Parameters-tab in the "Advanced" window of the Task, I put Name: FROMDATE (there's a "WHERE ... GE &FROMDATE"-statement in the fex), and Value: &FROM.
But this doesn't work.
Instead an e-mail is sent giving me this error-message: Fieldname or computational not recognized : &FROM
(also tried &&FROM and &&TO, to make them global, right? doesn't work either though).

Any further suggestions?

/edit: By the way, I used your example to make the date calculations fex.
 
Posts: 44 | Location: Belgium | Registered: January 10, 2005Report This Post
Silver Member
posted Hide Post
*shameless bump*

I still don't know how to pass variables from the pre-execution fex to the normal fex.

Any help is appreciated.
 
Posts: 44 | Location: Belgium | Registered: January 10, 2005Report This Post
Expert
posted Hide Post
As N.Selph mentioned, the Pre-process FEX is separate FEX - you cannot set up local or global DM varibles for use in the main FEX.

As he mentioned, the best solution would be to add a -INCLUDE in each of the fexes. The -INCLUDE creates the desired date variables.

The -INCLUDE could create -DEFAULT values for the "My Reports" variables - if the user runs the report online, he will either get prompted for the date variables or will have a published page with the parameter values as form objects.

When th report runs via ReportCaster, the -DEFAULT values kick in.
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Master
posted Hide Post
Here's an idea.

Create 2 global variables in the edasprof.prf called &&FSTDAYLSTMTH and &&LSTDAYLSTMTH. Use the -SET statements described above then the users could create the procedures and use these variables in the where statements. Then when caster runs the job it can uses these variable as well.
 
Posts: 865 | Registered: May 24, 2004Report This Post
Silver Member
posted Hide Post
Excellent idea TexasStingray.

Too bad I can't use these global variables in the parameters option in ReportCaster. That would be the ideal solution.

And thanks Francis, I now better understand what N.Selph was trying to explain, I'm going to try that solution as well and see which one works out best.

Thanks everyone for their feedback.
Once again, this forum proves its usefulness.
 
Posts: 44 | Location: Belgium | Registered: January 10, 2005Report This Post
Master
posted Hide Post
Tim, If the user uses the parameter in the procedure. They should be able to schedule it without adding parameters, and because it should exist the job should run just fine. give it a try and please post your findings.
 
Posts: 865 | Registered: May 24, 2004Report This Post
Silver Member
posted Hide Post
quote:
Originally posted by TexasStingray:
[qb] Tim, If the user uses the parameter in the procedure. They should be able to schedule it without adding parameters, and because it should exist the job should run just fine. give it a try and please post your findings. [/qb]
This does work indeed. But not every user that's making reports knows about this parameter.
I figured, if everybody could just write their own reports with their own parameter-names, I could just enter the global variables in the ReportCaster gui. But it doesn't really matter, that would be ideal, since I'm the one scheduling the reports.

I will inform the users about these parameters I created with your help, so they can use them in their reports. The users will be able to use the ReportCaster soon, there are still some other problems we need to fix first.

Thanks again.

*edit: typo.
 
Posts: 44 | Location: Belgium | Registered: January 10, 2005Report This Post
Platinum Member
posted Hide Post
Hi There

Sorry for the late reply I am just seeing this now.

What you can do is update the BOTPARMS table directly from the prefex. The only problem with this is that (I may be wrong) caster will load the botparms when the job starts so it is too late to update them for that job. They will be fixed for the next job.

In our environment we call SQL Server Stored procs from the caster prefex to manipulate BOTPARMS and BOTSCHED. For example, we delay reports by 10 minutes if the data they use is not yet loaded by using this method.

Can you use some other process, such as SQL Server DTS to update your BOTPARMS? What kind of table are they stored in?

Does this help?
 
Posts: 246 | Location: Montreal, QC, Canada | Registered: October 01, 2003Report This Post
Silver Member
posted Hide Post
Thanks for the reply.

I'll look into it later.
 
Posts: 44 | Location: Belgium | Registered: January 10, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     ReportCaster : dynamic parameters

Copyright © 1996-2020 Information Builders