web
You’re offline. This is a read only version of the page.
close
Skip to main content
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Show Previous Position...
Microsoft Dynamics AX (Archived)

Show Previous Position in SSRS

(1) ShareShare
ReportReport
Posted on by Community Member

Hi Everyone,

I'm stuck in a situation where i want to show current and previous position at the same table to present history of the employee positions in SSRS Report. but i could not able to show the previous position. its only show the current position.

my code is :


while select * from hcmPositionWorkerAssignment
join hcmjobdetail
join hcmjob
join hcmPositionDetail
join hcmPositionType
where hcmjobdetail.Job == hcmjob.RecId
&& hcmjob.RecId == hcmPositionDetail.Job &&
hcmPositionDetail.PositionType == hcmPositionType.RecId
&& hcmPositionDetail.Position == hcmPositionWorkerAssignment.Position
&& hcmPositionWorkerAssignment.Worker == hcmWorker.RecId
{
EmployeeOrganizationHistoryTmp.PositionDescription = hcmPositionDetail.Description;
EmployeeOrganizationHistoryTmp.PosStart = hcmPositionWorkerAssignment.ValidFrom;
EmployeeOrganizationHistoryTmp.PosEnd = hcmPositionWorkerAssignment.ValidTo;
EmployeeOrganizationHistoryTmp.Grade = HcmCompensationLevel::find(jobdetail.CompensationLevel).CompensationLevelId;
EmployeeOrganizationHistoryTmp.JobFunction = hcmPositionType.TypeId;

EmployeeOrganizationHistoryTmp.insert();

}

i want to show both of these positions like screenshot 1. but my report shows only single record which is current see screenshot 2

8640.Capture2.PNG7181.Capture3.PNG

and i also try this condition as well. but failed to present history.

hcmPositionWorkerAssignment.ValidTo >=  DateTimeUtil::utcNow(). 

*This post is locked for comments

I have the same question (0)
  • Verified answer
    Zain Bokhari Profile Picture
    Zain Bokhari 3,208 Moderator on at
    RE: Show Previous Position in SSRS

    Hi Safdar,

    Just to add to the information provided by Vilmos.

    A simple select or while select statement when applied to a validTimeState key enabled table.

    select tableObject or select * from tableObject


    is equivalent to 

    select validTimeState(DateTimeValueRightNow) * from tableObj;


    Which will get you the current(providedDateTime) active record/s.

    If you want to get inactive records as well, you will need to add a range with validTimeState keyword. Something like this:

    while select validTimeState(fromDate,toDate) * from tableObject
{
   ...
}


    That will give you active and inactive records both. I'm sure you'll figure out a way to get just the current and the previous one form the provided information.

  • Verified answer
    Vilmos Kintera Profile Picture
    Vilmos Kintera 46,149 on at
    RE: Show Previous Position in SSRS

    You are not applying date effective patterns correctly to tables defined with a validtimestatekey. Read the documentation, it has examples too:

    download.microsoft.com/.../using_date_effective_patterns_ax2012.pdf

    Here is the select statement syntax for reference too on how to use validTimeState keyword:

    msdn.microsoft.com/.../aa656402.aspx

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Dynamics 365 Community Calls Return
Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft
Responsible AI policies for the Community

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Andrés Arias – Community Spotlight

We are honored to recognize Andrés Arias as our Community Spotlight honoree for…

Congratulations to the August Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Syed Haris Shah Profile Picture

Syed Haris Shah 9

#2
Mea_ Profile Picture

Mea_ 4

#3
Martin Dráb Profile Picture

Martin Dráb 2 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans