Как составить правильно отчет?

← →
DimonS © (2006-01-11 04:21) [0]

С прошедшими праздниками всех! Вопрос такой.
Есть 3 БД:
Teh - список техники
Cod - Код техники
Name - Наименование
Cena - Цена 1 м/ч

Zak - список заказчиков
Nom - код заказчика
Name_Z - наименование его

TTN - БД товарно-тр. накладных
Zak - заказчик
Cod - техника
Chas - отработано часов

В общем, нужно сделать сводный отчет такого вида:

Техника | Зак.1 | Зак.2 | Зак.3 |
| Часы | Сум.|Часы | Сум.| Часы | Сум.|
-------------------------------------------------

В общем-то я такой отчет делал, но работал с TTable, путем циклов и выводил через Excel. Ну это конечно изврат, начиная от того, что долго делается и кончая тем, что с екселем у юзеров проблемы возникают.

Можно ли это решить одним-двумя SQL-запросами? Хотя бы сделать саму табличку в таком формате? Делаю типа такого запроса:

Select Name, Name_Z, Sum(Chas), Sum(Chas*Cena)
from Zak Z,Teh T,TTN B
where B.Cod=T.Cod and Zak=nom
Group by Name,Name_Z

при выполнении в общем то выдается сводная табличка, но в формате
Зак.1 Техника1
Зак.1 техника2
Зак.2 Техника1

а желательно, чтобы она была в том виде, что приведена выше. Как такое сделать? Или есть способ проще сделать отчет? Я работаю с FastReport, у него есть что-то похожее, типа неизвестного колиества столбцов, но так и не смог разобраться.

← →
sniknik © (2006-01-11 08:14) [1]

> Хотя бы сделать саму табличку в таком формате?
с неопределенным количеством полей... по часам/дням/месяцам (в бугалтерии это называется шахматка), делаеш запрос по часам "в высоту" (сыммы по часам в строках таблици) а после "переворачиваеш" ее

для access посмотри примеры по
Инструкция TRANSFORM, PIVOT Table
в файле JETSQL40.CHM (из справки мсофиса)

← →
DimonS © (2006-01-11 08:24) [2]

То sniknik ©
А пример можно? Т.е. нужно, чтобы в первой графе шел список техники, по вертикале, а по горизонтали - список заказчиков, ну а на пересечении строк и столбцов - суммы часов и денех. Просто пробовал, а ума не хватает :). Да и в литературе что-то не нашел такого примерчика.

← →
sniknik © (2006-01-11 09:09) [3]

> Да и в литературе что-то не нашел такого примерчика.
в конкретную справку ссылка была, а в литературе сам не знаю есть это или нет.

Инструкция TRANSFORM, примеры

Использование предложения SQL TRANSFORM для создания перекрестного запроса, демонстрирующего число заказов, принятых каждым сотрудником в каждом квартале 1994 г. Для выполнения данной процедуры требуется функция SQLTRANSFORMOutput.

Sub TransformX1()
Dim dbs As Database
Dim strSQL As String
Dim qdfTRANSFORM As QueryDef
strSQL = "PARAMETERS prmYear SHORT; TRANSFORM " _
& "Count(КодЗаказа) " _
& "SELECT Имя & "" "" & Фамилия AS " _
& "ФИО FROM Сотрудники INNER JOIN Заказы " _
& "ON Сотрудники.КодСотрудника = " _
& "Заказы.КодСотрудника WHERE DatePart " _
& "(""yyyy"", ДатаРазмещения) = [prmYear] "

