none
SSRS how to get the Tablix count

    Question

  • I am applying dynamic conditions to visible the Row in the tablix. say suppose my dataset contains 10 records. i just applied some condition on tablix group visible property. after i got the results in tablix as 2. now how is it possible to get the number of records in the table. please let me know if you still not understand the query

    Thanks in advance.

    Friday, May 10, 2013 7:27 AM

All replies

  • Hi,

    Try using CountRows function  - http://msdn.microsoft.com/en-us/library/ms156330%28v=sql.100%29.aspx

    =CountRows("MyDataset")


    sathya --------- Please Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    Friday, May 10, 2013 7:36 AM
    Moderator
  • please read my question properly. becuase i had mentioned iam getting only 2 rows after filteration. so i should get the output as 2

    Rassal

    Friday, May 10, 2013 9:21 AM
  • please read my question properly. becuase i had mentioned iam getting only 2 rows after filteration. so i should get the output as 2

    Rassal

    Hi,

    I would suggest to try and get row count accordingly from the query itself in this case

    For example:

    Two tablix with same dataset after and before applying visibility property

    Visibility expression :

    =IIF(IsNothing(Fields!Empl_X.Value),True,False)

    And i have used below query to get rows count

    select Empl_C,Empl_X,(SELECT COUNT(*) from TestTable WHERE Empl_X IS NOT NULL) RowsCount FROM TestTable

    Try like above example or wait for better approach :)


    sathya --------- Please Mark as answered if my post solved your problem and Vote as helpful if my post was useful.


    Friday, May 10, 2013 1:03 PM
    Moderator
  • still i could not resolve this problem. last method also did not work for my requirement.

    Rassal

    Tuesday, May 28, 2013 4:23 AM
  • I assume you want to display the tablix row count in a textbox outside the tablix?  It can be done but you need to use a small trick (hidden column to be referenced outside).  Here's how that works.

    First you need to add an additional column to the tablix.  Set the Hidden property to True to hide it.

    Put the following expression in the header cell: =CountRows()

    It's important to do that in the header cell which is completely outside all groups, otherwise it can't be referenced.

    Give the header textbox a clear name (Name property), for instance txtCount.

    Now use the following expression in a textbox outside the tablix: =ReportItems!txtCount.Value

    That textbox will now display the number of rows when rendered.


    MCITP SQL Server 2008 (BI & DEV); MCTS SQL Server 2008 (BI, DEV & DBA)
    Check out my articles at BI: Beer Intelligence?


    Tuesday, May 28, 2013 6:31 AM
  • Similar thing i've done it by reffering to internet. how i've done is

    1) added new textbox outside of the tablix, for instance txtCount

    2)tried to use similar expression. but i got error like "could not accessible ReportItems object"

    i just want ensure, am i done the same way what you explained? else Header Text is refferring to tablix header text? please do clarify. thanks


    Rassal

    Tuesday, May 28, 2013 6:40 AM
  • What you've done is not the same as what I've explained.  When you add a new Table to a report, it consists of two rows: the header row and the data (details) row.  The header cell (textbox) to which I refer is any of the textboxes in that header row.


    MCITP SQL Server 2008 (BI & DEV); MCTS SQL Server 2008 (BI, DEV & DBA)
    Check out my articles at BI: Beer Intelligence?

    Tuesday, May 28, 2013 6:54 AM