I would like to preface this post with saying that I have zero code access, completely zero, I can only use InfoAssist for everything I do.

I have many reports that need to be filtered by week, for example, year over reports comparing sales or quantity sold by week. This sounds extremely simple, we have a table that I can join to any other table that contains a date field which has all our fiscal dates, fiscal years, fiscal weeks.

So my thought is to use DTPART, this will allow me to pull the current week by putting this in CurrentWeek/I2=DTPART('&YYMD', WEEK), sweet it gives me a week number, but hold on, its off. This week does not match my fiscal week field its one week early in comparison. So I just create another define Week/I2=CurrentWeek + 1. Now the weeks match up, great! Later it dawns on me that the reason they are off is because calendar week of year is different than our fiscal week of year, at first I dismiss it but then later realize that since next year starts on a Sunday (as does our fiscal week) next year calendar week will match up with fiscal week. This is an issue as then at the first of the year all reports containing this filter will break as I've +1 to all the calendar weeks to match fiscal. This will put calendar week a week ahead of fiscal week as soon as the new year starts. Darn back to the books.

My next attempt is to simply create a DTPART within the table that contains our Fiscal Week and then make a permanent hold file and join to that. Now this Hold file contains both fiscal and calendar week, this way I can create a DTPART and filter by calander week straight across and just display fiscal week. (CurrentWeek/I2=DTPART('&YYMD', WEEK), WHERE CalendarWeek EQ CurrentWeek) This seems do be working great! Until I hard code a date filter to make sure these week filters are returning correct values... Weird, cases per week are now off whats the deal? I go into the table which contains both CalendarWeek and FiscalWeek and drag in FiscalYear, FiscalWeek, CalanderWeek, and DateFull (Daily field formatted YYMD) and run it. I now see the problem, there are two CalendarWeeks with in each FiscalWeek, for some reason calendar week starts on a Saturday (?????) while our fiscal week starts on a Sunday. This is when I learn about WEEKFIRST, except we can't SET things in Infoassist, however it turns out you can globally set it, sweet! So I ask one of the higher ups if that he could put that in on the server side of things and he does and everything is working great! Until I get an email from him the next day saying it was actually messing a lot of their reports up on the retail side of things so he had to revert it Not sure what to do at this point.

This is when DB_EXPR(native_SQL_expression) is made known to me, a function that lets you use/import native SQL functions. This in conjunction with DATEPART() sounds extremely promising as DATEPART's first day of the week is defaulted to Sunday! I first have to create a Today define Today/YYMD=&YYMD and then throw that into DATEPART DB_EXPR(DATEPART(WW, "Today")) it seems to be working! I even created a working filter within our fiscal dates table, and better yet it's displaying the correct week number 26! It matches our fiscal week number and starts on a Sunday! Finally I have found a solution... or so I thought, after trying to implement it into a report (Today/YYMD=&YYMD CurrentWeek/I2=DB_EXPR(DATEPART(WW, "Today")) WHERE FiscalWeek EQ CurrentWeek) I start receiving a plethora of errors the main one being "(FOC32605) NON OPTIMIZABLE EXPRESSION WITH SQL SPECIFIC SYNTAX: DB_EXPR". But only sometimes... sometimes it works, no idea how but mostly it just throws an error, often different ones than the one previously stated.

After several days of trying to figure out how to filter a report by week, I am truly stumped. This functionality is needed for many reports that are requested of me and I am not sure what to do... Is there a way I can use SETS in Infoassist? Is there something I am doing wrong with the SQL function? Is there another workaround??? Bottom line is, I need to be able to filter by week number, one that is automated, not hard coded, a report that runs itself monthly or weekly. Can anybody think of a way to make this work?

Much appreciated.

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

When you say that you must use InfoAssist for everything that you do, I assume that that means that you don't have the authorization to edit your InfoAssist created fex files in the text editor. If you did, then you would be able to create the fex in InfoAssist and then go into the text editor to add the set command...

Second option, are you allowed to create reporting objects? If so, I believe that you can add the set command in the reporting object (and also join your data sources), and then use the reporting object as the data source for your report.

If you don't have authorization for this, perhaps your sysadmin (or someone with the appropriate user rights) could create the reporting object for you (including the set command) and then you just create your reports off of that reporting object...

Thank you for the response!

Yeah I don't have access to edit my code in anyway or create reporting objects :/ But if I could get someone with code access to create these reporting objects for me (I'm not sure they are up for that) it would be independent of the actual master file? As in they could still create reports off of the original master file unaffected by the SET while we could reap its benefits?

Also while this may work, and I will ask them for sure, preferably I am looking for a way I could do this entirely myself as they are usually busy with their own projects and can't really afford the time or don't want to be constantly managing something like this.

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

Yep. The reporting object sits in your folder just like another fex, except you can right click on it and select create report off of the reporting object and then WebFocus will use the reporting object like the master file.

Hopefully some others on the board will chime in with a way for you to do it all in IA..