Hey all!

So, I recently figured out how to get the right values for a new DEFINE field I created. I then tried to use it as a filter via a WHERE clause statement and when the amper auto-prompting facility pops up, the single-select dropdown doesn't populate any values for me to choose from. It only shows the control box with the down arrow, but no values. If I use the DEFINE field in the report, I get the values without a problem. For some reason when trying to use it as a dynamic filter it doesn't play nice and give me values to choose from.

Has anyone here ever ran across this type of issue before and figured out how to fix it?

Here is my DEFINE field code:

WKEND/MDYY=IF SRPROD.DIMDATE.FISCALYEAR EQ '2016' AND SRPROD.DIMDATE.WEEKDAY EQ 7 THEN DATECVT(SRPROD.DIMDATE.DATEFULL, 'YYMD', 'MDYY');

Here is my WHERE clause code:

WHERE ( SRPROD.SRPROD.SRPDSTORENAME EQ '&SRPDSTORENAME.(FIND SRPROD.SRPROD.SRPDSTORENAME,SRPROD.SRPROD.SRPDSTORENAME IN SRPROD).Store:.' ) AND ( SRPROD.DIMDATE.WKEND EQ '&WKEND.(FIND SRPROD.DIMDATE.WKEND,SRPROD.DIMDATE.WKEND IN SRPROD).Week ending:.' )

Anyone see anything that should be different? Again, when I print WKEND out in my report request, I get my week end dates. Not sure why it can't be used as a dynamic parameter as well... ???

Thanks in advance for any help!

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

Just a thought....try putting a -RUN after your DEFINE. I suspect the WHERE clause is being parsed and run before the DEFINE gets executed.

Dan,

I put a -RUN directly after my DEFINE field with no luck (before the END command), and I then tried a -RUN right after the END command of the DEFINE request and before the TABLE FILE ... line with no luck either.

Any other guesses?

CoolGuy,

I suspect that the problem has to do with order of operation. I believe the -RUN executes after prompts are evaluated. Defines might do the same, not sure. You will want to run a sql trace to identify whether that case statement is pushing down to the database. My guess is, its not pushing the case statement, and since the data doesn't get run for the define until after the report is run, it has no data to populate the drop down.

Currently there are issues in my Dev environment beyond my control, so I can't give you anymore then this right now. But for now, I would start by commenting out the prompt, and running a SQL trace on the query. If your system is defaulting to a WebFOCUS managed join, my guess is thats your problem. If its not, then I would run the trace from the server by putting it in your edasprof. Just make sure to write it to another file so you can read it. (I have the code commented out in my EDASPROF, I can provide when my Dev is back up).

A workaround / other option, would be to create a virtual field in the table on the database that handles the conversion and read it in through the masterfile. One thing that I don't understand is why are you only changing the date in some circumstances? Also where is your ELSE statement? I would add ELSE MISSING or something to that affect so that you don't see any date if the conditions don't pass. Also, if its a smart date you don't have to convert it anyways. You could just say THEN SRPROD.DIMDATE.DATEFULL. Should work just fine, unless you are using DATECVT because it passes to the Database.

Here is the trace to put in your edasprof. Remember to remove the comments. And don't leave it in. Replace [location] and [app path] with the appropriate path information for where you want to store the trace file.

-* SET TRACEOFF=ALL
-* SET TRACEUSER=/[location]/ibi/apps/[app path]/traces.&TOD.EVAL.trc
-* SET TRACEON=SQLDI
-* SET TRACEON=SQLCALL
-* SET TRACEON=SQLAGGR//CLIENT
-* SET TRACEON=STMTRACE//CLIENT
-* SET TRACEON=STMTRACE/2/CLIENT
-* SET TRACEON=STMTRACE  

This trace will allow you to capture what is being passed to the DB for your prompts.

eric.woerle,

Thanks for your insights. I tried adding ELSE MISSING and get the following:

0 ERROR AT OR NEAR LINE      2  IN PROCEDURE ADHOCRQ FOCEXEC *
 (FOC171) TYPE OF MISSING CALCULATION IS NOT SPECIFIED ON LEFT SIDE: ELSE
 0 ERROR AT OR NEAR LINE      7  IN PROCEDURE ADHOCRQ FOCEXEC *
 (FOC003) THE FIELDNAME IS NOT RECOGNIZED: SRPROD.DIMDATE.WKEND
 BYPASSING TO END OF COMMAND
 (FOC009) INCOMPLETE REQUEST STATEMENT

I think the ELSE clause needs to be in valid DATE format? How can I get the ELSE clause to just display a string of text or MISSING instead of a date format like your saying to do? Sorry about not understanding a lot of this. Learning as fast as I can.

Also, where is this EDASPROF file to insert the trace code into? I've never done this before, so any more detail would be helpful. Thanks.

I tried adding the SQL trace code you gave me above my report request and it doesn't give me a trace to look at. So, I used the following instead and got:

