filter report based on FiscYearPer field
I am setting up a report using a Container that points to vdvBudgetAnalysis. This SQL view does not contain any TranDate or PostDate data. Instead, it contains FiscYear, FiscPer, and FiscYearPer fields. I don't need TranDate or PostDate data for my report, IF I can find a way to filter the report using these Fisc fields. I want to include Parameters that will allow the user to select a time range of say, for example, Jan2010 through Jun2011. Without TranDate or PostDate data, and without the system viewing the FiscYearPer field as an actual date datatype value of the form YYYY-MM, I don't know how it can be done. I need a way to make SMI see this field as DATE data so that it can be used to correctly filter the report. I've looked at the CAST and CONVERT functions, but haven't found the magic formula, if in fact there is one. I don't think CONVERT will work because its output is character data, which of course SMI can't use to properly filter the report based on the year/period.
So again, I need to set up Parameters based on time period range, but the problem is that I don't have any TranDate or PostDate data to work with, I only have FiscYearPer (e.g., "2011 - 3"). Has anyone tackled something like this before?
Sage 500 ERP v7.30.5, SQL 2008R2, Excel 2007 SP2
Views: 36
Replies to This Discussion
-
Permalink Reply by Arthur de Breyn on -
Hi Ralph,
I'm not familiar with Sage, but Excel has a function that you can use to 'build' dates from the data you have.
Assuming the FiscYearPer is in column C, see if the formula below helps:
=EOMONTH(DATE(LEFT(C2,4),TRIM(RIGHT(C2,2)),1),0)
This will calculate the last day of the month indicated by FiscYearPer.
Regards,
Arthur
-
Permalink Reply by Ralph on
-
Thanks, Arthur. That won't quite do it. One problem with that approach is that you can't use an Excel expression as a Parameter in SMI. Only actual fields from tables or views, or SQL expressions can be used as Parameters. I'm thinking what I might end up having to do is to just allow all records to be dumped out to the spreadsheet, and set up some Pass Through Variables to allow the user to enter beginning and ending periods for the report. I can then use those entered values (even though they won't actually be filtering the records that get dumped out to the spreadsheet) to populate my "Most Recent Month" and "YTD" columns as needed which I can then use for the pivot table, which is the end result I'm shooting for. It would be great if the FiscYearPer field could be used to fence-off the records being dumped to the spreadsheet, but I don't see a way to do it at this point. Certainly open to any other suggestions though.
-
Permalink Reply by Ralph on
-
By the way, this actually can be done. The trick is to create a new field that massages the FiscYearPer field to remove the dash and the spaces, so that it's simply "201103," for example. Then SMI will be able to use that field for parameters to correctly fence off the records based on period. E.g., return only records from the range Jan2010 (201001) through Jun2011 (201106). I used this SQL CASE statement in my Container, and the output datatype is varChar. It works fine.
CASE WHEN FiscPer < '10' THEN LEFT(FiscYearPer,4) + '0' + RIGHT(FiscPer,1) ELSE LEFT(FiscYearPer,4) + RIGHT(FiscPer,2) END
Follow Alchemex on twitter
Like Alchemex on facebook
© 2012 Created by Admin.
