Bentley Communities
Bentley Communities
  • Site
  • User
  • Site
  • Search
  • User
  • Welcome
  • Products
  • Support
  • About
  • More
  • Cancel
AssetWise ALIM
  • Product Communities
  • AssetWise
  • AssetWise ALIM
  • More
  • Cancel
AssetWise ALIM
AssetWise ALIM - Wiki Creating Report with dynamic field
    • Sign in
    • -AssetWise ALIM - Wiki
      • +1. Planning and Installation
      • +2. Configuring eB
      • -3. eB Objects
        • +Basket
        • +Change Requests and Revisions
        • +Documents
        • +File Launching, Viewing and Markups
        • +Files
        • +Folders
        • How to setup User Preferences for single End user
        • +People
        • +Physical Items
        • -Reports (Searching, EQL, SSRS)
          • Creating Report with dynamic field
          • eQL: Invalid Search Error Message
          • Error : Index was out of range ... in eB Web
          • Facing error while using Data NEAR Type in Full text Search
          • Getting error while downloading the Report Results
          • How to enable 'check box' in interactive report
          • Index Outside array issue with default search
          • Issue importing Extended and Quick Searches
          • Reports screen opened via direct link - quick search dropdown is blank
          • SSRS Authentication issue
          • SSRS Report Server MSSQLSERVER cannot load the SQLPDW extension
          • User is getting a 500 - Internal Server Error when attempting to fill in an eQL search parameter
        • +Tags & ED
        • +Transmittal and Distribution
        • +Virtual Items
        • +Work Orders
      • +4. eB Products and Services
      • +5. AssetWise ALIM LEARNServer - Videos and Presentation
      • +6. AssetWise Release Information
      • +7. Resolved Bugs
      • +8. Wikis to be assigned
      • A file added to a document is shown to have "UNKNOWN MIME TYPE"
      • ALIM eB - internet access needs
      • ALIM Web error - You need at least the 'View' permission on the 'Organization'
      • AssetWise (v. 16.7.60) What's New and ReadMe Files
      • AssetWise CONNECT ALIM login error - The login URL is not whitelisted for authentication.
      • Brava viewer does not display any markups (or markup layers)
      • Bulk Export job stuck
      • Bulk Import results in an error job in the work queue - Failed to Import Documents. Failed to get the files from the repositories.
      • Changes to a repository's properties are not saved
      • COLD process gets error creating new documents
      • Director displays text in small font size which is hard to read
      • Director's Basket Correct Spelling not working
      • Documents can only be found in a wildcard search but not by their full document numbers (prefixes)
      • eB Capture (Kofax Connector) Export Error
      • eB FilePlan rules incompatibilities
      • eB Services (sub-services) do not shut down properly when the eB Service Manager shuts down or restarts
      • eBSMC or Director error - This app can't be activated by the Built-in Administrator
      • eBweb Error When Launching or Viewing Files - Correlation code: 3e7d 19cf-4bf4-4f56-ad58-23133662dff0
      • eBweb user gets a "You don't have the required permission. You need at least the View permission on the 'folder'" error
      • Error accessing an ALIM website - There is an error with the current repository configuration. Please check configuration and ensure base end points are correct.
      • Error creating or deleting objects with a MVA (multi-value attribute)
      • Error Expanding an Auto Filing folder - The query syntax is invalid
      • Error when performing searches (or running reports) in eBweb - Error executing child request for ReportWizard.aspx
      • Export Connector error - eB API ChangeHost/connect
      • Getting Deadlock Error while importing data with ALIM Import/Export tool
      • Getting TimeOut error while expanding Organization and Location in Explorer (eB director)
      • How to delete the files listed under Favorites -> My Downloads?
      • License Management Tool (LMT) gets an error saying "Failed to contact the license server"
      • NED error message - The ProtoProxy is already set
      • Need to delete old publisher packages based on creation time
      • Need to find out users' logon activities
      • Not able to see the edit button, also the actions tab was not showing the Approve, Delete options
      • Question: Can the data and files for ALIM be encrypted?
      • Rendition Job processed by the Interplot Engine failed to create an instance of COM component - error: 8001010a
      • Setting the file name character replacement technique in eB
      • SSIS Connection Error with SSL Certificates
      • +The "New Revision" link/command is missing from the Actions menu
      • The module "adobepdflwrapper.dll" failed to load.
      • The “Email Transmittal(s)” option is missing from a Distribution Order/Transmittal
      • User cannot delete a document, getting error "The selected object is subject to an Approval Workflow and may not be deleted while the workflow is active"
      • User cannot edit (add/remove) the Projects related to a Distribution Order
      • User cannot Edit Document Title in ALIM Web
      • User cannot subscribe to documents - gets error "This Subscriber already exists for Delivery Def 1"
      • User cannot unapprove a document which is not Change Controlled
      • User get STARTTLS error with smtp.office365.com
      • User gets a "Failed to transfer" error message when trying to upload a file using eBweb
      • User gets a "PreventCache" or "502" error when tying to view a large multipage file in Brava
      • User gets a "value is out of range" error in the eB service properties screen
      • User gets a failed job in the mail queue with error "Could not resolve the email address"
      • User gets a timeout error using the ALIM Import Export Tool
      • User gets a Windows Security prompt when attempting to access an ALIMweb site
      • User gets an "Failed to retrieve a valid file cache response" error when attempting to view files using the Brava viewer
      • User gets an "Unable to find a valid user account..." in eBSMC when clicking on a community which uses Bentley Sign-On (IMS Authentication)
      • User gets an error about dependent renditions when processing disposition order list items
      • User gets an error batch in the Kofax Batch Manager regarding BAR CODE NUMBER
      • User gets an error opening excel files
      • User gets an error regarding certificate, security binding when attempting to run the eB System Management Console
      • User gets an error saying "Blazon is taking too long to write file to disk" when attempting to render a file
      • User gets an error uploading a PDF file: 'The signature of the uploaded file does not match that sent by the client'
      • User gets an error when trying to create new Oracle Data Source in the eBSMC
      • User gets an error when trying to unapprove a document
      • User gets an extra "Please click on the files that you want to launch" prompt in internet explorer
      • User gets an Id < 1 error when opening user preferences or system preferences
      • User gets error in a report - The multi-part identifier "j12t0.pdm_file_id" could not be bound
      • User gets error on various screens or does not see new functionalities in ALIM Web after an upgrade
      • User needs to find documents with hidden file(s)
      • User receives an error saying "Root element is missing" when trying to run eB System Management Console eBSMC
      • Users are not able to launch or upload files in a high availability (HA) environment
      • Users get error "No folder could be found that matches the supplied identifier" when trying to delete a folder from the System tree in Director
      • Users get error while accessing ALIM Web - The size of the request headers is too long
      • What are the maximum values I can set for Max Asynchronous Jobs and Max Asynchronous Jobs Per Type?
      • When a new Placeholder is created in Assetwise it is not displaying the ProjectWise Link in Assetwise for that Placeholder
      • Where can I create a service request?
      • Work queue jobs fail with error "Could not resolve the email address for all the recipients"
    • +AssetWise Client
    • +eB - Training Recording
    • eB Create Backup and Restore
    • +eB Standard Viewer
    • Error using Bentley Sing-On (IMS)
    • External user failed to access ALIM site
    • Firewall File and Port Exclusion list
    • Maharashtra Metro Bulk export issue
    • Maharashtra Metro Rail Bulk Update of Tags not working(Customization)
    • NED Menu options
    • Troubleshooting logging on in using IMS as your authentication method

     
     Questions about this article, topic, or product? Click here. 

    Creating Report with dynamic field

       
      Applies To 
       
      Product(s): eB Director
      Version(s):
      Environment:  N/A
      Area:  eQL Reports
      Subarea:  N/A
      Original Author: Gintautas Bajoriunas, Bentley Technical Support Group
       

     

     

     

     

     

     

     

     

    This wiki article explains how to create report with field which picks up values from different attributes.

    Steps to Accomplish

    You have different status attributes on different document types, and you want to design a report that will show those different status attributes in a single column.

    For example, say you have ‘Test Attrib Drawing’ attribute on a APP class, ‘Test Attrib Calculation’ on a ASB class, and ‘Test Attrib Delivery’ on a BILL class.

    In designing a report that includes documents from all these classes, your first inclination might be to just include all those status values in your eQL select statement, like below:

    START WITH Document
    SELECT
     
       Code,
       Name,
       Attributes["Global", "Drawing Status"].Value,
       Attributes["Global", "Calculation Status"].Value,
       Attributes["Global", "Delivery Status"].Value,
       Class.Name
    WHERE    Name LIKE  @Name 

    This gets the job done, but each status value shows in a separate column, and all but one column will be empty for any one document in the report (see attached screenshot)

    A way to collapse all those values into a single column is to use the CASE statement in eQL. The CASE statement acts like a set of if/then conditions. The CASE statement looks like this:

    START WITH Document
    SELECT
        Code,
        Name,
        CASE Class.Code
        WHEN 'APP' THEN Attributes["Global", "Drawing Status"].Value
        WHEN 'ASB' THEN Attributes["Global", "Calculation Status"].Value
        WHEN 'BILL' THEN Attributes["Global", "Delivery Status"].Value
        ELSE ApprovalStatus
       END,
        Class.Name
    WHERE
        Name LIKE @Name

    Note that each WHEN clause does not end with a comma. The comma comes after the END keyword.

    The CASE ... END acts like a single column and displays all the different statuses in that one column according to which WHEN clause the document satisfies. The ELSE clause will handle all those documents that don't have one of those statuses, and it's optional.

    In the Format Layout portion of the Report Designer, you should choose a label for the column that reflects the content of the column, like "Status".
    Obviously, you don't have to restrict yourself to status columns. You can use this technique whenever you want to add conditional logic into the SELECT or WHERE clause.

    See Also

     

    • reports
    • CASE
    • how to
    • eB Director
    • Share
    • History
    • More
    • Cancel
    • Gintautas Created by Bentley Colleague Gintautas
    • When: Wed, Dec 4 2013 6:08 AM
    • Gintautas Last revision by Bentley Colleague Gintautas
    • When: Wed, Dec 4 2013 6:32 AM
    • Revisions: 3
    • Comments: 2
    Recommended
    Related
    Communities
    • Home
    • Getting Started
    • Community Central
    • Products
    • Support
    • Secure File Upload
    • Feedback
    Support and Services
    • Home
    • Product Support
    • Downloads
    • Subscription Services Portal
    Training and Learning
    • Home
    • About Bentley Institute
    • My Learning History
    • Reference Books
    Social Media
    •    LinkedIn
    •    Facebook
    •    Twitter
    •    YouTube
    •    RSS Feed
    •    Email

    © 2021 Bentley Systems, Incorporated  |  Contact Us  |  Privacy |  Terms of Use  |  Cookies