Робота з даними

в службі звітності SQL Server 2005 озброїла творців звітів можливістю використання безлічі джерел даних різних типів Використовуючи параметри і вирази, можна постачити користувачів звітів елементами керування вмістом звіту, а також іншими засобами управління звітом У цьому розділі буде продемонстровано, як створити набори даних за допомогою джерел SQL і XML, як створити і використовувати параметри для відбору та фільтрації даних, а також як використовувати вирази

Робота з SQL в конструкторі звітів

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

Pwc 465 Розширений конструктор запитів спрощує процес написання інструкцій 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 ProductionProductcategory ORDER BY Name

Другий набір даних, SubcategoryList, повертає список підкатегорій Він включає в себе параметр, що дозволяє відібрати тільки підкатегорії заданої категорії Додавання умови перевірки на порожнє значення через оператор OR дозволяє цим запитом повертати все підкатегорії Це означає, що інші елементи звіту можуть використовувати цей же запит для отримання даних без необхідності вказівки як параметра ідентифікатора категорії

SELECT ProductSubcategorylD, ProductCategorylD, Name FROM ProductionProductSubcategory

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

ORDER BY Name

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

SELECT PProductID, P [Name], РProductNumber, PMakeFlag,

PColor, P [Size] , PListPrice, PStandardCost,

PProductSubcategorylD, SubCat[Name] AS Subcategory,

SubCatProductCategorylD, Cat[Name] AS Category FROM ProductionProduct P

INNER JOIN ProductionProductSubcategory SubCat

ON PProductSubcategorylD = SubCatProductSubcategorylD INNER JOIN ProductionProductcategory Cat

ON SubCatProductCategorylD = CatProductCategorylD WHERE (PProductSubcategorylD = @ProductSubcategoryID OR @ProductSubcategoryID IS NULL)

