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
870 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
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
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 combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now