How to create report in SSRS using two parameters from one dimension

65 Views Asked by At

I would like to create report as below:

https://i.stack.imgur.com/b5VCe.png

The first section of columns contain costs of:

  • November 2020
  • November 2021
  • November budget 2021

The second section of colums contains costs accumulated of:

  • 11M 2020
  • 11M 2021
  • 11M budget 2021

The first section and the second section of columns should be based on parameters. I would like to get data for current month and accumulated from begining year choosing the data from parameters. On the end of section should be calculation: act/buget -1

My code:


SELECT  LEFT(MIESIAC_BI_ID, 4) AS ROK
       ,MIESIAC_BI_ID AS MIESIAC
       ,B.MPK_1_NAZWA AS OBSZAR_MPK
       ,B.[MPK_2_NAZWA_KONTO] AS MPK
       ,C.[OBSZAR_KOSZT_NAZWA] AS OBSZAR_KOSZTU
       ,D.[DESCRIPTION_NAME] AS OPIS
       ,WYKONANIE
  FROM [Le_crd].[dbo].[F_WYKONANIE_BI_4_5_KO_PROBA] A
  INNER JOIN W_MPK_BI_PROBA B
  ON A.MPK_BI_ID = B.MPK_BI_ID
  INNER JOIN W_RODZAJ_KOSZTU_BI_PROBA C
  ON A.RODZAJ_KOSZTU_ID + '_' + A.RK_ID = C.RODZAJ_KOSZTU_ID
  INNER JOIN [Le_crd].[dbo].[W_TRESC_BI_DOK_PROBA] D
  ON D.ID = A.ID_D
  WHERE (LEFT(MIESIAC_BI_ID, 4) IN (@YEAR)) AND WYDZIAL_ID = 'WL'

As a result I am given below data:

https://i.stack.imgur.com/vboTA.png

For that moment I have created matrix report:

https://i.stack.imgur.com/W20Vk.png

Now I would like to for the first column by parameter get current month data and for the second aggregated data from begining of year.

Fisrt column eaual October Second column equal 10 Months

For that moment I am given date for 2022 for 12M and for 2023 for 10M and in second column the same result, but I would like to get October 2022 and October 2023.

https://i.stack.imgur.com/fagpa.png

How to define for each column different parameter?

0

There are 0 best solutions below