Creating XQuery Reports to Retrieve Web Folder Resources

You can create XQuery reports to retrieve each web folder resource. This allows you to make the web folder resources available directly in the Multimedia widget to MicroStrategy Mobile users.

If required, you can define custom icons to display for different file types in your web folder. For example, you can define your own icon to display for PDF files in the folder.

To Create XQuery Reports to Retrieve Web Folder Resources

1. In MicroStrategy Developer, choose File > New > Report.
2. On the Freeform Sources tab, select Create Freeform XQuery report.
3. In the Source area, select the database instance created to access web folder resources, as described in Retrieving Web Folder Resources with XQuery.

   If no database instance is defined in the metadata, the Freeform SQL Editor cannot be loaded and a message is displayed.

4. Click OK.
5. Enter or paste your XQuery statement in the SQL Statement pane.

   You can use the statement below to build your XQuery statement. If you use this statement, make sure you make the following substitutions in this XQuery statement:

   • WebDAV_Folder: The URI for the web folder that contains the resources to make available using the Multimedia widget. You can access web folders shared with the WebDAV protocol that are hosted with IIS, Sharepoint, and Apache.

     When providing the URI for a web folder, make sure that you end the URI with a / character. For example, a URI such as http://example.com/MyWebFolder/ uses the proper syntax, but supplying it as http://example.com/MyWebFolder without a / character at the end can cause connection problems.

   • Database_Instance_ID: The ID for the database instance that connects to the WebDAV resource. You must provide this in the XQuery statement as the value for the MW_DBIGUID attribute.
   • http://yourwebserver/icon_[extension]: If applicable, the URL for custom icons that you can use for the file types in your WebDAV folder. For example, http://example.com/images/icon_PDF.png.

     	declare copy-namespaces no-preserve, no-inherit;

     	declare namespace D= 'DAV:';

     	declare namespace
     	Office="urn:schemas-microsoft-com:office:office";

     	declare namespace mstr-rest =
     	"http://www.microstrategy.com/xquery/rest-functions";

     	declare function mstr-rest:webdav($uri, $method, $payload,
     	$headers) external;

     	declare function mstr-rest:uri-unescape($uri) external;

     	declare function local:bool-as-num($value) as xs:string

     	{

        		if (not($value))

        		then xs:string(0)

        		else xs:string($value)

     	};

     	declare function local:gethidden($value) as xs:string

     	{

        		if (not($value))

        		then xs:string(0)

        		else xs:string($value)

     	};

     	declare function local:getcollection($value, $resourcetype) as xs:string

     	{

        		if ($value)

        		then xs:string($value)

        		else if ($resourcetype/D:collection)

     			then xs:string(1)

     			else xs:string(0)

     	};

     	declare function local:getdisplayname($href) as xs:string
     	{ 
     		let $name2 := fn:replace($href, ".*/([^/]+)/*$", "$1")
     		return mstr-rest:uri-unescape($name2) 
     	};

     	(: Get the current file extension :)

     	(: Use this function if you are defining custom icons for
     	different file types :)

     	declare function local:getextension($displayname) as 
     	xs:string

     	{

     		let $rev-display := fn:codepoints-to-string(fn:reverse
     		(fn:string-to-codepoints($displayname)))

     		let $rev-ext:= fn:substring-before($rev-display, ".")

     		let $ext := fn:codepoints-to-string(fn:reverse
     		(fn:string-to-codepoints($rev-ext)))

     		return $ext

     };

     	(: Use custom icons for different file types :)

     	(: Use this function if you are defining custom icons for
     	different file types :)

     	declare function local:getthumbnailurl($ext) as xs:string

     	{

     		let $extU := fn:upper-case($ext)

     		let $file :=

     		(: xls files :)

     		if($extU = 'XLS' )

     		then "http://yourwebserver/icon_XLS"

     		(:pdf files :)

     		else if ($extU = 'PDF')

     		then "http://yourwebserver/icon_PDF"

     		(: default icon :)

     		(: To use the default icon configured for the Multimedia
     		widget, use a blank string "" :)

     		else "http://yourwebserver/icon_default"

         return $file

     };

     	(: Set $uri to your WebDAV folder :)

     	let $uri := ('WebDAV_Folder')

     	(: Encode the uri :)

     	let $uri := fn:iri-to-uri($uri)

     	(: Set up other parameters for the WebDAV external function 
     	call :)

     	let $method := ('PROPFIND')

     	let $payload := ('')

     	let $header1 := ('Depth: 0')

     	let $header2 := ('Depth: 1')

     	(: Make the call to the WebDAV function :)

     	let $result1 := mstr-rest:webdav($uri, $method, $payload,
     	$header1)

     	(: The following call ensures that either you return 
     	information for one resource or all the resources in all
     	folders :)

     	let $iscollectiontest := local:getcollection 
     	($result1/D:multistatus/D:response/D:propstat/D:prop
     	/D:iscollection, $result1/D:multistatus/D:response
     	/D:propstat/D:prop/D:resourcetype)

     	let $result := 
     	if ($iscollectiontest=xs:string(1)) then
     	mstr-rest:webdav($uri, $method, $payload, $header2)
     	else
     		$result1

     	(: The uri may need character replacement :)
     	let $baseuri := fn:replace($uri, "(^[h|H].+:/+[^/]*/).*", 
     	"$1")

     	return 

     	<Table>

       		<ColumnHeaders>

     			(:Create attribute form MW_URL with Type=Text:)

     			<ColumnHeader  name='MW_URL' type='xsd:string' />

     			(:Create attribute form MW_Name with Type=Text:)

     			<ColumnHeader  name='MW_Name' type='xsd:string' />

     			(:Create attribute form MW_ModifiedOn with
     			Type=DateTime :)

     			<ColumnHeader  name='MW_ModifiedOn' type='xsd:datetime' 
     			/>

     			(:Create attribute form MW_ModifiedOnText with
     			Type=Text :)

     			<ColumnHeader  name='MW_ModifiedOnText'
     			type='xsd:string' />

     			(:Create attribute form MW_DBIGUID with Type=Text :)

     			<ColumnHeader  name='MW_DBIGUID' type='xsd:string' />

     			(: The MW_ThumbnailURL column is optional, remove the 
     			comments if you return a custom icon for each resource 
     			and use Type=Text :)

     			(: <ColumnHeader  name='MW_ThumbnailURL' type=
     			'xsd:string' /> :)

     			(: The MW_Description column is optional, remove the 
     			comments if you return a description for each resource 
     			and use Type=Text :)

     			(:<ColumnHeader  name='MW_Description' 
     			type='xsd:string' /> :)

     		</ColumnHeaders>

     	<Data>

     	{

     		let $first_node := 
     		$result/D:multistatus/D:response[1]
     		for $response in $result/D:multistatus/D:response
     		for $prop in $response/D:propstat/D:prop

     			let $displayname := if (fn:data($prop/D:displayname) ) 
     				then fn:data($prop/D:displayname) 
     				else local:getdisplayname(fn:data ($response/
     				D:href))

     		(: final processing of baseuri to url :)

     		let $url := fn:replace(fn:data($response/D:href), "^(/)", 
     		$baseuri)

     			let $compare_name := if (is-same-node($first_node, 
     			$response)) then "" else 
     				if($displayname) then fn:upper-case($displayname) 
     				else fn:upper-case($response/D:href)

     			let $iscollection := local:getcollection 
     			(fn:data($prop/D:iscollection),$prop/D:resourcetype)

     		order by $prop/D:isFolder, $compare_name 

     		where $iscollection = '0'

     		return 

     	<Row>

     		<MW_URL>{$url}</MW_URL>

     		<MW_Name>{$displayname}</MW_Name>

     		<MW_ModifiedOn>{fn:data($prop/D:getlastmodified)}
     		</MW_ModifiedOn>

     		<MW_ModifiedOnText>{fn:data($prop/D:getlastmodified)}
     		</MW_ModifiedOnText>

     		(: Provide the ID of the database instance here :)
     		<MW_DBIGUID>Database_Instance_ID</MW_DBIGUID>

     		(: If you are using custom icons for file types, 
     		uncomment the following line :)

     		(: <MW_ThumbnailURL>
     		{local:getthumbnailurl(local:getextension($displayname))}
     		</MW_ThumbnailURL> :)

     		(: If your resource has description information, 
     		uncomment the following line and provide the syntax to 
     		return the description information :)
     		(: <MW_Description></MW_Description> :)

     	</Row>

     	}

     	</Data>

     	</Table>

