How to use max function in BusinessObjects Information Design Tool

[Former Member]

I am trying to use the "max" function in BusinessObjects Information Design Tool where I want to optain max date for "Last Outage Start Date" by the Unit Name.

I tried creating it this way:

max(@Select(SALES_FORECAST\Data part\Outage\Last Outage Start Date))

In (@Select(SALES_FORECAST\Data part\Unit Details\Unit Name))

The concept is to create similar computation in design tool as per the syntax in webi report

=Max ([Last Outage Start Date]) In ([Unit Name])

I would really appreciate if someone can guide me with the right syntax.

Thank you.

[Former Member · Accepted solution]

Create a predefined condition as:

Outage.StartDate IN

(SELECT  MAX(outg.StartDate) AS MaxStartDate   FROM   dbo.Outage outg   WHERE   (outg.UnitName = UnitName)   GROUP BY outg.UnitName )

Obviously replacing your column names with those stated above

[Former Member · Accepted solution]

Hi Mark,

I am alittle lost. Where will I be creating the predefined condition? In Data foundation or in Business Layer?

I am included a detailed version on why I am looking for the max and group by function.

Can you advice me based on that please?

Thank you.

[Former Member · Accepted solution]

I'm suggesting a predefined condition rather than a derived table. What it would do is create a correlated subquery when you use it in your report. I'd expect at the data foundation layer but can't be certain because I don't know your business model. Life is much easier with UDT

[Former Member]

Hi Swapnil,

Thank you but I don't think it is that simple for me.

the Unit name is created using syntax below:

CASE WHEN @Select(SALES_FORECAST\Data part\Data Calc\Unit Name C) IS NULL

THEN

@Select(SALES_FORECAST\Data part\Data Calc\Unit Name B)

ELSE

@Select(SALES_FORECAST\Data part\Data Calc\Unit Name C)

END

Where Unit Name C is from:

@catalog('EDW_TRANS')."TRANSITIONAL"."BIDSS_CONTRACT_MASTER_DATA"."UNIT_NAME_C"

And Unit Name B is from:

@catalog('EDW_TRANS')."TRANSITIONAL"."BIDSS_PBUS_MASTER_DATA"."UNIT_NAME_B"

Both B and C is joined to different field

Now the reason I wanted to create max was because in web-i unit name was appearing twice for data circled in blue. One with data and the other blank.

I realised if I can eliminate that in IDT using max function, then I will not have to create variables with syntax in BO web-i

=Max ([MillMfg]) In ([Unit Name]) 

(earlier was last outage date but the ones in blue are the actual culprit so if I can resolve for them I do not have to worry for the dates as there are 4 total tables with date and each table has 4 fields)

Before appying the variable in web-i report:

See the ones circled in blank, there are duplicates due to fields circled in red.

I did check the derived table tutorials but since unit name is not direct from a table so I am a little lost.

I am sorry for such long message (and detailed one I guess) but I am very newbie and just got assigned to this project, hence do require a little step by step guidance.

So if there is any alternative to create it at business layer instead of data foundation will be very ideal for me.

[Former Member]

why do you need to create any formula on IDT!!!

If you just want to eliminate entries where Mill Mfg value is null then simple on webi report

create filter like [Mill Mfg] is not null

Now to create the filter on webi report -> go to Analysis tab there you will find filter option

click on that & choose object [Mill Mfg] & in operator choose "is not null"

Thanks,

Swapnil

[Former Member]

you can create a derived table for same on IDT like

select Unit_name ,

max(last_outage_start_date])

from table

group by Unit_name;

Now on your report whenever you require max date then use the derived table in one of the queries on report to fetch the same.

I hope this will help you.

Thanks,

Swapnil

[Former Member]

Thanks Swapnil for the prompt reply.

However I am relatively new in this and I am still learning on Business Objects and its various other applications.

Can you please walk me through step by step on how to build the table in IDT and is it done in dfx or blx?

I can't create a dimension instead of a table?

I really appreciate the help.

Thank you.

[Former Member]

you can check following link for IDT realted stuff:

http://scn.sap.com/docs/DOC-8461

there is a section how to create derived table you can check that.

Working with Derived Tables

• Create a derived table based on an existing table in a data foundation (3:09)     interactive / video
• Create a derived table using the SQL Builder (3:03)     interactive / video
• Merge tables in a data foundation (1:48)     interactive / video

Thanls,
Swapnil