Solved

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

Posted on 2010-08-25
6
877 Views
Last Modified: 2012-05-10
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
Comment
Question by:kalbal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 27

Expert Comment

by:planocz
ID: 33527530
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
 
LVL 11

Expert Comment

by:tickett
ID: 33528764
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
 
LVL 9

Expert Comment

by:sureshbabukrish
ID: 33530210
the only option is that you need to make sure SQL query returns minum 25 rows everytime.
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

Author Comment

by:kalbal
ID: 33538848
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
 
LVL 11

Accepted Solution

by:
tickett earned 500 total points
ID: 33539584
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
 

Author Comment

by:kalbal
ID: 33658984
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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question