ZEN Report Calculation with Aggregate Value
53 views
Skip to first unread message
Ryan McCormick
Jul 9, 2010, 10:55:30 AM7/9/10
to InterSystems: Zen Community
I need to find a way to do calculations on aggregate values in a ZEN
report. Say you have an aggregate to sum 2009 values and an agregate
to sum 2010 values and you need to find the change between the two.
How would you do that? You have two fields being returned from your
SQL. Year and Value. To make a 2009 column you create an aggregate
something like:
<aggregate name="y2009" type="SUM" field="Value" fields="Year,Value"
accumIf='%val(""Year"")=""2009""'/>
Then to make a 2010 column you create an aggregate something like:
<aggregate name="y2010" type="SUM" field="Value" fields="Year,Value"
accumIf='%val(""Year"")=""2010""'/>
Then how can we perform calculations using these two aggregates? Find
the difference, find the % of one year to the next 2009/2010*100? I
know the calculations that I want to perform, just not how to use the
aggregate values in them. Any ideas?
report. Say you have an aggregate to sum 2009 values and an agregate
to sum 2010 values and you need to find the change between the two.
How would you do that? You have two fields being returned from your
SQL. Year and Value. To make a 2009 column you create an aggregate
something like:
<aggregate name="y2009" type="SUM" field="Value" fields="Year,Value"
accumIf='%val(""Year"")=""2009""'/>
Then to make a 2010 column you create an aggregate something like:
<aggregate name="y2010" type="SUM" field="Value" fields="Year,Value"
accumIf='%val(""Year"")=""2010""'/>
Then how can we perform calculations using these two aggregates? Find
the difference, find the % of one year to the next 2009/2010*100? I
know the calculations that I want to perform, just not how to use the
aggregate values in them. Any ideas?
Vlado
Jul 9, 2010, 2:06:41 PM7/9/10
to InterSystems: Zen Community
I have one report which, I think is something similarity.
It makes calculation between 2 Sums:
...... A1
...... A2
....
....... AN
------------
SUM1(SumIzdeliq)(y2009)
--------B1
........B2
........
........BM
-------------
SUM2(SumAmbalaz)(y2010)
-------------
SUM(=SUM1+SUM2)(SumNEK)(calculations between sums)
----------------------------------------------------------------------------------------------------
<aggregate type="CUSTOM" class="Izdeliq.NEKAmbReport.FormulaSum"
name='sumNEK' fields='StojnostIzdeliq,StojnostAmbalaz' />
===================================================
Class Izdeliq.NEKAmbReport.FormulaSum Extends
%ZEN.Report.CustomAggregate
{
Property sumNEK As %String [ InitialExpression = 0 ];
Method GetResult() As %String
{
s ..sumNEK=$G(%session.Data("StojnostIzdeliq"))+
$G(%session.Data("StojnostAmbalaz"))
Quit ..sumNEK
}
Method ProcessValue(pValue As %String) As %Status
{
s %session.Data("StojnostIzdeliq")=
$G(%session.Data("StojnostIzdeliq"))+%val("StojnostIzdeliq")
s %session.Data("StojnostAmbalaz")=
$G(%session.Data("StojnostAmbalaz"))+%val("StojnostAmbalaz")
Quit $$$OK
}
}
===================================================
In Method GetResult() you have to do your calculations.
Note, that I use Field Names. I think it will not work if in an
aggregate
you use aggregate names for calculations.
(aggregate name="CALC" type="Custom" fields="y2009,y2010" )
It makes calculation between 2 Sums:
...... A1
...... A2
....
....... AN
------------
SUM1(SumIzdeliq)(y2009)
--------B1
........B2
........
........BM
-------------
SUM2(SumAmbalaz)(y2010)
-------------
SUM(=SUM1+SUM2)(SumNEK)(calculations between sums)
----------------------------------------------------------------------------------------------------
<aggregate type="CUSTOM" class="Izdeliq.NEKAmbReport.FormulaSum"
name='sumNEK' fields='StojnostIzdeliq,StojnostAmbalaz' />
===================================================
Class Izdeliq.NEKAmbReport.FormulaSum Extends
%ZEN.Report.CustomAggregate
{
Property sumNEK As %String [ InitialExpression = 0 ];
Method GetResult() As %String
{
s ..sumNEK=$G(%session.Data("StojnostIzdeliq"))+
$G(%session.Data("StojnostAmbalaz"))
Quit ..sumNEK
}
Method ProcessValue(pValue As %String) As %Status
{
s %session.Data("StojnostIzdeliq")=
$G(%session.Data("StojnostIzdeliq"))+%val("StojnostIzdeliq")
s %session.Data("StojnostAmbalaz")=
$G(%session.Data("StojnostAmbalaz"))+%val("StojnostAmbalaz")
Quit $$$OK
}
}
===================================================
In Method GetResult() you have to do your calculations.
Note, that I use Field Names. I think it will not work if in an
aggregate
you use aggregate names for calculations.
(aggregate name="CALC" type="Custom" fields="y2009,y2010" )
Ryan McCormick
Jul 9, 2010, 2:59:24 PM7/9/10
to InterSystems: Zen Community
I'm a step closer I think, but still need help. Say you have the data
grouped by region or something like that and the XML looks something
like:
<Report>
<Region name="North">
<y2009>100</y2009>
<y2010>150</y2010>
</Region>
<Region name="South">
<y2009>200</y2009>
<y2010>300</y2010>
</Region>
<y2009>300</y2009>
<y2010>450</y2010>
</Report>
So you have the aggregates mentioned originally at the top level of
your report definition and you have a group named region with the
aggregates in that group as well. I read and mangled some code from
the documentation to create an XData block for XSLT transformation
like this:
XData AllXslt
{
<xsl:template name="difference">
<xsl:param name="y2009"/>
<xsl:param name="y2010"/>
<xsl:variable name="v1" select="$y2009[1]"/>
<xsl:variable name="v2" select="$y2010[1]"/>
<xsl:value-of select="format-number(($v2 - $v1),'##,##0)"/>
</xsl:template>
}
Then in order to use it you have to create a line like this:
<item call="difference" params="/Report/Region/y2009;/Report/Region/
y2010" paramNames="y2009;y2010"/>
The line above works fine for the first record in the Region group,
but I think because if the "$y2009[1]" part it is only using the first
record and not iterating through all of the records. It also work
fine outside of the group, because it is like a grand total and there
is only one y2009 and one y2010 value. So even though y2009 and y2010
should be passing new values for each record in the Region group (and
they may be) I'm not sure how to iterate through those values in the
xsl template. So my report comes out looking like:
North
2009 100
2010 150
Difference 50
South
2009 200
2010 300
Difference 50
2009 300
2010 450
Difference 150
So it calculates the value for the North region correctly, but uses
the same values from the North region when it should be displaying the
South region's values. Any clues, insights, suggestions?
grouped by region or something like that and the XML looks something
like:
<Report>
<Region name="North">
<y2009>100</y2009>
<y2010>150</y2010>
</Region>
<Region name="South">
<y2009>200</y2009>
<y2010>300</y2010>
</Region>
<y2009>300</y2009>
<y2010>450</y2010>
</Report>
So you have the aggregates mentioned originally at the top level of
your report definition and you have a group named region with the
aggregates in that group as well. I read and mangled some code from
the documentation to create an XData block for XSLT transformation
like this:
XData AllXslt
{
<xsl:template name="difference">
<xsl:param name="y2009"/>
<xsl:param name="y2010"/>
<xsl:variable name="v1" select="$y2009[1]"/>
<xsl:variable name="v2" select="$y2010[1]"/>
<xsl:value-of select="format-number(($v2 - $v1),'##,##0)"/>
</xsl:template>
}
Then in order to use it you have to create a line like this:
<item call="difference" params="/Report/Region/y2009;/Report/Region/
y2010" paramNames="y2009;y2010"/>
The line above works fine for the first record in the Region group,
but I think because if the "$y2009[1]" part it is only using the first
record and not iterating through all of the records. It also work
fine outside of the group, because it is like a grand total and there
is only one y2009 and one y2010 value. So even though y2009 and y2010
should be passing new values for each record in the Region group (and
they may be) I'm not sure how to iterate through those values in the
xsl template. So my report comes out looking like:
North
2009 100
2010 150
Difference 50
South
2009 200
2010 300
Difference 50
2009 300
2010 450
Difference 150
So it calculates the value for the North region correctly, but uses
the same values from the North region when it should be displaying the
South region's values. Any clues, insights, suggestions?
Ryan McCormick
Jul 9, 2010, 3:23:38 PM7/9/10
to InterSystems: Zen Community
Thank you for your response Vlado. I have tried the custom aggregate
route, but like you said it will not work if you are using aggregate
names as fields. In the custom aggregate code the
%val("YourAggregateHere") only shows <EMPTY> and no value so I am not
sure what I can do.
On Jul 9, 2:06 pm, Vlado <viliyc...@earthlink.net> wrote:
> I have one report which, I think is something similarity.
> It makes calculation between 2 Sums:
> ...... A1
> ...... A2
> ....
> ....... AN
> ------------
> SUM1(SumIzdeliq)(y2009)
> --------B1
> ........B2
> ........
> ........BM
> -------------
> SUM2(SumAmbalaz)(y2010)
> -------------
> SUM(=SUM1+SUM2)(SumNEK)(calculations between sums)
> ----------------------------------------------------------------------------------------------------
> > aggregate values in them. Any ideas?- Hide quoted text -
>
> - Show quoted text -
route, but like you said it will not work if you are using aggregate
names as fields. In the custom aggregate code the
%val("YourAggregateHere") only shows <EMPTY> and no value so I am not
sure what I can do.
On Jul 9, 2:06 pm, Vlado <viliyc...@earthlink.net> wrote:
> I have one report which, I think is something similarity.
> It makes calculation between 2 Sums:
> ...... A1
> ...... A2
> ....
> ....... AN
> ------------
> SUM1(SumIzdeliq)(y2009)
> --------B1
> ........B2
> ........
> ........BM
> -------------
> SUM2(SumAmbalaz)(y2010)
> -------------
> SUM(=SUM1+SUM2)(SumNEK)(calculations between sums)
>
> - Show quoted text -
Vlado
Jul 9, 2010, 4:35:59 PM7/9/10
to InterSystems: Zen Community
Ryan,
If you want to go this way, include
one more aggreagate:
<aggregate type="CUSTOM" class="XXX.XXX"
name='Difference' fields='Year,Value' />
And then in the custom aggregate try something like:
==========================================
Property Difference As %String [ InitialExpression = 0 ];
Method GetResult() As %String
{
s ..Difference=$G(%session.Data("Value2009"))-
$G(%session.Data("Value2010"))
Quit ..Difference
$G(%session.Data("Value2009"))+%val("Value")
If %val("Year")=2010 s %session.Data("Value2010")=
$G(%session.Data("Value2010"))+%val("Value")
Quit $$$OK
}
==================================================
If you want to go the other way see what will happend if
you make the exaple similar to this template:
==================================================
XData AllXslt
{
<xsl:template name="sum">
<!-- Initialize nodes to empty node set -->
<xsl:param name="nodes"/>
<xsl:param name="result" select="0"/>
<xsl:choose>
<xsl:when test="not($nodes)">
<xsl:value-of select="$result"/>
</xsl:when>
<xsl:otherwise>
<xsl:variable name="value" select="$nodes[1]"/>
<!-- recursively sum the rest of the nodes -->
<xsl:call-template name="sum">
<xsl:with-param name="nodes" select="$nodes[position( ) != 1]"/
>
<xsl:with-param name="result" select="$result + $value"/>
</xsl:call-template>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
}
====================================================
> > - Show quoted text -- Hide quoted text -
If you want to go this way, include
one more aggreagate:
<aggregate type="CUSTOM" class="XXX.XXX"
name='Difference' fields='Year,Value' />
And then in the custom aggregate try something like:
==========================================
Property Difference As %String [ InitialExpression = 0 ];
Method GetResult() As %String
{
s ..Difference=$G(%session.Data("Value2009"))-
$G(%session.Data("Value2010"))
Quit ..Difference
}
Method ProcessValue(pValue As %String) As %Status
{
If %val("Year")=2009 s %session.Data("Value2009")=
Method ProcessValue(pValue As %String) As %Status
{
$G(%session.Data("Value2009"))+%val("Value")
If %val("Year")=2010 s %session.Data("Value2010")=
$G(%session.Data("Value2010"))+%val("Value")
Quit $$$OK
}
==================================================
If you want to go the other way see what will happend if
you make the exaple similar to this template:
==================================================
XData AllXslt
{
<xsl:template name="sum">
<!-- Initialize nodes to empty node set -->
<xsl:param name="nodes"/>
<xsl:param name="result" select="0"/>
<xsl:choose>
<xsl:when test="not($nodes)">
<xsl:value-of select="$result"/>
</xsl:when>
<xsl:otherwise>
<xsl:variable name="value" select="$nodes[1]"/>
<!-- recursively sum the rest of the nodes -->
<xsl:call-template name="sum">
<xsl:with-param name="nodes" select="$nodes[position( ) != 1]"/
>
<xsl:with-param name="result" select="$result + $value"/>
</xsl:call-template>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
}
====================================================
Ryan McCormick
Jul 9, 2010, 5:23:27 PM7/9/10
to InterSystems: Zen Community
Well, the answer that I came up with is that you can't. Unless
someone responds to this thread later with a cool way to make it work,
I just wasn't able to. What I did was use your solution Vlado, but I
wasn't able to use the results of the first two aggregates in the
custom aggregate. So I had to write the same logic into the custom
aggregate that broke up the values into the groups that I needed and
store those values as user session data like in your example. I have
to have a custom aggregate for each of the groups that I want to use
this way but it is giving me accurate results now. Thank you vary
much for your help.
Ryan McCormick
someone responds to this thread later with a cool way to make it work,
I just wasn't able to. What I did was use your solution Vlado, but I
wasn't able to use the results of the first two aggregates in the
custom aggregate. So I had to write the same logic into the custom
aggregate that broke up the values into the groups that I needed and
store those values as user session data like in your example. I have
to have a custom aggregate for each of the groups that I want to use
this way but it is giving me accurate results now. Thank you vary
much for your help.
Ryan McCormick
> I have one report which, I think is something similarity.
> It makes calculation between 2 Sums:
> ...... A1
> ...... A2
> ....
> ....... AN
> ------------
> SUM1(SumIzdeliq)(y2009)
> --------B1
> ........B2
> ........
> ........BM
> -------------
> SUM2(SumAmbalaz)(y2010)
> -------------
> SUM(=SUM1+SUM2)(SumNEK)(calculations between sums)
> ----------------------------------------------------------------------------------------------------
> It makes calculation between 2 Sums:
> ...... A1
> ...... A2
> ....
> ....... AN
> ------------
> SUM1(SumIzdeliq)(y2009)
> --------B1
> ........B2
> ........
> ........BM
> -------------
> SUM2(SumAmbalaz)(y2010)
> -------------
> SUM(=SUM1+SUM2)(SumNEK)(calculations between sums)
Vlado
Jul 9, 2010, 5:41:15 PM7/9/10
to InterSystems: Zen Community
Here is my Zen report, if it can help you something,
but it uses relationships Parent->Child->Child:
=================================================
/// Izdeliq.NEKReport
Class Izdeliq.NEKAmbReport Extends %ZEN.Report.reportPage
{
/// Class name of application this report belongs to.
Parameter APPLICATION = "Izdeliq.Application";
/// This is the default display mode for this report.
Parameter DEFAULTMODE = "html";
/// This is the optional XML namespace used for the report.
Parameter REPORTXMLNAMESPACE;
/// This is the default display mode for this report.
Parameter XSLTMODE;
Property NomerNEK As %ZEN.Datatype.string(ZENURL = "ID");
/// This XML defines the logical contents of this report.
XData ReportDefinition [ XMLNamespace = "http://www.intersystems.com/
zen/report/definition" ]
{
<report xmlns="http://www.intersystems.com/zen/report/definition"
name='myReport'
sql="SELECT ID,Sklad,NomerNEK,DataNEK FROM Izdeliq.NEKAmb WHERE
(NomerNEK = ?)
order by NomerNEK">
<parameter expression='$p(..NomerNEK,"|",1)'/>
<group name='NomerNEK' breakOnField="NomerNEK"
sql="Select IzdAmb,ID,IzdeliqAmbalaz,Izdeliq,Ambalaz from
Izdeliq.NEKIzdAmb
WHERE (IzdAmb=?)AND(IzdeliqAmbalaz=0)">
<parameter field="ID"/>
<attribute name="NomerNEK" field="NomerNEK"
expression="..KillSession(%val)"/>
<attribute name='DataNEK' field='DataNEK' />
<attribute name="Sklad" field="Sklad" expression="..GetSklad()" />
<group name="Izdeliq" breakOnField='Izdeliq'
sql="SELECT
Id,NomerIzdeliq,NameIzdeliq,MqrkaIzdeliq,CenaIzdeliq,KolichestvoIzdeliq,StojnostIzdeliq
FROM Izdeliq.NEKAmbItemsIzdeliq where (IzdAmbIzdeliq=?)" >
<parameter field="ID"/>
<attribute name='Izdeliq' field='Izdeliq' />
<aggregate name='sumIzdeliq' type="SUM" field='StojnostIzdeliq'/>
<attribute name='NomerIzdeliq' field='NomerIzdeliq' />
<attribute name='NameIzdeliq' field='NameIzdeliq' />
<attribute name='MqrkaIzdeliq' field='MqrkaIzdeliq'/>
<attribute name='CenaIzdeliq' field='CenaIzdeliq'/>
<attribute name='KolichestvoIzdeliq' field='KolichestvoIzdeliq' />
<attribute name='StojnostIzdeliq' field='StojnostIzdeliq' />
</group>
</group>
</group>
<group name='NomerNEK' breakOnField="NomerNEK"
sql="Select Id,IzdAmb,IzdeliqAmbalaz,Izdeliq,Ambalaz
from Izdeliq.NEKIzdAmb WHERE (IzdAmb=?)AND(IzdeliqAmbalaz=1)">
<parameter field="ID"/>
<group name="Ambalaz" breakOnField='Ambalaz'
sql="SELECT
ID,NomerAmbalaz,NameAmbalaz,MqrkaAmbalaz,CenaAmbalaz,KolichestvoAmbalaz,StojnostAmbalaz
FROM Izdeliq.NEKAmbItemsAmbalaz where (IzdAmbAmbalaz=?)">
<parameter field="ID"/>
<attribute name='Ambalaz' field='Ambalaz' />
<aggregate name='sumAmbalaz' type="SUM" field='StojnostAmbalaz'/>
<attribute name='NomerAmbalaz' field='NomerAmbalaz'/>
<attribute name='NameAmbalaz' field='NameAmbalaz'/>
<attribute name='MqrkaAmbalaz' field='MqrkaAmbalaz'/>
<attribute name='CenaAmbalaz' field='CenaAmbalaz'/>
<attribute name='KolichestvoAmbalaz' field='KolichestvoAmbalaz' />
<attribute name='StojnostAmbalaz' field='StojnostAmbalaz' />
</group>
</group>
</group>
</report>
}
/// XML description for the display of this report. Used to generate
/// XSlT stylesheets for both HTML and XSL-FO
XData ReportDisplay [ XMLNamespace = "http://www.intersystems.com/zen/
report/display" ]
{
<report xmlns="http://www.intersystems.com/zen/report/display"
name='myReport' title='НЕК' style='standard'>
<document width="8.5in" height="11in" marginLeft="1.25in"
marginRight="1.25in" marginTop="1.0in" marginBottom="1.0in"></
document>
<body>
<header><p class="banner1">Нареждане за експедиция</p></header>
<group name="NomerNEK">
<table orient="col" width="8.5in" class='table2'>
<item field="@NomerNEK" width="1in"><caption value="N: НЕК:"
ifxpath="@NomerNEK&gt;0" /></item>
<item field="@DataNEK" width="1in"><caption value="Дата"
ifxpath="@NomerNEK&gt;0"/></item>
<item field="@Sklad" width="3.5in"><caption value="Склад:"
ifxpath="@NomerNEK&gt;0"/></item>
</table>
<group name="Izdeliq">
<table orient="row" width="8.5in" class='table2'>
<item field="@Izdeliq" width="6in"><caption value="Вид:" width="0.5in"/
></item>
</table>
<table orient="col"
group="recordIzdeliq" width="8.5in" class="table4" altcolor="#DFDFFF">
<item special="number" width=".45in" caption="#" />
<item field="@NomerIzdeliq" width="1in" caption="Номер" />
<item field="@NameIzdeliq" width="4in" caption="Наименования изделия"/
>
<item field="@MqrkaIzdeliq" width="0.5in" caption="М."/>
<item field="@CenaIzdeliq" style="text-align:right;" width="1in"
formatNumber='###,###,##0.00;(#)' caption="Цена"/>
<item field="@KolichestvoIzdeliq" style="text-align:right;"
width="1in" formatNumber='###,###,##0.000;(#)' caption="Количество"/>
<item field="@StojnostIzdeliq" style="text-align:right;" width="1in"
formatNumber='###,###,##0.00;(#)' caption="Стойност"/>
</table>
<table orient="row" width="8.5in" class='table2'>
<item field="sumIzdeliq" style="font-weight:bold;text-align:right;"
width="6.5in" formatNumber='###,###,##0.00;(#)'>
<caption value="Всичко за изделия:" width="2in"/></item>
</table>
</group>
<group name="Ambalaz" >
<table orient="row" width="8.5in" class='table2'>
<item field="@Ambalaz" width="6in"><caption value="Вид:" width="0.5in"/
></item>
</table>
<table orient="col" group="recordAmbalaz" width="8.5in" class="table4"
altcolor="#DFDFFF">
<item special="number" width=".45in" caption="#" />
<item field="@NomerAmbalaz" width="1in" caption="Номер"/>
<item field="@NameAmbalaz" width="4in" caption="Наименования амбалаж"/
>
<item field="@MqrkaAmbalaz" width="0.5in" caption="М."/>
<item field="@CenaAmbalaz" style="text-align:right;" width="1in"
formatNumber='###,###,##0.00;(#)' caption="Цена"/>
<item field="@KolichestvoAmbalaz" style="text-align:right;"
width="1in" formatNumber='###,###,##0.000;(#)' caption="Количество"/>
<item field="@StojnostAmbalaz" style="text-align:right;" width="1in"
formatNumber='###,###,##0.00;(#)' caption="Стойност"/>
</table>
<table orient="row" width="8.5in" class='table2'>
<item field="sumAmbalaz" style="font-weight:bold;text-align:right;"
width="6.5in" formatNumber='###,###,##0.00;(#)'>
<caption value="Всичко за амбалаж:" width="2in"/></item>
</table>
<table orient="row" width="8.5in" class='table2'>
<item field="sumNEK" style="font-weight:bold;text-align:right;"
width="6.5in" formatNumber='###,###,##0.00;(#)'>
<caption value="Всичко за нареждане:" width="2in"/></item>
</table>
</group>
</group>
</body>
</report>
}
/// Return a formatted version of the month property.
Method GetSklad() As %String
{
Set tObj = ##class(Nomenklaturi.Skladove).%OpenId(%val)
If ($IsObject(tObj)) {
Set Nomer = tObj.Nomer
Set Name = tObj.Name
}
Quit Nomer_" "_Name
}
Method KillSession(%val As %String) As %Status
{
k %session.Data("StojnostIzdeliq")
k %session.Data("StojnostAmbalaz")
Quit $$$OK
}
}
================================================
> > - Show quoted text -- Hide quoted text -
but it uses relationships Parent->Child->Child:
=================================================
/// Izdeliq.NEKReport
Class Izdeliq.NEKAmbReport Extends %ZEN.Report.reportPage
{
/// Class name of application this report belongs to.
Parameter APPLICATION = "Izdeliq.Application";
/// This is the default display mode for this report.
Parameter DEFAULTMODE = "html";
/// This is the optional XML namespace used for the report.
Parameter REPORTXMLNAMESPACE;
/// This is the default display mode for this report.
Parameter XSLTMODE;
Property NomerNEK As %ZEN.Datatype.string(ZENURL = "ID");
/// This XML defines the logical contents of this report.
XData ReportDefinition [ XMLNamespace = "http://www.intersystems.com/
zen/report/definition" ]
{
<report xmlns="http://www.intersystems.com/zen/report/definition"
name='myReport'
sql="SELECT ID,Sklad,NomerNEK,DataNEK FROM Izdeliq.NEKAmb WHERE
(NomerNEK = ?)
order by NomerNEK">
<parameter expression='$p(..NomerNEK,"|",1)'/>
<group name='NomerNEK' breakOnField="NomerNEK"
sql="Select IzdAmb,ID,IzdeliqAmbalaz,Izdeliq,Ambalaz from
Izdeliq.NEKIzdAmb
WHERE (IzdAmb=?)AND(IzdeliqAmbalaz=0)">
<parameter field="ID"/>
<attribute name="NomerNEK" field="NomerNEK"
expression="..KillSession(%val)"/>
<attribute name='DataNEK' field='DataNEK' />
<attribute name="Sklad" field="Sklad" expression="..GetSklad()" />
<group name="Izdeliq" breakOnField='Izdeliq'
sql="SELECT
Id,NomerIzdeliq,NameIzdeliq,MqrkaIzdeliq,CenaIzdeliq,KolichestvoIzdeliq,StojnostIzdeliq
FROM Izdeliq.NEKAmbItemsIzdeliq where (IzdAmbIzdeliq=?)" >
<parameter field="ID"/>
<attribute name='Izdeliq' field='Izdeliq' />
<aggregate name='sumIzdeliq' type="SUM" field='StojnostIzdeliq'/>
<aggregate type="CUSTOM" class="Izdeliq.NEKAmbReport.FormulaSum"
name='sumNEK' fields='StojnostIzdeliq,StojnostAmbalaz' />
<group name="recordIzdeliq" >
name='sumNEK' fields='StojnostIzdeliq,StojnostAmbalaz' />
<attribute name='NomerIzdeliq' field='NomerIzdeliq' />
<attribute name='NameIzdeliq' field='NameIzdeliq' />
<attribute name='MqrkaIzdeliq' field='MqrkaIzdeliq'/>
<attribute name='CenaIzdeliq' field='CenaIzdeliq'/>
<attribute name='KolichestvoIzdeliq' field='KolichestvoIzdeliq' />
<attribute name='StojnostIzdeliq' field='StojnostIzdeliq' />
</group>
</group>
</group>
<group name='NomerNEK' breakOnField="NomerNEK"
sql="Select Id,IzdAmb,IzdeliqAmbalaz,Izdeliq,Ambalaz
from Izdeliq.NEKIzdAmb WHERE (IzdAmb=?)AND(IzdeliqAmbalaz=1)">
<parameter field="ID"/>
<group name="Ambalaz" breakOnField='Ambalaz'
sql="SELECT
ID,NomerAmbalaz,NameAmbalaz,MqrkaAmbalaz,CenaAmbalaz,KolichestvoAmbalaz,StojnostAmbalaz
FROM Izdeliq.NEKAmbItemsAmbalaz where (IzdAmbAmbalaz=?)">
<parameter field="ID"/>
<attribute name='Ambalaz' field='Ambalaz' />
<aggregate name='sumAmbalaz' type="SUM" field='StojnostAmbalaz'/>
<aggregate type="CUSTOM" class="Izdeliq.NEKAmbReport.FormulaSum"
name='sumNEK' fields='StojnostIzdeliq,StojnostAmbalaz' />
<group name="recordAmbalaz">
name='sumNEK' fields='StojnostIzdeliq,StojnostAmbalaz' />
<attribute name='NomerAmbalaz' field='NomerAmbalaz'/>
<attribute name='NameAmbalaz' field='NameAmbalaz'/>
<attribute name='MqrkaAmbalaz' field='MqrkaAmbalaz'/>
<attribute name='CenaAmbalaz' field='CenaAmbalaz'/>
<attribute name='KolichestvoAmbalaz' field='KolichestvoAmbalaz' />
<attribute name='StojnostAmbalaz' field='StojnostAmbalaz' />
</group>
</group>
</group>
</report>
}
/// XML description for the display of this report. Used to generate
/// XSlT stylesheets for both HTML and XSL-FO
XData ReportDisplay [ XMLNamespace = "http://www.intersystems.com/zen/
report/display" ]
{
<report xmlns="http://www.intersystems.com/zen/report/display"
name='myReport' title='НЕК' style='standard'>
<document width="8.5in" height="11in" marginLeft="1.25in"
marginRight="1.25in" marginTop="1.0in" marginBottom="1.0in"></
document>
<body>
<header><p class="banner1">Нареждане за експедиция</p></header>
<group name="NomerNEK">
<table orient="col" width="8.5in" class='table2'>
<item field="@NomerNEK" width="1in"><caption value="N: НЕК:"
ifxpath="@NomerNEK&gt;0" /></item>
<item field="@DataNEK" width="1in"><caption value="Дата"
ifxpath="@NomerNEK&gt;0"/></item>
<item field="@Sklad" width="3.5in"><caption value="Склад:"
ifxpath="@NomerNEK&gt;0"/></item>
</table>
<group name="Izdeliq">
<table orient="row" width="8.5in" class='table2'>
<item field="@Izdeliq" width="6in"><caption value="Вид:" width="0.5in"/
></item>
</table>
<table orient="col"
group="recordIzdeliq" width="8.5in" class="table4" altcolor="#DFDFFF">
<item special="number" width=".45in" caption="#" />
<item field="@NomerIzdeliq" width="1in" caption="Номер" />
<item field="@NameIzdeliq" width="4in" caption="Наименования изделия"/
>
<item field="@MqrkaIzdeliq" width="0.5in" caption="М."/>
<item field="@CenaIzdeliq" style="text-align:right;" width="1in"
formatNumber='###,###,##0.00;(#)' caption="Цена"/>
<item field="@KolichestvoIzdeliq" style="text-align:right;"
width="1in" formatNumber='###,###,##0.000;(#)' caption="Количество"/>
<item field="@StojnostIzdeliq" style="text-align:right;" width="1in"
formatNumber='###,###,##0.00;(#)' caption="Стойност"/>
</table>
<table orient="row" width="8.5in" class='table2'>
<item field="sumIzdeliq" style="font-weight:bold;text-align:right;"
width="6.5in" formatNumber='###,###,##0.00;(#)'>
<caption value="Всичко за изделия:" width="2in"/></item>
</table>
</group>
<group name="Ambalaz" >
<table orient="row" width="8.5in" class='table2'>
<item field="@Ambalaz" width="6in"><caption value="Вид:" width="0.5in"/
></item>
</table>
<table orient="col" group="recordAmbalaz" width="8.5in" class="table4"
altcolor="#DFDFFF">
<item special="number" width=".45in" caption="#" />
<item field="@NomerAmbalaz" width="1in" caption="Номер"/>
<item field="@NameAmbalaz" width="4in" caption="Наименования амбалаж"/
>
<item field="@MqrkaAmbalaz" width="0.5in" caption="М."/>
<item field="@CenaAmbalaz" style="text-align:right;" width="1in"
formatNumber='###,###,##0.00;(#)' caption="Цена"/>
<item field="@KolichestvoAmbalaz" style="text-align:right;"
width="1in" formatNumber='###,###,##0.000;(#)' caption="Количество"/>
<item field="@StojnostAmbalaz" style="text-align:right;" width="1in"
formatNumber='###,###,##0.00;(#)' caption="Стойност"/>
</table>
<table orient="row" width="8.5in" class='table2'>
<item field="sumAmbalaz" style="font-weight:bold;text-align:right;"
width="6.5in" formatNumber='###,###,##0.00;(#)'>
<caption value="Всичко за амбалаж:" width="2in"/></item>
</table>
<table orient="row" width="8.5in" class='table2'>
<item field="sumNEK" style="font-weight:bold;text-align:right;"
width="6.5in" formatNumber='###,###,##0.00;(#)'>
<caption value="Всичко за нареждане:" width="2in"/></item>
</table>
</group>
</group>
</body>
</report>
}
/// Return a formatted version of the month property.
Method GetSklad() As %String
{
Set tObj = ##class(Nomenklaturi.Skladove).%OpenId(%val)
If ($IsObject(tObj)) {
Set Nomer = tObj.Nomer
Set Name = tObj.Name
}
Quit Nomer_" "_Name
}
Method KillSession(%val As %String) As %Status
{
k %session.Data("StojnostIzdeliq")
k %session.Data("StojnostAmbalaz")
Quit $$$OK
}
}
================================================
Derek Day
Jul 9, 2010, 5:54:50 PM7/9/10
to intersys...@googlegroups.com
I believe that you may also use a Composite--the same way as a custom aggregate--giving you full control over the XML representation for specifying your calculation. I haven't done this for the Report Definition in recent memory, but I believe that it should be pretty simple. Vlado, have you use composites in the Report Definition and Display yet?
--
Derek Day
Senior Support Specialist
Product Specialist Group
World Wide Response Center (WRC)
InterSystems, Inc.
~Derek
--
You received this message because you are subscribed to the Google Groups "InterSystems: Zen Community" group.
To post to this group, send email to InterSys...@googlegroups.com
To unsubscribe from this group, send email to InterSystems-Z...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/InterSystems-ZEN?hl=en
Zen Community Terms and Conditions: http://groups.google.com/group/InterSystems-ZEN/web/community-terms-and-conditions
--
Derek Day
Senior Support Specialist
Product Specialist Group
World Wide Response Center (WRC)
InterSystems, Inc.
Vlado
Jul 9, 2010, 6:23:19 PM7/9/10
to InterSystems: Zen Community
Nope.
On Jul 9, 2:54 pm, Derek Day <derek....@intersystems.com> wrote:
> I believe that you may also use a Composite--the same way as a custom
> aggregate--giving you full control over the XML representation for
> specifying your calculation. I haven't done this for the Report Definition
> in recent memory, but I believe that it should be pretty simple. Vlado, have
> you use composites in the Report Definition and Display yet?
>
> ~Derek
>
> >http://groups.google.com/group/InterSystems-ZEN/web/community-terms-a...
On Jul 9, 2:54 pm, Derek Day <derek....@intersystems.com> wrote:
> I believe that you may also use a Composite--the same way as a custom
> aggregate--giving you full control over the XML representation for
> specifying your calculation. I haven't done this for the Report Definition
> in recent memory, but I believe that it should be pretty simple. Vlado, have
> you use composites in the Report Definition and Display yet?
>
> ~Derek
>
>
> --
> Derek Day
> Senior Support Specialist
> Product Specialist Group
> World Wide Response Center (WRC)
> InterSystems, Inc.- Hide quoted text -
> --
> Derek Day
> Senior Support Specialist
> Product Specialist Group
> World Wide Response Center (WRC)
Vlado
Jul 9, 2010, 6:49:22 PM7/9/10
to InterSystems: Zen Community
Ryan,
You can use Indices in %session.Data.
Something like:
If %val("Year")=2009 s %session.Data(%val("Region"),"Value2009")=
$G(%session.Data(%val("Region"),"Value2009"))+%val("Value")
or even
s %session.Data(%val("Region"),%val("Year"))=
$G(%session.Data(%val("Region"),%val("Year")))+%val("Value")
> > - Show quoted text -- Hide quoted text -
You can use Indices in %session.Data.
Something like:
<aggregate type="CUSTOM" class="XXX.XXX"
name='Difference' fields='Region,Year,Value' />
If %val("Year")=2009 s %session.Data(%val("Region"),"Value2009")=
$G(%session.Data(%val("Region"),"Value2009"))+%val("Value")
or even
s %session.Data(%val("Region"),%val("Year"))=
$G(%session.Data(%val("Region"),%val("Year")))+%val("Value")
Ryan McCormick
Jul 12, 2010, 11:23:11 AM7/12/10
to InterSystems: Zen Community
Thanks for the tip Vlado!
Reply all
Reply to author
Forward
0 new messages