Главная  »»»  Информация, перенесённая из старых форумов  »»»  Обмен опытом по использованию iScala Payroll  »»»  Дни рождения сотрудников: Отчет MS SQL Server RS

Дни рождения сотрудников: Отчет MS SQL Server RS

Автор Сообщение
aav
Администратор
Администратор

Зарегистрирован: 14.09.2004
Сообщения: 1081
Откуда: Санкт-Петербург

 Добавлено: 27.01.2006 11:23 Заголовок сообщения: Дни рождения сотрудников: Отчет MS SQL Server RS
Коллеги,

Для тех, кто уже познакомился с технологией MS SQL Server 2000 Reporting Services, могу предложить простенький отчет о днях рождения сотрудников, базирующийся на зарплатной картотеке сотрудников iScala 2.2. Это мои первые шаги в пространстве данных модуля «Заработная плата», так что поправляйте, если найдете несоответствия.

Для начала хранимая процедура для получения требуемых данных:

Код:
CREATE PROCEDURE usr_RS_BirthDays
@SelectMonth int
as
select
convert(int,substring(PA04072,5,2)) MonthOfBirth,
convert(int,right(PA04072,2)) DateOfBirth,
left(PA04072,4) YearOfBirth,
left(PA04045,1)+lower(substring(PA04045,2,24))
+’ ‘+left(PA04046,1)+lower(substring(PA04046,2,24))
+’ ‘+left(PA04047,1)+lower(substring(PA04047,2,24)) FIO
from PA040100 (NOLOCK)
where PA04078<>1
and convert(int,substring(PA04072,5,2))=@SelectMonth
order by right(PA04072,4), PA04045, PA04046, PA04047


А теперь текст самого отчета:

