Crosstab report calculations using dimensional Model

[rpvs]

I am trying to create the following crosstab report from a dimensional model

                                     Monthtodate                Quartertodate                    Yeartodate
              Actual  Plan Prev Year    Actual   Plan  Prev Year      Actual  Plan    Prev Year
  
Product Line   Sales       888     777   666             888     777   666               888     777        666 
                     COGS     555     444    333            555     444    333              555     444        333   
               Ship Profit      (Calculation based on sales and cogs. How to create this ???)
                Ship Profit %  (Calculation based on ship profit. Not sure how to create this row)   

Rows are Product Line  Sales
                                Rev
                                Ship Profit
                                Ship Profit %

Columns are actuals, plan and previous year and based on Monthtodate    quartertodate and yeartodate

Product line, Time and sales and COGS( which are based on account codes) all are dimensions.
I have created query calculations for each column and am using if it's current month and sales accounts then get number else if..... which is giving me errors and am new to functions in dimensional modelling.
Question is am i headed in the right direction and if so what functions can i use??? Also how do i create a calculation for an entire row???

Thanks for your help

[MFGF]

Hi,

The approach I would take with the rows is as follows:

Drag the Sales member into the rows
Drag the COGS member below it into the rows
Drag a query calculation below it into rows, and code it as [Sales]-[COGS] (or whatever the appropriate calc for Ship Profit should be)
Use the same approach for Ship Profit %.

For the column calculations, I assume Monthtodate is the latest month you have available?

Drag in a Query Calculation for Monthtodate and define the expression as closingPeriod ([your Month level])
Quartertodate and yeartodate should be almost identical, but using the Quarter and Year levels respectively.
Drag the Actual and Plan members and nest them below the existing column calculations.

That just leaves the Prev year calcs.
Again, drag in a query calculation, and for the Prev Year within Monthtodate, use the expression lag(closingPeriod ([your Month level]),12)
The syntax will be similar for the Quarter and Year prev calcs but using the appropriate levels.

Hope that helps!

MF.

[rpvs]

Thanks MF for your detailed reply. I am running into issues when i do the [SALES] - [COGS] since as i mentioned earlier Sales are a set of account codes which are character and COGS are another set of account codes. Am using the function 'Cast' for the word 'Sales' when the account codes match the sales account codes. Believe because of this report studio is giving out errors and complaining.

Any ideas???

[MFGF]

Ah OK - so Sales and Cogs are not members but are query calculations?  Can you post up the syntax used to define Sales (for example) - change the original item names if they give away anything sensitive - as long as we can get the general idea of what's being done it may help us to suggest a solution.

Thanks,

MF.

[rpvs]

Yes am trying to do it through query calculations. Even the number for monthtodate ...etc though is the from the same data source , depending on cost or sales it's + or -ve. Due to the nature of the ERP implementation it's not really a straightforward thing. The syntax for the query calculation is

if([General Ledger Analysys].[Accounts].[Accounts].[Account Code].[Account Code] in ('3100','3101','3200','3400','3550'))then(CAST('Sales' as nvarchar(30))) else if([General Ledger Analysys].[Accounts].[Accounts].[Account Code].[Account Code] in ('4100','4150','4200','4300','4350','4400'))then(CAST('COGS' as nvarchar(30))) else ([General Ledger Analysys].[Accounts].[Accounts].[Account Code].[Account Code]).

I have done the same report in relation model with all the query calculation, but was not able to get the ship profit and ship profit % done and hence trying in Dimensional model .

[MFGF]

Hi,

is Account Code a member or an attribute?

You really need separate calculations for Sales and COGS. If Account Code is an attribute, you could replace the calculation with an expression such as

aggregate(currentMeasure within set filter([General Ledger Analysys].[Accounts].[Accounts].[Account Code],[General Ledger Analysys].[Accounts].[Accounts].[Account Code].[Account Code] in ('3100','3101','3200','3400','3550')))

Or alternatively, use the appropriate members instead, eg

aggregate(currentMeasure within set set([3100 member],[3101 member],[3200 member],[3400 member],[3550 member))

Whichever approach you use - call this calc Sales.

Use a similar approach for COGS, then the Ship Profit and Ship Profit % calcs should be easy based on these.

Regards,

MF.

[rpvs]

That worked for the rows. I am not getting any errors when i do the sales,cogs part and am able able to add query item for the ship profit calculations. Now am working on the columns part and getting the monthtodate qtd and ytd and will be adding a second set of query items for the measure and place it below the time query items. Time dimension has got dates currently upto 2015 and hence tried following for monthtodate

[General Ledger Analysys].[Time].[Time].[Year].[Year] = extract(year,current_date) and [General Ledger Analysys].[Time].[Time].[Month].[Month] = extract(month,current_date) - 1

But gives errors after adding this query item. The year and month are attributes. Clearly am doing something wrong here (similar query item worked in relational).

[MFGF]

Hi,

For the YTD column, try defining this as a query calculation using a filter() function

filter([General Ledger Analysys].[Time].[Time].[Year],[General Ledger Analysys].[Time].[Time].[Year].[Year]= extract(year,current_date))

If this gives an error, try casting the extract(year,current_date) part as char(4).

MF.

[rpvs]

Tried both and it does not give error as part of the query calculation but when i validate the entire report , gives an error QE-DEF-0459 CCLException DMB-ECB-0083 The level 'Accounts0' already exists in the hierarchy 'Accounts'.

If i use function like lastchild it does not give an error , but this function will not do the job. I dont mean trouble you with too many questions, but am almost there and it's really frustrating the way it keeps complaining if i add one little function.