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
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.
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:
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.
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
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!
Took a little more noodling but with your help I got it to work...thanks so much
Bret