Создание динамических отчетов с параметрами
Динамические отчеты — это отчеты, в которых разработчик может изменять данные в соответствии со спецификациями пользователей. Их ценность в том, что один отчет может использоваться в нескольких целях. При использовании динамических отчетов создается меньше отдельных отчетов, что позволяет экономить время и ресурсы организации.
С помощью параметров можно определить значения данных, которые должны приводиться в отчете, после чего данные в отчете фильтруются соответствующим образом.
Благодаря динамическим отчетам пользователи получают больший контроль над данными, отображаемыми в отчетах. Они могут изменять источник данных и самостоятельно фильтровать данные.
В следующем примере вы создали отчет для отдела продаж компании Tailwind Traders, где приводятся данные по продажам из базы данных SQL Server. Он дает целостное представление об эффективности работы отдела продаж. Хотя этот отчет полезен, но сотрудники отдела продаж хотят иметь возможность фильтровать его, чтобы просматривать только свои данные и сопоставлять свои успехи с целевыми показателями.
Создание динамических отчетов для отдельных значений
Чтобы создать динамический отчет, сначала нужно написать SQL-запрос. Затем используйте функцию Получения данных в Power BI Desktop для подключения к базе данных.
В этом примере вы подключаетесь к базе данных на SQL Server, выполнив следующие действия.
Введя сведения о сервере, в окне База данных SQL Server выберите Дополнительные параметры.
Вставьте SQL-запрос в поле Инструкция SQL, а затем нажмите кнопку ОК.
После подключения данные отображаются в окне предварительного просмотра.
Нажмите Изменить, чтобы открыть данные в редакторе Power Query.
Затем создайте параметр, выполнив следующие действия.
На вкладке Главная выберите Управление параметрами > Создать параметр.
В окне Параметры измените имя параметра по умолчанию на более понятное в соответствии с его назначением. В этом случае мы изменим имя на SalesPerson (Продавец).
В списке Тип выберите пункт Текст, а в списке Предлагаемые значения — пункт Любое значение.
Щелкните ОК.
Для созданного параметра отобразится новый запрос.
Теперь нужно изменить код SQL-запроса с учетом нового параметра:
Щелкните правой кнопкой мыши Запрос1 и выберите пункт Расширенный редактор.
Замените существующее значение в инструкции execute на амперсанд (&), за которым следует имя параметра (SalesPerson), как показано на следующем рисунке.
Убедитесь в том, что в нижней части окна нет ошибок, и нажмите кнопку Готово.
Хотя вы не видите разницы на экране, Power BI выполнил запрос.
Чтобы убедиться, что запрос был выполнен, можно запустить тест, выбрав запрос с параметрами и введя новое значение в поле Текущее значение.
Рядом с запросом может появиться значок предупреждения. В этом случае выберите запрос, чтобы просмотреть сообщение. В нем говорится о том, что для выполнения этого машинного запроса к базе данных требуется разрешение. Выберите Изменить разрешение, а затем нажмите Выполнить.
При успешном выполнении запроса параметр отображает новое значение.
Выберите Закрыть и применить, чтобы вернуться в редактор отчетов.
Теперь можно применить параметр к отчету:
Выберите Изменить запросы > Изменить параметры.
В окне Изменение параметров введите новое значение и нажмите кнопку ОК.
Выберите Применить изменения и снова выполните машинный запрос.
Теперь при просмотре данных отображаются данные для нового значения, переданного через параметр .
Теперь можно создать отчет, в котором за раз отображаются данные для одного значения. Для одновременного отображения данных для нескольких значений необходимо выполнить дополнительные действия.
Создание динамических отчетов для нескольких значений
Чтобы одновременно предоставлять несколько значений, необходимо сначала создать лист Microsoft Excel с таблицей, состоящей из одного столбца со списком значений.
После этого воспользуйтесь функцией Получить данные в Power BI Desktop для подключения к данных в этом листе Excel, а затем сделайте следующее:
В окне Навигатор выберите Изменить, чтобы открыть данные в Редактор Power Query, где отобразится новый запрос к таблице данных.
Измените имя столбца таблицы на более понятное.
Измените тип данных столбца на Текст, чтобы он соответствовал типу параметра и не возникало проблем с преобразованием данных.
В разделе Свойства запроса также измените имя источника данных на более понятное. В рамках этого примера укажите SalesPersonID (ИД_продавца).
Затем необходимо создать функцию, которая передает новый запрос SalesPersonID в Query1:
Щелкните правой кнопкой мыши Запрос1 и выберите пункт Создать функцию.
Введите имя функции и нажмите кнопку ОК.
Новая функция появится в области Запросы .
Чтобы Запрос1 не отображался в списке полей для отчета, создавая путаницу для пользователей, можно отключить его загрузку в отчете. Для этого еще раз щелкните правой кнопкой мыши Запрос1 и выберите пункт Включить загрузку (выбран по умолчанию), чтобы отключить эту функцию.
Выберите запрос SalesPersonID, загруженный из листа Excel, а затем на вкладке Добавление столбца выберите Вызвать настраиваемую функцию, чтобы выполнить функцию, которую вы создали.
В окне Вызвать настраиваемую функцию выберите функцию в списке Запрос функции.
Имя нового столбца обновляется автоматически, и по умолчанию выбирается таблица, содержащая значения, которые вы собираетесь передать через параметр.
Нажмите кнопку ОК и при необходимости выполните машинный запрос.
Рядом со столбцом SalesPerson появится новый столбец для функции GetSalesFromSalesPerson.
Щелкните значок с двумя стрелками в заголовке этого нового столбца, а затем установите флажки для столбцов, которые требуется загрузить. В этом разделе определяются сведения, доступные в отчете для каждого значения (идентификатор торгового лица).
Снимите флажок Использовать исходное имя столбца как префикс в нижней части экрана, так как имена столбцов в отчете не должны содержать префикс.
Щелкните ОК.
Вы сможете просматривать данные по выбранным столбцам для каждого значения (идентификатор продавца).
При необходимости можно добавить дополнительные значения (идентификаторы продавцов) в столбец SalesPersonID на листе Excel или изменить существующие значения.
Сохраните изменения и вернитесь в Редактор Power Query.
На вкладке Главная выберите Обновить просмотр, а затем снова выполните машинный запрос (при необходимости). Вы должны увидеть данные по продажам новых продавцов, идентификаторы которых были добавлены на лист.
Нажмите кнопку Закрыть и Применить , чтобы вернуться в редактор отчетов, где вы увидите новые имена столбцов на панели Поля.
Теперь можно приступить к созданию отчета.