How to view parametrized SQL stored procedure and analyze the data dynamically

[bisola1]

I'm new to Qlik and I'm trying to figure out how to use the results of a SQL stored procedure in Qlik sense with parameters. 

Currently I'm using the following in the Data Load Editor:

LIB CONNECT TO 'MyDataBase';

LOAD *;

SQL execute dbo.GetOrder_Report @Startdate='$(var)',@Enddate='$(var)',@Businessid='$(var)';

The stored procedure takes three parameters 

When I click the Load data button, I receive a Load Successful message

I then go to the Data Manager screen which states that the app contains no data but the columns are displayed without any value in them

I click the Load Data button in the top right hand corner of this screen which says the data load is successful

I click the option to Edit The Sheet, this is blank. I drag a Table into the screen

I click the Add Dimension button which gives me the options to add Data on the right hand side of the screen

I click the Add Column dropdown, select Dimension but there is no data to select

Can someone advise as to what I'm doing wrong and how I can see this data.

I've verified that he procedure does return results for these parameter values, there are no access issues and I can see from a trace on the database that the procedure is executing correctly.

Regards

[vinieme12]

declare the variable with values that you need to execute for;

let  vStartDate = '2022-01-01';

let  vEndDate = '2022-09-01';

let  vBusinessId = 'SomeID';

LIB CONNECT TO 'MyDataBase';

LOAD *;

SQL execute dbo.GetOrder_Report @Startdate=$(vStartDate),@Enddate='$(vEndDate)',@Businessid='$(vBusinessId)';

[bisola1]

thank you @vinieme12 

so the thing is, i have like 20 businessids and the date should be till today from inception, so incase i want to always filter on the dashboard, do i have to always come to the data editor to always set variables?

Can i do this  instead

let  vStartDate = '*';

let  vEndDate = '*';

let  vBusinessId = '*';

because i would want to see all the data and then filter it 

Thank you for your assistance

[vinieme12]

I'm not sure how the procedure is defined the Query may have been designed to accept a list of values or only one values

This is something only the person who has created the procedure can confirm

if the query is only created to accept only one value then  Request the db developer(whoever created the procedure for you) to  manipulate the procedure so that it can accept a list of values in the businessId parameter

This would just be a case of changing the below

Where BusinessID = 'vVar'       TO    Where BusinessID  IN ('vVar')

[bisola1]

yes, but is there an option for a dialog box to be displayed and then input the values in them? @vinieme12 

[vinieme12]

NO

[bisola1]

what about the case where you want the date from inception till date @vinieme12 

[vinieme12]

as I said earlier it depends on the underlying query in the stored procedure

if the underlying query is defined as below in the stored procedure

Select * from sometable where date between vStartDate and vEndDate and businessId='SomeId'

Then you will need to pass values for all parameters

if you want it to query all data irrespective of the date range then the underlying query for the stored procedure has be modified to below

Select * from sometable where businessId='SomeId'

You need to reach out to the developer of this stored procedure to make any changes 

As a work-around to query all data, you can pass extreme date values

start date as '1970-01-01'

end date as  '2022-10-10'  (current date or future date)