Question

Photo of Derek Mangrum

0

Show Workflow Attributes on a Report

2

I am working on creating some additional Facilities Requests for our Facilities teams to handle work orders. I have copied the provided Facilities Request Workflow and am making the needed changes. One thing I am looking at is how my Facilities manager can get reporting data on work orders. So, I have been looking at Data Views and Reports.

I am not finding a way to view Workflow Attributes on reports. They aren’t showing up of the ‘fields’ lists or anything. I am suspecting they might be available via some lava-fu… but my fu is weak!  :-)

As a work-around, and by work-around I mean I asked Nick A. and he did it for me (Thanks, Nick!!), we added a Run SQL action to save the ‘Work Order Description’ attribute value into the Workflow’s “Description” property.

UPDATE [Workflow] SET [Description] = '{{Workflow.Details}}' WHERE [Guid] = '{{Workflow.Guid}}'

Some things to note:  The ‘Persist Workflow’ action in the ‘Request’ activity had to be set to ‘Persist Immediately’. This is because we need a Guid (or Id) to reference in our SQL. Then, the last action in the ‘Request’ activity is our ‘Run SQL’ action.

Now, I can just add the Description field to my report, and I got what I need. Luckily, for this particular case, I had a nicely-unused database field that I could stuff my description in to. This certainly is a unique case. The database doesn’t have a bunch of unused fields, just waiting for me like this with other data I may want in a report. So, it would be great to have a way to get these Workflow attribute values on to a report.

If anyone has a Lava-based solution, that would be cool. Or, maybe this is (can/should be) a feature request?

Lastly, I realize this is similar to this question.

But, I thought it was different enough (or newer enough) to warrant a new post. If not, let me know.

  • Photo of Arran France

    0

    This Github issue might be of interest to you.

  • Photo of Ken Roach

    0

    Here's a simple idea for reporting on Workflows that might meet your needs, depending on your reporting requirements:

    Paste the following sql into PowerTools, SQL:

    select    w.[Id],w.[WorkFlowTypeId],wt.[Name] as WorkFlowTypeName,w.[Name] as WorkFlowName,
            w.[Status],[CompletedDateTime],[Key],
            a.[Name] as FieldName,a.[Description],a.[Order],av.[Value]
    from    workflow w, workflowtype wt, attribute a, attributevalue av
    where    w.[Status]='Active' and
            w.[WorkflowtypeId]='24' and
            w.[WorkflowTypeId]=wt.[Id] and
            a.[Id]=av.[AttributeId] and
            a.[EntityTypeQualifierValue]=wt.[Id] and
            av.[Entityid]=w.[Id]
    order by w.[Id], a.[Order];

    Where 24 is the WorkFlowTypeId that you can read from the URL when you are Editing or Managing the workflow you want to report on. 

    This will give you one line for each attribute for each workflow of that type, the name of the attribute, and the attributes value.

    Copy that out to Excel using the Export botton.

    Delete the first two lines of the Excel spreadsheet.

    Then use Excel's filters or Pivot Table functionality to group data to your reporting needs.

    Here's an example using a Pivot Table.

    pivot2.jpg