RESOLVED - Returning Null values in a list

[bpothier]

I have tried everything I can think of to get this to work

Here is what I am trying to do

Report looks at sales across 3 reps
only 1 of the reps has a sale so only that rep shows up on the report

I would like to the other 2 to show up as well only showing 0s

I have my data items, looking at if opty then count else 0
I tried using a separate query to just pull the names then use an outer join but it doubled all of the results

I tried changing the Aggregate to Calulated but again it removed the other 2 reps.

Not sure what I am doing wrong. Any help would be greatly appreciated

Bret

[adam_mc]

Try using a second Query and UNION it with the first.

In the second (Get Zero's) Query:
Have the same "Group By" elements (Rep, etc...) and force all the measures to be zero.
Set all measures to be Aggregated as Totals in both queries.
Note: In both driving queries of the union, data items should be defined in the same order and have the same number of data items in both.

When working with the Union Query, you should then get actual sales for the Rep1 and zero sales for Rep2 & Rep3.

I have found working with Unions rather than Outer Joins to be much more simple.

Hope this helps.

[bpothier]

Thanks for the reply Adam_mc.

I tried that and I get this error. Probably something simple I am just not wrapping my head around





The report server encountered an internal error. Check additional information associated with this error message. If cause of problem cannot be ascertained, increase the logging level in the IBM Cognos administration tool and reproduce the conditions that caused the error. If the problem persists, see the problem determination information on the IBM Cognos Support Portal page at http://www-947.ibm.com/support/entry/portal/Overview/Software/Information_Management/Cognos_Business_Intelligence.

     Details
CCLAssertError:0:Fatal: CCL_ASSERT(result);RSV-SRV-0042 Trace back:RSReportService.cpp(762): CCLAssertError: CCL_CAUGHT: RSReportService::process()RSReportServiceMethod.cpp(263): CCLAssertError: CCL_RETHROW: RSReportServiceMethod::process(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(808): CCLAssertError: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(260): CCLAssertError: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(864): CCLAssertError: CCL_RETHROW: RSASyncExecutionThread::processCommand(): asynchRunSpecification_RequestRom/RSRomNode.cpp(3701): CCLAssertError: CCL_THROW: 

[adam_mc]

Not sure that I can follow the error you are getting!

Does the report validate in Report Studio prior to running?

The most common error I've experience with Unions is not having the same number of Data Items in the driving Queries.

What do each of the queries look like?
They should look something like below:

Get Rep Sales Query:
---------------------------
Region
Rep
Sls Measure1
Sls Measure2

Returning
North, 1, 9999, 999

Get Rep Query (Zero Sales):
-----------------------------------
Region
Rep
Sls Measure1 (force to zero)
Sls Measure2 (force to zero)

Returning
North, 1, 0, 0
North, 2, 0, 0
North, 3, 0, 0

Ensure that each of the driving queries has the same number of data items in the order in which you wish them.
In my example above (in both queries) Region and Rep will have Aggregate of None, and Sls Measure 1 & 2 will have Aggregate of Total.

Then when you create a list on your page over the Union Query, you should see:

North, 1, 9999, 999
North, 2, 0, 0
North, 3, 0, 0


Hope that helps.

[bpothier]

can;t seem to attach a file to this post - get path error. I will see if I can type it in some kind of understandable way

Data Item
Employee Full Name            Set to NONE

Executive Call               All look similar to this and are set to NONE
Closing Call                  if ([Activity Type] = 'Executive Call')
Scheduled Call             then ([Activity Count])
Cold Call                      else (0)

Plan               Hardcoded to 8 and set to AUTOMATIC

Actuals           [total Scheduled Call]+[total cold call] + [total closing call]+ [total executive call]       Set to AUTOMATIC

% Plan  [Actuals]/[Plan]   set to AUTOMATIC

total scheduled call        All look similar to this and are set to AUTOMATIC
total cold call                  total([Scheduled Call])
total Closing call
total executive call

[adam_mc]

Try moving things around as follows:

Get Rep Sales Query:
---------------------------
Employee Name (NONE)
Executive Call    (TOTAL)
Closing Call       (TOTAL)
Scheduled Call  (TOTAL)
Cold Call           (TOTAL)

Get Rep Query (Zero Sales):
-----------------------------------
Employee Name (NONE)
Executive Call    (Force to Zero - TOTAL)
Closing Call       (Force to Zero - TOTAL)
Scheduled Call  (Force to Zero - TOTAL)
Cold Call           (Force to Zero - TOTAL)


Then Union these two together and add calculations for remaining Calculations in the final Union Query (I may have the aggregations on the calculation slightly off based on your needs, but I'm assuming you want these done on all the aggregated values in the Union Query):

Union Query):
-----------------------------------
[Union1].Employee Name (NONE)
[Union1].Executive Call    (TOTAL)
[Union1].Closing Call       (TOTAL)
[Union1].Scheduled Call  (TOTAL)
[Union1].Cold Call           (TOTAL)
Plan                                (Set to 8 - CALCULATED/AUTOMATIC)
Actuals                           (TOTAL/CALCULATED)
%Plan                            (CALCULATED)

Hope this helps!

[bpothier]

Took a little more noodling but with your help I got it to work...thanks so much

Bret