MicroStrategy ONE

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.

Prerequisites

  • Freeform SQL reports are created in MicroStrategy Developer. However, these reports can be manipulated and executed from both MicroStrategy Developer and MicroStrategy Web. Access to the Freeform SQL Editor is available only to Developer Designers with the Define Freeform SQL Report privilege and those with the Create Schema Objects Common privilege.

  • You need the ID value of the database instance used to access the web folder. This value is retrieved as part of the steps To configure a database instance to retrieve web folder resources.

  • If you want to use custom icons for the file types in your web folder, you must create the images to use for the icons, and upload them to a web server. Use the following guidelines when creating the images:

  • The recommended image formats are PNG, GIF, or JPEG.

  • The recommended resolution for the images is 50 x 50 pixels.

  • To take advantage of high-resolution screens on newer mobile devices, you may also upload higher-resolution versions of each image, with the following prefixes after the file name:

  • @1.5x: Recommended for Android devices with higher resolutions. The recommended resolution is 75 x 75 pixels.

For example, if the image you want to use for PDF files is icon_PDF.png, and is 50 x 50 pixels, the high-resolution version is icon_PDF@1.5x.png, and is 75 x 75 pixels.

  • @2x: Recommended for devices for very high resolutions, such as iOS devices with retina displays. The recommended resolution is 100 x 100 pixels.

For example, if the image you want to use for PDF files is icon_PDF.png, and is 50 x 50 pixels, the high-resolution version is icon_PDF@2x.png, and is 100 x 100 pixels.

  • You can include a generic icon for file types that do not have a custom icon defined. For example, if you have defined icons for PDF and XLS, you can include a generic icon for other file types, such as DOC or PPT. If you do not include a generic icon, the default icons for the Multimedia widget are used.

To create XQuery reports to retrieve web folder resources

  1. In MicroStrategy Developer, from the File menu, point to New, and then select Report. The New Grid dialog box opens.

  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 To configure a database instance to retrieve web folder resources.

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

  4. Click OK. The Freeform SQL Editor opens.

  5. In the SQL Statement pane (the top pane on the right), type or paste your XQuery statement. 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/: 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= '
      (: Use this function if you are using custom icons for different file types:)

    • declare function local:getextension($displayname) as

    •    return $file

    • };

      (: Set $uri to your WebDAV folder :)

      let $uri := ('      WebDAV_Folder')

      (: Encode the uri :)

      let $uri := fn:      Database_Instance_ID</MW_DBIGUID(: If you are using custom icons for file types,

  6. In the Mapping pane (the bottom pane on the right), 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 to close the Freeform SQL Editor. The Report Editor opens.

  8. Run the report to ensure that information is being returned from the web folder resource.

  9. From the File menu, select Save As. The Save Report As dialog box opens.

    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.