Extract values from a multi year prompt

[Steve]

Hi All -

Wondering of there is a way to extract values selected from a multi year prompt.

Ex: The report has a multi select value prompt for Years so the user can select 2015, 2020, 2021, 2023 etc.

The user wants to see total sales for each year in columns next to each other. I can use calculated columns with the expression:

  Case
    When [year] = extract(year,current_date)
    Then [Sales]
    Else 0
  End.

This is possible when you display current year sales, last year sales in columns next to each other but how to extract the years selected from the Prompt? Example to explain it further:

Values selected from the Prompt - 2020, 2022, 2023

List report to show data as:

Sales             2020       2022        2023
----------------------------------------------

USA          100,000   200,000    150,000
Canada       50,000   150,000    200,000


Thanks !

[MFGF]

Hi All -

Wondering of there is a way to extract values selected from a multi year prompt.

Ex: The report has a multi select value prompt for Years so the user can select 2015, 2020, 2021, 2023 etc.

The user wants to see total sales for each year in columns next to each other. I can use calculated columns with the expression:

  Case
    When [year] = extract(year,current_date)
    Then [Sales]
    Else 0
  End.

This is possible when you display current year sales, last year sales in columns next to each other but how to extract the years selected from the Prompt? Example to explain it further:

Values selected from the Prompt - 2020, 2022, 2023

List report to show data as:

Sales             2020       2022        2023
----------------------------------------------

USA          100,000   200,000    150,000
Canada       50,000   150,000    200,000


Thanks !

Hi,

Is there a reason this needs to be a list report? A crosstab looks to be the exact format you are looking for, and it provides the output with no real effort on your part - just put Year in the columns, Country in the rows and the Sales amount in the cells?

Cheers!

MF.

[Steve]

Hi MF!

The reason is that crosstab has a limitation that it does not show the column titles of any columns in the rows. The report has 4 columns in the rows.

Thanks

[MFGF]

Hi MF!

The reason is that crosstab has a limitation that it does not show the column titles of any columns in the rows. The report has 4 columns in the rows.

Thanks

Couldn't you add crosstab spaces across the top and populate them with the column titles? Or am I misunderstanding?

Cheers!

MF.

[Steve]

It doesn't let me add crosstab space on the top box but can add just above the columns which makes it look bad because of an extra row between the column titles and data. I added a table inside the box on top of the rows and by setting fixed widths of the rows, I was able to align the table cells with the columns in the rows. Thanks for showing me the direction.