20 Replies Latest reply: May 28, 2014 3:06 AM by Tresesco B

# Count number of rows in an expression label of a Straight Table

Is it possible to return the number of rows in my table as part of my expression label?

My table has conditional dimensions, so depending on what the user selects, it may display aggregate values or transaction level values.  I would like my label to display "Avg Rent / Week" if the value is an aggregate, or just "Rent / Week" if the user has selected transaction level details.

I can determine if it is at the transaction level by comparing the count of rows in the table with Sum(PropertyCount).  If the count of rows is less than Sum(PropertyCount), I will display "Avg Rent / Week", if the count of rows = Sum(PropertyCount), I will display "Rent / Week".

I have tried NoOfRows(), but it always returns 1.

If this does not work, an alternative may be to have two expressions (eg. Avg and Sum) and let the user choose which one to display.

• ###### Re: Count number of rows in an expression label of a Straight Table

try rowno(total) as expression

• ###### Re: Count number of rows in an expression label of a Straight Table

That also returns 1 every time

• ###### Re: Count number of rows in an expression label of a Straight Table

Try rowno()

• ###### Re: Count number of rows in an expression label of a Straight Table

I have tried all combinations of NoOfRecords and RowNo and they all return 1 every time, regardless of my selections.

• ###### Re: Count number of rows in an expression label of a Straight Table

Try the expression =NoOfRows(Total)

• ###### Re: Count number of rows in an expression label of a Straight Table

can attached the sample qvw have a look?

• ###### Re: Count number of rows in an expression label of a Straight Table

If you try to show how many rows in chart table and you have one dimension you can Count(DISTINCT Your_Dimension)

If you have several you can you chose  unique dimension or try something like this

=Count(DISTINCT [Calendar.Year] & Calendar.Month)

• ###### Re: Count number of rows in an expression label of a Straight Table

My table has multiple, conditional dimensions.  I do not know at design time which dimensions the user will be viewing.

This is the basis for needing to know the number of rows.

• ###### Re: Count number of rows in an expression label of a Straight Table

Have you tried using NoOfRows(Total) ?

I know this will work in the body of a chart - but will return '1' if used in the title.

• ###### Re: Count number of rows in an expression label of a Straight Table

tresesco and Clark,

I have tried all combinations of RowNo() and NoOfRows() with and without TOTAL.  The expression label appears to behave the same way as the chart title, and returns 1 in all situations.

• ###### Re: Count number of rows in an expression label of a Straight Table

Hi

select the component,open properties and in windows title use this

='String'& Count(fieldname)

• ###### Re: Count number of rows in an expression label of a Straight Table

just use the count(Dimension You are using)

• ###### Re: Count number of rows in an expression label of a Straight Table

sujeet and Vishwanatha,

As I described in my initial post, I am using a set of conditional dimensions.  The user could display any combination they want to, so those counts will not work.  I am trying to compare the count of rows in the table to count of unique records in the fact table.

• ###### Re: Count number of rows in an expression label of a Straight Table

Michael - can you post a sample QVW so we could see why it isn't working?

I have used NoOfRows(Total) in my apps many times for this result.

• ###### Re: Count number of rows in an expression label of a Straight Table

Hi,

It is showing total nos of rows as per your requirements

Regards,

• ###### Re: Count number of rows in an expression label of a Straight Table

Hi Max,

You are displaying the NoOfRows in the expression, not the expression header.

• ###### Re: Count number of rows in an expression label of a Straight Table

Well, if you want that in the header, you have to use aggr() like:

=Aggr(NoOfRows(Total),YourDimension)

See the attached sample, I have modified the max dreamer's sample file.

Note: In this case it is equivalent to =Count( Distinct YourDimension)         // when single dimension is used in the chart

• ###### Re: Count number of rows in an expression label of a Straight Table

The dimensions are conditional.  You cannot know at design time which ones the user is going to select.

That is the reason for the original requirement.

• ###### Re: Count number of rows in an expression label of a Straight Table

Then, you wont get directly using aggr(). May be some tricky way that depends on your calculated dimension expression could help. Try to post your sample app.