[Solved] Actual vs Budget

[CoginAustin]

We have 2 tables

Table: TransactionType
fields:
TransID,TransType
1, Budget
2, Actual
3, Void

Table: Transactions
fields:
TransID, Amount
1, $100
1, $200
2, $500
4, $5

I can get the Budget/Actual by looking at the transtype and grouping by it. However, my report writer says this is "clunky" and wants to instead see 1 table so it is easy to calculate totals:

EG:
Table: transactions
fields
budgetamount, actualamount, voidamount
budget,$100, $0,$0
budget,$200, $0,$0
actual,$0, $500,$0
void,$0,$0, $5

They already have numerous reports based on CustomerType(international, domestic, etc) using a common field SalesAmount from our fact table. I can't seem to get them to realize the we are talking the same thing. I also cant seem to get them to explain why 2 different customer types can be reported on but 2 transaction types can not. This report writer does not understand SQL or have any concept or worry about server performance.

So the questions are:
1. Is a derived table, like above, necessary and a best practice?
2. Is there any easier way to go about this, the correct way? Training? adding a table?
3. Should this be done in the database, fm, or report studio?

Database I would add a field to the fact table. And somewhere in our ETL process figure out the transtype and populate the correct field in the warehouse.

FM I would add a summary table with a calculated field for both budget and actual with a transtype and date

Report studio I would addÃ,  calc fields based on transtype((case when(type=budget) then amount else 0 end) as budgetamount)

I know there are several ways to go about doing any one thing but I am looking for best practice.

thank you

[sir_jeroen]

Hi CoginAustin,

Do you have MSN / Skype? If you do so, I'll contact you using MSN Audio or Skype if you leave me ur Email Address... because it's easy to be done, but difficult to write :S Sorry...

This is a typical example of Confirmed dimensions in Framework Manager

[CoginAustin]

I have neither unfortunately.  :-[

[sir_jeroen]

Btw.. First of all.. I would recommend you to take the course:
Cognos ReportNet 1.1 - Metadata Modeling Part 2

This is the ReportNet course you MUST attend

[CoginAustin]

I dont think it is a training issue. I can solve the problem any number of ways but I am looking for best practices.

A report writer who knows zero SQL and does not want to learn anything is hard to deal with. :)

[sir_jeroen]

Hahaha... but a Report Writer who manages a package  ???

[CoginAustin]

My report writer does not even know what they want but I am trying to figure out what they want and need on my own. I also am trying to figure out the best practice for giving them what they want.

I can easily write a report in RS giving them exactly what they want and have done so to give them an example to work off. However, they are very leary of using anything outside of drag and drop when writing reports, forget tabular models, calculated fields, etc. They want drag and drop only.

Yes, it does drive me crazy.  ;D

Really it is a simple question, i think. Do I create a summary table for them in FM, the warehouse, or just let them make their own calc field in RS..

If I make a summary table with budget and actual then what about the other 10 different transaction type? Do I make 10 fields each with different values..lol.. seems crazy to me..

[sir_jeroen]

No.
You have to make 3 fact-tables:
1 with "Buget" (FB) data, 1 with "Actual" data (FA) and 1 with "Void" data (FV) .

And all existing confirmed (joining) dimensions. You also have to set the granularity in each Query Subject... and so on....

After setting all necessary joins and granularity you create "Starschema groupings" and your users will be able to calculate everything between "budget", "actual" and "void".

This is very short description  ;)

Can you still follow it?

[CoginAustin]

Ok..
Our warehouse is 4 fact tables with numerous conformed dimension.s One fact table is finacials.
The financials fact table has a dimension named TransactionTypes. Transaction Types has values like Budget, Actual, Void, Opening, etc...
I am sure you can see how I can pull all Budget records from the fact table by using WHERE TransactionType='Budget'

Are you saying in FM I should create a fact table named BudgetFacts that filter our warehouse on TransactionType=Budget. Another table named ActualFact with a filter TransationType='Actual' etc..?

This is making more sense to me then creating 1 extra field tied to every record that 50% of the time will be null. Is this correct?

[sir_jeroen]

Yep, that's what i'm saying...:D

[CoginAustin]

Ok that makes much more sense to me. I am going to pass this idea onto our reporter and see what they think..lol..

wish me luck.. Thank you for your help!

[sir_jeroen]

Good luck!!!!