SQL for universe object has a case statement to create range literals(data in quotes below) on a report using the between keyword. When no data is found for the between clause the range is not printed on the report. How can I force the range literal to print when data for a range does not exist ?
SQL :
(CASE WHEN @Select(PSCF\principal balance) between 0.01 and 999.99 THEN '0.01 to 999.99'
WHEN @Select(PSCF\principal balance) between 1000.00 and 9999.99 THEN '1,000.00 to 9,999.99'
WHEN @Select(PSCF\principal balance) between 10000.00 and 19999.99 THEN '10,000.00 to 19,999.99'
WHEN @Select(PSCF\principal balance) between 20000.00 and 29999.99 THEN '20,000.00 to 29,999.99'
WHEN @Select(PSCF\principal balance) between 30000.00 and 39999.99 THEN '30,000.00 to 39,999.99'
WHEN @Select(PSCF\principal balance) between 40000.00 and 49999.99 THEN '40,000.00 to 49,999.99'
WHEN @Select(PSCF\principal balance) between 50000.00 and 59999.99 THEN '50,000.00 to 59,999.99'
WHEN @Select(PSCF\principal balance) between 60000.00 and 69999.99 THEN '60,000.00 to 69,999.99'
WHEN @Select(PSCF\principal balance) between 70000.00 and 79999.99 THEN '70,000.00 to 79,999.99'
WHEN @Select(PSCF\principal balance) between 80000.00 and 89999.99 THEN '80,000.00 to 89,999.99'
END)
A second data provider will not work as the ranges can not be stored anywhere. I believe the best bet is to modify the universe object to enable printing the ranges when there is no data found for a particular range.
Any ideas how to make the case else statements will function as below (this is the plan but this doesn’t parse):
(
CASE WHEN @Select(PSCF\principal balance) between 0.01 and 999.99 THEN '0.01 to 999.99'
ELSE '0.01 to 999.99'
WHEN @Select(PSCF\principal balance) between 1000.00 and 9999.99 THEN '1,000.00 to 9,999.99'
ELSE '1,000.00 to 9,999.99' END
WHEN @Select(PSCF\principal balance) between 10000.00 and 19999.99 THEN '10,000.00 to 19,999.99'
ELSE '10,000.00 to 19,999.99'
WHEN @Select(PSCF\principal balance) between 20000.00 and 29999.99 THEN '20,000.00 to 29,999.99'
ELSE '20,000.00 to 29,999.99'
WHEN @Select(PSCF\principal balance) between 30000.00 and 39999.99 THEN '30,000.00 to 39,999.99'
ELSE '30,000.00 to 39,999.99'
WHEN @Select(PSCF\principal balance) between 40000.00 and 49999.99 THEN '40,000.00 to 49,999.99'
ELSE '40,000.00 to 49,999.99'
WHEN @Select(PSCF\principal balance) between 50000.00 and 59999.99 THEN '50,000.00 to 59,999.99'
ELSE '50,000.00 to 59,999.99'
WHEN @Select(PSCF\principal balance) between 60000.00 and 69999.99 THEN '60,000.00 to 69,999.99'
ELSE '60,000.00 to 69,999.99'
WHEN @Select(PSCF\principal balance) between 70000.00 and 79999.99 THEN '70,000.00 to 79,999.99'
ELSE '70,000.00 to 79,999.99'
WHEN @Select(PSCF\principal balance) between 80000.00 and 89999.99 THEN '80,000.00 to 89,999.99'
ELSE '80,000.00 to 89,999.99'
thanks for your help, I am new at this and need more detail to understand your proposed solution. The example you had forwarded shows a solution in which missing months can be determined by using a second data provider that contains all the months, correct ?. I understand that a calendar object could compile a list of the months and be used by the first data provider. In my case, are you suggesting that I create an object that has all the ranges needed and tie that to the first data provider that aggregates all the data by ranges (between) ? If so, I am not sure what form that would take. How would I have the second provider (range literal) match the range (between SQL) in the first provider and aggregate two other measures for all the ranges ?
How about using a DB function to generate a band name for any given input value. Below is an Oracle function - its fairly generic so could be adapted to other DB syntax. It takes 4 numeric values : input, band width, min value, max value.
create or replace function get_band (
vInp in Number,
vBandW in Number default 100,
vMin in Number default 1,
vMax in Number default 1000)
RETURN varchar2 IS
vLower NUMBER ;
vUpper NUMBER ;
BEGIN
IF vInp < vMin THEN RETURN '<' || to_char(vMin);
ELSIF vInp > vMax THEN RETURN '>' || to_char(vMax);
ELSE
vLower := vMin + TRUNC((vInp-vMin)/vBandW)*vBandW ;
vUpper := LEAST(vLower + vBandW -1, vMax);
END IF;
RETURN to_char(vLower) || ' - ' || to_char(vUpper);
END;
For example, if you require intervals of 1,000 for all values between an anticipated min & max range of 1 and 100,000, you could use the above function as a dimension object in your universe : get_band(, 1000, 1, 10000). This will generate bands automatically & give bands of < 1 or > 100,000 when values lie outside the min max range prescribed. It will be lot neater then multiple CASE WHEN statements.
Looking at DR’s solution. It seems to be dependent on having a column that you are banding your results on (i.e. Month Name). Gtherrie’s issue is that there is no column to use in the second data provider.
I am looking at a similar issue where a measure of minutes between processes need to be banded into performance bands (<5mins, 5-10mins…)
I was thinking that worst comes to worst I can create a derived table in the universe that has a row per required band (using unions (I know, I know)).
(BOB member since 2003-11-27)
(BOB member since 2010-11-05)