6. In the Mapping pane, map the columns in your XQuery statement to attributes. Follow the requirements listed below when mapping columns in your XQuery statement to attributes:
• You must map a column to the ID form for each of the following attributes:
• MW_URL: Map the ID form of this attribute to the Text data type.
• MW_Name: Map the ID form of this attribute to the Text data type.
• MW_ModifiedOn: Map the ID form of this attribute to the DateTime data type.
• MW_ModifiedOnText: Map the ID form of this attribute to the Text data type.
• MW_DBIGUID: Map the ID form of this attribute to the Text data type.
• MW_ThumbnailURL: If you are not using custom icons for file types, do not create this attribute form. If you are using custom icons, map the ID form of this attribute to the Text data type.
• MW_Description: If you do not return a description of the web folder resource, you should not create this attribute form. If you do create this optional attribute, map the ID form of this attribute to the Text data type.
• When creating the attributes listed above, make sure you list them in the same order they are returned in the <Row></Row> section of the XQuery statement.
7. Click OK.
8. Run the report.
9. Choose File > Save As.

   You must save the report before you can run it, otherwise a message is displayed.

10. Enter a name for the report and click Save.

Once the XQuery report to retrieve the web folder resources is created, you must connect this report to a Multimedia widget. For information on configuring a Multimedia widget, see the MicroStrategy Mobile Administration Help.