UNION in CRXI

[syncdba]

Hi,
I have two formula's @Month & @Quarter
@Month
Date Month
8/1/2005 Month
9/1/2005 Month
10/1/2005 Month...

@Quarter
Date Month
8/1/2005 Quarter1
9/1/2005 Quarter2
10/1/2005 Quarter3...

I'm trying to create a parameter(Month/Quarter) which will ask user to see either "Month" or "Quarter" data from month/quarter field.
Date Month/Quarter
------------------------------
8/1/2005 Month
8/1/2005 Quarter
9/1/2005 Month
9/1/2005 Quarter..
For that I need to do UNION between @Month & @quarter.Any help..
@month(Formula)
==============
if (Month({sfdcOpportunity.CloseDate}) in [8])
and (year({sfdcOpportunity.CloseDate}) in [2005]) then "8/1/2005"
else
if (Month({sfdcOpportunity.CloseDate}) in [9])
and
(year({sfdcOpportunity.CloseDate}) in [2005]) then "9/1/2005"
else
if (Month({sfdcOpportunity.CloseDate}) in [10])
and
(year({sfdcOpportunity.CloseDate}) in [2005]) then "10/1/2005"
else
if (Month({sfdcOpportunity.CloseDate}) in [11])
and
(year({sfdcOpportunity.CloseDate}) in [2005]) then "11/1/2005"

@Quarter(Formula)
====================
if (Month({sfdcOpportunity.CloseDate}) in [8, 9, 10])
and (year({sfdcOpportunity.CloseDate}) in [2005]) then "Quarter 1"
else
if (Month({sfdcOpportunity.CloseDate}) in [11,12])
and
(year({sfdcOpportunity.CloseDate}) in [2005]) then "Quarter 2"
else
if (Month({sfdcOpportunity.CloseDate}) in [1])
and
(year({sfdcOpportunity.CloseDate}) in [2006]) then "Quarter 2"
else
if (Month({sfdcOpportunity.CloseDate}) in [2, 3, 4])
and
(year({sfdcOpportunity.CloseDate}) in [2006]) then "Quarter 3"
else
if (Month({sfdcOpportunity.CloseDate}) in [5, 6, 7])
and
(year({sfdcOpportunity.CloseDate}) in [2006]) then "Quarter 4"

 

[synapsevampire]

You might appraoch this by creating a Period table, which is how Data Warehouses tend to set this up (dimension).

Here's a FAQ on this:

faq767-4532

I also include the basic code in T-SQL (SQL Server) to create the table.

-k