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
878 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
Technology Partners: 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!

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

688 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