Good day all,

Any thoughts would be appreciated:

I have set up two embedded datasets: 

-one called 'GetTaskID', which generates a list of all Distinct 'TaskID' s with 'CreationDate' between two date parameters (@Start and @End) provided by the user.

- the other called 'GetData', takes  @TaskID  as a parameter and generates a recordset of actual data

I have set both the available and default values of @TaskID to 'Get Values from query' and specified that it will be the TaskID's returned from 'GetTaskID' query. Running the 'GetTaskID' query (@Start = 01/01/2015 AND @End = 01/02/2015 ) in either my Server Management Studio or the query builder returns the correct result : TaskID's are returned from 1097973 upwards (CreationDate : 05/01/2015) .

However when I run the report, the parameter dropdown for @TaskID is populated correctly (1097973 upwards) but the returned data - that which is dispayed in the report coming from 'GetData' relates to entries from 1078982 - The first entry in the view queried by 'GetTaskID' with a 'CreationDate' of '05/08/2012'.

'GetData' is executing a stored procedure , I think this may be the cause of the issue as I have seen a few articles on the limitations of data/fields returned when using stored procedures within Reporting Services.

I would not generally use a stored procedure to generate data in this fashion but I believe it necessary as I need to coalate an undetermined number of 'TaskNotes' entries for a single 'TaskID' and at the same time procure the rest of the necessary data in one hit.

Confirmation or explanation of my issue and further suggested methods would be greatly appreciated.

Kind regards 

 BigFudge

Can you show how you're implementing the filter for TaskID values in your procedure?

Also are @Start and @End parameters of date/time types (calendar controls)? Or are you passing them as varchar values through a dropdown?

Thanks for the hasty response

'GetTaskID' is such (note I have removed the @Start whilst attempting to debug):

SELECT DISTINCT TicketID FROM vMyView WHERE DateCreated > '01/01/2015' AND DateCreated< @EndDate

@Start AND @End were free text inputs with a default of '01/01/2015' and '01/02/2015' respectively.

'GetData' is like:

@NotesVARCHAR(8000)

SELECT@Notes=COALESCE(@Notes+'<hr><br><hr> ','')+  CONVERT(varchar(5000),ATaskNote)

FROMTaskTableWHERETaskID=@TaskID

SELECT@NotesASNotes,TheseFields....

Hi BigFudge,

Per my understanding that you have three parameters: @Start ,@End,@TaskID in the report, When you execute the query you can got the correct result, but in the report, the data is not correct due to the parameter you have selected, right?

I have tested on my local environment and can't reproduce the issue, please check details information below and try to provide snapshot of the query execution result and the preview result in the report, if you got some error message, please also provide it:

1. Please execute the store procedure in the query designer, make sure the three parameter's value are the same with that you have selected in the report.
2. If the result in the query designer is not the same with that in the report, the issue can be caused by the data not refreshed, may be create files with extension .rdl.data to cache data, please check if you have got the *.rdl.data  file  in the path of the project, if you got, please delete all.
3. If possible, please try to redesign an new report the same with this one to see if the issue will not exists.

If your problem still exists, please try to provide both snapshot mentioned above.

Any problem, please feel free to ask.

Regards
Vic