strSQL = strSQL & "GROUP BY Имя & " _
& """ "" & Фамилия " _
& "ORDER BY Имя & "" "" & Фамилия " _
& "PIVOT DatePart(""q"", ДатаРазмещения)"

" Укажите в следующей строке путь к базе данных "Борей"
" на вашем компьютере.
Set dbs = OpenDatabase("Борей.mdb")
Set qdfTRANSFORM = dbs.CreateQueryDef _
("", strSQL)

SQLTRANSFORMOutput qdfTRANSFORM, 1994
dbs.Close
End Sub

В следующем примере предложение SQL TRANSFORM используется для создания более сложного перекрестного запроса, демонстрирующего общую сумму заказов, принятых каждым сотрудником в каждом квартале 1994 г. Для выполнения данной процедуры требуется функция SQLTRANSFORMOutput.

Sub TransformX2()
Dim dbs As Database
Dim strSQL As String
Dim qdfTRANSFORM As QueryDef
strSQL = "PARAMETERS prmYear SMALLINT; TRANSFORM " _
& "Sum(Всего) SELECT Имя & "" """ _
& "& Фамилия AS ФИО " _
& "FROM Сотрудники INNER JOIN " _
& "(Заказы INNER JOIN [Сумма заказов] " _
& "ON Заказы.КодЗаказа = " _
& "[Сумма заказов].КодЗаказа) " _
& "ON Сотрудники.КодСотрудника = " _
& "Заказы.КодСотрудника WHERE DatePart" _
& "(""yyyy"", ДатаРазмещения) = [prmYear] "

strSQL = strSQL & "GROUP BY Имя & "" """ _
& "& Фамилия " _
& "ORDER BY Имя & "" "" & Фамилия " _
& "PIVOT DatePart(""q"",ДатаРазмещения)"

" Укажите в следующей строке путь к базе данных "Борей"
" на вашем компьютере.
Set dbs = OpenDatabase("Борей.mdb")
Set qdfTRANSFORM = dbs.CreateQueryDef _
("", strSQL)

SQLTRANSFORMOutput qdfTRANSFORM, 1994
dbs.Close
End Sub

Function SQLTRANSFORMOutput(qdfTemp As QueryDef, _
intYear As Integer)
Dim rstTRANSFORM As Recordset
Dim fldLoop As Field
Dim booFirst As Boolean
qdfTemp.PARAMETERS!prmYear = intYear
Set rstTRANSFORM = qdfTemp.OpenRecordset()

Debug.Print qdfTemp.SQL
Debug.Print
Debug.Print , , "Квартал"

With rstTRANSFORM
booFirst = True
For Each fldLoop In .Fields
If booFirst = True Then
Debug.Print fldLoop.Name
Debug.Print , ;
booFirst = False
Else
Debug.Print , fldLoop.Name;
End If
Next fldLoop

Debug.Print

Do While Not .EOF
booFirst = True
For Each fldLoop In .Fields
If booFirst = True Then
Debug.Print fldLoop
Debug.Print , ;
booFirst = False
Else
Debug.Print , fldLoop;
End If
Next fldLoop
Debug.Print
.MoveNext
Loop
End With

End Function

← →
DimonS © (2006-01-11 09:36) [4]

Ок, попробую разобраться. Просто именно про шахматку не попадалось ничего.

← →
msguns © (2006-01-11 09:39) [5]

Sniknik, конечно, подсказал универсальное решение, но..
Насколько я понял, судя по реквизитному наполнению, т.н. TTN, вовсе и не товарно-транспортная накладная, да и вообще не накладная, а скорее всего акт выполненных работ. Очевидно, фирма выполняет работы по сопровождению или сервисному обслуживанию КТ.
Если так, то кол-во "заказчиков" у такой фирмы должно быть велико, а техника разнообразна. Т.е. при решением "кросс-отчетом" (предложение Sniknik) шахматка получится пустой процентов на 80-90.
Для подобных задач, думаю, удачнее будет давать возможность получать "срезы" по ключевым сущностям (что-то вроде OLAP). Каждый срез в виде конечной двухмерной таблицы и получается обычным запросом, выводимым в грид, откуда нужно предусмотреть возможность печати или экспорта в Эксель. Как пример: при указанном конкретном значении сущности "Техника" выводится полный перечень заказчиков с суммарным временем и суммой. Или при указанном "Заказчике" выдается полный перечень техники опять же с подсчетами времени-денег.

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

← →
Dioman © (2006-01-11 09:58) [6]

можно использовать следующую конструкцию:

select Name,
sum (case when nom = id_zak1 then z.Name_Z else 0 end) as name1,
sum (case when nom = id_zak1 then s1 else 0 end) as sum1,
sum (case when nom = id_zak1 then s2 else 0 end) as sum_chas1,

sum (case when nom = id_zak2 then z.Name_Z else 0 end) as name2,
sum (case when nom = id_zak2 then s1 else 0 end) as sum2,
sum (case when nom = id_zak2 then s2 else 0 end) as sum_chas2,

sum (case when nom = id_zak3 then z.Name_Z else 0 end) as name3,
sum (case when nom = id_zak3 then s1 else 0 end) as sum3,
sum (case when nom = id_zak3 then s2 else 0 end) as sum_chas3,

from
(
--ваш исходный запрос
Select Name, Name_Z, Z.nom, Sum(Chas) as s1, Sum(Chas*Cena) as s2
from Zak Z,Teh T,TTN B
where B.Cod=T.Cod and Zak=nom
Group by Name,Name_Z, Z.nom
)
group by Name

блоки первого списка выборки (Select) формируешь циклом из результата запроса по списку заказов по которым хочешь вывести информацию, либо даешь юзеру править (вдруг сильно много заказов... и будет не читабельно).. пусть выбирает те по которым хочет получить инфу.
в цикле требуется подставить вместо id_zak1(2,3....) коды выбранных заказов, а вместо name1(2,3...) , sum..., sumchaz... подставляешь итератор цикла типа "name" + inttostr(i), потом когда запрос отработает расшифровываешь и представляешь в удобном виде.

← →
msguns © (2006-01-11 10:03) [7]

>Dioman © (11.01.06 09:58) [6]

Это для акцеса ?

← →
DimonS © (2006-01-11 10:27) [8]

То msguns ©

В общем дело обстоит так.
Фирма - это автобаза, БД TTN - это действительно товарки, просто я привел только нужные поля, записей за месяц около 3000. Техники - около 100, но в месяц может использоваться не вся.

Что конкретно нужно:
Отчет в печатаемом виде, где в первой графе занесена техника, используемая в этом месяце (ну или вся полностью), сгруппированная по наименованию, а в следующих столбцах - Наименование Заказчиков и против каждой техники - сколько отработано часов и сумма к оплате. Действительно, пустых значений довольно много, % 80. Вот как это все же лучше реализовать? Лучше бы конечно, без циклов...

← →
msguns © (2006-01-11 10:54) [9]

Смутило слово "техника",- как-то не подумалось, что кроме компьютерной бывает еще и другая, например, авто ;)
При "шахматном подходе" не удастся получить при таких объемах печатаемый отчет. Это будет простыня, которую придется полчаса склеивать перед тем, как юзать. Ищи решение по схеме, приведенной в [5].
Возможно, годится такой вариант - в гриде показывать "шахматку", а печатать только общие итоги по всей технике либо подробный отчет по выбранной единице техники/заказчику.
Ну и, конечно, надо предусмотреть выгруз в Эксель. А вот там пусть делают с ним все, что хотят, в т.ч. и печатают+шьют простынь, если уж очхочется.

Насчет простыни это точно, листов на 15-20 получается, это не мои заботы :).

Значит, все-таки проще шахматкой реализовать?
А без екселя можно обойтись? У меня щас происходит туда выгрузка, но юзера имеют привычку закрывать ексель и при повторном отчете, ессно, выскакивает ошибка.

>DimonS © (11.01.06 11:02) [10]
>Значит, все-таки проще шахматкой реализовать? А без екселя можно обойтись?

Мальчик хочет в Тамбов ? Там холодно, там чай не растет и туда не летают самолеты, а он все равно хочет ? Дадим мальчику самокат и нехай едет !
На здоровье !
В смысле, если юзер хочет печатать видимую таблицу несмотря на ее "простынность", то Бога ради ! Но в Экселе ! А там пусть и размечает печать и собственно печатает.
Если же ты сам берешь на себя эти функции, то и на претензии будешь сам отвечать. И глюки ловить при отладке, и мозги парить при разработке "гибкого" репорта.

Дык в том то и дело, выходит так, что мне ничего не остается, как так делать, через Ексель. Размечать им естественно придется. Другого выхода пока не вижу.

Как составить правильно отчет? Найти похожие ветки