• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 881
  • Last Modified:

ADDING BLACK ROWS TO A SSRS REPORT LAYOUT TABLE BASED ON THE NO OF RECORDS RETURNED BY A DATASET

Hi Experts
In ref to adding rows to a table in a SSRS report layout, I have a dataset and the output of that dataset is used to populate a table in the report layout. The issue is the dataset can have a varying no of records output. I want to keep this static in the layout, i.e. I want assign a fixed 25 rows to the output of this dataset. So for eg. if there are 10 records in the dataset, then I want the layout table to display these 10 records and also show (25-10=)15 blank rows. So no matter how many records are returned by the dataset the report layout table always has a fixed 25 rows for display.
Now how can this be achieved at run time/dynamically? thanks in advance
0
kalbal
Asked:
kalbal
1 Solution
 
planoczCommented:
I have not seen what you are talking about in SSRS. But you could try grouping your data and do a page break per group.
Or You might try  making a group withthe max of 25 records by using this code....
Page Break on 25 Large Records
Make a group call it PageBreak and in the Expression place this....
No need for a sort Var.
Set Visibility Hidden =True for the Bottom group footer
place this in the Group By expression box
=CInt(Ceiling(RowNumber(Nothing)/25))
OR
=CInt(Ceiling(RowNumber("MYGROUPNAME")/25))
0
 
tickettCommented:
I'm new to SSRS but my mind is telling me you might best achieve the desired result within the query itself:

SELECT TOP 25 * FROM
(
SELECT your, existing, fields, 1 as my_rank FROM your_existing_table
union
SELECT '', '', '', 2
union
SELECT '', '', '', 2
union
SELECT '', '', '', 2
union
SELECT '', '', '', 2
union
SELECT '', '', '', 2
) ORDER BY my_rank ASC

Hope that makes sense!

L
0
 
sureshbabukrishCommented:
the only option is that you need to make sure SQL query returns minum 25 rows everytime.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
kalbalAuthor Commented:
thanks Tickett,
however the solution would work if it is a simple SELECT statement.
My dataset is a bit more complicated-
1) My original Dataset already consists of 4 select statements using UNION ALL
2) This dataset also has group by on 2 fields that are used to create 2 grouping levels in the report layout table.
3)What I am trying to do is fix 25 rows in group2 (inside group1). So i want it to bring Top 25 rows under each instance of Group 1.
Also i am not sure if the syntax of your select top 25* is correct above as was giving me an error.

Thanks
0
 
tickettCommented:
I changed it a bit:

SELECT TOP 25 * FROM
(
SELECT 'your' a, 'existing' b, 'fields' c, 1 as my_rank
union all
SELECT '', '', '', 2
union all
SELECT '', '', '', 2
union all
SELECT '', '', '', 2
union all
SELECT '', '', '', 2
union all
SELECT '', '', '', 2
) t ORDER BY my_rank ASC


But- you are right... if you're trying to get 25 rows within a group it may be trickier!

How about this approach:

1) place 25 rows in group footer 2- then on each row place a statement which surpresses each row depending on how many rows are within the group? (i.e. if 25+ rows are returned all group footer 2 rows will be hidden. if 10 rows are returned 10 rows will be hidden etc?)

L
0
 
kalbalAuthor Commented:
Adding 25 blank rows and then displaying them based on no of rows returned in the dataset worked.
I also had to use SSRS function distinctcount(unique column, table_group1) to determine the rows returned in the dataset.

thanks for your help.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now