Displaying date fields in multiple format

[MSTRPRG]

I would like to be able to display date attributes in multiple format, based on the context. For example D1 in Report1 would display as "mm-dd-yyyy" and the same D1 in Report2 would display as "dd-mm-yy."

To accomplish this, I have created a DayFormatTable which contains uniquely formatted date data in each column of each row. For example:
Col1: 01-01-2005
Col2: 01/01/2005
Col3: 01-01-05


By creating additional "Forms" for my D1 date attribute, I could choose between different forms and hence different formats. By adding additional forms , I have created a "relationship" between my fact table and the DayFormatTable table.

The question is how can I do the same thing for another date attribute in my Fact2 logical table, without creating a relationship between Fact1 and Fact2 tables ?

Thanks.

 

[FLB]

I am unclear as to why the default formatting options do not meet your requirements (ala Excel formatting with custom formats), but I guess you have your reasons.

For your new date attribute, you have to do the same thing you did for your first one (either multiple forms or multiple attributes? Not sure what you picked). Make sure you use table aliases to create a new alias for the DayFormatTable table and have your second data attribute point to it. There should be no relationship / reuse of objects between Fact1/Fact2 (i.e. Date1 and Date2).

HTH!
FLB.

 

[MSTRPRG]

Dear FLB,

In order to utilize my date attributes in transformation, they are saved in numeric data type as oppose to date data type.

I guess a compeletely different route for me is to use ApplySimple and have DB format my date column for me. In other words, I would create multiple forms , one for each type of formatting. Inside the ApplySimple, I would create a (DateTime or SmallDateTime ) data type with my numeric data and then use (in MS-SQL case ) CONVERT function to obtain the desired format.

Now I am wondering which one is more efficient:
1- Using the lookup tables or
2- Utilizing ApplySimple ?

I would be able to rid of lookup tables compeletely since they are needed for transformation, but that is based on user request.

Thanks.

 

[FLB]

A few things you might want to keep in mind:
- every operation that you do in a query generates both CPU and disk usage.
- some operations will prevent the use of indexes (not sure if that applies in your case; depends on the query and the database server)
- lookup tables (usually) helps the DB optimizer guess how many rows are supposed to come back and plan the query accordingly
- lookup tables are very fast to access when retrieving a list of elements/

If you convert your existing ID into dates (i.e. your transformation are using dates instead of numbers), you don't need to do anything anymore (just define the formatting you want to see in each reports).

I guess it boils down to this:
- is it more efficient for you to use tranformation tables (more disk space, less CPU usage)
- or to use applysimple in your transformation (less disk space, more CPU usage)
- does the user need to pick one of those values from a list (prompt)?

In my opinion, disk is a lot less expensive than CPU....

The other option you have is to define _one_ form where you convert your number into a date and change the formatting of the attribute form at the report level. This become a whole different ball of game if the end user needs to be prompted on the elements....

2 cents,
FLB