Код:
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<RightMargin>2.5cm</RightMargin>
<Author>Васильев А.А.</Author>
<Body>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>14pt</FontSize>
<TextAlign>Center</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<Height>0.75cm</Height>
<CanGrow>true</CanGrow>
<Value>= Globals!ReportName</Value>
</Textbox>
<Table Name="table1">
<Height>1cm</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.5cm</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<TextAlign>Center</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Фамилия, имя, отчество</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<TextAlign>Center</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>День рождения</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.5cm</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="FIO">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=IIF( Fields!MonthOfBirth.Value=month(now()),IIF(Fields!DateOfBirth.Value=day(now()),"Yellow","White"),"White")</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>=IIF( Fields!MonthOfBirth.Value=month(now()),IIF(Fields!DateOfBirth.Value=day(now()),"Bold","Normal"),"Normal")</FontWeight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>FIO</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!FIO.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>dd.MM.yyyy</Format>
<BackgroundColor>=IIF( Fields!MonthOfBirth.Value=month(now()),IIF(Fields!DateOfBirth.Value=day(now()),"Yellow","White"),"White")</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>=IIF( Fields!MonthOfBirth.Value=month(now()),IIF(Fields!DateOfBirth.Value=day(now()),"Bold","Normal"),"Normal")</FontWeight>
</Style>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=DateSerial(Year(Now()), Fields!MonthOfBirth.Value, Fields!DateOfBirth.Value )</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>ExecSQL</DataSetName>
<Top>0.9cm</Top>
<TableColumns>
<TableColumn>
<Width>6.3cm</Width>
</TableColumn>
<TableColumn>
<Width>2.7cm</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>2cm</Height>
<ColumnSpacing>1cm</ColumnSpacing>
</Body>
<TopMargin>1cm</TopMargin>
<DataSources>
<DataSource Name="Payroll">
<rd:DataSourceID>feea08bb-5c52-4539-982f-d8abd3365ab2</rd:DataSourceID>
<DataSourceReference>Payroll</DataSourceReference>
</DataSource>
</DataSources>
<Code />
<Width>9cm</Width>
<DataSets>
<DataSet Name="ExecSQL">
<Fields>
<Field Name="MonthOfBirth">
<DataField>MonthOfBirth</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="DateOfBirth">
<DataField>DateOfBirth</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="YearOfBirth">
<DataField>YearOfBirth</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="FIO">
<DataField>FIO</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Payroll</DataSourceName>
<CommandText>exec usr_RS_BirthDays @SelectMonth</CommandText>
<QueryParameters>
<QueryParameter Name="@SelectMonth">
<Value>=Parameters!SelectMonth.Value</Value>
</QueryParameter>
</QueryParameters>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>2.5cm</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<PageHeight>29.7cm</PageHeight>
<rd:DrawGrid>true</rd:DrawGrid>
<Description>Дни рождения сотрудников организации в заданном месяце</Description>
<PageWidth>21cm</PageWidth>
<rd:ReportID>119ef86f-005b-47d6-813f-2873dd6c48f9</rd:ReportID>
<PageFooter>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>6pt</FontSize>
<VerticalAlign>Middle</VerticalAlign>
<TextAlign>Center</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<CanGrow>true</CanGrow>
<Value>="Дата и время распечатки отчета: " &amp; Format( Globals!ExecutionTime,"dd.MM.yyyy HH:mm") &amp; ". Страница " &amp; Globals!PageNumber &amp; " из " &amp; Globals!TotalPages</Value>
</Textbox>
</ReportItems>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
<Style />
<Height>0.29531in</Height>
</PageFooter>
<BottomMargin>1cm</BottomMargin>
<ReportParameters>
<ReportParameter Name="SelectMonth">
<DataType>Integer</DataType>
<DefaultValue>
<Values>
<Value>=month(now())</Value>
</Values>
</DefaultValue>
<Prompt>Месяц</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>1</Value>
<Label>Январь</Label>
</ParameterValue>
<ParameterValue>
<Value>2</Value>
<Label>Февраль</Label>
</ParameterValue>
<ParameterValue>
<Value>3</Value>
<Label>Март</Label>
</ParameterValue>
<ParameterValue>
<Value>4</Value>
<Label>Апрель</Label>
</ParameterValue>
<ParameterValue>
<Value>5</Value>
<Label>Май</Label>
</ParameterValue>
<ParameterValue>
<Value>6</Value>
<Label>Июнь</Label>
</ParameterValue>
<ParameterValue>
<Value>7</Value>
<Label>Июль</Label>
</ParameterValue>
<ParameterValue>
<Value>8</Value>
<Label>Август</Label>
</ParameterValue>
<ParameterValue>
<Value>9</Value>
<Label>Сентябрь</Label>
</ParameterValue>
<ParameterValue>
<Value>10</Value>
<Label>Октябрь</Label>
</ParameterValue>
<ParameterValue>
<Value>11</Value>
<Label>Ноябрь</Label>
</ParameterValue>
<ParameterValue>
<Value>12</Value>
<Label>Декабрь</Label>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
</ReportParameters>
<Language>en-US</Language>
</Report>


Что сделать?
1. Создать хранимую процедуру, используя текст выше (разумеется, код компании Вы можете заменить на нужный).
2. Выделить текст отчета, скопировать в буфер и вставить в notepad. Сохранить в формате UTF-8 с расширением .rdl, например, «Дни рождения сотрудников организации.rdl»
3. Закачать на сервер отчетности и указать нужную Data Source. Разумеется, Вы должны уметь делать те несложные операции, которые обычно производятся при добавлении нового отчета.
4. Пробовать
Кстати, удобство при использовании хранимой процедуры состоит в том, что легко сделать кастомизацию под специфические настройки конкретной инсталляции, не трогая сам отчет.
Jugulator
Главный форумщик

Зарегистрирован: 08.10.2004
Сообщения: 428

 Добавлено: 30.01.2006 12:37 Заголовок сообщения: Пример списка дней рождения сотрудников (запрос SQL)
