At your convenience, without taking too much of your time, have you faced this issue in the past,
The actual error is as following,
"At least one invalid member reference was encountered in the query.'Cube-xx-xxx-rollup.Iss_Date.Iss_Date.Day->:PC.@MEMBER.20130103' The code looks like the following,
Quoteprompt('BeginDate','date',$current_timestamp,'','','T00:00:00Z'),'yyyymmdd'))#
Is it because there is no data associated to the specific member,
].20130103?
The report works just fine if the user enters previous dates that exists in the cube but if the date doesn't exist,
We are getting the error.
Is there any kind of workaround I can use at the cube creation level or the actual report
to handle this error.
Thx in advance for your time.
:(
The error you're getting makes sense if you don't have that member in the cube. It would be like trying to run a select against a non-existent field in your database. You do have a few options though.
1. Why not generate the entire time dimension? That way your users would be unlikely to select an out of bounds date.
2. How is the user selecting this date? Is the problematic date the default value? Try setting _add_days($current_timestamp,-1) to ensure the default date is always set to yesterday.
3. Set up some JS to do validation on the date prompt before the user runs the report. This is much easier on 10.2, but still possible on all versions.
4. If none of those options are feasible, you can always use the filter function on the day level: filter([Cube].[Iss_Date].[Iss_Date].[Day],[Cube].[Iss_Date].[Iss_Date].[Day].[Category Code]=#prompt('BeginDate','date')#) The filter function will either return a set or not, but won't return an error. Slicing on an empty set will simply return no data.
Paul,
I appreciate the response back.
I would have rather go with the filter statement since presently the use of union and periodtodate is very slow and once I change the date selection in the
same exact report for the same selections, it takes 6 second.
I am getting the following error when I use the filter statement in a Query,
Filter expressions must evaluate to Boolean. Error processing filter 'filter(members([Cube].[Iss_Date].[Iss_Date].[Day]),[Cube].[Iss_Date].[Iss_Date].[Day].[Day - Category Code] = 2013-01-01)'.
Thx so much
All powercube attributes are strings, so wrap the prompt in sq. Also, ensure that the category code is yyyy-mm-ddi. It may include time or other nonsense; just add + ' 00:00:00.000' or whatever to make it match.
Paul,
I'm getting closer but getting this error still,
Filter expressions must evaluate to Boolean. Error processing filter 'filter(members([Cube].[Iss_Date].[Iss_Date].[Day]),[Cube].[Iss_Date].[Iss_Date].[Day].[Day - Category Code] = '00000000')'.
Quotefilter([Cube].[Iss_Date].[Iss_Date].[Day],[Cube].[Iss_Date].[Iss_Date].[Day].[Day - Category Code]
=#sq(timestampmask(prompt('BeginDate','date',$current_timestamp,'/','/','T00:00:00Z'),'yyyymmdd'))#)
I have this search based on
Begin Date and End Date using the Date Prompt working using the following but it is
very slow Quoteunion([Cube].[Iss_Date].[Iss_Date].[Day]->:[PC].[@MEMBER].#sb(timestampmask(prompt('BeginDate','date',$current_timestamp,'','','T00:00:00Z'),'yyyymmdd'))#,
except(
periodsToDate ([Cube].[Iss_Date].[Iss_Date].[Iss_Date],[Cube].[Iss_Date].[Iss_Date].[Day]->:[PC].[@MEMBER].#sb(timestampmask(prompt('EndDate','date',$current_timestamp,'','','T00:00:00Z'),'yyyymmdd'))#)
,periodsToDate ([Cube].[Iss_Date].[Iss_Date].[Iss_Date],[Cube].[Iss_Date].[Iss_Date].[Day]->:[PC].[@MEMBER].#sb(timestampmask(prompt('BeginDate','date',$current_timestamp,'','','T00:00:00Z'),'yyyymmdd'))#)))
Thx again!! 8)
timestampMask doesn't know how to process '2012-01-01/T00:00:00Z' get rid of the slahes from inside the prompt macro.
You can get rid of the union by doing a prevMember() on the second group.
How big is the time dimension? That expression alone shouldn't take more than (at most) a few hundred ms.