Forum Discussion

Ciapek's avatar
Ciapek
Copper Contributor
Mar 13, 2024

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_bbb's avatar
    ih_bbb
    Copper Contributor
    If 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.
    • Ciapek's avatar
      Ciapek
      Copper Contributor

      ih_bbb

      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")

      )

      )

Resources