SQL Server Performance Forum – Threads Archive

First & Last Functions

How and where First & Last Aggregate Functions are used? I am trying to use them in the data section in matrix.
Matrix has three row groups and one column groups.
but the data is not getting displayed. But when I add subtotal field, they are displayed there.
I want them in data field only. Any clue?

---

XtreamProg, Have used the First/Last in the grouping in row and columns. But for data, usually want to display a count, sum… based on the row/column grouping. With knowing the grouping expression, it would be helpful to rule out a data stream problem. I suggest the following: 1) In the data page, modify the query to return a few items and very their display.
2) In the layout, modify the groupings so that no aggregate functions are used and a simple aggregate is used in the data section.
3) Preview the report. If the subset appears as expected then the problem is verified to be in the grouping expression. Publish them and perhaps the issue can be spotted. dmatta59

---

Hi,
thanks for the reply. But i think i should elaborate my problem in detail.
Here it goes:
I’ve one matrix having 3 row groups and 1 col group. In data section I have 5 rows.
In 3 rows im using SUM function on some database fields. But in rest 2 rows, i want to use FIRST and LAST function on some other database fields. But when i preview the report, its not showing me the field where FIRST & LAST functions are used. Its displaying blank. And my query does not send any blank as i’ve replaced all null fields with 0 in my stored proc. So report should atleast display a zero there. When i have only two rows in data section having FIRST & LAST function, it is working fine and giving the expected results. But as soon as i add third row having SUM function, it bombs. The FIRST & LAST functions does not display the value. U can try this.

---

]]>