SET MSG          = ON
SET TRACEOFF     = ALL
SET TRACEON      = SQLAGGR//CLIENT
SET TRACEON      = STMTRACE//CLIENT
SET TRACEUSER    = ON
SET XRETRIEVAL   = ON
-SET &ECHO       = 'ALL'

14.16.55 BT  (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
14.16.55 BT  (FOC2594) AGGREGATION IS NOT APPLICABLE TO THE VERB USED
14.16.55 AE    SELECT
14.16.55 AE   T2."DateFull",
14.16.55 AE   T2."WeekDay",
14.16.55 AE   T2."FiscalWeekOfYear",
14.16.55 AE   T2."FiscalYear"
14.16.55 AE    FROM
14.16.55 AE   [server].dbo.DimDate T2
14.16.55 AE    WHERE
14.16.55 AE   (T2."WeekDay" = 7) AND
14.16.55 AE   (T2."FiscalYear" = '2016')
14.16.55 AE    ORDER BY
14.16.55 AE   T2."FiscalWeekOfYear";
0 NUMBER OF RECORDS IN TABLE=       52  LINES=     52
0 HOLDING HTML FILE ON PC DISK ...

I'm not sure if this source trace output gives me what will tell me if my system is defaulting to a WF managed join like you were curious about or not.

I'm really new to this whole SQL traces thing. I need to read up more on it.

Thanks for what help you are giving me eric.woerle.

quote:

I tried adding ELSE MISSING and get the following:

I forgot to mention that you also need to allow missing on the define. Write it as such and you should be ok:

 WKEND/MDYY MISSING ON=IF SRPROD.DIMDATE.FISCALYEAR EQ '2016' AND SRPROD.DIMDATE.WEEKDAY EQ 7 THEN DATECVT(SRPROD.DIMDATE.DATEFULL, 'YYMD', 'MDYY') ELSE MISSING;
 

The code that I provided is meant for the Reporting Server. The Reporting Server has things called Profiles. The general user profile is EDASPROF. The EDASPROF (or whatever profile is being invoked) will execute every time you make a connection to the reporting server. It basically holds the connection information. It can hold other things, but we'll leave it at that for now. The SET TRACEUSER command in the trace syntax is providing a location to store the file. Doing it this way will tell you exactly what SQL is being generated by the prompt request.

What I did notice in your SQL statement is that you don't have a case statement. To me that means that your define isn't getting translated and passed to the database and it is being handled on the reporting server. Because of this, I doubt that the define is in play when the prompt is returning. If you get your trace working in the EDASPROF, I bet you'll see an error. I would try pushing your define into the Master File or as a virtual field into the database. Of course, you could always create an HTML launch page and by pass the need for an auto prompt all together...

eric.woerle,

Thanks for the added information regarding the need to turn MISSING ON in the DEFINE. That got rid of the error.

Where does one find the general user profile EDASPROF? Are you saying the TRACEUSER URL defines the location for the EDASPROF file? Or just a trace file with output from the EDASPROF file?

I didn't have a case statement in the SQL statement because you were saying to comment it out earlier I believe:

DEFINE FILE SRPROD
WKEND/MDYY MISSING ON=IF SRPROD.DIMDATE.FISCALYEAR EQ '2016' AND SRPROD.DIMDATE.WEEKDAY EQ 7 THEN DATECVT(SRPROD.DIMDATE.DATEFULL, 'YYMD', 'MDYY') ELSE MISSING;
END
TABLE FILE SRPROD
PRINT
SRPROD.DIMDATE.WKEND AS 'End of Week'
BY  LOWEST SRPROD.DIMDATE.FISCALWEEKOFYEAR AS 'Fiscal Week'
WHERE ( SRPROD.DIMDATE.FISCALYEAR EQ '2016' ) AND ( SRPROD.DIMDATE.WEEKDAY EQ 7 );
-*WHERE ( SRPROD.DIMDATE.WKEND EQ '&WKEND.(FIND SRPROD.DIMDATE.WKEND IN SRPROD).WKEND:.' );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
...

Should I rerun with it uncommented? I did such and I get a bunch of HTML/Js gobbledygook because the auto-prompt facility pops up asking for a WKEND value without and values to choose from.

Sorry I keep asking questions. I do really appreciate your help though.

quote:

I didn't have a case statement in the SQL statement because you were saying to comment it out earlier I believe:

If the code you provided just now is what you ran for the trace, then you did what I was suggesting. Since WKEND is a part of the report, I would have expected it to be in the SQL trace. Thats what I was looking for and what I didn't see. Defines should be passed down to the database. When they aren't, the request will generally ask for more information then it needs. Joins can get a little Wonky depending on their complexity, etc. When I run a trace, I like to see everything happening at the Database Level. If I don't I generally look for a different way to do it.

quote:

Where does one find the general user profile EDASPROF?

You need to log onto the reporting server, go to the Workspace and expand the configuration files. There you will see the edasprof.

quote:

Are you saying the TRACEUSER URL defines the location for the EDASPROF file? Or just a trace file with output from the EDASPROF file?

The Traceuser URL defines where to save the trace file so that you can find it later. Provide the full path and map it to where you want to retrieve it from. Putting it somewhere in your app path just makes it easier to get to from your Data Server, but as long as you can get to the directory on the server, you can always go directly there, however you prefer.

When you update the edasprof and run it again, run it with the prompts uncommented. Once the prompts come up, you will have the trace you need. Go to the directory that you told it to store it in, and check what messages are in the log. There will probably be a few so you might need to look at them all to find the one you need.

eric.woerle,

Thank you so much for all your great information and clearing things up for me! I will look into what you've given me and see how far I get, and then get back with you in regards to SQL traces and applying them to the EDASPROF file, etc.

While trying to figure out why my defined field wasn't populating any values for WKEND, I came across some additional help and utilized it to get the functionality I will be needing I believe based off of a real field for now. My new code is as follows:

-SET &ECHO=ALL;
DEFINE FILE SRPROD
-*-- DETERMINE MONDAY AND FRIDAY DATES ---
MON/YYMD = DATEMOV(SRPROD.DIMDATE.DATEFULL, 'BOW');
FRI/YYMD = DATEMOV(SRPROD.DIMDATE.DATEFULL, 'EOW');

-*-- DETERMINE SUNDAY AND SATURDAY DATES ---
SUN/YYMD = DATEADD(MON, 'DAY', -1);
SAT/YYMD = DATEADD(FRI, 'DAY', +1);

-*-- DETERMINE PREVIOUS SUNDAY AND SATURDAY DATES ---
PSUN/YYMD = DATEADD(SUN, 'DAY', -7);
PSAT/YYMD = DATEADD(SAT, 'DAY', -7);

-*-- DETERMINE 2wks PREVIOUS SUNDAY AND SATURDAY DATES ---
PSUN2/YYMD = DATEADD(SUN, 'DAY', -14);
PSAT2/YYMD = DATEADD(SAT, 'DAY', -14);

-*-- DETERMINE 3wks PREVIOUS SUNDAY AND SATURDAY DATES ---
PSUN3/YYMD = DATEADD(SUN, 'DAY', -21);
PSAT3/YYMD = DATEADD(SAT, 'DAY', -21);

-*-- DETERMINE 4wks PREVIOUS SUNDAY AND SATURDAY DATES ---
PSUN4/YYMD = DATEADD(SUN, 'DAY', -28);
PSAT4/YYMD = DATEADD(SAT, 'DAY', -28);
END
-RUN

TABLE FILE SRPROD
PRINT
     SRPROD.DIMDATE.DATEFULL AS 'Base Date'
     SRPROD.DIMDATE.MON AS 'This Mon'
     SRPROD.DIMDATE.FRI AS 'This Fri'
     SRPROD.DIMDATE.SUN AS 'This Sun'
     SRPROD.DIMDATE.SAT AS 'This Sat'
     SRPROD.DIMDATE.PSUN AS 'Prev Sun'
     SRPROD.DIMDATE.PSAT AS 'Prev Sat'
     SRPROD.DIMDATE.PSUN2 AS '2wks ago Sun'
     SRPROD.DIMDATE.PSAT2 AS '2wks ago Sat'
     SRPROD.DIMDATE.PSUN3 AS '3wks ago Sun'
     SRPROD.DIMDATE.PSAT3 AS '3wks ago Sat'
     SRPROD.DIMDATE.PSUN4 AS '4wks ago Sun'
     SRPROD.DIMDATE.PSAT4 AS '4wks ago Sat'
WHERE ( FISCALYEAR EQ '2016' ) AND ( SRPROD.DIMDATE.DATEFULL EQ '&DATEFULL.(FIND SRPROD.DIMDATE.DATEFULL IN SRPROD).DATEFULL:.' );
ON TABLE NOTOTAL
END

Tomorrow I will work with the above code to see if I can get a control populating based off of the SAT defined field or not. Hope I will have success. If it still gives me troubles, I'm going to try the tracing you spoke of and look for logs that pertain to the error and case statement not getting passed to the database. I may try and add the defines in the master file to see if that fixes it, and if not, talk some more with one of our DBAs to just add a WKEND, WKEND2, WKEND3 and WKEND4 to our dimdate table for this weekenddate and the 3 previous weekenddates if possible for a solution.

Thanks again eric.woerle! Will keep you and this thread posted on progress.

eric.woerle and others,

I got back in to work this morning and tried adding my DEFINE fields directly to the master file and then use one of them as a user-supplied parameter, and that did the trick! I am getting values to choose from in the auto-prompt facility!

Thanks again for all your help eric.woerle and others!