Отчёт "Визуализация допустимых комбинаций кодов". Получи бесплатно.

С 9 по 12 октября я проводил тренинг для новых сотрудников. По разным модулям и, в том числе, показывал, как вводить допустимые комбинации кодов, т.е. соответствие бухгалтерского счёта и другого учётного измерения. После выполнения практического задания один из участников спросил меня, как можно посмотреть все введённые комбинации, не для конкретного бухгалтерского счёта, а, вообще, все. И ведь такого стандартного отчёта, чтобы это увидеть, тем более в удобной визуальной форме, нет. Я хотел было показать свой отчёт «Визуализация допустимых комбинаций кодов», даже внёс в него изменения после окончания рабочего дня, но так и не смог его опубликовать на сервере клиента, т.к. мне не предоставили соответствующих прав. И тогда мне пришла в голову идея опубликовать его на всеобщее обозрение с описанием шагов, что нужно сделать, чтобы он заработал.

Если Вы далеки от технической стороны вопроса, но картинка Вам нравится, просто заставьте Ваших технических специалистов проделать действия, которые я опишу ниже 🙂

Условия использования:

Отчёт и хранимые процедуры SQL сервера поставляются «КАК ЕСТЬ» без каких-либо обязательств со стороны автора и возможности со стороны получателя предъявить какие-либо претензии. Вы НЕ можете убрать из отчёта и из текста хранимых процедур ссылку на автора. Предполагается, что у Вас имеется iScala, а также установленный и настроенный соответствующим образом (работающий) сервер отчётов (компонент MS SQL Server Reporting Services. Если у Вас есть iScala, то и этот компонент у Вас тоже есть, его не нужно покупать, он входит в комплект сервера баз данных). Если Вы принимаете эти условия, тогда проделайте следующие шаги, чтобы отчёт заработал на Вашем сервере отчётов.

Сначала скопируйте следующий фрагмент, вставьте его в Блокнот и сохраните как Valid_Coding_Combinations.sql в формате Unicode (смотрите картинки, описывающие процесс сохранения файла ниже)

Текст SQL запроса. Щелкните, чтобы показать/скрыть

/*
Вывод списка финансовых лет для компании
Автор: Васильев А.А. http://scala.org.ru
Дата создания/Последнего изменения: 25.09.2007/25.09.2007
*/
create procedure [dbo].[usr_RS_CompanyYears]
@CC nvarchar(2)
as
select distinct right(convert(varchar,[Year]) ,2) as YY, [Year] as YYLabel
from ScaCompanyFeature (nolock)
where CompanyCode=@CC
order by [Year] desc


GO

/*
Получить список компаний
Автор Васильев А.А. http://scala.org.ru
Дата создания/последнего изменения: 25.09.2007/25.09.2007
*/
create procedure [dbo].[usr_RS_GetCompanies]
as
select CompanyCode as CC, CompanyCode + ' - ' + CompanyName as CName from ScaCompanies (nolock)
GO

/*
Получить список учётных сегментов
Автор Васильев А.А. http://scala.org.ru
Дата создания/последнего изменения: 10.10.2018/10.10.2018
*/
CREATE procedure [dbo].[usr_RS_GetCompanySegments]
@CC nchar(2)
AS
SELECT
CHAR(65+[SegmentID]) as Segment,
[Name]
FROM [ScaCompanySegment]
where CompanyCode=@CC and SegmentID>0
GO

