Alternate way for Cross Join

[kgcognos]

Hi

I am using a Package in my report.
I have a Report Date value prompt(only 1 date allowed to be selected) that is tied to our calendar table. Based on the selected date, I set up another query [Selected Calendar Date Query] to retrieve certain fields from the calendar table which I need to generate my report. This should retrieve 1 record only from calendar table.

My main report [Main Query] shows Company, Daily Bookings/Sales This Year and Last Year, MTD Bookings/Sales This Year and Last Year, YTD Bookings/Sales This Year and Last Year.
I had to enable the Cross Product Allowed in the query property of the [Main Query] so that I can use the calendar fields info from [Selected Calendar Date Query] 

It works fine but it is taking forever to run.

Any suggestions on how to do this in another way?

Thank you in advance.

 

[bdbits]

You did not say but this sounds like a relational package. It also sounds like the relationships are not properly defined in the Framework Manager model that generated the package. With your method, you are likely invoking local processing which is why it is taking so long.

You should discuss this with whoever created/maintains your package. If there are dates on the tables where you have the sales data, it should be joined to the calendar table in the model.

[kgcognos]

Our calendar table is correctly joined to our Sales tables.
The problem is I am reporting MTD and YTD for this year and last year.
I cannot add a filter to select the calendar record based on the report date(format YYYYMMDD) in the [Main Query] because it will cause to filter only 1 day of sales. I need to read 2 years of data.

I need the fiscal year, month and business day number where the report date belongs to.
then i can use it in my columns.

For example [This Year MTD] data item contains this code:
IF (
[Business Layer].[Calendar File].[Fiscal Year CCYY] = [Query2.Sel Fiscal Year] and [Business Layer].[Calendar File].[Fiscal Month] = [Query2.Sel Fiscal Month] and [Business Layer].[Calendar File].[Fiscal Month Day] <= [Query2.Sel Day No])

THEN ([Business Layer].[Actual Bookings (BOOKFILE)].[Book - USD Curr])
ELSE (NULL)   

Please advice if there's another way to do this instead of cross joins. Thank you

[Lynn]

I think you should filter your calendar table for the entire range you need: this year MTD and last year MTD. Then bucket the sales figures accordingly. If the calendar table is correctly joined to the sales table then there is no need to do what you're doing.

Does your calendar table have relative information to help you easily identify current MTD and last year MTD? Even if it doesn't you could filter like this:

Code Select Expand


[Your Calendar Date] between _first_of_year(current_date) and current_date
or
[Your Calendar Date] between _first_of_year(_add_years(current_date, -1) ) and _add_years(current_date, -1)


Then create your This year MTD item as:

Code Select Expand


case
  when [Your Calendar Date] between _first_of_year(current_date) and current_date
  then [Your Sales Amount]
  else 0
end


You'd do the same thing for the Last Year MTD except altering the date range.

I don't really understand why you have a date prompt, but you could easily substitute a parameter for current_date.

[kgcognos]

Thanks for a quick response. I am not getting how to determine the Fiscal Month/Year and Business day in Main Query. 
   
Our calendar table has these fields that I need in my report:
Calendar Date, Fiscal Year, Fiscal Month , Business Day number.
Note we are using 5-4-4 Calendar.
Calendar Date of May 1, 2015 for example still belongs to April Fiscal Month.

We need a date prompt in order to allow the user to re-run the report based on selected date.

Based on the selected date, I am getting the Fiscal month, year and Business day # of that date in [Query2]

Then on my [Main Query], I added a Detail filter to select
[Business Layer].[Calendar File].Fiscal Year in([Query2.Sel Fiscal Year - 1, [Query2.Sel Fiscal Year]
and [Business Layer].[Calendar File].Fiscal Month <= [Query2.Sel Fiscal Month]

Here are the report columns in the [Main Query]:
Company,
Daily Bookings/Sales This Year and Last Year,
MTD Bookings/Sales This Year and Last Year,
YTD Bookings/Sales This Year and Last Year. 

For [Daily] data item:
IF ([Business Layer].[Calendar File].[Fiscal Year CCYY] = [Query2.Sel Fiscal Year] and
[Business Layer].[Calendar File].[Fiscal Month] = [Query2.Sel Fiscal Month] and
[Business Layer].[Calendar File].[Fiscal Month Day] = [Query2.Sel Day No])

THEN ([Business Layer].[Actual Bookings (BOOK)].[Book - USD Curr])
ELSE (NULL) 

For [MTD] data item:
IF (
[Business Layer].[Calendar File].[Fiscal Year CCYY] = [Query2.Sel Fiscal Year] and
[Business Layer].[Calendar File].[Fiscal Month] = [Query2.Sel Fiscal Month] and
[Business Layer].[Calendar File].[Fiscal Month Day] <= [Query2.Sel Day No])

THEN ([Business Layer].[Actual Bookings (BOOK)].[Book - USD Curr])
ELSE (NULL)   

For [YTD] data item:
IF (
([Business Layer].[Calendar File].[Fiscal Year CCYY] = [Query2.Sel Fiscal Year] and
[Business Layer].[Calendar File].[Fiscal Month] < [Query2.Sel Fiscal Month]) 
or
([Business Layer].[Calendar File].[Fiscal Year CCYY] = [Query2.Sel Fiscal Year] and
[Business Layer].[Calendar File].[Fiscal Month] = [Query2.Sel Fiscal Month] and
[Business Layer].[Calendar File].[Fiscal Month Day] <= [Query2.Sel Day No])

THEN ([Business Layer].[Actual Bookings (BOOK)].[Book - USD Curr])
ELSE (NULL)   

Same for Last Year except the year is [Query2.Sel Fiscal Year - 1]

Please let me know if there's any other way to accomplish this. Thank you