Here is the high level context (I have struggled with what looks like driving a square peg through a round hole. Need guidance):
I am moving some reports which are driven by Stored Procs from the SSRS platform to Tableau and hence the need to use the existing Stored Procs is paramount.
- Need to use Stored Proc to run this report.
- Need to use 4 dynamic parameters to accept input from user to generate the reports.
Challenges:
- The Stored Proc aggregates granular data, which has 4 Dimensions (say A, B, C and D) and a measure ($ amount), based on only 2 of the 4 Dimensions (A and B). The below is a temporary table which is populated based on a series of business logic queries.
- The output generated by the Stored proc has only 2 Dimensions (A and B and a $ Amount). However, the Stored Proc takes input parameters corresponding to 4 dimensions (A, B, C and D).
- The Dimensions C and D don’t have a clear hierarchical relationship with dimensions A and B. Hence I can’t create a new table to establish the relationship between A, B, C and D in order to bring that into the Tableau Datasource canvas and join it with the main data set.
- When I use Stored proc as a data source then I can’t join it with any other Table in order to enable the population of the Dynamic Parameters.
- The data sets of the 4 dimensions can change from month to month.
- This will be a monthly report and the underlying data changes only once a month on a preset date.
Question:
Is there a way so that I can generate the report using the Stored Proc as described above and also provide a set of 4 Dynamic parameters for the user to generate the report based on that?
Hello @Manoj Menon,
What I was describing here is that using pass-though parameters, you could control one data set from another set.
Parameters in Tableau are global, which means they can be accessed and modified by all vizzes, so in this instance, you could for example, have a chart or table linked to one data set, that interacting with shall modify some linked parameters.
So if we had a table that contains Region, Category and Customer running from the Superstore data source, you have three parameters defined: p_Region, p_Category and p_Customer.
Interacting with the table - clicking fields, shall update the parameters with the values from the table.
Next though, you have a bunch of other charts that run from a Custom SQL output that looks like this:
Select
Division As Sales_Division
,Employee As Divisional_Employee
,Country As Divisional_Country
,Trading_Date
,Count(1) As Divisional_Headcount
,Max(Forecast) As Forecast
,Sum(Sales) As Divisional_Sales
From Company_KPI
Where Division = Coalesce(<parameters.p_Category>,Division)
And Division_Region = Coalesce(<parameters.p_Region>,Division_Region)
So all I'm saying is, is that if your values can exist in a data set of sorts, then they can be utilised in a parameter to control a different set.
Steve
