Главная » Microsoft SQL Server, Базы данных » Работа с данными

0

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

Работа с SQL в конструкторе отчетов

Панель инструментов конструктора отчетов содержит кнопку, позволяющую переключаться между стандартным (Generic Query Designer) и расширенным (Advanced Query Designer) конструкторами запросов. Стандартный конструктор состоит из двух панелей, SQL и Results, в то время как расширенный добавляет к ним еще две: Giagram и Grid. На рис. 46.5 показано окно расширенного конструктора запросов, в котором отображается запрос Products, созданный ранее в этой главе. Обратите внимание на то, что окно Datasets, находящееся в левой части, содержит набор данных Products, в том числе поля, возвращаемые запросом SQL.

Pwc. 46.5. Расширенный конструктор запросов упрощает процесс написания инструкций SQL

Для улучшения использования SQL Server и минимизации обслуживания отчета создайте хранимые процедуры и вызывайте их в отчете. Например, набор данных Products может возвращать хранимая процедура uspProducts. Вместо написания инструкции SQL непосредственно в отчете можно установить тип команды в text и вызвать хранимую процедуру следующим образом: ехес uspProducts.

Хранимую процедуру можно вызвать также с помощью установки типа команды в storedProcedure и ввода имени этой процедуры в строке запроса. В данном случае, если хранимая процедура имеет параметры, следует использовать вкладку Parameters страницы Dataset Properties.

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

Использование параметров запроса для отбора и фильтрации данных

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

ЕХЕС uspProducts ©CategorylD, @SubcategoryID

Вложенные параметры отчета

В предлагаемом примере продемонстрировано, как использовать параметры в наборах данных SQL и как создавать параметры, зависимые от выбора других параметров. Например, выбор категории Bikes параметра Category наполняет параметр Sybcategory списком подкатегорий велосипедов.

Для создания этого вложенного параметра в созданный ранее отчет списка товаров мы добавим два дополнительных набора данных. Оба этих набора данных в качестве источника используют учебную базу данных AdventureWorks. Первый набор данных, CategoryList, возвращает список категорий, используя следующий запрос SQL:

SELECT ProductCategorylD, Name FROM Production.Productcategory ORDER BY Name

Второй набор данных, SubcategoryList, возвращает список подкатегорий. Он включает в себя параметр, позволяющий отобрать только подкатегории заданной категории. Добавление условия проверки на пустое значение через оператор OR позволяет этому запросу возвращать все подкатегории. Это значит, что остальные элементы отчета могут использовать этот же запрос для получения данных без необходимости указания в качестве параметра идентификатора категории.

SELECT ProductSubcategorylD, ProductCategorylD, Name FROM Production.ProductSubcategory

WHERE (ProductCategorylD = @ProductCategoryID) OR (@ProductCategoryID IS NULL)

ORDER BY Name

Обновите набор данных Product с помощью следующего запроса, содержащего новые параметры (в данном случае мы также используем дополнительное условие, позволяющее возвращать все товары, если параметры не указаны):

SELECT P.ProductID, P.[Name], Р.ProductNumber, P.MakeFlag,

P.Color, P. [Size] , P.ListPrice, P.StandardCost,

P.ProductSubcategorylD, SubCat.[Name] AS Subcategory,

SubCat.ProductCategorylD, Cat.[Name] AS Category FROM Production.Product P

INNER JOIN Production.ProductSubcategory SubCat

ON P.ProductSubcategorylD = SubCat.ProductSubcategorylD INNER JOIN Production.Productcategory Cat

ON SubCat.ProductCategorylD = Cat.ProductCategorylD WHERE (P.ProductSubcategorylD = @ProductSubcategoryID OR @ProductSubcategoryID IS NULL)

