Печать отчета

[Прокопьева]

Почтенные эксперты!
Подскажите плиз новичку такую вещь: возможно ли штатными средствами построить отчет по одной таблице так, чтобы значения одного столбца выводились в наименования столбцов отчета, значения одного столбца выводились в наименованиях строк отчета, а на их пересечениях выводилось кол-во по третьему столбцу.
В Аксессе такое представление называлось как перекрестный запрос и использовалась опция SQL - pivot.
Здесь такого не найду, может надо искать какие-то другие возможности, или это вообще не возможно воплотить?
За ранее благодарна.

[AndyD]

Сводные таблицы Excel?

[mazzy]

Цитата:

Сообщение от Прокопьева

Здесь такого не найду, может надо искать какие-то другие возможности, или это вообще не возможно воплотить?

Штатных средств для построения отчетов, растущих в ширину, в Аксапте нет.
Запрогаммировать можно.

Есть два способа:
1. вы заранее создаете некотрое максимальное число колонок, в коде скрываете ненужные.
2. вы из кода программно создаете колонки

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

Я предпочитаю первый способ, поскольку при большом числе колонок Аксапта начинает сильно маштабировать отчет. А управлять разбивкой на страницы вручную мне кажется нецелесообразной тратой времени и ресурсов.

[mazzy]

Цитата:

Сообщение от AndyD

Сводные таблицы Excel?

Максимум 256 колонок

[Gustav]

Цитата:

Сообщение от AndyD

Сводные таблицы Excel?

Поддерживаю. Хорошее решение при минимальных усилиях.

Цитата:

Сообщение от mazzy

Максимум 256 колонок

В Excel 2007 обещается 16 тыщ колонок и чуть больше мильона строк. Ждать осталось недолго.
Хотя на сводные таблицы существуют какие-то свои внутренние ограничения. По-моему, например, уникальных строк сводной таблицы не может быть больше 8 тысяч.

Цитата:

Сообщение от Прокопьева

В Аксессе такое представление называлось как перекрестный запрос и использовалась опция SQL - pivot.

Можно попробовать "дотянуть" данные до Аксеса и сделать там. Это не так сложно, как может показаться. Примерный набросок сценария можно посмотреть здесь: Альтернативные конструкторы отчетов в Axapta


P.S. Виноват! Вру! Не 65, а только 16 тысяч столбцов будет (исправил выше):
В России представили вторую бету Office 2007
(т.е. очевидно строк будет 65536*16 = 1 048 576, а колонок 256*64 = 16 384)

[mazzy]

Цитата:

Сообщение от Gustav

Можно попробовать "дотянуть" данные до Аксеса и сделать там.

Тогда уж лучше в Reporting Service.
http://axapta.mazzy.ru/lib/reportingservice/

Хотя, если подумать, то и так и иначе - изврат.

[Прокопьева]

Цитата:

Сообщение от AndyD

Сводные таблицы Excel?

Можно немного поподробнее, плиз, как воспользоваться возможностью построить сводную таблицу Excel по таблице Аксапты?

[Gustav]

Цитата:

Сообщение от Прокопьева

Можно немного поподробнее, плиз, как воспользоваться возможностью построить сводную таблицу Excel по таблице Аксапты?

Как по любой другой внешней для Excel таблице. Сначала настроить источник ODBC (если он еще не настроен). Далее по меню Excel: Данные / Сводная таблица / опция (вторая) "во внешнем источнике данных" - и далее мастер Вас поведет.

Этот вариант годится только на тот случай, когда Вас не интересуют вопросы ограничения доступа. Т.е. предполагается, что Вы или пользователь Вашей будущей сводной таблицы имеет доступ ко всем данным исходной таблицы Аксапты. Либо Вы настраиваете сводную таблицу не на table, а на view в СУБД и уже во view отфильтровываете все необходимые пользователю такого отчета записи.

Если вопросы ограничения доступа для Вас существенны (RLS, например), то тогда лучше построить запрос в Аксапте и вывести данные в Excel любым нравящимся способом на один лист, после чего на другом листе построить сводную табицу - для этого воспользоваться командой меню Excel: Данные / Сводная таблица / опция (первая) "в списке или базе данных Microsoft Excel" ...
(пункты меню называю по русской версии Excel 2000)

Конечно же, всё что Вы можете сделать руками через меню, Вы можете потом записать макрорекордером Excel и получившийся VBA-код переложить на X++.

[AxPr-r]

а использовать OLAP из аксапты, не пробовали?

[Gustav]

Цитата:

Сообщение от AxPr-r

а использовать OLAP из аксапты, не пробовали?

Из Аксапты - нет (не имею такой возможности). Извне - да. Хорошая вещь.
Если госпожа Прокопьева имеет возможность им воспользоваться, то всячески можно тоже рекомендовать как вариант. Возможно даже как наиболее предпочтительный, особенно для больших таблиц.

[Gustav]

2 Прокопьева: опять-таки на случай, когда Вас не интересуют вопросы ограничения доступа, можно-таки попробовать построить перекрестный запрос в любимом Access'е.

В качестве примера построим перекрестный запрос, отражающий за заданный период обороты по количеству номенклатуры (строки) в разрезе по складам (столбцы) - на основе таблиц InventTrans и InventDim.

Итак, по шагам:

1. Убедитесь, что у вас на компьютере уже создан источник ODBC, настроенный на базу данных Аксапты, и вам известны все его необходимые параметры (в т.ч. логин и пароль к БД).

2. Создайте новую базу данных Access (именно базу в формате mdb, а не проект в формате adp - в проекте вы, увы, не сможете создать перекрестный запрос).

