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 !
Quote from: Steve on 03 Aug 2023 04:07:39 PM
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.
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
Quote from: Steve on 04 Aug 2023 11:44:16 AM
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.
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.