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

Комментарии

• mac Минск

  February 2004 отредактировано 08:17

  Вот как это можно сделать с помощью банда 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 отредактировано 08:17

  написал:

  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 отредактировано 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 отредактировано 08:17

  Опять что-то в запросе не так: 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 отредактировано 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 отредактировано 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
  и нормально полчаешь искомый результат. Единственное, что не устраивает - невозможность настройки размеров полей, прятанья ненужных полей по выбору. Т.е. свободы мало. А так компонента хорошая.
  Большое спасибо, что затратил на мой вопрос столько времени, сам бы
  я ещё долго разбирался, что к чему.