Generating a Report Users can Filter on

[Amanda16]

I'm very new to using Alteryx and am trying to build out a report that my user's can filter on. The report is currently generated daily in Excel where they can filter on the information relevant to each individual. There is also conditional formatting to highlight the more urgent matters for them. I'm struggling with how to produce something comparable through Alteryx without having to add the manual step of opening the output file in Excel to add the filters and conditional formatting. Does anyone have any recommendations on how resolve this?

[MarqueeCrew]

Welcome @Amanda16!

If you have a private gallery (server), you can create an application that users would interact with and it would create the filtered view for each individual.  If the filters are static, you can generate static views of the data and implement the conditional reporting using Alteryx report tools.

Now, if your users are expecting their data in Excel and you want to use conditional formatting then here are my suggestions:

1. Consider using Tableau
2. Use Excel and write your generic data to a hidden tab (RAW_DATA)
3. Setup a Pivot Table (I like pivot tables) that reads data from the hidden tab
   1. Set Option to refresh data on open
   2. Create conditional formatting in Excel
4. Publish the Excel document

Using Alteryx, you can readily identify urgent matters and create alerts/warnings that would generate a list of users with action recommendations.

If you have more specific questions, I or others will be happy to assist you.  Also, if you are going to Inspire, you can bring your data to the solutions center and someone there will be available to work through a solution with you.

Thanks,

Mark

[JohnJPS · Accepted solution]

In the reporting "Table" tool, you can enable column rules to dynamically highlite rows based on certain conditions.  I've done this for some very minimal formatting in the attached workflow.

Also, if you have access to Powershell, you can use that to enable auto-filter on the resulting spreadsheet.  I also did that in the attached workflow after the spreadsheet was rendered.

The powershell code for this is as follows:

param([string]$excelFileFath='C:/testDir/tmpReport.xlsx')

## Instantiate the COM object
$excelObj = New-Object -ComObject Excel.Application

$excelWorkBook = $excelObj.Workbooks.Open($excelFileFath)
$excelWorkSheet = $excelObj.WorkSheets.item("sheet1")
$excelWorkSheet.activate()

## Turn on auto-filter
$headerRange = $excelWorkSheet.Range("a1","z1").AutoFilter() | Out-Null

## close everything
$excelWorkBook.Save()
$excelWorkBook.Close()
$excelObj.Quit()

## make sure the process is really gone...
Get-Process | where {$_.Name -like "Excel*"} | % {
    $Id = $_.id
    if ((Get-WmiObject Win32_Process -Filter "ProcessID = '$id'" | select CommandLine) -like "*embed*")
    {
        Stop-Process -Id $id -WhatIf
    }
}

You'll need to save that off to the .ps1 file that the workflow is calling using the Run Command tool.

(Aside: I otherwise don't know of any way to enable the auto-filter directly from Alteryx) 

This is certainly tweakable to suit your needs; (e.g. "C:/testDir" is hard coded in the workflow... adjust as needed.)

Hope that helps!