AND (SubCatProductCategorylD = (©ProductCategorylD OR @ProductCategoryID IS NULL)

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

Для конфігурації параметрів запиту виберіть у меню пункт Reports Report Parameters На рис 466 показано діалогове вікно Report Parameters, в якому виділений параметр ProductSubcategorylD

Рис 466 Діалогове вікно Report Parameters дозволяє здійснювати розширене управління параметрами звіту

Для конфігурації параметрів звіту виконайте наступні дії

1 Виділіть параметр ProductCategorylD

2 Змініть тип даних на Integer

3 Змініть підпис елемента на щось більш зрозуміле для користувача, наприклад

Категорія

4 Встановіть перемикач в розділі Available Values ​​в положення From Query Це дозволить заповнити список доступними значеннями з набору даних

5 Виберіть у списку набір даних CategoryList

6 У списку Value Field виберіть елемент ProductCategorylD

7 У списку Label Field виберіть елемент Name

8 Аналогічним чином настройте параметр ProductSubcategorylD, згідно значень, показаним на рис 466

Скористайтеся попереднім переглядом звіту Слід зауважити, що параметр підкатегорії відключений до тих пір, поки ви не виберете значення для параметра категорії Після вибору категорії в параметрі підкатегорії відображається список відповідних підкатегорій Клацніть на кнопці View Report, і в створеному звіті відобразяться тільки товари з обраної підкатегорії

Багатозначні параметри звіту

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

Зміна параметрів у багатозначні вимагає внести деякі невеликі зміни в запити для наборів даних SubcategoryList і Products Зокрема, пропозиція WHERE тепер повинно містити оператор IN замість EQUALS До того ж ми повинні усунути можливість повернення всіх рядків, коли параметр не визначений, тому що в противному

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

Змініть запит SQL для набору даних SubcategoryList наступним чином:

SELECT ProductSubcategorylD, ProductCategorylD, Name FROM ProductionProductSubcategory WHERE ProductCategorylD IN (@ProductCategoryID)

ORDER BY Name

Змініть запит і для набору даних Products:

SELECT PProductID, P[Name], PProductNumber, PMakeFlag,

PColor, P [Size] , PListPrice, PStandardCost,

PProductSubcategorylD, SubCat[Name] AS Subcategory,

SubCatProductCategorylD, Cat[Name] AS Category FROM ProductionProduct P

INNER JOIN ProductionProductSubcategory SubCat

ON PProductSubcategorylD = SubCatProductSubcategorylD INNER JOIN ProductionProductcategory Cat

ON SubCatProductCategorylD = CatProductCategorylD WHERE PProductSubcategorylD IN (@ProductSubcategoryID)

AND SubCatProductCategorylD IN (@ProductCategoryID)

Для конфігурації багатозначного параметра виконайте наступні дії

1 Виберіть у меню пункт Reports Report Parameters

2&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Виділіть параметр ProductCategorylD і встановіть прапорець Multi-value

3&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Виділіть параметр ProductSubcategorylD і встановіть прапорець Multi-value

4&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Клацніть на кнопці Про До, щоб зберегти зміни

Запустіть звіт і виберіть категорії Accessories і Clothing-в списку підкатегорій відобразяться елементи обох обраних категорій (рис 467) Виділіть декілька підкатегорій і запустіть звіт – тепер він міститиме всі товари обраних підкатегорій і не буде тісний вибором лише одного значення параметра

Рис 467 У цьому прикладі продемонстровані багатозначні вкладені параметри

Додавання в набір даних обчислюваних полів

Після визначення набору даних в нього можна додати нові поля і визначити для них відповідні вирази Наприклад, в набір даних товарів можна додати поле Margin і встановити для нього вираз List Price – StandardCost Щоб додати це поле, виконайте такі дії

1 Відкрийте вікно Datasets, вибравши в меню пункт View ^ Datasets

2 Клацніть правою кнопкою миші на наборі даних Products і виберіть у контекстному меню пункт Add

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

4 Встановіть перемикач в положення Calculated Field і введіть такий вираз: = Fields ListPriceValue – Fields StandardCostValue

5 Клацніть на кнопці OK, і нове поле буде додано в набір даних

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

Також можна включати вирази і в розкладку звіту проте це вимагає багаторазового написання виразів в тілі звіту Додавання обчислюваних полів в набір даних дозволяє централізувати визначення виразів і спростити створення і супровід звіту

Робота з джерелами даних XML

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

У наступному прикладі продемонстровано створення звіту на основі даних з двох джерел XML: файлу XML і Web-служби Цей звіт використовує файл XML для заповнення параметра StockSymbol Обраний стандартний символ акції передається як параметр Web-службі, яка завантажує останню доступну інформацію про обраних акціях Наступний файл XML містить деякі символи акцій і назви асоційованих з ними компаній:

&lt?xml version=&quotl0&quot encoding=&quotutf-8&quot &gt

&ltStockSymbols&gt

&ltSymbol Value=&quotAAPL&quot Name=MApple Computer, Inc (AAPL)&quot /&gt

&ltSymbol Value=&quotMSFT&quot Name=&quotMicrosoft Corporation (MSFT)&quot /&gt

&ltSymbol Value=&quotORCL&quot Name=&quotOracle Corporation (ORCL)&quot /&gt

&ltSymbol Value=&quotSTK&quot Name=&quotStorage Technology Corp (STK)&quot /&gt

&ltSymbol Value=&quotSUNW&quot Name=&quotSun Microsystems, Inc (SUNW)&quot /&gt

&lt/StockSymbols&gt

Для створення нового набору даних, відповідного файлу StockSymbols Xml, виконайте такі дії

1&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Створіть файл StockSymbols Xml, який містить наведений вище код, і збережіть його в кореневому каталозі свого Web-cepBepa

2&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Створіть новий набір даних dsStockSymbols

3&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Конфігуруйте джерело даних з типом XML і наступним рядком підключення: http://localhost/StockSymbolsxml

4&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp В якості запиту формування набору даних використовуйте наступний код:

&ltQuery&gt

&ltElementPath IgnoreNamespaces=&quottrue&quot&gt

StockSymbols/Symbol &lt/ElementPath&gt

&lt/Query&gt

1 Виберіть режим аутентифікації Windows (цей режим обраний за замовчуванням при створенні нового джерела даних)

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

Тепер створіть параметр звіту Symbols і використовуйте набір даних dsStockSymbols для заповнення його значеннями Для цього виконайте такі дії

1&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Відкрийте діалогове вікно параметрів звіту, вибравши в меню пункт Reports Report Parameters

2&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Клацніть на кнопці Add, щоб додати новий параметр

3&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Надайте параметру імя Symbols, встановіть тип даних в String і введіть для нього підпис типу Символ акції.

4&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp У розділі Available Values ​​встановіть перемикач в положення From Query

5&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp У списку Dataset виберіть набір даних dsStockSymbols

6&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp У списку ValueField виберіть Value

7&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp У списку Label Field виберіть Name

8&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Клацніть на кнопці ОК, щоб зберегти нові параметри і закрити діалогове вікно Report Parameters

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

1 Створіть новий набір даних dsStockQuote

2 Конфігуруйте джерело даних з типом XML і наступним рядком підключення: http://wscdynecom/delayedstockquote/delayedstockquoteasmx

3 В якості запиту формування набору даних використовуйте

&ltMethod Namespace=&quothttp://wscdynecom/&quot

Name=nGetQuote}&quot /&gt

&ltSoapAction&gthttp://wscdynecom/GetQuote&lt/SoapAction&gt

&lt/Query&gt

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

5 Виконайте запит, щоб переконатися в працездатності підключення та правильності параметрів При цьому також буде оновлено визначення набору даних у звіті

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

Робота з виразами

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

Puc 468 Редактор виразів тепер оснащений додатковими засобами, такими як IntelliSense і список доступних функцій

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

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

