Diferent Count Columns in the same Dataset in Queries

MarkKs

Hello! So I'm here with a problem (might not be a big deal for you) with a development i'm actually doing. I have to create a chart for Productivity so i'm using tables 5772 and 5773 to count Picks and Put-Aways per worker. I'm trying to this with a table and with a query (testing both ways), but with a query seems to be more efficient, as I said I need to count the Picks and Put-Aways per worker so I created two columns to count the records (lines per header) and I can't find a way to count only the picks in one column and count the put-aways in other both under the same dataset. Here's the actual dataset:


(Currently both count colums do the same thing and count all the lines, and that's what I don't want, they must be different)

With a table, I tried to write to code for it and well worked for Put-Away with 341 lines inserted and the count field summed, but with the Picks there's workers that have over 100000, so this would mean low performance and if I i'm right there's no way of DISTINCT the records like in SQL using tables in NAV. I could create new keys for the tables but in this moment I'm not allowed to do that.

For me using a Query in this case would be better, hope you can help me or atleast give me suggestions.

Thank you!

Slawek_Guzek

In this case you cannot use COUNT. You need SUM, but then you need to sum up two different fields, which means that you would need to modify standard object, insert two custom fields, and add some code populating them.

Alternatively you can use two separate query objects, and combind the data in some temp table.

Alternatively you can use .NET, create a separate connection to the same database, fire a SQL query whatever you like, and bring in the results. See codeunit 104050 - SQL Mgt. in upgrade toolkit fob, one handling dimesnsions update from pre 2013 version. You should find them on installation DVD

Slawek

Slawek_Guzek

Hi,

Nope, not possible with queries. Not like that

The query generates simple COUNT(*) or SUM(..) aggregates, so if you aggregate on one level/one data item you won't get different COUNT results.

Try to build the qury like on the picture below, and use different links on sub data items 1 & 2



(disclaimer - haven't tried tham myself )

Slawek

MarkKs

I tried that solution and didn't work because the query does not support these two datasets in the same level, in different levels with filters no data was returned. I'm still looking for a way of doing this, If anyone knows something about it...

Slawek_Guzek

In this case you cannot use COUNT. You need SUM, but then you need to sum up two different fields, which means that you would need to modify standard object, insert two custom fields, and add some code populating them.

Alternatively you can use two separate query objects, and combind the data in some temp table.

Alternatively you can use .NET, create a separate connection to the same database, fire a SQL query whatever you like, and bring in the results. See codeunit 104050 - SQL Mgt. in upgrade toolkit fob, one handling dimesnsions update from pre 2013 version. You should find them on installation DVD

Slawek

MarkKs

Thank you for all the help, but unfortunately a table or query for the chart it's all I can use in another words my resources are very limited for this work.