Retrieve data on table click

[konlin22]

Hello - 

I have a datasource that by default connects to a SQL table and retrieves all data.  Too much data to place into a report table.  I'm trying to filter the data on click from another table.  For example - i have one table with records of current ORDERS.  When i click on that table, i want the other datasource to retrieve data only for that order.  Like passing a parameter basically.  But right now, if i drag a table onto the screen and assign fieds to it, it wants to populate all data.  1000s of records.  How can i get it to only retrieve sql data on click from the ORDERS table?  thank you.

[Icey]

Hi @konlin22 ,

If you want to filter data before loading into data model, you can enter your requirements in SQL statement.

Best Regards,
Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[konlin22]

Thanks for the replies.  I would like to use the SQL statement option, but I can't figure out how to pass the parameter from the first table.  For example, if I could create a data statement like:

SELECT * From OrderDetails WHERE ORDERID = '@orderID'

And then pass the orderID parameter from the selected row in my first data table.  That would save me retrieving 100,000 + records and then filtering locally.  And it would be quicker.  

Any help would be appreciated.  thanks

[gckcmc]

Sounds like you're connecting to a datasource and using it in direct query mode, and not import.  If that's true, then you want to either pull down the data and then use filters to restrict the content (statically in the query, or dynamically with slicers etc)....or you want to restrict the data downloaded in the first place.

I don't know of a way to pass a dynamic parameter to a query that would restrict the amount of data retrieved, after it's already loaded...meaning in direct query you pull the data and then the visuals operate on it.  You can certainly setup a query that restricts the amount of data by passing in a parameter value to restrict, but that's a static method.

You could achieve this by passing in an order as a query and build multiple queries with different order #, and then make those all available...that might get tedious depending on the number of them to build.

otherwise, I'd pull the data down and then operate with slicers.  1000s of records isn't big, IMHO, I'm working with data sets much bigger than that, and pull the data down to work on.