Thanks, In the example the start and end date are parameters selected by the user. In this case the start and end date are not parameters set by the user but rather set by the id selected by the user. So the question is how do I pass them to the SQL query based on the selected id.
Okay, that's a bit trickier. What if you set up a parameter for the ID. So the user will select an ID from that parameter then...use a custom SQL with a subquery. I don't know your database schema, but it might look something like this:
SELECT * FROM Calendar WHERE dte BETWEEN (SELECT MIN([Order Date]) From Orders where [Customer ID]=<Parameters.ID>) and (SELECT MAX([Order Date]) From Orders where [Customer ID]=<Parameters.ID>)
I appreciate all your help, unfortunately the id does not exist in the table.
The table is a calendar table that lists the dates and if the day is a business day or not. Hence the need to be able to select an ID and then pass the start and end dates to the custom sql based on the selected id. if I could pass field values or set the dates to parameters this would be a non issue
OK, but where do the dates come from? How do you get a start and end date from the selection of an ID?
They are part of a SharePoint dataset. A person will enter the project id, the start date and the date the project is completed. I connect to the SharePoint list and am trying to use the start and end date to calculate business days from the calendar table that lives in SQL Server.
So, you have 2 data sources--SharePoint and SQL Server--correct?
By any chance, can you provide some details on the structure of each of these sources and, perhaps, a sample of each?
Here you go,
One more question. How are you connecting to the SharePoint list?
Just through the SharePoint options through data source > new data source > SharePoint lists
It's a bit difficult without having actual SQL and SharePoint data sources to work with, but I'll give it a go. How about something like this?
Bring in both your SQL table and your SharePoint list as 2 different data sources. I'm using a single spreadsheet with two different tabs, but you'd have separate data sources (more on a potential issue with this later).
Once you have both tables in the data pane, set up a join like this:
This will give you all the records in the SQL table where the Dte field is between the Start Date and Completion Date from your SharePoint list.
From there, I think you could just add a filter on Project ID.
The biggest potential issue here is the ability to do the >= and <= joins on a SQL data source combined with a SharePoint data source. I have a suspicion that might not work.
let me give that a whirl