I should have explained that any conversion function (such as CAST) would do the same thing, but the "TO_CHAR" function gives you control of the format. If I use evaluate('TO_CHAR(%1,%2)' as char, "Your Date", 'yyyy / mm'), it looks exactly like your original requirement:

I've had some frustration with the same issue in data models where I want to select from a limited, discrete set of archive dates, and I don't know what the archive dates are. If you apply a TO_CHAR function to the date field, such as EVALUATE('TO_CHAR(%1,%2)' as char, "Project Info"."Snapshot Date",'mm/dd/yyyy'), the…

Is this happening on a dashboard? You'll have to adjust the column or section properties, or both.

Here's another way (If you must). Works in Tables and Pivots.

Dimitri, I'm working in 11.1.1.9.5, but I've dealt with similar requirements. In my version, the one-column pivot you see here: Looks like this on the dashboard without any special formatting: Have you tried it on a dashboard?

Hi bej, I work in 11.1.1.9.5, but there is a conditional format option under the style dialog for graph properties. You'll have to set the format for however many categories you're dealing with, then repeat it in all of your charts.

I do this type of union quite often, unless I'm not clear on the OP's requirements. The first query brings in Project #, Order #, Order Value. Then add null columns of the data types matching Business Unit and Region, such as "cast(null as char)". The second query brings in Project #, two null columns matching data types…

If the objective here is to open specific sections based on the rowcount in the dummy query, then I have done something similar. My dummy query was a single column date query (Advertise Actual, below) with a filter like this: You'll have to add clauses to the filter as new options are added to the prompt.

That's '<a href=@{biServer.variables.variablename}>weblink</a>' without the red squiggles. I'm in 11.1.1.9.5.

Joel Acha's solution works for me using this syntax: Results look like this: It takes me to the correct website.

You can use a column prompt to set a presentation variable here: Then reference the presentation variable in the other subject area-filters of the union.

You can create a new column or revise the column in question using various string functions. 

The pivot charts are usually my first option, because I've found them to be more flexible and adaptable. In the position selector you can choose "chart only" to hide the actual pivot table.

I believe the issue is that the result of the MOD statement is not an integer, even though displayed as one. So when you cast to an integer, it is applied to 6.77..., not 6 in your first example. So, that "Months" statement has to be nested in a TRUNC function (bolded below). This statement: EVALUATE('TO_CHAR(%1,%2)' as…

The simplest way that I know of is to use a a pivot chart, which gives you the option: "Display as Running Sum".

Thanks. Just edited my answer.

I have pasted an answer from the Stack-Overflow website by Alex Poole - (see bottom for proper credit). I've adapted it to OBIEE front-end. Replace my "Project Schedule Milestones"."CON Complete Actual" with your hire_date. Months Diff: EVALUATE('MONTHS_BETWEEN(%1,%2)' as double,CURRENT_DATE,"Project Schedule…

Hi Adam, I have tried what you suggested, and been unable to reproduce the error. So far, it's only occurred (or been discovered) in this migrated query. My main concern is about the hundreds of migrated queries. The discrepancy was pretty obvious in the example I cited, but may be lurking elsewhere. Another quirk I've…

Glad I could help. Just an FYI: I encountered the problem while using 11.1.1.9.5. 

I have run into a similar, if not identical issue: My problem was caused by a date format that Excel didn't recognize: [FMT:DateShort] . When I changed the date format, it downloaded without problems.