We want to categorize donors into the amounts we are targeting and the by amounts they gave.
Level Goal Donors by Level Goal Amount Actual Donors by Level Actual Amount
$5,000 and above 2 $10,000 1 $5,000
$1,000-$4,999 25 $25,000 10 $10,000
Less than $1,000 30 $30,000 0 0
I have created a case statement that says
case
when [Amount] >= 5000
then '$5,000 and above’
when [Amount] >= 1000
then '$1,000-$4,999’
else ‘Less than $1,000’ end
If there are no donors in one of the categories (rows), how can I make the row print with the appropriate Level, Goal Donors by Level, and Goal Amount values with zeros or blanks for the Actual Donors by Level and Actual Amount? We are on Cognos BI 8.1 MR2. Thanks in advance for any assistance!
Hi Debra,
If you create a query that returns three rows for the three levels with the metrics as zero, you can then union this query with your data query. The union query becomes the basis for your list.
Another option is similar. Create the query with the three categories and then outer join to your data query.
I don’t know how your data is modeled, so take this with a grain of salt…
It looks like you have a goal table out there somewhere with goals for donor counts and amounts, so maybe you can use that? So create a goal query that always has the desired categories. You’d need some logic to derive those labels if they are not explicitly stored, but it seems there is a concept of donor level there. Case when donor level = 1 then ‘$5,000 and above’ etc.
The actual columns are just hard-coded zero values. Example result:
Level Goal Donors by Level Goal Amount Actual Donors by Level Actual Amount
$5,000 and above 2 10000 0 0
$1,000-$4,999 25 25000 0 0
Less than $1,000 30 30000 0 0
Now create a query that returns actual donors. In this case the goal columns are the hard-coded zero values.
Level Goal Donors by Level Goal Amount Actual Donors by Level Actual Amount
$5,000 and above 0 0 1 5000
$1,000-$4,999 0 0 10 10000
Now you have two structurally equivalent queries that can be unioned. The dummy zeros don’t affect the aggregation and your result is all three categories no matter what. Hope this works out! There are always a lot of different ways to go about things, so keep an open mind!
There isn’t a goal table. There is only a table with donor ids and amounts, and I created the data item that counts them according to the amount they gave. The level, goal donors by level, and goal amount are also data items. I understand how to do the union, I just don’t understand how to create a query containing level, goal donors by level, and goal amount when they don’t exist in a table to query from. I’m sorry I’m not getting this.
Certainly no reason to apologize! Bad assumption on my part obviously.
Here are a couple of other options to get the data you need even though there is no table for it.
is there a very fast table you can query and RELIABLY get the three rows you need? If so, you can do the same type of case statement to derive the labels (case when keyValue=1 then ‘$5,000 and above’ when keyValue=2 then …). The actual value you are transforming is arbitrary - you just need three distinct values returned from SOMETHING that you can then use to dummy up the labels.
you can union more than two queries together at a time, so just create queries with all hard coded values
’$5,000 and above’, 0, 0, 0, 0
now create another query
’$1,000 - $4,999’, 0, 0, 0, 0
and the third query in the same manner
Union these three queries together along with the actual donor query. You do this by dragging in the union object and then stacking all four queries on it (your three dummy queries and your data query).
Use a sql query object from the toolbox and enter the SQL there.
select ‘$5,000 and above’ as LevelLabel, 0 as GoalDonors, 0 as GoalAmt, 0 as ActualDonors, 0 as ActualAmt
union
select ‘$1,000-$4,999’ as LevelLabel, 0 as GoalDonors, 0 as GoalAmt, 0 as ActualDonors, 0 as ActualAmt
union
select ‘Less than $1,000’ as LevelLabel, 0 as GoalDonors, 0 as GoalAmt, 0 as ActualDonors, 0 as ActualAmt
I almost have this working. I used the first suggestion and my problem is that I am getting two rows for the values that have someone included, one with the actual values, and another with the zero values. How do I get just one row for the groups with actual values?
