where to set a variable parameter in webi

[Former Member]

i need to default a webi to display only, the current fiscal year period records.

in the universe (UNX) feeding the webi, the records are returned as follows, by order type, and fiscal year period, and amount

e.g.

ORDT1  2012006   100

ORDT2  2012006   150

ORDT1  2012007   110

ORDT2  2012007   160

ORDT1  2012008   120

ORDT2  2012008   170

as we are now in 2012007

i wish the webi, which is based over a universe, to only fetch the last two rows into the report.

this hopefully can be done when the report is scheduled, AND, when the next fiscal period comes up, i wish the webi only to display 2012008 and so on

so i am guessing, i need some kind of place to put some logic based around the day that the webi is run.

i do not want all the records returned to the webi, i am trying to filter them out, prior to hitting the webi, maybe in the query panel or universe ?

thanks in advanced

g

[Former Member · Accepted solution]

Hi Glen,

First off: if you're the author of the 'Caboodle' blogs: thank you for your effort!

To help you out, I've created a cube with 3 transactional records in it:

2222    2012006   100 BT

3333    2012007   110 BT

5555    2012008   120 BT

In response to your question: you could try the following:

1. Create a derived table in your DF, based on the 0FISCVARNT table.

a- Right click in your data foundation

b- Select 'Insert' > 'Derived Table'

Make sure you follow the syntax above and make sure you select 'Standard SQL-92'

This will create a derived table, with one record: the current fiscal year / period. I have chosen the 0FISCVARNT table for this, as it contains only a few records.

2. Join the new derived table to your fact table (in my case IB2AC003C)

a- Right click in your data foundation

b- Select 'Insert' > 'Join'

c- Select your fact table (ie the cube) and join 0FISCPER with CurFisPer as seen below

d- Detect cardinality and make sure that 'Outer join' is checked on the fact table side

3. Create (or refresh) your business layer

a- Make sure that the object [Derived Fisc Per\Cur Fis Per] is there

b- Publish your universe

4. Create a Webi on the new universe

a- Make sure to restrict [Fiscal Year / Period] on the [Cur Fis Per] object as seen below:

4. Results, voilà

Is this what you were looking for?

KR

Bart Santing

[Former Member · Accepted solution]

hi bart, yes i am the author of caboodle, thanks

i will look at your solution soon, cheers for the help

[Former Member · Accepted solution]

hi bart, i have finally got round to this. i think i am very near.

1. must you have the variable in the webi ? i know i asked for it, but can it be removed.

2. how do you get the filter in the webi pointing to the derived Cur Fisc Per. i cannot seem to do that

ta

[Former Member · Accepted solution]

Hi bart, i did manager to apply your solution, and it is working a treat, thanks, g

[Former Member · Accepted solution]

Glad to be of help, Glen. You saved me a ton of time setting up an NW + BO4 sandbox system.

Cheers!

[Former Member]

You can do this on the report

1. Create a variable

    VAR1 = ToNUmber(Year(currentdate())+"00"+MonthNumberofYear(CurrentDate()))

     what the above variable will do is it will get the value 2012007 or 2012008 depending on the month you are in.

2. Once you have the above create another variable

     VAR2 = if(VAR1 = fiscal year period;1;0)

when you place this variable beside the fiscal year period column it will show 1 on the rows where you have 2012007 and 0 for other rows

3. Apply a report filter using var2 and set var2 =1 you will only see the rows where you have fiscal year period 2012007

Hope that helps

[Former Member]

i do not want to do this on the report, i wish to filter the universe some way to stop all records being returned to the report

[Former Member]

In universe create a object which will give you the current fiscal period value. The below definition will work on oracle you need to modify it to fit other DB

Current Fiscal Month = TO_CHAR(sysdate(),'YYYY')||'0'||TO_CHAR(sysdate(),'MM')

so this will give you 2012007 once you have this object in your query apply a filter as below

Fiscal Year period = Current Fiscal Month and it will only return you the two rows and since the above definition will update automatically the month you run the report the next month you will get 2012008 and so on

Hope this is Helpful

[Former Member]

hi tauseef, i am sorry, but you are going to have to spell this one out. i am not familiar with the IDT and UNX universes.

i have opened my UNX and where i can create objects in the Business Layer pane, if i create a filter there, i just dont know how to add your code, or even use it once added.

i do see the ability to set a filter on the data foundation of the universe, is that what you mean ?

please note, i am in information design tool of BI 4.0, not the older universe environments.

ta

g

[Former Member]

Which database do you have the universe against in IDT

[Former Member]

i have it over a relational db connection, on SAP, over an Oracle db

[Former Member]

if you are using IDT you will need to create the object in the Business Layer within the universe. The UNX file contains a connection, data foundation and business layer.

the above picture shows a Business Layer in the IDT. Right click on the Class yo want to add the new object in and select New -> Dimension

