Link to home
Create AccountLog in
Avatar of Butler Bros
Butler BrosFlag for United States of America

asked on

Only getting the 1st record in a recordset in SSRS

HELP!

I can't I seem to add the appropriate grouping, to simply allow for all records to be seen..
My SSRS report is returning only the 1st record..
The query within SSRS  is returning 276 records in query designer..
e.g. right click Dataset, "Query", and feed the query the two parameters it needs.
I have a number of fields that are all inside of a tablix control..
And so I dragged my field to group on, down to "Row Groups"
I went into that Group Properties, and chose to insert a Page Break "Between each instance..."
Same result.
I then right clicked that group, and chose "Add group / Child Group"
I chose "Show Detailed Data", hit OK, re-ran report, and am getting only 1 record...every time

I cannot figure out what I'm doing wrong...

Thanks,
Rich
Avatar of Noah
Noah
Flag of Singapore image

Hi there! :)

Is this in a text box? If so, you need to edit the grow property for the textbox. You may refer to the following link provided by the official Microsoft Support team.

Reference: https://docs.microsoft.com/en-us/sql/reporting-services/report-design/allow-a-text-box-to-grow-or-shrink-report-builder-and-ssrs?redirectedfrom=MSDN&view=sql-server-ver15
Avatar of Butler Bros

ASKER

Hi Noah,
Is this in a text box? If so, you need to edit the grow property for the textbox. You may refer to the following link provided by the official Microsoft Support team.
Is what in a text box?
If the output is supposed to appear in a text box, you will need to enable the grow property.
>> And so I dragged my field to group on, down to "Row Groups"

Just to start from the beginning, kindly try removing the Grouping option and check whether you are able to see all those 276 records or not..
If so, then your tablix is fine and the grouping is having some issues.
Kindly check and let me know so that we can proceed or further troubleshooting.
I ran it with no grouping.
It took about the right amount of time to process, as if it was grabbing the 276 records it's supposed to..
But it only displayed the 1st one..

hmmm

Rich
ok, additional info:
I just ran the query from Dataset/QueryDesigner..using the same 2 parameter values, and got all 276 records..
So, it would suggest something is amiss in my report...structure perhaps?
But I've compared this report with another working similar report, and it looks as if it's configured the same as far as
the tablix & grouping etc..
stumped here..
>> I just ran the query from Dataset/QueryDesigner..using the same 2 parameter values, and got all 276 records..
>> I ran it with no grouping., But it only displayed the 1st one.

Since you are able to see the data from DataSet and not in the tablix even after removing grouping, then I suspect some problem with your tablix configuration.
Kindly try deleting your existing tablix and create a new one to see how it goes..
Kindly share more info on how you wish to group the data by giving some sample data so that I can suggest you the best way to do the grouping..
To start with try the default one by right clicking on the cell on which you wish to implement Grouping and then Select Add Group -> Row Group -> Adjacent Above -> Show Details Data.
Let me look into that.
Was hoping to avoid that measure, as it's got a lot of work into it, but if that's what's needed...

I'll report back with my findings..

Rich
ok, Deleted the tablix.
Added  a new "List"
dragged 3 fields onto it(first field is the item I'm grouping by)
That field is set as the Row Group, "Group By" item.
In Group properties, I set 'Page Breaks' to "Between Each Instance.."
Here's the thing as it looks :
User generated image
Here's my Group Properties:
User generated image
When I run it, I see only the 1st record...
And again, if I go into query design, and feed it the same parameters, it returns all 276 rows....
WTH am I doing wrong!?
~sigh

Rich
ok, just created a NEW report, dropped a List item, dropped some fields, set the Row Group to have a Page Break after,....
and it works...
so, no clue...sadly
but, thanks for your help,
Rich
ASKER CERTIFIED SOLUTION
Avatar of Butler Bros
Butler Bros
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Just noticed in your comment, there was a snapshot with fields header_number, invoice_date and customer_id_no arranged in ROW manner instead of Column manner.. Place those as Columnar values to get all the values..
 https://www.experts-exchange.com/questions/29166708/Only-getting-the-1st-record-in-a-recordset-in-SSRS.html?anchorAnswerId=42998295#a42998295