Solved

Details section of table in SSRS should be fixed based on Dataset.

Posted on 2010-09-18
5
271 Views
Last Modified: 2012-05-10
hi

I have one query in SSRS.  i have a report which should be in pdf format.  in that i have a table which should have 5 details ( i mean lines).
Here I have question,  suppose if my dataset for the table is returing only 2 rows means, I need the report should be like first two rows should be with data and the remaining rows should be empty.

As of now this is not happening, because of this my report alignment is changing.

Ex:  
DataSet

Eno  ename
1       A
2       B

Output should be in Table ( SSRS)

EmployeeNo              EmployeeName
         1                                  A
         2                                  B
         -                                    -
         -                                    -
         -                                    -


Suppose if my dataset is returning :
0 roww    means, remaining 4 rows should be with "-"
1 row    means, remaining 4 rows should be with "-"
2 row    means, remaining 3 rows should be with "-"
3 row    means, remaining 2 rows should be with "-"
4 row    means, remaining 1 rows should be with "-"

If anyone knows the solution, please advise me.
Thanks in Advance.

Regards
Prakash
0
Comment
Question by:UJayaPrakashRao
[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
  • 2
5 Comments
 
LVL 4

Expert Comment

by:timexist
ID: 33707740
HI UJayaPrakashRao
first, as I believe, the database always should prepare the data before it pass them to report.
so I think you should at the database stored procedure, prepare the data format and empty value format.

it means you always return 5 rows for stored procedure.

second, you put a IIF will fix the problem.
0
 
LVL 4

Expert Comment

by:timexist
ID: 33707743
I have a ssrs doing such things.
0
 
LVL 13

Accepted Solution

by:
devlab2012 earned 500 total points
ID: 33807011
The following query will always return 5 rows:

select TOP 5 * from
(
select 1 as IsDBRecord
union all
select 0
union all
select 0
union all
select 0
union all
select 0
) a LEFT OUTER JOIN(select EmployeeNo, EmployeeName from employee) b
ON 1=1
ORDER BY IsDBRecord DESC
the highlighted portion is your actual query. For the actual rows from the table, value of IdDBRecord will be 1 other wise 0.
Now in your report, check the value of IsDBRecord column. If it is 1 then display the actual value of EmployeeNo, EmployeeName etc. and if it is 0 then display "-".
It should work fine.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34662539
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

738 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