Solved

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

Posted on 2010-09-18
5
272 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 101

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

Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

Question has a verified solution.

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

A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
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…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

696 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