Modyfikacje raportów

<Argument>
<Type>List</Type>
<Name>DocumentType</Name>
<VisibleName>Licz dla</VisibleName>
<DefaultSql>
<![CDATA[
DECLARE @docTypeId INT
SET @docTypeId = (SELECT COALESCE(
(
SELECT TOP 1 CAST(ruav.ArgumentValue AS nvarchar(max))
FROM esm.ReportsUserArguments ruav WHERE ruav.UserName = @LoginName AND ruav.ReportFileName = @ReportFileName AND ruav.ArgumentName = 'DocumentType'
)
, ( SELECT 1)
))
			  
SELECT * FROM (
SELECT 0 as Id, 'Zamówień' as Name
UNION ALL
SELECT 1, 'Ofert'
UNION ALL
SELECT 2, 'Faktur'
 ) p WHERE p.Id = @docTypeId 
]]>
</DefaultSql>
<ListSql>
<![CDATA[
SELECT 0, 'Zamówień'
UNION ALL
SELECT 1, 'Ofert'
UNION ALL
SELECT 2, 'Faktur'
]]>
</ListSql>
</Argument>

<Argument>
<Type>Date</Type>
<Name>DateFrom</Name>
<VisibleName>Data początkowa</VisibleName>
<DefaultSql>
<![CDATA[
SELECT COALESCE(
(
SELECT TOP 1 CAST(ruav.ArgumentValue AS DATETIME)
FROM esm.ReportsUserArguments ruav WHERE ruav.UserName = @LoginName AND ruav.ReportFileName = @ReportFileName AND ruav.ArgumentName = 'DateFrom'
)
, ( SELECT esm.GetDateFromParts ( YEAR(GETDATE()), 1, 1 ) )
)
]]>
</DefaultSql>
<ListSql></ListSql>
</Argument>

<Argument>
<Type>Text</Type>
<Name>ArticleCode</Name>
<VisibleName>Kod artykułu</VisibleName>
       <DefaultSql><![CDATA[SELECT ' ']]></DefaultSql>
</Argument>

<LayoutRow>
<SpaceColumn width="10"/>
<SpaceColumn width="5"/>
<SpaceColumn width="10"/>
<SpaceColumn width="5"/>
</LayoutRow>

<HeaderRow>
<TextColumn>Rejon</TextColumn>
<TextColumn>Plan</TextColumn>
<TextColumn precision="2">Realizacja</TextColumn>
<TextColumn precision="2">%</TextColumn>
</HeaderRow>

<Element>
<Id>31</Id>
<Header>Realizacja w podziale na towary</Header>
<Type>Table</Type>
<Sql>
<![CDATA[
DECLARE @OperatorId int
SELECT 
@OperatorId = vad.OperatorId
FROM esm.vAuthData vad
WHERE LOWER(vad.[Login]) = LOWER(@LoginName)
declare @Articles table 
(
[Acronym] nvarchar(max),
[Name] nvarchar(max),
[Group] nvarchar(max),
[Quantity] numeric(12,4),
[Value] numeric(12,2),
[Progress] numeric(12,2)
)
insert into @Articles([Acronym], [Name], [Group], [Quantity], [Value], [Progress])
exec esm.[Reports_GetSalePlansTable1_pol] @OperatorId, @SalesPlan, 1, @DocumentType
select
ISNULL([Acronym], '') as [Acronym],
ISNULL([Name], '') as [Name],
ISNULL([Group], '') as [Group],
[Quantity],
[Value],
[Progress]
from @Articles
]]>
</Sql>
<IsExecute>
<![CDATA[ SELECT CASE WHEN @RealizationType = 1 THEN 1 ELSE 0 END ]]>
</IsExecute>
</Element>

<TotalSummaryColumns>
<TextColumn align="center">Podsumowanie</TextColumn>
<TextColumn></TextColumn>
<SqlColumn align="center" precision="2">TotalOrdersNetValue</SqlColumn>
<SqlColumn align="center" precision="2">TotalOrdersGrossValue</SqlColumn>
<SqlColumn align="center">TotalOrdersNumber</SqlColumn>
</TotalSummaryColumns>

<Element>
<Id>1</Id>
<HeaderSql>
<![CDATA[
SELECT 'Wartość ' + (
CASE 
WHEN @DocumentType = 0 THEN 'zamówień'
WHEN @DocumentType = 1 THEN 'ofert'
WHEN @DocumentType = 2 THEN 'faktur'
ELSE 'dokumentów'
END
) + ' za okres od ' + CAST(CAST(@DateFrom AS DATE) AS NVARCHAR(MAX)) + ' do ' + CAST(CAST(@DateTo AS DATE) AS NVARCHAR(MAX)) ]]></HeaderSql>
<!--<Type>MultiLevelTable</Type>-->
<Type>PivotTable</Type>
<Sql>
<![CDATA[
exec [esm].[Reports_pGetOrdersStatsBySubRegions_plPL] 2, @LoginName, @DateFrom, @DateTo, @DocumentType, @SessionId
]]>
</Sql>
<PivotTable showTotalSummary="true" visibleRows="8">
<RowTemplate>
<ColumnTemplate width="1" name="Rejon nadrzędny" sqlColumn="Region" showSummary="true" align="left"/>
<ColumnTemplate width="1" name="Rejon podrzędny" sqlColumn="SubRegion" showSummary="true" align="left"/>
<ColumnTemplate width="1" name="Pracownik" sqlColumn="Employee" showSummary="true" align="left"/>
<ColumnTemplate 
width="1" 
name="Wartość netto (PLN)" 
nameSql="
DECLARE @CurrencyCode nvarchar(max)
SET @CurrencyCode =
(
SELECT
RTRIM(WaN_Symbol) AS [Value]
FROM CDN.WalNag
LEFT JOIN CDN.Konfig ON Kon_Numer=211 AND RTRIM(WaN_Symbol)=RTRIM(Kon_Wartosc)
WHERE Kon_System = 1
)
				
SELECT 'Wartość netto (' + @CurrencyCode + ')'
"
groupingType="Sum" 
sqlColumn="OrderNetValue" 
align="right"
sortOrder="desc"
precision="2" />
<ColumnTemplate 
width="1" 
name="Wartość brutto (PLN)" 
nameSql="
DECLARE @CurrencyCode nvarchar(max)
SET @CurrencyCode =
(
SELECT
RTRIM(WaN_Symbol) AS [Value]
FROM CDN.WalNag
LEFT JOIN CDN.Konfig ON Kon_Numer=211 AND RTRIM(WaN_Symbol)=RTRIM(Kon_Wartosc)
WHERE Kon_System = 1
)
				
SELECT 'Wartość brutto (' + @CurrencyCode + ')'
"
groupingType="Sum" 
sqlColumn="OrderGrossValue" 
align="right"
precision="2" />
<ColumnTemplate 
width="1" 
name="Ilość" 
groupingType="Sum" 
sqlColumn="it" 
align="right" />
</RowTemplate>
</PivotTable>
</Element>

<Element>
<Id>1</Id>
<HeaderSql>...</HeaderSql>
<Type>Line</Type>
<Sql>...</Sql>
<ChartColumns>
<LabelsSqlColumn>Date</LabelsSqlColumn>
<SeriesSqlColumn>LoginName</SeriesSqlColumn>
<ValuesSqlColumn>DispositionsCount</ValuesSqlColumn>
</ChartColumns>
</Element>

<VerticalLayout width="1" height="1">
<Element cssclass="tab" height="1" width="10">1</Element>
<Horizontal height="5" width="10">
<Element cssclass="pie" height="5" width="5">3</Element>
<Element cssclass="bar" height="5" width="5">2</Element>
</Horizontal>
</VerticalLayout>

<Css>
.avg-perf { color: white; font-weight: bold; height: 100%; }
.avg-perf-value { font-size: 5em; }
.avg-perf-title { font-size: 2em; }
.customHeaderColumn {height: 35px;}
.average-performance-element>.report-element-content { background-color: #a8c6d5; padding: 5px 50px 15px 50px; }
.weekend-day { color: red; }
</Css>