AND (SubCat.ProductCategorylD = (©ProductCategorylD OR @ProductCategoryID IS NULL)

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

Для конфигурирования параметров запроса выберите в меню пункт Reports Report Parameters. На рис. 46.6 показано диалоговое окно Report Parameters, в котором выделен параметр ProductSubcategorylD.

Рис. 46.6. Диалоговое окно Report Parameters позволяет осуществлять расширенное управление параметрами отчета

Для конфигурирования параметров отчета выполните следующие действия.

1.              Выделите параметр ProductCategorylD.

2.              Измените тип данных на Integer.

3.              Измените подпись элемента на что-нибудь более понятное для пользователя, например

Категория.

4.              Установите переключатель в разделе Available Values в положение From Query. Это позволит заполнить список доступными значениями из набора данных.

5.              Выберите в списке набор данных CategoryList.

6.              В раскрывающемся списке Value Field выберите элемент ProductCategorylD.

7.              В раскрывающемся списке Label Field выберите элемент Name.

8.              Аналогичным образом сконфигурируйте параметр ProductSubcategorylD, согласно значениям, показанным на рис. 46.6.

Воспользуйтесь предварительным просмотром отчета. Следует заметить, что параметр подкатегории отключен до тех пор, пока вы не выберете значение для параметра категории. После выбора категории в параметре подкатегории отображается список соответствующих подкатегорий. Щелкните на кнопке View Report, и в созданном отчете отобразятся только товары из выбранной подкатегории.

Многозначные параметры отчета

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

Изменение параметров в многозначные требует внести некоторые небольшие изменения в запросы для наборов данных SubcategoryList и Products. В частности, предложение WHERE теперь должно содержать оператор IN вместо EQUALS. К тому же мы должны устранить возможность возвращения всех строк, когда параметр не определен, так как в противном

случае это может привести к искажению синтаксиса всего запроса SQL во время выполнения. Однако следует заметить, что при этом функциональность не теряется, так как для многозначных параметров служба отчетности автоматически включает возможность выбора всех значений (Select All).

Измените запрос SQL для набора данных SubcategoryList следующим образом:

SELECT ProductSubcategorylD, ProductCategorylD, Name FROM Production.ProductSubcategory WHERE ProductCategorylD IN (@ProductCategoryID)

ORDER BY Name

Измените запрос и для набора данных Products:

SELECT P.ProductID, P.[Name], P.ProductNumber, P.MakeFlag,

P.Color, P. [Size] , P.ListPrice, P.StandardCost,

P.ProductSubcategorylD, SubCat.[Name] AS Subcategory,

SubCat.ProductCategorylD, Cat.[Name] AS Category FROM Production.Product P

INNER JOIN Production.ProductSubcategory SubCat

ON P.ProductSubcategorylD = SubCat.ProductSubcategorylD INNER JOIN Production.Productcategory Cat

ON SubCat.ProductCategorylD = Cat.ProductCategorylD WHERE P.ProductSubcategorylD IN (@ProductSubcategoryID)

AND SubCat.ProductCategorylD IN (@ProductCategoryID)

Для конфигурирования многозначного параметра выполните следующие действия.

1.              Выберите в меню пункт Reports Report Parameters.

2.              Выделите параметр ProductCategorylD и установите флажок Multi-value.

3.              Выделите параметр ProductSubcategorylD и установите флажок Multi-value.

4.              Щелкните на кнопке О К, чтобы сохранить изменения.

Запустите отчет и выберите категории Accessories и Clothing— в списке подкатегорий отобразятся элементы обеих выбранных категорий (рис. 46.7). Выделите несколько подкатегорий и запустите отчет — теперь он будет содержать все товары выбранных подкатегорий и не будет стеснен выбором только одного значения параметра.

Рис. 46.7. В этом примере продемонстрированы многозначные вложенные параметры

Добавление в набор данных вычисляемых полей

После определения набора данных в него можно добавить новые поля и определить для них соответствующие выражения. Например, в набор данных товаров можно добавить поле Margin и установить для него выражение List Price – StandardCost. Чтобы добавить это поле, выполните следующие действия.

1.              Откройте окно Datasets, выбрав в меню пункт View^ Datasets.

2.              Щелкните правой кнопкой мыши на наборе данных Products и выберите в контекстном меню пункт Add.

3.              Присвойте полю имя Margin.

4.              Установите переключатель в положение Calculated Field и введите следующее выражение: =Fields!ListPrice.Value – Fields!StandardCost.Value

5.              Щелкните на кнопке OK, и новое поле будет добавлено в набор данных.

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

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

Работа с источниками данных XML

Служба отчетности в SQL Server 2005 предоставляет возможность использования XML в качестве источника данных для отчетов. Например, вы можете получать такие данные от Web-служб и извлекать из фактов XML, после чего отображать их в отчете.

В следующем примере продемонстрировано создание отчета на основе данных из двух источников XML: файла XML и Web-службы. Этот отчет использует файл XML для заполнения параметра StockSymbol. Выбранный стандартный символ акции передается в качестве параметра Web-службе, которая загружает последнюю доступную информацию о выбранных акциях. Следующий файл XML содержит некоторые символы акций и названия ассоциированных с ними компаний:

<?xml version="l.0" encoding="utf-8" ?>

<StockSymbols>

<Symbol Value="AAPL" Name=MApple Computer, Inc. (AAPL)" />

<Symbol Value="MSFT" Name="Microsoft Corporation (MSFT)" />

<Symbol Value="ORCL" Name="Oracle Corporation (ORCL)" />

<Symbol Value="STK" Name="Storage Technology Corp. (STK)" />

<Symbol Value="SUNW" Name="Sun Microsystems, Inc (SUNW)" />

</StockSymbols>

Для создания нового набора данных, соответствующего файлу StockSymbols .xml, выполните следующие действия.

1.              Создайте файл StockSymbols .xml, содержащий приведенный выше код, и сохраните его в корневом каталоге своего Web-cepBepa.

2.              Создайте новый набор данных dsStockSymbols.

3.              Сконфигурируйте источник данных с типом XML и следующей строкой подключения: http://localhost/StockSymbols.xml

4.              В качестве запроса формирования набора данных используйте следующий код:

<Query>

<ElementPath IgnoreNamespaces="true">

StockSymbols/Symbol </ElementPath>

</Query>

1.              Выберите режим аутентификации Windows (этот режим выбран по умолчанию при создании нового источника данных).

2.              Выполните введенный запрос, чтобы проверить успешность подключения. Это также позволит обновить определение набора данных в отчете.

Теперь создайте параметр отчета Symbols и используйте набор данных dsStockSymbols для заполнения его значениями. Для этого выполните следующие действия.

1.              Откройте диалоговое окно параметров отчета, выбрав в меню пункт Reports Report Parameters.

2.              Щелкните на кнопке Add, чтобы добавить новый параметр.

3.              Присвойте параметру имя Symbols, установите тип данных в String и введите для него подпись типа “Символ акции”.

4.              В разделе Available Values установите переключатель в положение From Query.

5.              В раскрывающемся списке Dataset выберите набор данных dsStockSymbols.

6.              В раскрывающемся списке ValueField выберите Value.

7.              В списке Label Field выберите Name.

8.              Щелкните на кнопке ОК, чтобы сохранить новые параметры и закрыть диалоговое окно Report Parameters.

При выполнении отчета пользователю будет предложено выбрать символ акций для параметра, чтобы получить соответствующий курс. Теперь нам осталось только создать набор данных, извлекающий информацию из Web-службы. Корпорация CDYNE предлагает Web-службу для извлечения отсроченных курсов акций. Метод Get Quote этой Web-службы возвращает курс акций и требует предоставления двух параметров: StockSymbol (символ акции) и LicenseKey (ключ лицензии). Первый параметр можно ассоциировать с одноименным параметром отчета, а второму параметру в данном случае нужно назначить значение 0 — этот ключ предлагается компанией для тестовых задач. Для создания набора данных Stock Quote выполните следующие действия.

1.              Создайте новый набор данных dsStockQuote.

2.              Сконфигурируйте источник данных с типом XML и следующей строкой подключения: http://ws.cdyne.com/delayedstockquote/delayedstockquote.asmx

3.              В качестве запроса формирования набора данных используйте <Query>

<Method Namespace="http://ws.cdyne.com/"

Name=nGetQuote}" />