3. В этом новом mdb-файле перейдите на закладку "Модули", создайте новый модуль и скопируйте в него следующую небольшую процедуру, создающую 3 запроса:

'Код - Access VBA
 
Sub TestPivotQuery()
 
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
 
    Set dbs = CurrentDb
 
 
    'Запрос 1 - получение исходных данных для построения перекрестного запроса
    Set qdf = dbs.CreateQueryDef("qry_1_GetSourceData")
    qdf.Connect = "ODBC;DSN=yourDSN;UID=yourUserID;PWD=yourPassword;SERVER=yourServer;"
 
    'ЗАПРОС К СЕРВЕРУ - на диалекте SQL используемой СУБД(!)
    '(ненужное закомментировать)
    '-----------------------------------------
    'для MS SQL Server
    'qdf.SQL = "SELECT t.ItemId, d.InventLocationId, t.TransType, t.Direction, t.StatusReceipt, t.StatusIssue, t.Qty" & _
              " FROM InventTrans t, InventDim d" & _
              " WHERE UPPER(t.InventDimId) = UPPER(d.InventDimId)" & _
              " AND t.DataAreaId = d.DataAreaId" & _
              " AND t.DataAreaId = 'yor'" & _
              " AND t.DatePhysical Between '2006-05-01' And '2006-05-31'"
    '-----------------
    'для Oracle
    qdf.SQL = "SELECT t.ItemId, d.InventLocationId, t.TransType, t.Direction, t.StatusReceipt, t.StatusIssue, t.Qty" & _
              " FROM InventTrans t, InventDim d" & _
              " WHERE UPPER(t.InventDimId) = UPPER(d.InventDimId)" & _
              " AND t.DataAreaId = d.DataAreaId" & _
              " AND t.DataAreaId = 'yor'" & _
              " AND t.DatePhysical Between TO_DATE('01.05.2006', 'DD.MM.YYYY') And TO_DATE('31.05.2006', 'DD.MM.YYYY')"
    '-----------------------------------------
    '(здесь запросы для разных СУБД отличаются только самой последней строчкой с датами)
    Set qdf = Nothing
 
 
    'Запрос 2 - "приведение в порядок" вещественных значений: в данном случае это Qty
    '(это преобразование текста в число реально имеет смысл только для Oracle)
 
    Set qdf = dbs.CreateQueryDef("qry_2_NormalQty")
 
    'запрос на диалекте Access SQL
    qdf.SQL = "SELECT ItemId, InventLocationId, TransType, Direction, StatusReceipt, StatusIssue," & _
              " Val(Replace([Qty],',','.')) AS QtyN FROM [qry_1_GetSourceData]"
    Set qdf = Nothing
 
 
    'Запрос 3 - собственно перекрестный запрос
    Set qdf = dbs.CreateQueryDef("qry_3_Cross")
 
    'запрос на диалекте Access SQL
    qdf.SQL = "TRANSFORM Sum(QtyN) AS [Sum_Qty]" & _
              " SELECT ItemId, Sum(QtyN) AS [Total Qty]" & _
              " FROM [qry_2_NormalQty]" & _
              " GROUP BY ItemId" & _
              " PIVOT InventLocationId"
    Set qdf = Nothing
 
 
    Set dbs = Nothing
 
End Sub

4. В коде процедуры:
- раскомментируйте строки, относящиеся к вашей СУБД и закомментируйте другую;
- пропишите свои параметры подключения к источнику ODBC (вместо "your...") в операторе qdf.Connect = ...;
- пропишите свою компанию - DataAreaId - вместо 'yor';
- подкорректируйте по вашему желанию период по датам для поля DatePhysical с таким расчетом, чтобы в выборку попало не более 10-20 тыс.записей из таблицы InventTrans.

5. Убедитесь, что в меню Tools / References включена ссылка на библиотеку "Microsoft DAO 3.6 Object Library" (если она еще не включена, то включите).

6. Запустите процедуру (например, находясь в любом месте ее тела, нажмите F5). По окончании ее работы на закладке "Запросы" окна базы данных появятся три запроса, созданные процедурой.

7. Запустите на выполнение запрос "qry_3...". Через некоторое (небольшое) время вам будут предъявлены результаты выполнения в стандартном табличном виде: количественные обороты номенклатуры (строки) на складах (столбцы) за заданный в условиях первого запроса период.

Все эти запросы вы могли бы создать и вручную на закладке "Запросы", но в рамках примера кодом быстрее (и мне объяснять, и вам воплощать). Обращаю внимание, что первый запрос - это запрос к серверу, вручную через меню строится так (в англ.версии Access): на закладке Queries/ кнопка New/ Design View (OK)/ Close (в ответ на Show Table)/ (далее в строке меню) Query/ SQL Specific/ Pass-Through.

Это еще не всё. Если у вас - Access 2002-2003, то можно воспользоваться еще одной фичей. Откроем второй запрос qry_2... в конструкторе (Design). В меню выберем: View / PivotTable View. Дальше, думаю, всё понятно и не так сложно. Если список полей для перетаскивания в сводную таблицу не виден, то выполните команду меню View / Field List.

При желании далее можно выполнить команду меню PivotTable / Export to Microsoft Office Excel и мы окажемся в другом любимом продукте, причём, как с исходными данными (на одном листе), так и с бланком сводной таблицы (на другом). В общем, разнообразных "сводно-перекрестных таблиц" у нас теперь - просто завались

P.S. Для того, чтобы фича "PivotTable View" была доступной в Access, на вашем компьютере должны быть установлены Office Web Components (при установке Office по умолчанию они могут быть выключены).

[Прокопьева]

Спасибо
Попробую разобраться с импортом, может и получиться (ранее его не касалась).
Но если появятся еще идеи - я готова выслушать ...