I have a reqriement where
I will substract one column(A) from column(B) and store the result in column(C).
On run time when user see the report in dashboard, from the prompt he/she will select a value from prompt(X).
The prompt(X) has hardcoded value like 2,3,4,5 upto 10.
when he select any value from prompt(X), and click go, then column(C) result should be equally divided into that many part.
Can I have any clues on the above solution process.
get the value of prompt in Presentation variable.( you can set this up in the prompt)
Use the presentation variable to divide column C in your report.
unless you mean Column C to be made to N equal columns or rows.
Create a table with three columns. Column1 - dummy Key, column2 - parts column, 3 - unique identifier
table will looklik
col1 col2 col3
a 1 1
a 2 2
a 2 3
a 3 4
a 3 5
a 3 6
now create a columns in the fact table which has the measure which needs to be split to n parts and populate it with only 'a'
join this column with col1. use col2 for prompt (this is a must or else you query will go on a Cartesian join and will crash the system. There should always be a filter on col2).
in the report
Add col C, col2, col 3 . then divide col c by col2. hide col3.( col3 is added in order to avoid groupby col2)
you will get the split in rows.
Use a pivot table to make it to columns.