Skip to Content

Hi,

I would like to share a way to filter a table by the line numbers of its rows.

Firstly, there are some restrictions on it´s use :

1 – The line numbers in this solution is based on the default ordering and the position of each object in the table elements;

2 – If you re-order or re-arrange the table , you´ll have to alter the formula.

That´s said, , let´s start!

I´ll use a query based on e-fashion that returns [Year], [Month] and [Quantity sold] , which gives me the following table :

/wp-content/uploads/2014/12/table_601097.jpg

I´ll need three variables :

[row], a measure variable . = 1 +( Previous(Self) In ([Year];[Month]), this will give me the number of each line. The secret here is the use of the In

context redefinition. Through the dimensions in the In operator one can set how the rows will be counted. In this case, ordered by [Year] and then by month. If the order is altered ([Month;[Year]) the numbering of the rows will be as the table was sorted by [Month] and then [Year].

The following two variables will be dimension variables, [ln] and [Linenumber].

The first one , [ln] = [row], will hold the value of the lines as a dimension.

The second, [RowNumber]  = NoFilter([ln] ForEach ([Year];[Month];[ln])) ), will be responsible for unically identify each row in the table. The process of unically associating a row number to each row is achieved through the use of the ForEach operator.

Redefining the context through ForEach, in this case means that [RowNumber] can be used outside the block as a standalone dimension

The redefinition of the context must follow the order of the dimension  objects inside the table , if the table is re-arranged, the contexto redefinition in the formula must be altered to consider it.

The report should be filtered by [LineNuber]

The next Picture, shows the table filtered by [LineNumber] between 5 and 10



table 2.jpg


This process, besides being used to filtering has many other uses , for instance creating a dense ranking at report side.



Cheers,

Rogerio

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Dhyanendra Kumar

    Hi Rogerio,

    Thank you very much for this trick. I have found that the approach works fine for Objects from one query. However it fails when we use merged dimension.

    One of the error is Multivalue Where dimension value is coming from both queries.

    -Dhyan

    (0) 
    1. Rogerio Plank Post author

      Hi Dhyan,

      I really dindn´t try it with merged dimensions but I think it should work, can you share the structure of your both queries as well as the table in which you´re trying the approach ?

      Cheers,

      Rogerio

      (0) 

Leave a Reply