<SoapAction>http://ws.cdyne.com/GetQuote</SoapAction>

</Query>

4.              Во вкладке Parameters окна свойств набора данных (чтобы открыть это окно, щелкните на кнопке с многоточием рядом с раскрывающимся списком DataSet) добавьте параметр StockSymbol и выберите Symbols в качестве его значения. Добавьте второй параметр, LicenseKey, и установите для него значение 0 (не используйте при этом знак равенства). Проверьте правильность регистров символов в именах параметров.

5.              Выполните запрос, чтобы убедиться в работоспособности подключения и правильности параметров. При этом также будет обновлено определение набора данных в отчете.

Добавьте содержимое в тело отчета, чтобы отображать курсы акций. Воспользуйтесь предварительным просмотром, и вся мощь средств XML предстанет перед вашими глазами.

Работа с выражениями

Выражения являются мощным ресурсом для создателей отчетов. Например, с помощью выражений можно консолидировать данные, выполнять вычисления, форматировать текст и управлять композицией отчета. На рис. 46.8 показан редактор выражений, входящий в состав Visual Studio 2005.

Puc. 46.8. Редактор выражений теперь оснащен дополнительными средствами, такими как IntelliSense и список доступных функций

Функциональность редактора выражений была расширена с целью облегчения Новинка ^ работы при создании выражений. Теперь он содержит список доступных функ- 2005    ций, а также средства IntelliSense, обеспечивающие правильное моделирова

ние инструкций, проверку синтаксиса и отображение оперативной информации

о          параметрах.

