Как получить значение параметра на основе набора результатов из хранимой процедуры в SSRS 2014?

Я хотел бы знать, как выбрать параметр на основе набора результатов из хранимой процедуры.

Мой код выглядит примерно так:

CREATE PROCEDURE usp_UserInformation (@ReportRunDate DATE = NULL) 
AS
WITH CTE AS (
SELECT -- some fields
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
WHERE RegisteredDate = DATEADD(MONTH, DATEDIFF(MONTH, -1, @ReportRunDate) - 2, -1)
--I'll output the result set after applying all the logic like

SELECT UserID, UserName, DOJ
FROM CTE

Теперь, когда пользователь выбирает дату в отчете, я создал еще один параметр и пытаюсь получить записи из «Доступных значений», используя «Получить значения из запроса». Я хотел бы показать список идентификаторов пользователей из приведенного выше набора результатов запроса и разрешить пользователю SELECT на основе этого.

Параметры

Доступные значения параметров

Значения из запроса

Дизайн

5-е изображение

ОБНОВЛЕНИЕ: я создал еще одну хранимую процедуру, так как другой альтернативы нет. Теперь мой вопрос: как передать второе значение хранимой процедуры первому в предложении WHERE?

CREATE PROCEDURE usp_UserInformation (@ReportRunDate DATE = NULL) 
AS
WITH CTE AS (
SELECT -- some fields
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
WHERE RegisteredDate = DATEADD(MONTH, DATEDIFF(MONTH, -1, @ReportRunDate) - 2, -1)
--I'll output the result set after applying all the logic like

SELECT User_ID, UserName, DOJ
FROM CTE
WHERE User_ID = EXEC usp_Get_UserID

Я ищу что-то подобное, где пользователь может сначала выбрать идентификатор и любую дату по своему выбору в отчете.

Параметры отчета

PS: мне не разрешено писать встроенный SQL, разрешены только SP. Я не хочу создавать два SP для одного и того же отчета.


business-intelligence reporting-services sql-server-2014 ssrs-2014
person Julaayi    schedule 08.07.2016    source источник

Ответы (2)


arrow_upward
0
arrow_downward

Используйте эту хранимую процедуру в качестве источника доступных значений для параметра UserID и убедитесь, что параметр UserID указан ПОСЛЕ параметра ReportDate. Тогда UserID доступных значений не будут заполнены до тех пор, пока пользователь не выберет ReportDate. Затем эта процедура будет выполняться с использованием выбранного пользователем файла ReportDate.

Также я думаю, что вам нужно удалить параметр @User_ID из ваших параметров DataSet (последний скриншот). Ваша хранимая процедура имеет только один параметр ReportRunDate. Параметры в вашем наборе данных SSRS должны соответствовать параметрам в хранимой процедуре, которую вы вызываете.

Относительно этой ошибки:

Сервер не предоставил осмысленного ответа; это может быть вызвано несоответствием контракта, преждевременным завершением сеанса или внутренней ошибкой сервера.

Похоже, ваш код хранимой процедуры был изменен после того, как вы в последний раз редактировали отчет SSRS, и теперь он не возвращает ожидаемые значения. Используйте трассировку профилировщика, чтобы увидеть, какая команда отправляется из SSRS на ваш SQL Server, затем выполните эту команду в SSMS и посмотрите на результаты. Какие имена столбцов возвращаются? Это одинаковые имена на вкладке Fields вашего набора данных? Это те же имена, что и ваши Value Field и Label Field в параметре отчета?

person Tab Alleman    schedule 08.07.2016
comment
Я пробовал это, но это не сработало. В раскрывающемся списке UserID показаны некоторые числа, которых не существует (например, значение -1 или несколько нулей) в фактическом наборе результатов из SP. Кроме того, выбор параметра «Доступные значения» для получения идентификаторов пользователей занимает много времени для загрузки в раскрывающийся список SSRS. - person Julaayi; 08.07.2016
comment
Хорошо, я добавил скриншоты в свой ОП. - person Julaayi; 08.07.2016
comment
Я добавил еще один скриншот в конце ОП. Вы говорите об этом? - person Julaayi; 08.07.2016
comment
Когда я запускаю хранимую процедуру в SSMS, я получаю весь набор результатов (то, что нужно). Когда я пытаюсь отфильтровать это в отчете на основе идентификатора, это не работает. При обновлении я получаю сообщение об ошибке Выражение, используемое для параметра @User_ID в наборе данных @User_ID, относится к полю. Поля нельзя использовать в выражении параметра запроса. - person Julaayi; 08.07.2016
comment
Какое выражение вы используете для значения параметра @User_ID? На вашем скриншоте видно только <<Expr>> - person Tab Alleman; 11.07.2016
comment
Значение, которое я имею в выражении, равно Fields!User_ID.Value - person Julaayi; 11.07.2016
comment
Я сейчас в замешательстве. В вашем первом абзаце говорится об использовании параметра User_ID. И ваш второй абзац говорит удалить параметр User_ID. Если я удалю параметр User_ID, как дать клиенту возможность выбрать User_ID по своему выбору? - person Julaayi; 11.07.2016
comment
Хорошо, в первом абзаце я говорю о параметре User_ID отчета. Во втором абзаце я говорю о параметре User_ID набора данных. Это должен быть набор данных, который используется для заполнения параметра User_ID вашего отчета. Этот набор данных не должен иметь сам по себе параметр User_ID. - person Tab Alleman; 11.07.2016
comment
Ну, это то, что я пытался в самом начале. Я получаю сообщение об ошибке Сервер не предоставил осмысленного ответа; это может быть вызвано несоответствием контракта, преждевременным завершением сеанса или внутренней ошибкой сервера. при этом. - person Julaayi; 11.07.2016
comment
Давайте продолжим обсуждение в чате. - person Julaayi; 11.07.2016

arrow_upward
0
arrow_downward

В итоге я использовал отдельный SQL с советами многих экспертов, чтобы получить User_ID. Я добавил еще один параметр в хранимую процедуру и передал второе значение параметра из автономного SQL.

CREATE PROCEDURE usp_UserInformation (@ReportRunDate DATE = NULL, @User_ID VARCHAR(10)) 
AS
WITH CTE AS (
SELECT -- some fields
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
WHERE RegisteredDate = DATEADD(MONTH, DATEDIFF(MONTH, -1, @ReportRunDate) - 2, -1)
AND UserID = @User_ID
--I'll output the result set after applying all the logic like

SELECT UserID, UserName, DOJ
FROM CTE

И мой автономный SQL выглядит примерно так:

SELECT DISTINCT User_ID
FROM MyTable
ORDER BY User_ID
person Julaayi    schedule 12.07.2016