Помогите с кросс-отчётом

Luckyman · February 2004

Столкнулся тут с такой проблемой - не знаю как сделать
кросс-отчёт в Fastreport 2.51.

Есть такая таблица (Interbase):

create table Logs (
DateIn Date not null, /* дата потребления*/
codedrink integer not null, /* напиток */
Count integer not null, /* кол-во порций */
constraint PK_LOGS PRIMARY KEY (codedrink, DateIn)
)

Надо её вывести вот в таком виде, понятном юзеру:

| дата | нап.1 | нап.2 | нап.3 | нап.4 | нап.N |

| 01.01.2004 | 1 | 2 | 3 | 10 | 0 |
| 02.01.2004 | 3 | | 0 | 1 | 0 |

где нап.N - название напитка N

Есть FastReport 2.51. В нём есть компонент TfrCrossView,
который выводит в таком виде, но мне не надо выводить сумму
всех напитков за день, только итого потребления каждого напитка
за весь период. Как отключить вывод ненужного поля - не нашёл.
Да и как-то ограничен в настройке он - хочется свободы побольше.
Как сделать такое же самому с помощью бандов? Пробовал с одним
датасетом(исходная таблица) - фигня. Пробовал с мастер-датасетом
(distinct-список дат) - что-то название напитка выводится только
одно - да и геморой с 2-мя датасетами. Понятно, что делаю что-то
не так - но вот что

.
Самое интересное, что в компоненте выводится всё нормально, значит
можно сделать такой отчёт и ручками?

Буду очень благодарен за любую помощь, а то отчёт очень нужен -
учёба стоит.