Редактор выражений можно открыть с помощью выбора элемента <Expression. . . > в списке значений параметра, а также щелчком правой кнопкой мыши на объекте с последующим выбором в контекстном меню пункта Expression. Также можно щелкнуть на функциональной кнопке с надписью fx, расположенной рядом с параметром на странице свойств. Редактор выражений содержит окно текста выражения, дерево категорий, элементы категорий и панель описаний. Дважды щелкните на значении на панели категорий или описаний, и в окно текста выражения будет вставлен соответствующий код.

В табл. 46.2 перечислены категории, доступные в редакторе выражений.

Таблица 46.2. Категории, доступные в редакторе выражений

Категория

Описание

Constants

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


Категория

Описание

Globals

Содержит функции доступа к информации об отчете и его выполнении, такие как ExecutionTime (время выполнения), PageNumber (номер страницы), TotalPages (общее число страниц в отчете) и useriD (идентификатор пользователя)

Parameters

Содержит список параметров, определенных в отчете

Fields

Содержит список полей набора данных, находящихся в области определения выбранного элемента или параметра отчета. Например, при редактировании выражения для ячейки таблицы, связанной с набором данных Products, в этой категории будут перечислены все поля этого набора данных

Datasets

Содержит все наборы данных, определенные в отчете. При выборе набора данных отображается итоговая функция, определенная по умолчанию для каждого поля этого набора. По умолчанию итоговая функция зависит от типа данных поля. Например, в наборе данных Products итоговой функцией по умолчанию для поля ListPrice (цена по прайс-листу) является sum (ListPrice). После двойного щелчка на этом поле в окно текста выражения будет добавлен следующий код:

Sum(Fields!ListPrice.Value,"Products")

Operators

Содержит следующие операторы: арифметические, сравнения, конкатенации, логические/побитовые и смещения битов, которые можно использовать при создании выражений

Common

Содержит функции для работы с текстом, датами и временем, математические, управ

Functions

ления потоком команд, итоговые, финансовые и преобразования данных

Область определения выражения

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

В качестве примера рассмотрим отчет о продажах в разрезе товаров. В нем содержится таблица с группой, имеющей имя grpProduct. Чтобы добавить в этот отчет столбец Running Total, который инициализирует накопительный итог для каждой группы товаров, можно использовать следующее выражение:

=RunningValue(FieldsILineTotal.Value, Sum, "grpProduct")

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

=RunningValue(Fields!LineTotal.Value, Sum)

Область определения выражения может также быть важной при добавлении выражения в текстовое поле. Так как элемент текстового поля в отчете не может быть непосредственно связан с набором данных, выражение должно включать область определения набора данных. Следующее выражение вычисляет сумму значений поля LineTotal (итог по строке) в наборе данных Sales (продажи):

=Sum(FieldsILineTotal.Value, "Sales")

Оформление отчета с помощью выражений

В приведенных ниже примерах продемонстрированы некоторые наиболее распространенные выражения, используемые в отчетах. Используя следующее выражение в качест

ве свойства строки деталей элемента таблицы, можно выделить цветом фона AliceBlue все нечетные строки:

=1If(RowNumber(nothing) mod 2=1, "AliceBlue", "White")

Целесообразно добавлять в отчет дату и время его выполнения. Следующее выражение позволит включить в отчет строку типа “Отчет выполнен: Monday, August 15, 2005 в 2:24:33 pm”:

="Отчет выполнен: " & GlobalsIExecutionTime.ToLongDateString & " в " & Globals!ExecutionTime.ToLongTimeString

Выражения можно использовать для форматирования текста. Следующее выражение вычисляет сумму по полю LineTotal набора данных Sales и форматирует результат как строку денежного типа, такую как 4 231 205,23р.:

=FormatCurrency(Sum(Fields!LineTotal.Value, "Sales"), 2, true, true, true)

Иногда не имеет смысла отображать некоторые элементы отчета; при этом решение принимается на основе выбранных параметров. Для переключения свойства видимости некоторого элемента отчета или даже ячейки таблицы для параметра Visibility .Hidden используют выражение, подобное следующему:

=IIf(Parameters!CategorylD.Value = 10, true, false)

С помощью выражений можно даже спроектировать текст команды, формирующей набор данных. Выражение, приведенное в следующем примере, позволяет включить в инструкцию SELECT предложение WHERE только в том случае, когда для параметра идентификатора подкатегории (SubcategorylD) указано значение:

="Select * From Products " &

Ilf(Parameters!SubcategorylD.Value = -1, "",

WHERE ProductSubcategorylD = " & Parameters!SubcategorylD.Value) &

" Order By ProductName"

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии