I have an recurring issue with being unable to create question dimensions in BO.
In a report I’m working on, I need to create a dimension that tests jobs to see if they’ve been done in targeted timescales. I want a bloolean output from it so it mirrors an alternate object I already retrieve from the universe.
Below is the statement that I use to judge the jobs. In it, [Priority] is a dimension and [Number of Seconds] is a measure that returns the amount of time in seconds it’s taken for a job to be completed.
=If([Priority]=“E” And [Number of Seconds]>14400;1;If([Priority]=“U” And [Number of Seconds]>259200;1;If([Priority]=“R” And [Number of Seconds]>1296000;1;If([Priority]=“P” And [Number of Seconds]>3888000;1;0))))
When I create the variable, webi creates it as a measure. I can’t then perform any kind of analysis against in in my report i.e. my variable that I need to report giving me the number of jobs (at week level) that were classed as emergencies and were done on time - doesn’t work.
=Count([Job ID] Where ([Priority Description]=“Emergency” And [Job Overall OOT? Parallel]=0))
I think the issue is that I need to create a dimension, specific to each Job ID as I’m getting lovely old #Multivalue error from my formulas.
One issue with that error this time is that I know for a fact on certain days I’m looking at there is only one result to my IF statement as all jobs on those days were done on time = ergo why the multivalue error - there is no multiple value…
Second (& main) issue is that webi will let me change the measure (If statement) to a dimension, then promptly not treat it as such and choose to change it back to a measure when I refresh the report in any fashion.
Any ideas? Perhaps I need 1 variables per [Priority] to limit the IFs?
To convert a measure to a Dimension - Create a variable and use Format number function (as format number gives string as output)…Something like:
Variable Name = Test
Qualification Type = Dimension
Formula = FormatNumber(Measure; “####”);
The above should convert your measure to Dimension as the output of FormatNumber function is string.
Thanks Adbas, but I had already tried that & in webi it seems to be an issue with IF statements in general. I can create an IF statement saying if this is true then say, “true” else say “false” and generate a string output. - Or in my current situation, my IF statement has multiple permeatations & even more in another report I’m trying to work on.
What I’m working on is actually a bit more complicated than my original question lets on (don’t want to flood people with prose!). I have a data provdier with data on a lot of jobs done over a few days. Within each job there are several events that occur, therefore several rows of data in the data provider per job. I’m trying to aggregate information up to week level. So, I have a count of jobs done per week which is easy to get to) and then I need to test each job to see if it was done on time and get a count of jobs done on time at week level. It’s at this point bo lets me down.
If I create a table with job id in it, then my test variable (on time true or false based on priority) works fine. However, since BO forces it to be a measure regardless of whether or not it produces an integer or a string then when I try judging data at a higher level, so week in this case, it fails completely and gives me my old friend - the multivalue error.
This is a bit disappointing really since there’s no great reason that what I’m trying to do won’t work - it just plain doesn’t and I believe it’s because BO is forcing my IF statement to be a measure instead of a dimension applicable to the job id. Webi itself is of no help either since I seem to have lost the ability to hover over a variable / report content and see the context it’s in like I used to be able to do in deski - If I’m wrong there, then I bow to superior wisdom but I can’t see where or how to do that in webi.
To be honest, I don’t think this is do-able. I’ve tried referencing and creating my IF statement in multiple ways, for all for each in this and in that, to no avail.
If you want calculate a result at a dimension that is not in your table you have to tell BO othewise it uses the dimensions in the table to come up with the answer. Try something like this:
=SUM(If([Priority]=“E” And [Number of Seconds]>14400;1;If([Priority]=“U” And [Number of Seconds]>259200;1;If([Priority]=“R” And [Number of Seconds]>1296000;1;If([Priority]=“P” And [Number of Seconds]>3888000;1;0)))) ForEach([JobId]))
the ForEach will force the calculate at the JobId dimension and the Sum function will sum up the job id results to the week.
I think this will work if there is a one to one relationship between JobId and Priority and Number of Seconds. If not then you might have to first roll the value somehow up to the JobId level (Maybe us a Max function to get a single value for each JobId and then roll up to the week.).
Also when you create a variable you can change the qualification to whatever you want overriding what Business Objects chooses. So if you want this to be a dimension just change the qualification to a dimension.
Cheers for that , I’ll give that a go tomorrow when I’m back in.
Just on the point you made regards my ability to force BO to treat variables as either, dimensions, details or measures- I really can’t! I’ve tried till I’m blue in the face, trust me! That said I will try this option before posting back tomorrow. I had tried working with the for each function earlier, but am nottoo au fait with it, so thanks again for help
Many thanks both for your help but it’s been to no avail I’m afraid.
No matter what I do, I can’t get my test variable to set itself as a dimension so there is no way I can judge my data at Job ID level = grrrr! I’ve made a few changes to my report and everything it is based on is at Job ID level now and it still will not work and still insists on converting the dimension I create to a measure.
As you rightly say, the ForEach syntax should force BO to recognise that it needs to perform, and make a judgement on the number of seconds calculation at job id level - but it doesn’t.
In this instance, I’ve had to give up and do the only sensible thing and abandon BO & do it in Excel. Not such an issue to me to be honest, but I’d rather have sorted this in BO.
Can you not do the calculation at the universe or database level instead?
Are your transactions at the granularity of job ID or more detailed below?
If more detailed than job ID you could create a materialized view or derived table to aggregate the information at the job level, then do your calculations based on that aggregate table and make it a dimension in your universe.
Ha! Sorry but not in this instance:-)! If we had the ability I’d have done it first off but we can’t manipulate our universe(s) here and have to attempt to work with what we’ve got!! Not ideal I know, and yes we are going to change this but for now I have work at report level.
Try creating a variable as a Detail (niether Measure nor Dimension). Then create another variable based on that detail and save it as a Dimension. I seem to recall having to do this in the past and think it worked (not positive).
Used to be able to trick deski xi & 6.5 like that, but not Webi XI - the origin of the data is still the same = still the same result. I seem to remember another way of forcing it to treat statements like this as dimensions was to create Group variables based on them in deski and then use that in the report. Not anymore it seems!
Thanks to all of you on this thread. I was able to work around a similar issue using the comments here.
This formula gave me exactly what I wanted but I wanted it to be dynamic so the hard coding of the 9/30/2008 date was unacceptable
=Sum(If([MUR Date]=ToDate(“9/30/2008”;“MM/dd/yyyy”);0;[Participants]))/12
Since my data included 13 months I simply wanted to exclude that 13th month so I created a MinMURDate variable and used it like this but kept getting a MULTIVALUE error. webi would not let me change MinMURDate to a dimension since it included an aggregate function.
=Sum(If([MUR Date]=ToDate([MinMURDate];“MM/dd/yyyy”);0;[Participants]))/12
I then created a new variable called 13MonthsAgo and made it a dimension which webi accepted.
=[MinMURDate]
I then used it back in my original formula and SUCCESS
=Sum(If([MUR Date]=ToDate[13MonthsAgo];“MM/dd/yyyy”);0;[Participants]))/12
(BOB member since 2009-11-02)
!)
(BOB member since 2008-06-13)
(BOB member since 2002-06-20)