Hi,
I'm trying to create a daily report that should be sent automatically. I've managed to create it and send it automatically, however the date doesn't update to today's date, I'll have to manually change it.
I've read that there should be a Date Filter option, but I don't have that, it seems that it isn't recognized as a Date and instead I have Label Filter(?, it's Etikettfilter in Swedish, don't know in English). How do I solve this, is it possible to make Jet to understand that it is a Date Filter? Is there any other way to auto-update the Date Filter using Pivot?
Preferably, I'd like to use it in the Filter-field in Pivot, and then have it automatically update. Is it possible?
Date
Votes
5 comments
-
Jet Reports Historic Posts The purpose of DateFilter is to create a range of dates for the report to run from.
I don't know the Swedish translations, so will give this to you in English and hope that others on the forum can help if you need anything translated.
Example (I'm using mm/dd/yyyy format here):
C3 = 1/1/2016
C4 = TODAY()
NL("DATEFILTER",C3,C4) would give me a return of 1/1/2016..8/12/2016
This allows the report to generate all sales within that period of time.
If all you need is to have the current date on the report, then you could use the Excel =TODAY() formula to give the current date, and add a Jet trick to make that number static after the report runs.
Here's the formula in English: =TODAY()*NL(,NP("INTEGERS",1,1)
basically this multiplies the TODAY date by 1, and if your report is saved as a VALUES report, the Jet function at the end causes the number to become a static number once the report has run. -
Jet Reports Historic Posts Hi,
I am not using any NL functions, just pure pivot table. The problem is that the dates aren't recognized as dates. Therefore when I try to filter on dates as rows, instead of having the options from "Date Filter", I have "Label Filter" instead. How can I change so Jet understands that the dates are dates? We have unknowns allowed, is the problem there, since unknown cannot be recognized as a date? -
Jet Reports Historic Posts I am going to guess and say that yes, probably the 'unknowns' are your problem.
In working with Excel Pivot tables, if you have any text in a number field - the pivot table treats the entire field as 'general' instead of formatting as a number type. The dates probably still LOOK like dates (at least mine did when testing), but I can't group by them as I normally would with a pivot table.
If your Datasource has an actual text value for the 'unknowns', then that will cause problems for you.
Perhaps you could try building something into your Jet query to transform the unknowns into a different value? -
Jet Reports Historic Posts The problem was that no tables was recognized as time tables in the Data Warehouse. We had time tables in the Stage database, but when we moved the data they were "normal" tables. Changing the date table to a time table, and also recogninzing the dimensions as "time" solved the problem.
-
Jet Reports Historic Posts I'm glad you were able to find a solution, I had not considered that you might be dealing with Data Warehouse issues, because this is the Jet Professional section of the forum - not the Jet Enterprise area. That's what I get for making assumptions! ;)
Please sign in to leave a comment.