Variable to create new dimension value in Webi

[Former Member]

hi There

in a webi report, i have couple of variables, what i need to do is to create a new dimension by these variables, so i can use it in a chart

for example:

i have current month, previous month, previous of previous month, all of them are variables, i need to create a new dimension, so i can use it in a crosstab to create a chart

thanks

Cleo

[Former Member]

Hi,

Just try dragging the variables one below each other while creating the chart.

[Former Member]

HI There

This one is only working for bar chart, it does not work for line chart, and i need a line chart to see the trend

[former_member188911]

Hi Cleo,

you need to create a table for example Year - Month, and then use an outer join to return all results even if there are none.

Does it make sense?

Thanks

Simone

[Former Member]

Hi,

How are you deriving (variable definition) the current month, previous month, previous of previous month?

If you could provide us with sample data and expected result it will help to provide solution.

Create formula to get all these in one varialbe like.

=If(Month([Date])=Month(CurrentDate());"current month";If(MonthNumberOfYear([Date])=MonthNumberOfYear(CurrentDate())-1;"previous month";If(MonthNumberOfYear([Date])=MonthNumberOfYear(CurrentDate())-2;"previous of previous month")))

[Former Member]

because the client needs 6 months trend report, and in webi level, it is hard to use the report date prompted by the user to calculate the 5 previous months, so in universe level, i create 5 objects to get 5 previous month of report month prompted by the user, for example, if the user input the report month ="3/24/2014", the previous month will be 2/1/2014, and previous previous will be 1/1/2014, then 12/1/2013, 11/1/2013, 10/1/2013

under each month, i will create 6 variables for another metric, say % medium error=total tickets opened where priority = medium, the reason, i create these fields manually, because not all of the month will have tickets opened by all support group, i know i can create a 6 months object in universe level, but if i add drill down filters, if the support has no ticket, the 6 months trend will be changed to 2 or 3 months, the user wants report layout has no change no matter if it will have data or not, that is why i create different variables for the month, and different variables for other metrcis. i already finished this table part, but the issue is i cannot use this table data to create a chart, so i need to creat new variables to include all these 6 months variables and metrcis "% medium error" variables

thanks,

[Former Member]

Hello Tao,

for Last 6 month trend report why you need to go for prompt in webi?

just create 2 dimensions in universe, one for current month and one for last 6th month.

use them in separate query and in  report query filter put month<= current month and month >= Last 6th month for operannds select the object from other query feature..

and schedule the report as usual.. which will give last 6 months trend values..

Regards

Subbarao M

[Former Member]

As I understand, The user doesn't have to input current date.

Should be 6 month back from input date only

[Former Member]

If this is the case,NO need to create the dimension in universe.. (since the query should run for all the historical data.. which takes lot of time...)

In report level we should create the variable based on the prompt value....

[Former Member]

the report will allow the users input the report month they need, for example, if now it is March 2014, the user can input report month as Feb 20, 2014, so the 6 month trend will be Feb 20, 2014, 01/01/2014, 12/01/2013, 11/01/2013, 10/01/2013, 09/01/2013. The reason i need to create the previous 5 months dimension in the universe is not all the month will have the data the user look for, if that month has no data, the user still wants to see the month with ZERO information. the report layout should be always 6 months.

in our database, we dont have a column called reporting month which will archive the data every month like call center database, that is why i need to add extra objects to write down the reporting month. the issue i am facing now, i need to use this 6 months table information to create a line chart, that is why i am asking if it is doable to create a variable from multiple variables.