How to count Number of Rows in Table Visualization in NSAW Workbook?

Vismay Barot

Although there are several workarounds present, such as Aggregating a Field/Measure and then counting:

Else, exporting into CSV/Excel to see number of rows.

But what is the most optimal solution to this?

Federico Venturin

Hi @User_G1RXQ ,

You should replace 1 with rcount(1) directly in your myCalc calculations

[Deleted User]

Rcount just counts the rows in the result set used for visualizing:

Rcount

This function takes a set of records as input and counts the number of records encountered so far.

RCOUNT(expr)

expr is an expression of any datatype.

Example: select month, profit, RCOUNT(profit) from sales_subject_area where profit > 200

Benjamin Arnulf-Oracle

You can create a new calculation and use the below formulas:

Count(*)

CountDistinct()

Rcount() *—> This one is for running aggregate.

The first viz generated by Auto-Insights is usually creating the calculation for you.

ACE or experts might provide additional guidance.

Vismay Barot

Thanks Ben for your Prompt Response, Count (*) is something I would personally consider because the other 2 basically uses another Column to count.

Now, the problem with Count (*) is, please have a look at this simple example, No Filters and No Complex Calculation, simply pulling Item Name into Table and put a "Calculation" beside it to see

#myCalc is nothing but : COUNT(*)

Technically, I would expect 1, 1, 1, …. but it has "> 1" in some of the cases.

So, I believe any standalone approach isn't available to count Rows (please correct if I am wrong).

Lastly, can you please throw some light on this workaround:

Let's say, in a Calculation, I am putting nothing but "1", then why does myCalc change to "A" rather than a measure "#", because as you can see I cannot summarize this column for Grand Total:

Your help will be highly appreciated 😊

[Deleted User]

In your above image you have "Item Name" and then the count. If the count is bigger than 1 then that means that you have the item 0038 twice for example. The count doesn't invent things :) It's an aggregation based on the available data.

Vismay Barot

Exactly @Christian Berg -Oracle , because Count* is indirectly taking a Column into consideration, which is why I am trying to have a simple workaround that does nothing but simply count number of rows irrespective of any aggregation.

Would you share some insights from this snippet from my previous comment:

Let's say, in a Calculation, I am putting nothing but "1", then why does myCalc change to "A" rather than a measure "#", because as you can see I cannot summarize this column for Grand Total:

If somehow, I am able to summarize this "myCalc" column than no matter what aggregation is, I will be able to see total number of rows in "Grand Total" row.

@Benjamin Arnulf-Oracle please feel free to comment here.

Your help will be highly appreciated 😊

Vismay Barot

Perfect! That's exactly what I was looking for, now I'll treat "myCalc" as an ID Column which is auto-incremental and Grand Total shows total number of rows.

Thank You so much @Federico Venturin , you've optimized numerous tedious-repetitive steps.

Much appreciated 😊

[Deleted User]

Let's take one step back. Oracle Analytics is an Analytics platform as the name implies it will try to count/sum/do whatever math according to aggregation rules and logic.

Here's an example:

You can see that what the engine does is count the number of occurrences of each Product Category with regards to the data stream which is used to generate the visualization.

The fact that I have 920'000 fact records doesn't bother it because it's a dimension-only query. The grain is Product and it finds a total of 72 products with their respective counts of parent category as you can verify when looking at the raw data in the CSV output:

Physically we see this query:

SAWITH0 AS (select count(T62.PROD_NAME) as c1,
     T62.PROD_CATEGORY as c2
from 
     SH.PRODUCTS T62
group by T62.PROD_CATEGORY)

As you can see things like query grains and aggregations are key and critical and from our seats we can't see what you're building how and which granularities come into play.

Vismay Barot

Agreed @Christian Berg -Oracle and appreciate your detailed response in walking me through the back-end. I am actually middle of Report Building from metrics in NetSuite and in data validation with NetSuite Saved Search, the first thing I want to look at is number of rows (with same applied filters). Which is why I wanted something that quickly tells me number of rows, although there is a provision to see Number of Rows and Columns in bottom-right corner, it's not precise:

And looks like adding a simple calculation of rcount(1) does this job.

Once again thank you and hope you continue to have a great day ahead 😊

[Deleted User]

@User_G1RXQ since you posted while I was writing my post:

Yes that's precisely the point. It all depends on query grains and what you are actually counting how. A count(1) will work just as well if the granularity is correct.

If a different query grain is being forced then the results will differ.