Total Count of String fields

[Former Member]

Dear all,

I am creating a new report which should include a total number of Lenses grouped by gate numbers. However to calculate it, I have two String fields.

Stock Code 1

Stock Code 2

Now for counting them separately, I did not have any problems, I used

Count(StockCode1)

Count(StockCode2)

Example 1

gate No. ==== StockCode1====StockCode2
1            ====  456           ====  456
2            ====  758           ====  234
3            ====  800           ====  100

Now what I need is the combination of both. i.e (Count(StockCode1+StockCode2)).

Example 2

gate No. ==== StockCode1====StockCode2====TotalStockCode
1            ====  456           ====  456           ==== 912
2            ====  758           ====  234           ==== 992
3            ====  800           ====  100           ==== 900

What I am getting is a grand total number of StockCode1 and 2. For example I am getting 2804 in all rows of TotalStockCode.

So it looks like this

Example 3

gate No. ==== StockCode1====StockCode2====TotalStockCode
1            ====  456           ====  456           ==== 2804
2            ====  758           ====  234           ==== 2804
3            ====  800           ====  100           ==== 2804

What I need is the Total count of Stockcode1+2 individually and not grand total. Just like in my example 2 I need it to work like that.

Any ideas how to go about this?

Many thanks

Regards

Jehanzeb

[Former Member · Accepted solution]

Hi Jehanzeb,

Write a formula u201CTotalStockCodeu201D which will convert the StockCode1 & StockCode2 to ToNumber and placed it in the report.

For example:

NumberVar SC1:= ToNumber({Tablename.StockCode1});

NumberVar SC2:= ToNumber({Tablename.StockCode2});

SC1+SC2;

Hope it helps!!

Regards,

Alpana

[Former Member · Accepted solution]

Alpana When I use your code it says

String is non-numeric.

Any other ideas?

Regards

Jehanzeb

[Former Member · Accepted solution]

Hi Jehanzeb,

I tried to create a similar situation at my end with Xtreme sample database with Customers and Orders table.

Created a group on customer ID (similar to Gate No here)

Then instead of using Count(StockCode1) I inserted the summary on Orders.Orders ID and selected the value for u201CCalculate this Summary as :u201D as u2018Countu2019. And placed it in Group Header.

Same thing is repeated for Count(StockCode2)

After this created a formula where I added these two summary fields and placed in the same Group Header.

And I got the expected results. It is showing the Sum of the two fields.

Hope this would be helpful.

Regards.

[Former Member · Accepted solution]

Aditya,

I may have taken your answer wrong, I did the same however this doesn't work.

I created group by Gate number, I used Count order.no, then I used two summaries, Stockcode1 and Stockcode2. Both showing right results.

Now I created a formula and I inserted both summaries in it

Count ({order_progressed.stock_code1}, {lab_gates.gate_no});
Count ({order_progressed.stock_code2}, {lab_gates.gate_no});

This for obvious reasons doesn't work.

However if I put + in between it gets me Grand total on each row.

Can you please shed some more light on your suggestion.

Many thanks

Regards

Jehanzeb

[Former Member · Accepted solution]

Hi,

Yes, you are right Jehanjeb. And I am really sorry for the confusion. What I said was, u201CI added these two summary fields and placed inu2026u2026.u201D . That is what I mean to say, that I made the addition of these two summary fields in the formula. This formula should return the addition of these two summary fields and then placed this formula in the same group header.

Regards.

[Former Member · Accepted solution]

hi Jehanzeb,

As you have mentioned,

- Stock Code 1 and Stock Code 2 are String fields.

- You have calculated the count of Stock Code 1 and Stock Code 2 individually and placed it in Group Header/Footer.

- The group is based on Gate No.

As I understand the query, you would like to calculate the sum(count(Stock Code 1)+Count(Stock Code 2)).

Let me know if I missed missed out anything.

Please try below formula and let me know if it works for you.

Count({TableName.Stock Code1}, {TableName.Gate No.}) + Count({TableName.Stock Code2}, {TableName.Gate No.})

Place above formula in Group Header/Footer.

Regards,

Alpana

[Former Member · Accepted solution]

Amazing Alpana,

This has worked like a charm ).

I thought when I place any formula in group footer it should be grouped by it automatically, I guess it doesn't hence had to use {table.Gate_no.}.

I have to tell you at some point I thought I am so stupid not to have used "Sum" however that wouldn't have worked like this.

Many thanks

10 Stars for you!

Regards

Jehanzeb