/* Пример списка дней рождения сотрудников */
select PA04001 /* Код сотрудника */
, PA04072 /* Дата рождения в формате ГГГГММДД */
, [AGE] = CASE WHEN convert(int,SUBSTRING(PA04072,5,2)) < MONTH(GETDATE()) THEN /* Возраст сейчас */
YEAR(GETDATE()) — convert(int,LEFT(PA04072,4))
WHEN convert(int,SUBSTRING(PA04072,5,2)) = MONTH(GETDATE())
AND convert(int,SUBSTRING(PA04072,7,2)) <= DAY(GETDATE()) THEN
YEAR(GETDATE()) — convert(int,LEFT(PA04072,4))
ELSE YEAR(GETDATE()) — convert(int,LEFT(PA04072,4)) — 1
END
, [TODAY] = CASE WHEN convert(int,SUBSTRING(PA04072,5,2)) = MONTH(GETDATE()) /* Если ДР сегодня */
AND convert(int,SUBSTRING(PA04072,7,2)) = DAY(GETDATE()) THEN ‘X’ ELSE » END
, UPPER(PA04045) AS PA04045 /* Фамилия */
, UPPER(PA04046) AS PA04046 /* Имя */
, UPPER(PA04047) AS PA04047 /* Отчество */
from PA040100 /* Компания 01 */
where substring(PA04072,5,2) = ’01’ /* Месяц в формате ММ */
—PA04012 = 0 /* Показывать только неуволенных сотрудников */
order by substring(PA04072,5,4), UPPER(PA04045), UPPER(PA04046), UPPER(PA04047)
/* Сортировка: дата рождения, фамилия, имя, отчество */
aav
Администратор
Администратор

Зарегистрирован: 14.09.2004
Сообщения: 1081
Откуда: Санкт-Петербург

 Добавлено: 30.01.2006 13:40 Заголовок сообщения: Re: Пример списка дней рождения сотрудников (запрос SQL)
Спасибо!

Кстати, в предложенном выше отчете уже не требуется получать в запросе флаг того, что день рождения сегодня:

Jugulator писал(а):
[TODAY] = CASE WHEN convert(int,SUBSTRING(PA04072,5,2)) = MONTH(GETDATE()) /* Если ДР сегодня */
AND convert(int,SUBSTRING(PA04072,7,2)) = DAY(GETDATE()) THEN ‘X’ ELSE » END


Вместо этого уже в самом отчете используется условное форматирование:

Код:
<BackgroundColor>=IIF( Fields!MonthOfBirth.Value=month(now()),IIF(Fields!DateOfBirth.Value=day(now()),"Yellow","White"),"White")</BackgroundColor>


и

Код:
<FontWeight>=IIF( Fields!MonthOfBirth.Value=month(now()),IIF(Fields!DateOfBirth.Value=day(now()),"Bold","Normal"),"Normal")</FontWeight>


Т.е., если день рождения сотрудника сегодня, строка выделяется жирным шрифтом и желтым цветом фона. Кстати, у нас сегодня 4 таких строки Very Happy
Jugulator
Главный форумщик

Зарегистрирован: 08.10.2004
Сообщения: 428

 Добавлено: 31.01.2006 17:42 Заголовок сообщения: Запрос SQL "Дни рождения сотрудников" без CASE
select PA04001 /* Код сотрудника */
, PA04072 /* Дата рождения в формате ГГГГММДД */
, [AGE] = YEAR(GETDATE()) — convert(int,LEFT(PA04072,4)) — (
1 — SIGN(1 — SIGN(convert(int,SUBSTRING(PA04072,5,4)) — (MONTH(GETDATE()) * 100 + DAY(GETDATE()))))
) /* Возраст сейчас */
, [TODAY] = LEFT(‘X’,
1 — ABS(SIGN(convert(int,SUBSTRING(PA04072,5,4)) — (MONTH(GETDATE()) * 100 + DAY(GETDATE()))))
) /* Отметить, если день рождения сегодня */
, UPPER(PA04045) AS PA04045 /* Фамилия */
, UPPER(PA04046) AS PA04046 /* Имя */
, UPPER(PA04047) AS PA04047 /* Отчество */
from PA040100 /* Компания 01 */
where substring(PA04072,5,2) = ’01’ /* Месяц в формате ММ */
—PA04012 = 0 /* Показывать только неуволенных сотрудников */
order by substring(PA04072,5,4), UPPER(PA04045), UPPER(PA04046), UPPER(PA04047)
/* Сортировка: дата рождения, фамилия, имя, отчество */