Hey all,

I'm trying to build out a set of reports that need the user to select a fiscal year to view via amper auto-prompt before the report runs.

If I use a basic dynamic single-select list of values based of the field containing the fiscal year options (2003-2030), the user ends up having to sift through almost 30 years to select the year desired for viewing.

Is there a way to still dynamically pull year options from the field specified above but reduce the range shown to the user? Like if I wanted the user to only have 2013-2017 to choose from, can that be done in addition to those options being able to update themselves every year so we don't have to come back every year and update a static list of possible years for the user to view?

Any help will be much appreciated.

Thanks!

This message has been edited. Last edited by: CoolGuy,

SUM instead of PRINT?

Without knowing how your data is built from where you are extracting your fiscal year...

Add filter in your code such as

-SET &CY = &YYMD / 10000;
-SET &CY_P2 = &CY - 2;
-SET &CY_N2 = &CY + 2;
-TYPE &CY, &CY_P2, &CY_N2

TABLE FILE....
PRINT FiscalYear
BY FiscalYear
WHERE FiscalYear GE &CY_P2;
WHERE FiscalYear LE &CY_N2;
ON TABLE PCHOLD FORMAT XML
END

MartinY,

Thanks for the insights! I'm pretty new to building reports with WebFOCUS. If you're willing, a few quick questions:

Right up front, thanks for your patience while I try to grasp this. I'm using a column named FISCALYEAR/A4 that holds the values 2003 to 2030 to fill the values list dynamically for a prompt for a particular report. How would I get this filter of yours to work with the data from FISCALYEAR, and/or where would I put it and then connect it to get it to work with the data in the prompt? Side-question: Why did you divide &YYMD by 10000?

Again, thanks!

quote:

Originally posted by MartinY:
Without knowing how your data is built from where you are extracting your fiscal year...

Add filter in your code such as

-SET &CY = &YYMD / 10000;
-SET &CY_P2 = &CY - 2;
-SET &CY_N2 = &CY + 2;
-TYPE &CY, &CY_P2, &CY_N2

TABLE FILE....
PRINT FiscalYear
BY FiscalYear
WHERE FiscalYear GE &CY_P2;
WHERE FiscalYear LE &CY_N2;
ON TABLE PCHOLD FORMAT XML
END

Welcome to Focus !

First, it may not the prettiest way to do it but having "&YYMD / 10000" keep only the year part of the CCYYMMDD date.

Second what you could do is the fallowing

-SET &CY = &YYMD / 10000;
-SET &CY_P2 = &CY - 2;
-SET &CY_N2 = &CY + 2;
-TYPE CurrentDate =&YYMD, CurrentYear= &CY, Previous2Yr= &CY_P2, Next2Yr= &CY_N2

DEFINE FILE.....
FY /D4 = EDIT(FISCALYEAR);
END
TABLE FILE....
PRINT FY
BY FY
WHERE FY GE &CY_P2;
WHERE FY LE &CY_N2;
ON TABLE PCHOLD FORMAT XML
END

CoolGuy,

AutoAmper prompting is for lazy people.

It's just collects all possible values for a field an offers them to the end-user.

Best solution:
If you want more control, make an HTML-screen.
Here you can choose to populate a dropdown with the values from another fex. You could easily make a fex that selects only the years you want by using a WHERE. ( you cannot do that in autoprompt ).

More solutions:
1) "Quiet okay"
You could fill and save a hold file with only the years you want and let the code refer to that file for available field values.

2) "Okay, but needs maintenance"
Do not dynamically retrieve the values. Just hardcode it:

&YEAR.(<2013>,<2014>,<2015>,<2016>,<2017>).Select year.

3) "Nasty, h4ck3rs..."

DEFINE FILE <yourfile>
   SELECTABLE_YEAR/I4 = IF YEAR GE 2013 AND YEAR LE 2017 THEN YEAR ELSE 2013; 
END

...and then use SELECTABLE_YEAR instead of YEAR.



__
Happy h4ck!ng !

MartinY,

Thanks for the filter solution. It looks great and probably works great. Again, I'm way new and am still unsure how to apply this filter to the 'combobox20' HTML control via HTML Composer. How do I get it to populate within the control? Do I need to have this filter in its own .fex file?

Side note: You've been way helpful thus far and I really appreciate your time you're taking to help me.

Thanks!

quote:

Originally posted by MartinY:
Welcome to Focus !

First, it may not the prettiest way to do it but having "&YYMD / 10000" keep only the year part of the CCYYMMDD date.

Second what you could do is the fallowing

-SET &CY = &YYMD / 10000;
-SET &CY_P2 = &CY - 2;
-SET &CY_N2 = &CY + 2;
-TYPE CurrentDate =&YYMD, CurrentYear= &CY, Previous2Yr= &CY_P2, Next2Yr= &CY_N2

DEFINE FILE.....
FY /D4 = EDIT(FISCALYEAR);
END
TABLE FILE....
PRINT FY
BY FY
WHERE FY GE &CY_P2;
WHERE FY LE &CY_N2;
ON TABLE PCHOLD FORMAT XML
END

Dave,

Thanks for your insights and understandings.

Your "Best solution" seems great but I am unsure how you would do that. Solution 1...same. Solution 2 is what I've defaulted to until I can get a solution that I understand and can implement. Solution 3 would work but it wouldn't update itself year after year.

Appreciate it!

quote:

Originally posted by Dave:
CoolGuy,

AutoAmper prompting is for lazy people.

It's just collects all possible values for a field an offers them to the end-user.

