Story Report in SuccessFactors- I am trying to get count of objectives every employee has.

[PrinceThomas]

I created a data base with #Story and want to have aggregate type count for objective ID so that i can see for each employee row how many objectives they have>
Eg 

Name Department objectiveCount  totalweightage

AAAA  Marketing          4                    100%

BBBB  Sales                5                     120%

This was easy in table report but in story report query builder the aggregate type is missing count. 

[PrinceThomas]

I tried this and somehow it still doesn't work. I created table widget as a cross tab. added row dimensions and then added column dimensions i.e. objective Id and weight. This should have auto calculated columns and sum. I also trid creating cross calculation colun then selected aggregation type as Sum and dimension as weight. even this doens't show correct value. .

[Olga_Antipina]

Firstly, you build a table with employees and goals in the Designer mode with a Table widget, not in the Query.
Secondly, when you create a table widget, just add Employees (and other data needed) as a Dimension and a relevant Measure (Count or any custom built measure) and it’s totally possible with the Agregated type of table and the Cross-tab type. If you need to have also %, you need to create an additional measure for that first and then add it to your table. Everything is done in the Designer mode (not Query).

[PrinceThomas]

I tried this and somehow it still doesnt work. I created table widget. Added row diemnsions.

[PrinceThomas]

I tried this and somehow it still doesn't work. I created table widget as a cross tab. Added row diemensions. Added column dimensions as objective Id and Weight. Basically adding objective Id and weight as column dimension should automatically count and sum weightage right? This doesn't seem to work.

[PrinceThomas]

Finally i figured ut the issue. I believe this is the cause. The relationship between objective and basic user table is set as Left Table (Audit sender )= Right Table (User ID). It should ideal be User Id = User Id. I am wondering how to fix it.

[Olga_Antipina]

Only Employee name and Job info should be as a Dimension added to the rows. Into columns you must add 2 measures: Weight (can come as a measure from the Query) and Number of goals (it's a custom measure that you need to create: Use type of calculation "Aggregation" -> Count dimension by Objective ID.

[PrinceThomas]

Hello Olga, Thank you for info and I again tried the solution you gave. to be clear and if i am understanding correcting. While building query Ileft user information as dimension. I set weight as Measure. I also set Objective Id as measure.

[PrinceThomas]

Hello Olga, Thank you for info and I again tried the solution you gave. To be clear and if I am understanding correctly your suggestion. While building query I left user information as dimension. I set weight as Measure. I also set Objective Id as Dimension. Then went to designer mode, added table widget. added user information as dimension. selected cross tab and added weight(measure) as column dimension and this worked. Now added calculation and selected type Aggregation, gave a name for calculation. Under properties selected count dimension and selected aggregation dimension as Objective ID.

[PrinceThomas]

Hello Olga, Thank you for info and I again tried the solution you gave. To be clear and if I am understanding correctly your suggestion. While building query I left user information as dimension. I set weight as Measure. I also set Objective Id as Dimension. Then went to designer mode, added table widget. added user information as dimension. selected cross tab and added weight(measure) as column dimension and this worked. Now added calculation and selected type Aggregation, gave a name for calculation. Under properties selected count dimension and selected aggregation dimension as Objective ID. With this new issue is anyone with null or no objective is showing s 1. I want it to show as 0. As this is NOT the solution I tried another option to convert objective Id as measure instead of dimension. I then went to table widget then add calculation. again use Agrregation, Count excl null measure objective ID. now it asks aggregation dimension here. I don't get obejctive ID as its measure if I select user Id or Business Unit the count is as 1 even for employees with 5 objectives. Also please stop asuming that i didn't go through training. i did go through story reporting administration training and job aids multiple times. the issue with training is they just teach simple things like 2+2 =4 but don't teach If (A+B/C*D) greater than equal to1 then count or sum etc.. or to get % of employees in business unit with atleast 1 objective kind of scenario.