про параметри

Редактор виразів можна відкрити за допомогою вибору елемента в списку значень параметра, а також клацанням правою кнопкою миші на обєкті з подальшим вибором в контекстному меню пункту Expression Також можна клацнути на функціональній кнопці з написом fx, розташованої поруч з параметром на сторінці властивостей Редактор виразів містить вікно тексту вираження, дерево категорій, елементи категорій і панель описів Двічі клацніть на значенні на панелі категорій або описів, і у вікно тексту виразу буде вставлено відповідний код

У табл 462 перераховані категорії, доступні в редакторі виразів

Таблиця 462 Категорії, доступні в редакторі виразів

Категорія

Опис

Constants

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

Категорія

Опис

Globals

Містить функції доступу до інформації про звіт і його виконанні, такі як ExecutionTime (час виконання), PageNumber (номер сторінки), TotalPages (загальне число сторінок у звіті) і useriD (ідентифікатор користувача)

Parameters

Містить список параметрів, визначених у звіті

Fields

Містить список полів набору даних, що знаходяться в області визначення обраного елемента або параметра звіту Наприклад, при редагуванні вирази для комірки таблиці, повязаної з набором даних Products, в цій категорії будуть перераховані всі поля цього набору даних

Datasets

Містить всі набори даних, визначені в звіті При виборі набору даних відображається підсумкова функція, визначена за замовчуванням для кожного поля цього набору За замовчуванням підсумкова функція залежить від типу даних поля Наприклад, в наборі даних Products підсумкової функцією за замовчуванням для поля ListPrice (ціна за прайс-листом) є sum (ListPrice) Після подвійного клацання на цьому полі у вікно тексту вираження буде додано наступний код:

Sum(FieldsListPriceValue,&quotProducts&quot)

Operators

Містить наступні оператори: арифметичні, порівняння, конкатенації, логічні / побітові і зміщення бітів, які можна використовувати при створенні виразів

Common

Містить функції для роботи з текстом, датами і часом, математичні, управ

Functions

лення потоком команд, підсумкові, фінансові та перетворення даних

Область визначення вираження

Підсумкові функції дозволяють задавати область, яку охоплюють обчислення Під областю визначення розуміється або імя набору даних, або імя групи або діапазону даних, що містять елемент звіту, в якому використовується підсумкова функція

Як приклад розглянемо звіт про продажі в розрізі товарів У ньому міститься таблиця з групою, що має імя grpProduct Щоб додати до цього звіту стовпець Running Total, який ініціалізує накопичувальний підсумок для кожної групи товарів, можна використовувати такий вираз:

=RunningValue(FieldsILineTotalValue, Sum, &quotgrpProduct&quot)

Для створення аналогічного стовпця накопичувального результаті, не ініціалізіруемих в кожній групі товарів, використовується наступний вираз:

=RunningValue(FieldsLineTotalValue, Sum)

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

=Sum(FieldsILineTotalValue, &quotSales&quot)

Оформлення звіту за допомогою виразів

У наведених нижче прикладах продемонстровані деякі найбільш поширені вирази, які використовуються у звітах Використовуючи такий вираз в качест

ве властивості рядка деталей елемента таблиці, можна виділити кольором фону AliceBlue всі непарні рядки:

=1If(RowNumber(nothing) mod 2=1, &quotAliceBlue&quot, &quotWhite&quot)

Доцільно додавати до звіту дату і час його виконання Наступне вираз дозволить включити в звіт рядок типу Звіт виконаний: Monday, August 15, 2005 в 2:24:33 pm:

= Звіт виконаний: & GlobalsIExecutionTimeToLongDateString & в & Globals ExecutionTimeToLongTimeString

Вирази можна використовувати для форматування тексту Наступне вираз обчислює суму по полю LineTotal набору даних Sales і форматує результат як рядок грошового типу, таку як 4231 205,23 р:

=FormatCurrency(Sum(FieldsLineTotalValue, &quotSales&quot), 2, true, true, true)

Іноді не має сенсу відображати деякі елементи звіту при цьому рішення приймається на основі вибраних параметрів Для перемикання властивості видимості деякого елемента звіту або навіть осередки таблиці для параметра Visibility Hidden використовують вислів, подібне наступному:

=IIf(ParametersCategorylDValue = 10, true, false)

За допомогою виразів можна навіть спроектувати текст команди, формує набір даних Вираз, наведене в наступному прикладі, дозволяє включити в інструкцію SELECT пропозицію WHERE тільки в тому випадку, коли для параметра ідентифікатора підкатегорії (SubcategorylD) зазначено значення:

=&quotSelect * From Products &quot &amp

Ilf(ParametersSubcategorylDValue = -1, &quot&quot,

WHERE ProductSubcategorylD = &quot &amp ParametersSubcategorylDValue) &amp

&quot Order By ProductName&quot

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*