How to resolve the incorrect aggregated values resulting due to formula(count) in webi?

[former_member188628]

Hi All

      Please help me out in resolving the following problem( Well this post may seem repetitive,but I am stuck).   

Following is my sql requirement in (Query in sql server) along with output:-

Following is the corresponding BO-variable formula:

Following was the overall BO output :-

The TOPN results:-

This output is not what is expected as in the sql server, It is displaying incorrect count. The actual count for that job is 1119. Well Got this using custom sql but need to implement by the webi functionality. Please help on this Gurus

[amitrathi239 · Accepted solution]

try this.

change your variable formula and try.

=Sum(Count([RunId])) in ([JobName];[Status])

or try this.

=Sum(Count([RunId];All)) in ([JobName];[Status])

[former_member188628 · Accepted solution]

Hi Amit 

Very much thankful for the quickest response.It worked.Thanks One more request, its displaying the overall aggregated count repetitively,only if I use TopN results, the outputs will be displayed like an aggregated value??

[amitrathi239 · Accepted solution]

share your table screenshot where values are repeating..try with Foreach function instead of IN in the variable.