In the new window under SQL Definiton you will see area for SELECT clause paste the below definition over there

TO_CHAR(sysdate,'YYYY')||'0'||TO_CHAR(sysdate,'MM') and click ok your new object will be created. You can give a meaningful name to the object. Export or synchronize your project with the server to make this new object available to you.

Hope this is Helpful

[Former Member]

hello tauseef,

i think i tried what you said, and this is what i got

but the new dimension fails an integrity check, see following

i must be doing something wrong

[Former Member]

It is ok if it gives you error because when you parse a object it looks for if it is associated with a table name in the definition the formula being used for the object we are not associating it with any table we are just using different oracle functions. You can save and export this object it will work ok on the report side

Please check below snap shots

Used the object in a sample query

Thanks,

Tauseef

[Former Member]

hi tauseef

again, just cannot get it to work like you say

here is a shot of my query attempt, in the IDT

[Former Member]

Tauseef, I can see you're a skilled guy there - sorry for breaking in your thread.

Glen, are you using a JCO connection on BW and a Multisource-enabled Data Foundationon top? If so, Tauseef's PL/SQL code (which by the way is 100% correct) will not parse.

The Multisource-enabled universe restricts you to use only SQL-92 syntax (Standardized SQL) as opposed to native SQL- in this case: Oracle PL/SQL. The reason for enforcing SQL-92 is that you could potentially add another connection to the Multisource-enabled Data Foundation, say a MS SQL Server connection. SQL Server speaks T-SQL, which is slightly different from PL/SQL. They both should be able to understand SQL-92. Other databases (i.e. MaxDB) also speak SQL-92 as a second language.

Below an illustration of what I mean by 'different syntax':

PL/SQL          SQL-92         T-SQL

to_char()        toString()       cast(... as char(..))

sysdate          curDate()       getdate()

||                     concat(.., ..)        +

I think this produces your error.

[Sujit]

Can you try this create a filter in a universe with something like (Max (Fiscal Year) - 2) and drag this filter on the report.

also, in Webi you can create variable as per above logic and use that in the filter/condition as constant.

Regards,

Sujit Honrao

[Former Member]

i am looking to filter the records prior to the webi being displayed, so i need something in the universe, or the webi query panel, or something like that ...

[Sujit]

I am not sure, I got you, but if you do not want to create the filter on report level then you would have to create it at Universe level, however, even in that case, the filter will have to be dragged in the query panel while creating the report, It will work the same way even if you create the filter at the report level. Only difference n the filters will be...Universe filter is accessible to all and report is limited to this particular report.

Other option is to filter the data at the DB leve, but don't think its of any use here.

Regards,

Sujit Honrao

[Former Member]

interesting, so are you saying i can perform the filter you suggested earlier "...(Max (Fiscal Year) - 2) " in the universe (that is in the IDT) ?

[Sujit]

Yes, you can, however the above \\ "...(Max (Fiscal Year) - 2) "\\ is not the exact logic, you will have to figure out the correct one from the objects have, take the maximum year values to \\ to only fetch the last two rows into the report.\\

On second thought, if you want to display only last two values? did you try using Rank function to display top/bottom values?

Regards,

Sujit Honrao

[Former Member]

can you point me to where in the IDT, i can then use the formulae as you suggest.

regarding you second thought, my example is not the full story, so it wont work.

ta

[Sujit]

I don't have access to IDT right now, but could you Refer section 8.2.2.2 To add a filter to a query in the IDT quide.

Sujit

[Sujit]

In IDT, you can go to Business Layer Pane > Right click on a class or on a blank space and from the options select 'Conditions'

Let me know if this helps. Thanks.

Regards,

Sujit Honrao

[Former Member]

hi sujit

not sure where you are looking at, but i have searched the IDT everywhere, and cannot find anything pertaining to "Conditions" as you say.

even so, i did find a way to create a filter in the Business Layer pane, and is it here you say i can create variables under the object value ?

[ravi_chandra3]

Hi

What is the datasource you are using ? If u are using the BEx query as the datasource they u can write the customer exit.

Please let me know the datasource you are using..

Regards,

RaviChandra

[Former Member]

Hi  I have already stated it's a universe

[ravi_chandra3]

HI

Can u tell me the universe is created on the top of the which datasource.

Regards,

RaviChandra

[Former Member]

Hi, it's a BW 7.3 multiprovider

[ravi_chandra3]

Hi Glen,

Create the query on the top of the multiprovider and then write the customer exit under cmod tcode and onthe top of that query create the universe and then create the webi on the top of that universe.

In the customer exit write the code to populate the current fiscal period data only.

I think this flow will display the output the correctly...

Regards,

RaviChandra

[Former Member]

hi, its nice of you to try, but that is not what i am looking for. i know i can do that, my question is about webi /universe parameter. can i do the filtering there. thanks