Input tool stored procedure parameter all values

ckelley0

I am able to connect to the stored procedures in our server and I have been able to get data out.  For some of these SPs, there is an additional parameter for which I want all values that exist for that field.  How can I get all values for this parameter?

ckelley0

more details

Our users are exporting excel files from our Practice Engine reporting system which uses stored procedures on the back end.  In order to incorporate these reports into Alteryx workflows, it can be challenging to pull directly from the SQL server.  Leveraging the dynamic select tool looks promising but so far results are mixed.  The ‘spquery.yxmd’ file contains a dynamic select tool and an input tool for the parameter.  I tried ChatGPT to modify the SQL query on the dynamic select tool and the SQL query is below, but it is giving an error in Alteryx ‘Error opening Provider=SQLOLEDB/User ID=birt;Initial Catalog=Engine_CBIZ;Data Source=CAN1DEVDB010;...: no fields found’.  However when I run that in SSMS it works.  Any insights you have will be appreciated.

 

Thanks,

Chris

EXEC dbo.client_rpt_Group_Billing_Comparison_Summary @Staff = -100, @PtrMgr = 1 -- Assuming @PtrMgr needs to be specified too

WITH RESULT SETS

(

    (

        OfficeTitle VARCHAR(MAX),

        Partner VARCHAR(MAX),

        YTDHours DECIMAL(18,2),

        YTDInvoiced MONEY,

        YTDFeesNoProgress MONEY,

        YTDAdjustments MONEY,

        YTDBilled MONEY,

        YTDBilledNoProgress MONEY,

        YTDRealization DECIMAL(18,4),

        LYTDHours DECIMAL(18,2),

        LYTDInvoiced MONEY,

        LYTDFeesNoProgress MONEY,

        LYTDAdjustments MONEY,

        LYTDBilled MONEY,

        LYTDBilledNoProgress MONEY,

        LYTDRealization DECIMAL(18,4),

        HoursVariance DECIMAL(18,2),

        InvoicedVariance MONEY,

        AdjustmentVariance MONEY,

        BilledVariance MONEY,

        PreviousYearStart DATETIME,

        PreviousYearEnd DATETIME,

        CurrentYearStart DATETIME,

        CurrentYearEnd DATETIME,

        CurrentYear INT,

        LastYear INT,

        PEName VARCHAR(255),

        RepTitle VARCHAR(MAX)

    )

);

sp query.yxmd