[How to group data already group in mongoDB]

Hi all,

Hope somebody could help me with this topic... I have a mongoDB query based in Objects as "Ticket" structure, each ticket usually contains Tasks Object and each of them have an Owner, if the Ticket is Open, the associate Owner is “OpnPrps.CurAgtNme”, by other hand if the ticket is Closed, the associate Owner is “Nms.CloAgt”.

This is how my database (JSON) looks:

 "result" : [

        {

            "_id" : NumberLong(3131032306336),

            "TicId" : 1147552,

            "OrgId" : 729,

            "Sts" : "Closed",

            "CrtDat" : ISODate("2015-04-23T18:50:46.000Z"),

            "CloDat" : ISODate("2015-04-23T19:46:26.000Z"),

            "ShtDes" : "Copy of Employment agreement",

            "Des" : "EE wants a copy of employment agreement. address was verified.",

            "DesSum" : "EE wants a copy of employment agreement. address was verified.",

            "Sol" : "ISIGHT TICKET NUMBER:<br><h1>US-04-15-01109</h1>",

            "CrtAgtId" : 20444,

            "CloAgtId" : 20149,

            "CrtApp" : null,

            "IsInt" : false,

            "HasPrtTsk" : null,

            "PexBodId" : "",

            "PayGrp" : "",

            "RvwDat" : null,

            "RclDat" : null,

            "IsDynDueDatDef" : false,

            "OlaDat" : ISODate("2015-04-25T00:50:50.000Z"),

            "SlaDat" : ISODate("2015-04-25T00:50:50.000Z"),

            "DynDueDatElaDat" : null,

            "ReaTim" : "00:00:00",

            "LstUpd" : ISODate("2015-04-23T19:46:26.000Z"),

            "VrsOnl" : 2,

            "VrsArc" : 1,

            "OpnPrps" : null,

"Nms" : {

                "Cmp" : "Organization1 US",

                "Org" : "Organization1",

                "Srv" : "Policies",

                "SrvGrp" : "17. Workforce Administration (NGR)",

                "Wkg" : "WORKGROUP1",

                "Pri" : "",

                "Tir" : "T1",

                "Src" : "Call",

                "CrtAgt" : "Arun ",

                "CloAgt" : "Felicia"

            },

"Olas" : {

                "_id" : 2,

                "EntNam" : "ENTITY",

                "DueDat" : ISODate("2015-04-25T00:50:50.000Z"),

                "AmbDat" : ISODate("2015-04-24T20:50:49.000Z"),

                "DueDatElaDat" : null,

                "AmbDatElaDat" : null,

                "SlaDuration" : 18,

                "TotTim" : NumberLong(0),

                "TotTimPndEnt" : NumberLong(0),

                "TotTimPndEmp" : NumberLong(0),

                "RclInSla" : false

            },

"Tsks" : {

                "_id" : 1,

                "Typ" : "Planned",

                "CrtDat" : ISODate("2015-04-23T18:50:46.000Z"),

                "CloDat" : null,

                "LstUpd" : ISODate("2015-04-23T18:50:46.000Z"),

                "DueDat" : ISODate("2015-04-25T00:50:50.000Z"),

                "TimCplTsk" : 1080,

                "DueDatEla" : false,

                "AgtOwnId" : null,

                "WkgSklId" : 45387,

                "EntId" : 2,

                "Sts" : "Open",

                "PrdTskId" : 201,

                "Ttl" : "Provide Navigational Assistance",

                "Des" : "Provide Navigational Assistance",

                "SrvSklId" : 45792,

                "PriSklId" : null,

                "TotTim" : 0,

                "PtnId" : null,

                "PtnTic" : null,

                "DepTskId" : null,

                "IsAct" : true,

                "IsMndOnCloTic" : false,

                "Nms" : {

                    "AgtOwn" : null,

                    "Wkg" : "CM_T1",

                    "Ent" : "ENTITY",

                    "SrvSkl" : "Policies",

                    "PriSkl" : null,

                    "Ptn" : null,

                    "Frm" : ""

                },

                "AscTicItm" : null,

                "FrmId" : null,

                "Flds" : []

 

            },

 

And Query in mongodb looks like:

db.tickets.aggregate([

{$match:{

    'Nms.Org': 'Organization1',

    'Nms.Cmp':'Company',

    'Nms.Wkg':'Workgroup’

 

}},

{$project:{

    _id:0,

    'Tsks._id':1,

    'Tsks.Sts':1,

    'Tsks.DueDat':1,

    'Sts':1,

    'Nms.Org':1,

    'Nms.Cmp':1,

    'Nms.Wkg':1,

    'Nms.CloAgt':1,

    'OpnPrps.CurAgtNme':1,

    'OpnPrps.CurEntNme':1,

    'Olas.EntNam':1

    }},

 

{$unwind : "$Olas" },

{$unwind : "$Tsks" },

{$match:{$and:[{

    'Tsks.DueDat': {$ne: null},

    'Olas.EntNam': 'ENTITY',

    'Tsks._id':{$gt:0}

    }]}},

   

    {$group:

{_id:{

            Org:'$Nms.Org',

            Cmp:'$Nms.Cmp',

            Wkg:'$Nms.Wkg',

            DueDate:'$Tsks.DueDat',

            CurEntNme:'$Olas.EntNam',

            CurTskId: '$Tsks._id',

            DueDate:'$Tsks.DueDat',

            Owner1:'$OpnPrps.CurAgtNme',

            Owner2:'$Nms.CloAgt'

            },

            All: {$sum: { $cond:

                         [

                            {   $eq: [ '$Tsks.DueDat' , null ] } ,0,1

                         ]}}

               

        },

       

    }

])

With this query I’m able to get every ticket for every owner (even if they’re open or closed) and their associate tasks, my problem is that I want to group all the tickets withouth taking in consideration the status , something like a second group after the results I have now, see below:

The table I get with jasper studio with this query looks like:

Owner       Inventory  

---------   --------------

Noemi          1                   Owner1:Noemi | Owner2:null

Carl           2                   Owner1:null | Owner2:Carl

Darla          2                   Owner1:Darla| Owner2:null

Carl           1                   Owner1:Carl| Owner2:null

Paola          2                   Owner1:null| Owner2:Paola

Noemi          2                   Owner1:null | Owner2:Noemi

As you can see, I’m getting repeated values due to the different field from each ticket. The table I'm looking for should be like this:

 Owner       Inventory  

 ---------   --------------

 Noemi          3                   Owner1:Noemi | Owner2:Noemi

 Carl           3                   Owner1:Carl | Owner2:Carl

 Darla          2                   Owner1:Darla| Owner2:null

 Paola          2                   Owner1:null| Owner2:Paola

So, my problem is that I can’t found the way to group these results again to obtain the second table. Any ideas please?

Thanks in advance. 

Kind regards.

[Conditional expresion in a dataset parameter]

Ok I found the mistake myself, the problem was I missed two { } between objects.

$and:[{"OpnPrps.CurAgtNme":null},{"Nms.CloAgt":null}] 

[how to get sum of field value]

Hello, 

Do you want to add only the total for each column or do you want to add both columns and rows?

[Conditional expresion in a dataset parameter]

Hi all,

I have a .jrxml report connected with MongoDB. Inside this reports I have a table with many Dataset parameter in order to create dinamically the query, unfortunately I have problems with one of this parameters, it looks like:

($P{Owner}==null || $P{Owner}.equals(""))

?new String("")

:($P{Owner}.equals("Pool"))

?"$and:["OpnPrps.CurAgtNme":null,"Nms.CloAgt":null]"

:"$or:["OpnPrps.CurAgtNme":""+$P{Owner}+"","Nms.CloAgt":""+$P{Owner}+""]"

 

My problem is that when I preview my reports, the $and, $or part of my query can't be executed and I have the following error:

 

net.sf.jasperreports.engine.JRException: com.mongodb.util.JSONParseException: 

{

'findQuery' : {

"OlaDat":{'$lte':{ "$date" : 1434438846714},'$gt':{ "$date" : 1402902732089}},

$and:["OpnPrps.CurAgtNme":null,"Nms.CloAgt":null],

"Nms.Src":"e-mail"

}

 

Is there any way to change this conditions in order to get evaluable values inside a dataset parameter?

Any help,please?

 

Thanks in advance.

Kind regards.

[Conditional report values]

Thank you, however,I'm using MongoDB, and also is not quite easy, I mean, I need to find a way to configure a conditional param in the subquery of my details in order to get the values I'm searching.

[Need help with a Crosstab (columns are a field)]

Hi Albert,

I developed many crosstabs, I hope my screenshoot could help you configuring yours. See below:

 

Kr, 

[When Key is Null. How to ignore it???]

Hi, 

Usually I solve this problem with a conditional expresion, something like this:

IF($F{FieldName}==null,"Text",$F{FieldName})

or

($P{Parameter}==null || $P{Parameter}.equals(""))?new String(""): ""Attibute":""+$P{Parameter}+"","

Kr,

[Conditional report values]

Hi! 

Ok, I will try....

I have the table from Image 1, And when I click in "Pool" value, itdrilldown many details in other table, this drilldown is sorted by this Owner, however, when Pool (Pool means all the null/empty values), the reports is being sorted by any data (all the inventory) but I want to sort them with Owner == null. 

[Conditional report values]

Hi all, 

I hope you can help me. Let me explain this bug as well as I can:

 

1)I have a report sorted by parameters, the primary is the "Owner", the table looks like:

 

As you can see, there's a "Pool" owner, this owner comes because this condition in Text Field:

IF($F{_id.Owner}==null,"Pool",$F{_id.Owner})

2)Clicking any value, they will pass to another report, with some parameters associated to each "Ticket (Creation date, due date, name of owner, description...) This report works perfectly except  when I try to drilldown this "Pool" Owner, when I drilldown it, the reports with details is returning all the repository, I mean, all the "tickets" associated to any owner, and what I want to see is all the "unassigned tickets", which could have the owner name as empty or null or white space...

I have a condition in the Dataset Table report parameter like: 

($P{Owner}==null || $P{Owner}.equals(""))?new String(""): ""OpnPrps.CurAgtNme":""+$P{Owner}+"","

I'm thinking that maybe the problem is the initial condition $P{Owner}==null || $P{Owner}.equals(""), and this equals("") is not getting correctly all the values but I'm not sure. 

Thanks & kind regards.

AlbaO.