Count Cost Center Code

nverma

For audting purposes, i need to create a report that will show me how many times a particular cost center was used in since the beginning of last year(2011) in G/L Entry.

G/L Entry - OnPreDateItem 
ReferenceDate := 010111D;

G/L Entry - OnAfterGetRecord
"G/L Entry".SETFILTER("Posting Date", '%1..%2',ReferenceDate,TODAY);

G/L Entry, Body - OnPreSection

IF "G/L Entry".FINDSET THEN REPEAT
 // IF

    //CurrReport.SHOWOUTPUT(FALSE);

UNTIL "G/L Entry".next =0;

How can i filter on it so it only shows a particular cost center code once, rather than every time it shows up in the G/L Entry.

DenSter

Don't put data code in section triggers. Section triggers should only be used for managing display properties.

What do you need? First you say you need to count how many times a cost center is used, then you ask how to show unique cost centers.

Again, you need to think about this analytically. Instead of looping through the G/L Entry table, loop through the cost centers and filter the G/L Entry for each one. Count as you go along.

You need some figuring out skills, don't you have a senior that can help you?

nverma

Basically I need to go thought the G/L Entry data (table) and each line has some Cost Center Code assigned to it.

There are thousands of lines in G/L Entry table, so some of the them have the exact same cost center code.

Rather then displaying the same cost center code over and over again. I just want to display how many times a particular Cost Center Code was used/assigned.

I am just not sure how I can filter this to display that Cost Center once in the report and right beside that, it should display how many times it appeared.

DenSter

There is no command in C/AL that shows you the unique cost centers, similar to the SQL statement "SELECT DISTINCT".

You're working on a report, so you have to figure out a way to do this in a different way. My suggestion is to loop through the Cost Center table, and filter the G/L Entry for each record, and only show the ones that have details to show.

Look I'm not trying to make things difficult for you, but you are asking the forum to do the work for you. You've been told to study the DEV training materials many times, and still you are asking questions that are covered in those training courses. You need the help of a senior person to show you these basic skills, this forum is simply not the right place to build those skills.

nverma

I am going to try doing it with Cost Center Tale as the dataitem.

I have read the introduction book numerous times. But, I feel that you get good at something with practice/experience. Some of the Users here have years of experience and I have like 3-4 months of experience. Its not like I am lazy or anything, I am still in the learning phase . I will try not to ask too many questions.

nverma

Solved!

// - ADD Start
// Forces the GLEntry to display data where posting data is between Jan 1st 2011 to current date
ReferenceDate := 010111D;
GLEntry.SETFILTER("Posting Date", '%1..%2',ReferenceDate,TODAY);
// - ADD End

Cost Center - OnAfterGetRecord()
//- ADD Start
// Filters on the Cost Center Code and if it is not found in the GLEntry, then it skips it
GLEntry.SETRANGE("Cost Center Code", "Cost Center".Code);
IF NOT GLEntry.FINDSET THEN
  REPEAT
      CurrReport.SKIP;
  UNTIL GLEntry.NEXT=0;
//- ADD End

DenSter

I don't think you read it very carefully, because you're asking very rudimentary questions about simple reporting mechanics. There are plenty of reporting exercises in both training manuals that cover grouping, the flow of a report, properties like PrintOnlyIfDetail. From some of the questions you've been asking, you clearly have no clue about any of that. I'm not saying this is a bad thing, but you clearly did not get a lot out of the training material.

Did you do the exercises? If you weren't able to do the exercises, did you get help from a senior?

DenSter

nverma wrote:

Solved!

// - ADD Start
// Forces the GLEntry to display data where posting data is between Jan 1st 2011 to current date
ReferenceDate := 010111D;
GLEntry.SETFILTER("Posting Date", '%1..%2',ReferenceDate,TODAY);
// - ADD End

Cost Center - OnAfterGetRecord()
//- ADD Start
// Filters on the Cost Center Code and if it is not found in the GLEntry, then it skips it
GLEntry.SETRANGE("Cost Center Code", "Cost Center".Code);
IF NOT GLEntry.FINDSET THEN
  REPEAT
      CurrReport.SKIP;
  UNTIL GLEntry.NEXT=0;
//- ADD End

Good first step, but as a final solution that's not good at all.

Don't use FINDSET, use ISEMPTY. You don't need to actually loop through the G/L Entry records, all you need to know is whether records exist, like this:

GLEntry.SETRANGE("Cost Center Code", "Cost Center".Code);
IF GLEntry.ISEMPTY THEN
  CurrReport.SKIP;

You could also add a G/L Entry dataitem, link it through the cost center code, and turn on the cost center dataitem's PrintOnlyIfDetail property, and you wouldn't need to add any programming at all.

nverma

DenSter wrote:

nverma wrote:

Solved!

// - ADD Start
// Forces the GLEntry to display data where posting data is between Jan 1st 2011 to current date
ReferenceDate := 010111D;
GLEntry.SETFILTER("Posting Date", '%1..%2',ReferenceDate,TODAY);
// - ADD End

Cost Center - OnAfterGetRecord()
//- ADD Start
// Filters on the Cost Center Code and if it is not found in the GLEntry, then it skips it
GLEntry.SETRANGE("Cost Center Code", "Cost Center".Code);
IF NOT GLEntry.FINDSET THEN
  REPEAT
      CurrReport.SKIP;
  UNTIL GLEntry.NEXT=0;
//- ADD End

Good first step, but as a final solution that's not good at all.

Don't use FINDSET, use ISEMPTY. You don't need to actually loop through the G/L Entry records, all you need to know is whether records exist, like this:

GLEntry.SETRANGE("Cost Center Code", "Cost Center".Code);
IF GLEntry.ISEMPTY THEN
  CurrReport.SKIP;

You could also add a G/L Entry dataitem, link it through the cost center code, and turn on the cost center dataitem's PrintOnlyIfDetail property, and you wouldn't need to add any programming at all.

I have never used...isempty ( i will read about it...and then give it a shot).