cancel
Showing results for 
Search instead for 
Did you mean: 

Query Builder: Active Users by Last Login Time with Email Address in Results

Experimenter
Experimenter

I have a query that allows me to search active users by the last login time.  It is built searching on the POM_User class.  What I need to be able to do, though, is to display the results in such a way as to be able to see the associated person's email address.  Is there a way to do this?  In other words, I want to see the user's ID, Name, Last Login Time, and Email Address as columns in the Details tab of the search results.

 

I can see the person's email address if I search on the Person class, but then I cannot search on the current status.  If I search on the POM_User class, I can search on the status, but I cannot display the email address.

 

Thanks for the help.

 

Ken

 

 

4 REPLIES

Re: Query Builder: Active Users by Last Login Time with Email Address in Results

Creator
Creator

Status is on the User object, and email is on the Person object. I don't think you can configure the Rich Client to show two connected objects at the same time.

 

You could create a report where the data is combined. You need to create a PLMXML PropertySet and add the needed properties, and you need an XSL stylesheet to arrange your data. You can go with HTML or Excel format (or both).

 

pic1.jpg

 

Here's XSL I have used...

 

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" 
           xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
           xmlns:plm="http://www.plmxml.org/Schemas/PLMXMLSchema"
           xmlns="urn:schemas-microsoft-com:office:spreadsheet"
           xmlns:o="urn:schemas-microsoft-com:office:office"
           xmlns:x="urn:schemas-microsoft-com:office:excel"
           xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
           xmlns:html="http://www.w3.org/TR/REC-html40" >

	<xsl:output method="xml" version="1.0" indent="yes" />

	<xsl:strip-space elements="*"/>

	<xsl:template match="/">

		<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
    		 xmlns:o="urn:schemas-microsoft-com:office:office"
    		 xmlns:x="urn:schemas-microsoft-com:office:excel"
    		 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
    		 xmlns:html="http://www.w3.org/TR/REC-html40">    	 
			<Styles>
				<Style ss:ID="Subject">
					<Interior ss:Color="#0099ff" ss:Pattern="Solid"/>
					<Font ss:Size="12" ss:Bold="1" />
				</Style>    	     
				<Style ss:ID="header1">
					<Interior ss:Color="#0099ff" ss:Pattern="Solid"/>
					<Font ss:Bold="1" />
				</Style>
				<Style ss:ID="header2">
					<Interior ss:Color="#A0A0A0" ss:Pattern="Solid"/>
					<Font ss:Bold="1" />
				</Style>
				<Style ss:ID="header3">
					<Interior ss:Color="#FFFF99" ss:Pattern="Solid"/>
					<Font ss:Bold="1" />
				</Style>
				<Style ss:ID="s23">
					<Interior ss:Color="#ffffff" ss:Pattern="Solid"/>
				</Style>
				<Style ss:ID="s24">
					<Interior ss:Color="#ffffff" ss:Pattern="Solid"/>
				</Style>
				<Style ss:ID="s63">
					<NumberFormat ss:Format="yyyy\-mm\-dd;@"/>
					<Interior ss:Color="#ffffff" ss:Pattern="Solid"/>
				</Style>				
			</Styles>

			<Worksheet ss:Name="UserList">
				<Table>
					<Column ss:AutoFitWidth="0" ss:Width="60" AutoFitWidth="1"/>
					<Column ss:AutoFitWidth="0" ss:Width="150" AutoFitWidth="1"/>
					<Column ss:AutoFitWidth="0" ss:Width="150" AutoFitWidth="1"/>
					<Column ss:AutoFitWidth="0" ss:Width="80" AutoFitWidth="1"/>
					<Column ss:AutoFitWidth="0" ss:Width="70" AutoFitWidth="1"/>	
					<Column ss:AutoFitWidth="0" ss:Width="60" AutoFitWidth="1"/>
					<Column ss:AutoFitWidth="0" ss:Width="70" AutoFitWidth="1"/>
					<Column ss:AutoFitWidth="0" ss:Width="55" AutoFitWidth="1"/>
					<Row>
						<Cell ss:StyleID="header1">
							<Data ss:Type="String">User Id</Data>
						</Cell>
						<Cell ss:StyleID="header1">
							<Data ss:Type="String">Person</Data>
						</Cell>
						<Cell ss:StyleID="header1">
							<Data ss:Type="String">email</Data>
						</Cell>
						<Cell ss:StyleID="header1">
							<Data ss:Type="String">Organization</Data>
						</Cell>
						<Cell ss:StyleID="header1">
							<Data ss:Type="String">City</Data>
						</Cell>
						<Cell ss:StyleID="header1">
							<Data ss:Type="String">License</Data>
						</Cell>
						<Cell ss:StyleID="header3">
							<Data ss:Type="String">Inactive User</Data>
						</Cell>
						<Cell ss:StyleID="header3">
							<Data ss:Type="String">Last Login</Data>
						</Cell>
					</Row>

					<xsl:call-template name="user"/>

				</Table>	 
				<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
					<Selected/>
					<ProtectObjects>False</ProtectObjects>
					<ProtectScenarios>False</ProtectScenarios>
				</WorksheetOptions> 
			</Worksheet>
		</Workbook>		

	</xsl:template>
	<xsl:template name="user" >
		<xsl:for-each select="//plm:User">
			<xsl:variable name="personref" select="substring-after(./@personRef, '#')" />
			<xsl:variable name="person" select="//plm:Person[@id=$personref]" />
			<Row>
				<Cell ss:StyleID="s24">
					<Data ss:Type="String">
						<xsl:value-of select="plm:UserData/plm:UserValue[@title='user_id']/@value" />
					</Data>
				</Cell>
				<Cell ss:StyleID="s24">
					<Data ss:Type="String">
						<xsl:value-of select="plm:UserData/plm:UserValue[@title='user_name']/@value" />
					</Data>
				</Cell>
				<Cell ss:StyleID="s24">
					<Data ss:Type="String">
						<xsl:value-of select="$person/plm:UserData/plm:UserValue[@title='PA9']/@value" />
					</Data>
				</Cell>
				<Cell ss:StyleID="s24">
					<Data ss:Type="String">
						<xsl:value-of select="$person/plm:UserData/plm:UserValue[@title='PA6']/@value" />
					</Data>
				</Cell>
				<Cell ss:StyleID="s24">
					<Data ss:Type="String">
						<xsl:value-of select="$person/plm:UserData/plm:UserValue[@title='PA2']/@value" />
					</Data>
				</Cell>
				<Cell ss:StyleID="s24">
					<xsl:choose>
						<xsl:when test="(plm:UserData/plm:UserValue[@title='license_level']/@value = 1)">
							<Data ss:Type="String">consumer</Data>
						</xsl:when>
						<xsl:when test="(plm:UserData/plm:UserValue[@title='license_level']/@value = 0)">
							<Data ss:Type="String">author</Data>
						</xsl:when>
						<xsl:when test="(plm:UserData/plm:UserValue[@title='license_level']/@value = 5)">
							<Data ss:Type="String">admin</Data>
						</xsl:when>
						<xsl:otherwise>
							<Data ss:Type="String">other</Data>
						</xsl:otherwise>
					</xsl:choose>												
				</Cell>
				<Cell ss:StyleID="s23">
					<Data ss:Type="Number">
						<xsl:value-of select="plm:UserData/plm:UserValue[@title='status']/@value" />
					</Data>
				</Cell>
				<Cell ss:StyleID="s63">
					<Data ss:Type="DateTime">
						<xsl:value-of select="plm:UserData/plm:UserValue[@title='last_login_time']/@value" />
					</Data>
				</Cell>
			</Row>		
		</xsl:for-each>
	</xsl:template>
</xsl:stylesheet>

 

Re: Query Builder: Active Users by Last Login Time with Email Address in Results

Experimenter
Experimenter

You can use a PFF (Property Finder Formatter) and map the other properties you mentioned from other properties in other classes in the PFF. Hope this helps.

Re: Query Builder: Active Users by Last Login Time with Email Address in Results

Experimenter
Experimenter

Using PFF is what I had to do.  I was hoping there was a way to configure a query to do this that I was not seeing.

 

Thanks everyone for the help.

Re: Query Builder: Active Users by Last Login Time with Email Address in Results

Creator
Creator

Forgot about PFF. It's a good option Smiley Happy I like the report, because it skips a couple of steps I would make in any case.