Best solution:
If you want more control, make an HTML-screen.
Here you can choose to populate a dropdown with the values from another fex. You could easily make a fex that selects only the years you want by using a WHERE. ( you cannot do that in autoprompt ).

More solutions:
1) "Quiet okay"
You could fill and save a hold file with only the years you want and let the code refer to that file for available field values.

2) "Okay, but needs maintenance"
Do not dynamically retrieve the values. Just hardcode it:

&YEAR.(<2013>,<2014>,<2015>,<2016>,<2017>).Select year.

3) "Nasty, h4ck3rs..."

DEFINE FILE <yourfile>
   SELECTABLE_YEAR/I4 = IF YEAR GE 2013 AND YEAR LE 2017 THEN YEAR ELSE 2013; 
END

...and then use SELECTABLE_YEAR instead of YEAR.



__
Happy h4ck!ng !

This will change every year, no maintenance required:

  
-SET &BEG_DATE = &DATEYY - 2;
-SET &END_DATE = &DATEYY + 2;

Your Calendar Code Goes Here:
TABLE FILE YOUR_CALENDAR_TABLE_VIEW
SUM
    YEAR
  BY YEAR
WHERE YEAR GE '&BEG_DATE'
  AND YEAR LE '&END_DATE';
 ON TABLE HOLD AS BASEAPP/PROMPT_YRS FORMAT FOCUS 
END
-RUN

Then in your program:

  
WHERE YEAR EQ '&YEAR.(FIND YEAR IN PROMPT_YRS.YEAR.Select Year';

You could, of course, introduce a new selection where years are grouped in, say, 5-year intervals, and have that chain to the main list of fiscal years. This selection could be a check box, for example. And the list of real years you can build dynamically rather than hard-coding the list.

George P,

Thanks for the ideas! Will keep that them in mind.

quote:

Originally posted by George Patton:
You could, of course, introduce a new selection where years are grouped in, say, 5-year intervals, and have that chain to the main list of fiscal years. This selection could be a check box, for example. And the list of real years you can build dynamically rather than hard-coding the list.

Tom F.,

Hey, thanks for the filter code. Looks like a good option.

So, I'm new to all this, and am working in AppStudio. I can switch to DevStudio if needed, because I have that too. My problem I have now is where the filter code goes, and how within AppStudio or DevStudio do I link the filter to the HTML drop-down list control so that it populates correctly. Does the filter code go in its own .fex? The WHERE clause in your code example...That goes within my report .fex I'm guessing. Once that is clear to me, where within either dev environment (within what ribbon, panel, etc.) do I go to connect the filter to the control?

Again, thanks for your time and patience with me, and all your help! Everyone else also!

quote:

Originally posted by Tom Flynn:
This will change every year, no maintenance required:

  
-SET &BEG_DATE = &DATEYY - 2;
-SET &END_DATE = &DATEYY + 2;

Your Calendar Code Goes Here:
TABLE FILE YOUR_CALENDAR_TABLE_VIEW
SUM
    YEAR
  BY YEAR
WHERE YEAR GE '&BEG_DATE'
  AND YEAR LE '&END_DATE';
 ON TABLE HOLD AS BASEAPP/PROMPT_YRS FORMAT FOCUS 
END
-RUN

Then in your program:

  
WHERE YEAR EQ '&YEAR.(FIND YEAR IN PROMPT_YRS.YEAR.Select Year';

The code can be run once per year on Jan 1; it will automatically roll to the next 5 years, and Yes, it is in its own fex in a folder on the server(can even be in Baseapp). The baseapp folder is "USUALLY" always on the APP PATH, so, it is always there and the FOCUS DB will always be available.

You can place the focexec in another folder, but, it must be on your APP PATH. If it is not on the APP PATH, then you'll need to add APP PREPENDPATH YOUR_FOLDER_NAME at the top of the report focexec, described below.

The WHERE clause goes in the report focexec. For multi-select, you'll need to add OR to the FIND statement(lookup FIND in the manual)...

hth

My Bad, you are now doing HTML.
In HTML Composer, left-click the List/Drop Down box, select Dynamic, External Procedure, and point to that fex. You'll need to to replace ON TABLE HOLD FORMAT FOCUS to ON TABLE PCHOLD FORMAT XML

Next you'll need to Bind your ampere variable so it will be passed to your report fex.

Tom,

Thank you for the added insights and help. Could you elaborate a bit on how to "Next you'll need to Bind your ampere variable so it will be passed to your report fex."

Thanks!

quote:

Originally posted by Tom Flynn:
My Bad, you are now doing HTML.
In HTML Composer, left-click the List/Drop Down box, select Dynamic, External Procedure, and point to that fex. You'll need to to replace ON TABLE HOLD FORMAT FOCUS to ON TABLE PCHOLD FORMAT XML

Next you'll need to Bind your ampere variable so it will be passed to your report fex.

You need to open a manual or get some training...
IBI does the training, I don't, opening the manual doesn't require training...

Tom,

I understand I've asked you quite a bit today and appreciate what help you have given me. No worries on additional help. I'll figure it out. IBI's online docs are pathetic when it comes to applying concepts within AppStudio context, etc. I've poured over "manuals" they provide but maybe I haven't hit the one pertaining to my issues yet. Hope you have a good rest of the day. Thanks again.

quote:

Originally posted by Tom Flynn:
You need to open a manual or get some training...
IBI does the training, I don't, opening the manual doesn't require training...

Seems pretty extensive to me...

Got it figured out. Thanks to all who tried to and did help in this thread. I ended up using an external procedure tied to the control with the filtering specified and read as XML.