Solved

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

Posted on 2010-09-18
5
270 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
  • 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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

Time Corrections for Reports Working with a report, we made some interesting discoveries about the time corrections/updates We are using the following Parameters: Starting Entered Date (Date) formatted as Data type: "Date/Time" Ending Entered …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

685 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