With best regards, Serg Kutuzov.
[url='mailto:Luckyman2003@pisem.net'>Luckyman2003@pisem.net[/url]

mac · February 2004

Вот как это можно сделать с помощью банда CrossData

Создай Query с запросом (это будет Master )

select dictinct DateIn from Logs order by DateIn

Создай еще один Query с запросом (Это будет Detail )

select distinct CodeDrink, (select sum(Count) as DrinkCount
from Logs Logs2
where Logs2.CodeDrink=Logs1.CodeDrink
where DateIn = :DateIn)
from Logs Logs1
order by CodeDrink

Второму Query пропиши в property DataSource первый MasterQuery

Теперь дизайни непосредствено FastReport
Кладешь Band ColumnHeader.
Кладешь Band MasterData. Кладешь на него поле DateIn из Query1
Кладешь Band CrossData. Указываешь ему DataSource на пересечении с MasterData и с ColumnHeader - это будет твой второй ( Detail ) Query
Теперь на пересечении CrossData и ColumnHeader кладешь поле Сodedrink из второго запроса. А на пересечении CrossData и MasterData кладешь поле DrinkCount.
Компоненты TfrMEmoView должны быть строго в рамках пересечения бэндов.
Во втором запросе используется именно такой запрос а не с помощью GroupBy для того, чтобы количество возвращяемых строк было всегда одинаковым.

В качестве DataSource для CrossData может быть использован только frDBDataSet. Поэтому используй два TfrDBDataSet для Master и Detail запросов.

Luckyman · February 2004

написал:

select distinct CodeDrink, (select sum(Count) as DrinkCount
from Logs Logs2
where Logs2.CodeDrink=Logs1.CodeDrink
where DateIn = :DateIn)
from Logs Logs1
order by CodeDrink

as DrinkCount пришлось перенести за скобки, иначе не работает

Ну в ообщем, стало выглядеть так:

select distinct CodeDrink, 
                  (select sum(Count)  from Logs Logs2
                     where Logs2.CodeDrink = Logs1.CodeDrink
                           and DateIn = :DateIn)  as DrinkCount
  from Logs Logs1
    order by CodeDrink

При попытке открытия ругается вот таким матом:
"DataModDesigner.Datamod.DetailReport.SelectQuery:
An error was found in the application program input
parameters for the SQL statemen.Dynamic SQL Error.
SQL Error Code = -804.
SQLDA missing or incorrect version,
or incorrect number/type of variables."

Без distinct открывается нормально, но результат, естественно, не тот, которые нужен.
Ну я так понял, названия столбцов отображаются по набору с первой датой,
поэтому надо получить такой же набор, как и исходный,
только надо включить записи по отсутствующим напиткам с NULL в
поле Count. Но как это сделать - не врублюсь. Я пока разобрался, как
этот запрос работает... И то, до конца не понял идею. Жульство какое-то.

mac · February 2004

За скобку надо было название вынести - это моя ошибка.
Вообще-то чего оно у тебя ругается не знаю. Ты банды ложил как я написал?
Ну да ладно, давай так попробуем: судя по всему у тебя CodeDrink это ссылка на справочник напитков (DrinkTable). Если так, то переделываем второй запрос вот так:

Select DrinkTable.Name, Sum(Logs.Count) as DrinkCount
from DrinkTable left outer join Logs on (DrinkTable.CodeDrink=Logs.CodeDrink)
where Logs.DateIn = :DateIn
and DrinkTable 
group by DrinkTable.Name
order by DrinkTable.Name

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

Luckyman · February 2004

Опять что-то в запросе не так: and DrinkTable наверное ненадо

?
Запрос выполняется, но выводится опять только одно название.
Как работает кросс-отчёт в принципе, понятно, надо только написать
нужный запрос, который возвращает названия напитков для
даты + NULL(кол-во), если нет записей с ними для этой даты.

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

Таблица напитков:

create table Drinks (
CodeDrink            INTEGER                        not null,
NameDrink            CHAR(50)                       not null,
constraint PK_DRINKS primary key (CodeDrink));

Таблица потребления напитков:

create table ConsumptionDrinks (
CodeApparat          INTEGER                        not null,
CodeDrink            INTEGER                        not null,
dateindrink          DATE                           not null,
countdrink           INTEGER                        not null,
constraint PK_CONSUMPTIONDRINKS primary key (CodeApparat, CodeDrink, dateindrink));

Код аппарата принимаем везде равным, допустим 6.

Мастер-запрос:

select distinct DateInDrink 
from CONSUMPTIONDRINKS
where
    CodeApparat = 6/*?MAS_CodeApparat*/
order by DateInDrink

Деталь-запрос:

Select Drinks.NameDrink, Sum(ConsumptionDrinks.CountDrink) as DrinkCount
from Drinks left outer join ConsumptionDrinks 
  on (Drinks.CodeDrink = ConsumptionDrinks.CodeDrink)
where ConsumptionDrinks.DateInDrink = ?MAS_DateInDrink
  and CodeApparat = 6
group by Drinks.NameDrink
order by Drinks.NameDrink

Данные таблицы Напитки:

CODEDRINK;NAMEDRINK
4;чай
5;кофе

Данные таблицы ConsumptionDrinks:

CODEAPPARAT;CODEDRINK;DATEINDRINK;COUNTDRINK
6;4;18.02.2004;1
6;4;19.02.2004;20
6;5;17.02.2004;2
6;5;18.02.2004;3
6;5;19.02.2004;5

В итоге получаю вот такой отчёт:

     кофе   
17.02.2004   2
18.02.2004   3     1
19.02.2004   5     20

Как видно, второе название(чай) не выводится, так как деталь-запрос для даты "17.02.2004" выдаёт (кофе,2), а надо чтоб выдавал (кофе,2),(чай,null).
Ну как надо сбацать деталь-запрос?

Ещё разок извинусь за большой объём письма, но просто хочется всё более подробно показать.

mac · February 2004

Очередная моя ошибка

Действительно тот Detail запрос который я написал в последнем письме работает не верно. Я не поленился и создал у себя твои таблицы что бы проверить все точно. Таким образом вот этот запрос проверен мной в реальности. Правда тут опять используется техника схожая с моей первой поопыткой, когда у тебя писало ошибку. Эти запрос я проверял в работе с сервером Sybase ASA. Ну все же попробуй.

Select Drinks.NameDrink, ConsumptionDrinks.DrinkCount
from Drinks as Drinks left outer join
(select ConsumptionDrinks.CodeDrink, Sum(ConsumptionDrinks.CountDrink) as DrinkCount
from
ConsumptionDrinks
where 1=1
and  ConsumptionDrinks.DateInDrink = :DateInDrink
 and CodeApparat = 6 
group by ConsumptionDrinks.CodeDrink
) as ConsumptionDrinks  on (Drinks.CodeDrink = ConsumptionDrinks.CodeDrink)

order by Drinks.NameDrink

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

Надеюсь мы добъем эту задачу

Luckyman · February 2004

mac написал:
Очередная моя ошибка Действительно тот Detail запрос который я написал в последнем письме работает не верно. Я не поленился и создал у себя твои таблицы что бы проверить все точно. Таким образом вот этот запрос проверен мной в реальности. Правда тут опять используется техника схожая с моей первой поопыткой, когда у тебя писало ошибку. Эти запрос я проверял в работе с сервером Sybase ASA. Ну все же попробуй.
Select Drinks.NameDrink, ConsumptionDrinks.DrinkCount
from Drinks as Drinks left outer join
(select ConsumptionDrinks.CodeDrink, Sum(ConsumptionDrinks.CountDrink) as DrinkCount
from
ConsumptionDrinks
where 1=1
and  ConsumptionDrinks.DateInDrink = :DateInDrink
 and CodeApparat = 6 
group by ConsumptionDrinks.CodeDrink
) as ConsumptionDrinks  on (Drinks.CodeDrink = ConsumptionDrinks.CodeDrink)

order by Drinks.NameDrink 
И еще. Ты сначала проверь как будет работать эта связка у тебя без frREport. Т.е. положи на фрму два DBgrid , свяжи их с запросами и посмотри как работает.

Надеюсь мы добъем эту задачу

Этот запрос обязательно попробую. Но я обошёл эту проблему немного через ж... Создал view, в котором получил список всех напитков, использованных в таблице ConsumptionDrinks, таким образом избавился от distinct в деталь-запросе:

CREATE VIEW UNICCODEDRINK(CODEDRINK)
AS
  select distinct cd1.CodeDrink
from ConsumptionDrinks cd1

Деталь - запрос стал выглядеть так:

select NameDrink,
(select CountDrink from ConsumptionDrinks cd2
 where cd2.DateInDrink = ?MAS_DateInDrink
    and cd2.CodeDrink = cd1.CodeDrink
    and CD2.CodeApparat = ?MAS_CodeApparat) as DrinkCount
from UnicCodeDrink cd1 LEFT JOIN Drinks
  ON (CD1.CodeDrink = Drinks.CodeDrink)
ORDER BY NameDrink

Скорее всего в ошибке с distinct виноваты либо компоненты FIB+, либо сервер Yaffil, так как FastReport не подключал.
Теперь всё работает, но реализация по-моему мнению, через ж...
Как уже говорил, есть компонента TfrCrossView в Fasreporte, которой
подсовываешь просто ОДИН(!) набор -выборку из ConsumptionDrinks
и нормально полчаешь искомый результат. Единственное, что не устраивает - невозможность настройки размеров полей, прятанья ненужных полей по выбору. Т.е. свободы мало. А так компонента хорошая.
Большое спасибо, что затратил на мой вопрос столько времени, сам бы
я ещё долго разбирался, что к чему.

Помогите с кросс-отчётом

Комментарии

Оставить комментарий