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

Определение различных наборов требований

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

• Набор требований 1:
  • Группировка по: Категория, Подкатегория
  • Агрегация: Сумма продаж, Сумма налога, Сумма доставки
• Набор требований 2:
  • Группировка по: Категория, Подкатегория
  • Агрегация: Сумма продаж, Сумма доставки
• Набор требований 3:
  • Группировка по: Категория, Продукт
  • Агрегация: Сумма продаж, Сумма налога

Проектирование отчета

Теперь, когда мы определили различные наборы требований, давайте начнем проектировать наш отчет. Мы начнем с запроса, который возвращает все необходимые поля для всех наборов требований. Вот пример запроса:

SELECT c.EnglishProductCategoryName AS Category,
       s.EnglishProductSubcategoryName AS Subcategory,
       p.EnglishProductName AS Product,
       SUM(f.SalesAmount) AS SalesAmt,
       SUM(f.TaxAmt) AS TaxAmt,
       SUM(f.Freight) AS FreightAmt
FROM DimProductCategory c
INNER JOIN DimProductSubcategory s ON c.ProductCategoryKey = s.ProductCategoryKey
INNER JOIN DimProduct p ON s.ProductSubcategoryKey = p.ProductSubcategoryKey
INNER JOIN FactInternetSales f ON p.ProductKey = f.ProductKey
GROUP BY c.EnglishProductCategoryName, s.EnglishProductSubcategoryName, p.EnglishProductName

Затем мы добавляем таблицу в тело отчета и перетаскиваем необходимые поля в панель группировки строк. Мы также добавляем поля измерений (SalesAmt, TaxAmt и FreightAmt) в ячейки данных и форматируем их как валюту.

Теперь здесь проявляется гибкость. Мы не можем скрыть заголовки столбцов группы, но мы можем обойти это ограничение. Перетаскивая поля группы в новые ячейки справа от таблицы и удаляя исходные заголовки столбцов группы, мы можем достичь желаемого результата. Мы также устанавливаем текстовые поля в этих ячейках, чтобы скрывать повторяющиеся значения внутри соответствующих групп.

Добавление параметров для гибкости

Чтобы сделать наш отчет еще более гибким, мы можем добавить параметр, который позволяет пользователям выбирать требуемый набор требований. Мы создаем параметр с именем “pQuery” с тремя доступными значениями, каждое из которых представляет различную комбинацию полей. Вот доступные значения:

• Доступное значение 1 (Метка: Продажи, налог и доставка по подкатегории):

  SELECT c.EnglishProductCategoryName AS Category,
         s.EnglishProductSubcategoryName AS Subcategory,
         SUM(f.SalesAmount) AS SalesAmt,
         SUM(f.TaxAmt) AS TaxAmt,
         SUM(f.Freight) AS FreightAmt
  FROM DimProductCategory c
  INNER JOIN DimProductSubcategory s ON c.ProductCategoryKey = s.ProductCategoryKey
  INNER JOIN DimProduct p ON s.ProductSubcategoryKey = p.ProductSubcategoryKey
  INNER JOIN FactInternetSales f ON p.ProductKey = f.ProductKey
  GROUP BY c.EnglishProductCategoryName, s.EnglishProductSubcategoryName
  

• Доступное значение 2 (Метка: Продажи и доставка по подкатегории):

  SELECT c.EnglishProductCategoryName AS Category,
         s.EnglishProductSubcategoryName AS Subcategory,
         SUM(f.SalesAmount) AS SalesAmt,
         SUM(f.Freight) AS FreightAmt
  FROM DimProductCategory c
  INNER JOIN DimProductSubcategory s ON c.ProductCategoryKey = s.ProductCategoryKey
  INNER JOIN DimProduct p ON s.ProductSubcategoryKey = p.ProductSubcategoryKey
  INNER JOIN FactInternetSales f ON p.ProductKey = f.ProductKey
  GROUP BY c.EnglishProductCategoryName, s.EnglishProductSubcategoryName
  

• Доступное значение 3 (Метка: Продажи, налог и доставка по продукту):

  SELECT c.EnglishProductCategoryName AS Category,
         p.EnglishProductName AS Product,
         SUM(f.SalesAmount) AS SalesAmt,
         SUM(f.TaxAmt) AS TaxAmt
  FROM DimProductCategory c
  INNER JOIN DimProductSubcategory s ON c.ProductCategoryKey = s.ProductCategoryKey
  INNER JOIN DimProduct p ON s.ProductSubcategoryKey = p.ProductSubcategoryKey
  INNER JOIN FactInternetSales f ON p.ProductKey = f.ProductKey
  GROUP BY c.EnglishProductCategoryName, p.EnglishProductName
  

Наконец, мы можем использовать выражение, чтобы передать выбранную строку запроса в набор данных. Открыв свойства набора данных и используя конструктор выражений, мы можем заменить весь текст запроса следующим выражением:

=Parameters!pQuery.Value

Теперь, когда мы переключаемся в режим предварительного просмотра, мы можем выбрать любой из трех параметров, и отчет будет динамически отображать только столбцы, возвращаемые соответствующим запросом. Это дает нам полностью гибкий отчет, который может удовлетворять различным требованиям без необходимости в нескольких отчетах.

Следуя этим шагам, вы можете создавать гибкие отчеты в SQL Server, которые могут удовлетворять различным запросам и наборам данных. Этот подход не только экономит время разработки, но и уменьшает затраты на обслуживание. Попробуйте и посмотрите, как он может быть полезен для ваших потребностей в отчетности!

Filed under: SQL Syndication, SSRS Design

Tagged: Dynamic column visibility