Discussions
Categories
Choose a Product
THRUST SERVICES
CORE APPS
CE PRODUCTS
...
Quick Links
POPULAR
HELPFUL TIPS
Groups
My Links
FOR SIGNED IN MEMBERS:
Back to website
Home
Designing Analytics Reports
Dynamically select crosstab columns from cube
Migrateduser
<p>Hi All,</p><p> </p><p>I need to show 'sales' by either Week or Month in a crosstab. User would select using parameter - either Week or Month. I've seen a few solutions that cover doing this in a report with a data set (or two) but not a crosstab using a cube. Not only does the column in the crosstab need to change, but also the aggregate rows below.</p><p> </p><p>Learning and appreciate all the help!</p><p> </p><p>John</p><p> </p><p> </p><p> </p><p>Here's the relevant portion of the SQL - I am bringing back YYYYWW (week) and YYYYPP (month). I thought about using a param to return either into a single column name based on param value but the value of the new field was literally the parameter value. Param ? = "YYYYWW", Select ? as YYYYXX returned "YYYYWW" literally for the value of YYYYXX for all records. </p><p> </p><p> </p><div>SELECT</div><div>program_profile_id,</div><div>full_date, CR_fiscal_Year_Num, </div><div>CR_YYYYPP, CR_YYYYWW,</div><div>a.date_created,</div><div>a.tran_code,</div><div>e.tran_cat,</div><div>sum(if(a.reversed, -1, 1)) as Net_Count,</div><div>Sum(a.transaction_amount) as Amount</div><div> </div><div>FROM...</div>
Find more posts tagged with
Comments
There are no comments yet