Forum Discussion
DataSet - How to retrieve values from grouped columns?
I have SQL which return data:
SELECT
dbo.TOWARY.TWR_NUMER AS INDEKS,
dbo.TOWARY.TWR_NAZWA AS NAZWA,
dbo.SL_JM.SLJM_KOD AS JM,
SUM (dbo.WAZENIA.WZN_ILOSC * dbo.WZN_CNY.WZNCNY_NETTO) / SUM (dbo.WAZENIA.WZN_ILOSC) AS CENA,
DATEPART(yyyy, dbo.ZAKUPY.ZKP_DATA_ZAKUPU) AS ROK,
dbo.MAGAZYNY.MG_KOD AS MG_KOD,
dbo.MAGAZYNY.MG_NAZWA AS MG_NAZWA,
ROW_NUMBER () OVER (
PARTITION BY
dbo.TOWARY.TWR_NUMER
ORDER BY
dbo.TOWARY.TWR_NUMER,
DATEPART(yyyy, dbo.ZAKUPY.ZKP_DATA_ZAKUPU) DESC
) AS NUMER
[ROK] - Year the number of columns is variable
I grouped the columns by: year.
I want to divide the years between them, e.g.: 2021/2020 2022/2021 2023/2022 (...)
for rows (Example 20001):
0,61 / 0,64
0,74 / 0,64
But i don't know how take value from Column (Year)?
%First / Last
=(First(Fields!CENA.Value, "INDEKS")-Last(Fields!CENA.Value, "INDEKS"))/Last(Fields!CENA.Value, "INDEKS")
2 Replies
- ih_bbbCopper ContributorIf it is me, I will get the value of each year in the query itself, bring back as part of the result set. Then, you can just do the division using the 2 columns in the report. There are few ways to do it in query. You can use temp table, or CTE. Good luck.
- CiapekCopper Contributor
Counting in SQL is not necessary, solution is simple:
=IIF(
(Previous(Sum(Fields!CENA.Value), "ROK") = 0 OR
Sum(Fields!CENA.Value) = 0),
"-",
(
Sum(Fields!CENA.Value) -
IIF(
Previous(Sum(Fields!CENA.Value), "ROK") = 0,
Sum(Fields!CENA.Value),
Previous(Sum(Fields!CENA.Value), "ROK")
))
/
IIF(
Previous(Sum(Fields!CENA.Value), "ROK") = 0,
Sum(Fields!CENA.Value),
Previous(Sum(Fields!CENA.Value), "ROK")
)
)