December 17, 2009 at 11:09 am
I'm trying to come up with a working version of the following query, any help would be much appreciated. The problem is coming up with a valid way to compare 'Pay From Date' with 'Date Hired'. (If I got this working, I'd ultimately be adding a year to date hired, i.e. what is the total wages after the employees first anniversary date.) The query shown does not error, but returns no results. Changing the test in the filter to just 'true' returns the results which would be expected without the required comparison, as does changing '([Employee].[Date Hired])' to '0'.
select
{[Measures].[Hours], [Measures].[AMOUNT]} on columns
, non empty {[Employee].[Employee].[Employee], [Employee].[Employee].[All]} on rows
from [005_PayHistory]
where (
[Payroll Company].[Payroll Company].[My Company]
, [Date].[Year].[2009]
, [Pay Elements Trans].[Posted].&[1]
, {[Pay Elements Trans].[Pay Element Type].&[None], [Pay Elements Trans].[Pay Element Type].&[Salary]}
, {[Pay Elements Trans].[Pay Category].&[Hourly], [Pay Elements Trans].[Pay Category].&[Weekly]}
, filter([Pay Elements Trans].[Pay From Date].[Pay From Date], ([Pay Elements Trans].[Pay From Date]) > ([Employee].[Date Hired]))
)
December 18, 2009 at 10:08 am
I don't know if it's the best solution, but I eventually stumbled on a working filter for my problem:
filter({[Pay Elements Trans].[Pay From Date].[Pay From Date].members * [Employee].[Date Hired].[Date Hired].members}, [Pay Elements Trans].[Pay From Date].currentmember.membervalue > vba!dateadd("yyyy", 1, [Employee].[Date Hired].currentmember.membervalue))
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply