Using footer on dashboard elements
 |
Case summary
If you have a lot of data present on table dashboard element, you can include footer on element and add calculations on it. For example, you can include summary calculation for individual row on table dashboard component.
- Create custom element
- Edit SQL statements
|
1. Create custom element
To include SUM calculation on tabele dashboard component, you have to edit SQL statements. As SQL statements cannot be edited on DL default elements, you have to either create your own element or copy exisitng DL default element.
2. Edit SQL statement
Footer is not visible by default on dashboard element. This means that footer won't interfere with exisiting dashboard or element. Footer will only appear on the elements that have defined SUM in it.
SUM is defined in element in it's caption. Below is a case of select statement as there are several options how to include SUM in caption.
select
case when isnull(ATI.acSubject, '') = '' then nde.acCaption else ATI.acSubject end as #$sqlSubject$#,
cast(sum(case when IsNull(ATI.adDateDue, Dateadd(day, -1, Getdate())) < GetDate() then (ATI.anCredit - ATI.anDebit) as 'SUM_#$sqlDue$#',
cast(sum((ATI.anCredit-ATI.anDebit) ) else case when ATR.addate <= A.LastDate then end else 1 end end) as decimal(15,2)) as SUM_Claim
from vHE_AcctTransItem ATI
2.1 Add SUM on custom caption
If you are creating element from custom SQL, you have to include caption on desired measurement. In our case above custom caption is Claim (as Claim). To calculate SUM for this value, you add SUM_ to caltion (correct caption would be SUM_Claim).
2.2 Add SUM on DL default caption (translatable caption)
You can also add SUM to value if caption is default (or translatable). Such captions are written as #$sqlDue$#. In this case you add SUM_ before the caption and add whole caption to single quotation marks (correct caption would be 'SUM_#$sqlDue$#').
3. Add element to dashboard
Once you added SUM to selected captions, you then have to add this element to dashboard where SUM will be shown. If you added SUM to custom caption, caption is written with square brackets ( [] ) where DL default caption is written normally.
 |
Warning
SUM can only be included in columns with numerical values. If you add SUM to caption of non numerical value, you will get an error.
|