/*
Dataset для отчёта Valid Coding Combination
Автор Васильев А.А. http://scala.org.ru
Дата создания/последнего изменения: 10.10.2018/10.10.2018
*/
CREATE procedure usr_RS_Valid_Coding_Combinations
@CC nchar(2),
@YY nchar(2),
@Dim nchar(1)='B' -- Like GL03001 B=Dim1, C=Dim2 and etc
as
exec('
select
AccountNumber,
AccountingDimension,
sum(Used) as Used
from (
select distinct
rtrim(GL46001) + space(1) + GL53002 as AccountNumber,
case when left(GL03002,3)=left(GL03003,3) then '''' else GL03002 + space(1) end + GL03003 as AccountingDimension,
case
when GL03002 between rtrim(ltrim(GL46003)) and rtrim(ltrim(GL46004))
then
case
when GL46005=0 then 1
when GL46005=1 then -2
end
else 0
end as Used
from GL46'+@CC+@YY+' (nolock)
cross join (
select GL03002,GL03003,GL03004 from GL03'+@CC+@YY+' (nolock) where GL03001='''+@Dim+''' and GL03014=0
union all
select distinct ltrim(rtrim(GL46003)), ''Missing'', ''Missing'' from GL46'+@CC+@YY+' (nolock)
left join GL03'+@CC+@YY+' (nolock) on rtrim(ltrim(GL46003))=GL03002
where GL03002 is null
) GL03
left join GL53'+@CC+@YY+' (nolock)
on GL46001=GL53001) ungroupped
group by AccountNumber,
AccountingDimension
order by AccountNumber, AccountingDimension')
GO

Подробно в картинках:

Вставьте скопированный текст в Блокнот:

Сохраните как:

Имя файла: с расширением .sql

Тип файла: *.* (не .txt!!!)

Кодировка: Юникод

Затем откройте сохранённый файл в Microsoft SQL Server Management Studio (можно просто создать новый запрос и вставить туда скопированный текст) и выполните его (нажав клавишу «F5»). Не забудьте сначала выбрать базу данных, где хранятся таблицы iScala:

После этого скопируйте следующий текст и также вставьте его в Блокнот:

Код отчёта. Щелкните, чтобы показать/скрыть

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns:cl="http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition">
  <AutoRefresh>0</AutoRefresh>
  <DataSources>
    <DataSource Name="scalaDB">
      <ConnectionProperties>
        <DataProvider>SQL</DataProvider>
        <ConnectString>Data Source=YourSQLServerName;Initial Catalog=YourDatabaseName</ConnectString>
        <IntegratedSecurity>true</IntegratedSecurity>
      </ConnectionProperties>
      <rd:SecurityType>Integrated</rd:SecurityType>
      <rd:DataSourceID>125a0825-e10d-498a-90a8-75f9290aad13</rd:DataSourceID>
    </DataSource>
  </DataSources>
  <DataSets>
    <DataSet Name="DS_Valid_Coding_Combinations">
      <Query>
        <DataSourceName>scalaDB</DataSourceName>
        <QueryParameters>
          <QueryParameter Name="@CC">
            <Value>=Parameters!CC.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@YY">
            <Value>=Parameters!YY.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@Dim">
            <Value>=Parameters!Dim.Value</Value>
          </QueryParameter>
        </QueryParameters>
        <CommandType>StoredProcedure</CommandType>
        <CommandText>usr_RS_Valid_Coding_Combinations</CommandText>
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
      <Fields>
        <Field Name="AccountNumber">
          <DataField>AccountNumber</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="AccountingDimension">
          <DataField>AccountingDimension</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Used">
          <DataField>Used</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
    <DataSet Name="Companies">
      <Query>
        <DataSourceName>scalaDB</DataSourceName>
        <CommandType>StoredProcedure</CommandType>
        <CommandText>usr_RS_GetCompanies</CommandText>
      </Query>
      <Fields>
        <Field Name="CC">
          <DataField>CC</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="CName">
          <DataField>CName</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
    <DataSet Name="Years">
      <Query>
        <DataSourceName>scalaDB</DataSourceName>
        <QueryParameters>
          <QueryParameter Name="@CC">
            <Value>=Parameters!CC.Value</Value>
          </QueryParameter>
        </QueryParameters>
        <CommandType>StoredProcedure</CommandType>
        <CommandText>usr_RS_CompanyYears</CommandText>
      </Query>
      <Fields>
        <Field Name="YY">
          <DataField>YY</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="YYLabel">
          <DataField>YYLabel</DataField>
          <rd:TypeName>System.Int16</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
    <DataSet Name="Segments">
      <Query>
        <DataSourceName>scalaDB</DataSourceName>
        <QueryParameters>
          <QueryParameter Name="@CC">
            <Value>=Parameters!CC.Value</Value>
          </QueryParameter>
        </QueryParameters>
        <CommandType>StoredProcedure</CommandType>
        <CommandText>usr_RS_GetCompanySegments</CommandText>
      </Query>
      <Fields>
        <Field Name="Segment">
          <DataField>Segment</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Name">
          <DataField>Name</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
  </DataSets>
  <ReportSections>
    <ReportSection>
      <Body>
        <ReportItems>
          <Tablix Name="matrix1">
            <TablixCorner>
              <TablixCornerRows>
                <TablixCornerRow>
                  <TablixCornerCell>
                    <CellContents>
                      <Textbox Name="HeaderLabel">
                        <CanGrow>true</CanGrow>
                        <KeepTogether>true</KeepTogether>
                        <Paragraphs>
                          <Paragraph>
                            <TextRuns>
                              <TextRun>
                                <Value>="Account / " &amp; Parameters!Dim.Label</Value>
                                <Style>
                                  <FontStyle>Italic</FontStyle>
                                  <FontWeight>Bold</FontWeight>
                                </Style>
                              </TextRun>
                            </TextRuns>
                            <Style>
                              <TextAlign>Center</TextAlign>
                            </Style>
                          </Paragraph>
                        </Paragraphs>
                        <ZIndex>3</ZIndex>
                        <Style>
                          <Border>
                            <Style>Solid</Style>
                          </Border>
                          <BackgroundColor>White</BackgroundColor>
                          <VerticalAlign>Middle</VerticalAlign>
                          <PaddingLeft>2pt</PaddingLeft>
                          <PaddingRight>2pt</PaddingRight>
                          <PaddingTop>2pt</PaddingTop>
                          <PaddingBottom>2pt</PaddingBottom>
                        </Style>
                      </Textbox>
                    </CellContents>
                  </TablixCornerCell>
                </TablixCornerRow>
              </TablixCornerRows>
            </TablixCorner>
            <TablixBody>
              <TablixColumns>
                <TablixColumn>
                  <Width>0.4cm</Width>
                </TablixColumn>
              </TablixColumns>
              <TablixRows>
                <TablixRow>
                  <Height>0.3cm</Height>
                  <TablixCells>
                    <TablixCell>
                      <CellContents>
                        <Textbox Name="Used">
                          <CanGrow>true</CanGrow>
                          <KeepTogether>true</KeepTogether>
                          <Paragraphs>
                            <Paragraph>
                              <TextRuns>
                                <TextRun>
                                  <Value>=IIF(Sum(Fields!Used.Value)&gt;0,"+",IIF(Sum(Fields!Used.Value)&lt;0,"-",""))</Value>
                                  <Style>
                                    <FontSize>6pt</FontSize>
                                    <Format> ; ; </Format>
                                  </Style>
                                </TextRun>
                              </TextRuns>
                              <Style>
                                <TextAlign>Center</TextAlign>
                              </Style>
                            </Paragraph>
                          </Paragraphs>
                          <rd:DefaultName>Used</rd:DefaultName>
                          <Style>
                            <Border>
                              <Style>Solid</Style>
                            </Border>
                            <BackgroundColor>=IIF(Fields!Used.Value&gt;0,"Gold",IIF(Sum(Fields!Used.Value)&lt;0,"Red","White"))</BackgroundColor>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                            <PaddingBottom>2pt</PaddingBottom>
                          </Style>
                        </Textbox>
                      </CellContents>
                      <DataElementOutput>Output</DataElementOutput>
                    </TablixCell>
                  </TablixCells>
                </TablixRow>
              </TablixRows>
            </TablixBody>
            <TablixColumnHierarchy>
              <TablixMembers>
                <TablixMember>
                  <Group Name="matrix1_CostCenter">
                    <GroupExpressions>
                      <GroupExpression>=Fields!AccountingDimension.Value</GroupExpression>
                    </GroupExpressions>
                  </Group>
                  <TablixHeader>
                    <Size>0.5cm</Size>
                    <CellContents>
                      <Textbox Name="AccountingDimension">
                        <CanGrow>true</CanGrow>
                        <KeepTogether>true</KeepTogether>
                        <Paragraphs>
                          <Paragraph>
                            <TextRuns>
                              <TextRun>
                                <Value>=Fields!AccountingDimension.Value</Value>
                                <Style>
                                  <FontSize>8pt</FontSize>
                                </Style>
                              </TextRun>
                            </TextRuns>
                            <Style />
                          </Paragraph>
                        </Paragraphs>
                        <ZIndex>2</ZIndex>
                        <Style>
                          <Border>
                            <Style>Solid</Style>
                          </Border>
                          <BackgroundColor>White</BackgroundColor>
                          <VerticalAlign>Middle</VerticalAlign>
                          <PaddingLeft>2pt</PaddingLeft>
                          <PaddingRight>2pt</PaddingRight>
                          <PaddingTop>2pt</PaddingTop>
                          <PaddingBottom>2pt</PaddingBottom>
                          <WritingMode>Vertical</WritingMode>
                        </Style>
                      </Textbox>
                    </CellContents>
                  </TablixHeader>
                  <DataElementOutput>Output</DataElementOutput>
                  <KeepTogether>true</KeepTogether>
                </TablixMember>
              </TablixMembers>
            </TablixColumnHierarchy>
            <TablixRowHierarchy>
              <TablixMembers>
                <TablixMember>
                  <Group Name="matrix1_AccountNumber">
                    <GroupExpressions>
                      <GroupExpression>=Fields!AccountNumber.Value</GroupExpression>
                    </GroupExpressions>
                  </Group>
                  <TablixHeader>
                    <Size>4cm</Size>
                    <CellContents>
                      <Textbox Name="AccountNumber">
                        <CanGrow>true</CanGrow>
                        <KeepTogether>true</KeepTogether>
                        <Paragraphs>
                          <Paragraph>
                            <TextRuns>
                              <TextRun>
                                <Value>=Fields!AccountNumber.Value</Value>
                                <Style>
                                  <FontSize>6pt</FontSize>
                                </Style>
                              </TextRun>
                            </TextRuns>
                            <Style />
                          </Paragraph>
                        </Paragraphs>
                        <rd:DefaultName>AccountNumber</rd:DefaultName>
                        <ZIndex>1</ZIndex>
                        <Style>
                          <Border>
                            <Style>Solid</Style>
                          </Border>
                          <PaddingLeft>2pt</PaddingLeft>
                          <PaddingRight>2pt</PaddingRight>
                          <PaddingTop>2pt</PaddingTop>
                          <PaddingBottom>2pt</PaddingBottom>
                        </Style>
                      </Textbox>
                    </CellContents>
                  </TablixHeader>
                  <DataElementOutput>Output</DataElementOutput>
                  <KeepTogether>true</KeepTogether>
                </TablixMember>
              </TablixMembers>
            </TablixRowHierarchy>
            <RepeatColumnHeaders>true</RepeatColumnHeaders>
            <RepeatRowHeaders>true</RepeatRowHeaders>
            <DataSetName>DS_Valid_Coding_Combinations</DataSetName>
            <Top>0.75cm</Top>
            <Height>0.8cm</Height>
            <Width>4.4cm</Width>
            <Style />
          </Tablix>
          <Textbox Name="textbox2">
            <CanGrow>true</CanGrow>
            <KeepTogether>true</KeepTogether>
            <Paragraphs>
              <Paragraph>
                <TextRuns>
                  <TextRun>
                    <Value>Visualization of Existing Coding Combinations</Value>
                    <Style>
                      <FontStyle>Italic</FontStyle>
                      <FontSize>16pt</FontSize>
                      <FontWeight>Bold</FontWeight>
                    </Style>
                  </TextRun>
                </TextRuns>
                <Style />
              </Paragraph>
            </Paragraphs>
            <rd:DefaultName>textbox2</rd:DefaultName>
            <Height>0.75cm</Height>
            <Width>19cm</Width>
            <ZIndex>1</ZIndex>
            <Style>
              <PaddingLeft>2pt</PaddingLeft>
              <PaddingRight>2pt</PaddingRight>
              <PaddingTop>2pt</PaddingTop>
              <PaddingBottom>2pt</PaddingBottom>
            </Style>
          </Textbox>
          <Textbox Name="Copyrights">
            <CanGrow>true</CanGrow>
            <KeepTogether>true</KeepTogether>
            <Paragraphs>
              <Paragraph>
                <TextRuns>
                  <TextRun>
                    <Value EvaluationMode="Constant">Designed by Alexey Vasilyev / Автор отчёта: Алексей Васильев © 2018</Value>
                    <Style>
                      <FontSize>6pt</FontSize>
                      <TextDecoration>Underline</TextDecoration>
                      <Color>DarkBlue</Color>
                    </Style>
                  </TextRun>
                </TextRuns>
                <Style>
                  <TextAlign>Left</TextAlign>
                </Style>
              </Paragraph>
            </Paragraphs>
            <ActionInfo>
              <Actions>
                <Action>
                  <Hyperlink>/module/ssrs/</Hyperlink>
                </Action>
              </Actions>
            </ActionInfo>
            <Top>1.89653cm</Top>
            <Height>0.346cm</Height>
            <Width>19cm</Width>
            <ZIndex>2</ZIndex>
            <ToolTip>Click to learn more / Щелкните, чтобы узнать больше</ToolTip>
            <Style>
              <Border>
                <Style>None</Style>
              </Border>
              <PaddingLeft>2pt</PaddingLeft>
              <PaddingRight>2pt</PaddingRight>
              <PaddingTop>2pt</PaddingTop>
              <PaddingBottom>2pt</PaddingBottom>
            </Style>
          </Textbox>
        </ReportItems>
        <Height>2.24253cm</Height>
        <Style />
      </Body>
      <Width>19cm</Width>
      <Page>
        <PageHeight>29.7cm</PageHeight>
        <PageWidth>21cm</PageWidth>
        <LeftMargin>2.5cm</LeftMargin>
        <RightMargin>2.5cm</RightMargin>
        <TopMargin>2.5cm</TopMargin>
        <BottomMargin>2.5cm</BottomMargin>
        <ColumnSpacing>1cm</ColumnSpacing>
        <Style />
      </Page>
    </ReportSection>
  </ReportSections>
  <ReportParameters>
    <ReportParameter Name="CC">
      <DataType>String</DataType>
      <Prompt>Company</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>Companies</DataSetName>
          <ValueField>CC</ValueField>
          <LabelField>CName</LabelField>
        </DataSetReference>
      </ValidValues>
    </ReportParameter>
    <ReportParameter Name="YY">
      <DataType>String</DataType>
      <Prompt>Financial Year</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>Years</DataSetName>
          <ValueField>YY</ValueField>
          <LabelField>YYLabel</LabelField>
        </DataSetReference>
      </ValidValues>
    </ReportParameter>
    <ReportParameter Name="Dim">
      <DataType>String</DataType>
      <Prompt>Accounting Dimension</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>Segments</DataSetName>
          <ValueField>Segment</ValueField>
          <LabelField>Name</LabelField>
        </DataSetReference>
      </ValidValues>
    </ReportParameter>
  </ReportParameters>
  <Language>en-US</Language>
  <ConsumeContainerWhitespace>true</ConsumeContainerWhitespace>
  <rd:ReportUnitType>Cm</rd:ReportUnitType>
  <rd:ReportID>6072e7af-caf6-4a1c-9cfc-a9bca27705ba</rd:ReportID>
</Report>

Подробно по шагам с картинками:

Вставим код отчёта в блокнот:

Сохраним как файл с расширением .rdl

Имя файла: с расширением .rdl (Report Definition Language — Язык определения отчёта)

Тип файла: *.* (не .txt!!!)

Кодировка: Юникод

Теперь всё готово к публикации отчёта, но сначала я советую создать общий источник данных, если он у Вас ещё не создан.

После создания источника данных опубликуйте отчёт на сервере отчётов

Не забудьте дать права на папку с отчётом для соответствующей группы пользователей

При подготовке данного материала никто не пострадал, кроме нескольких часов моего времени 🙂 Очень надеюсь, что у Вас всё получится и данный отчёт Вам пригодится. Постараюсь опубликовать и другие общеупотребимые отчёты, со временем 🙂