0

I am currently working on SSRS reports 2008 displaying them in Website created in VS 2010 i.e., ASP.NET 4.0 C#.

My current issue is I have a report with only a Letterhead on it. And this report page needs to be printed multiple times based on the value in number of pages TextBox as shown enter image description here


To be a bit descriptive:

When the user enters the value in Number of Pages TextBox and clicks on this Print button icon, he/she lands on the page with ReportViewer control on it, displaying the report.This report has only a letterhead in the PageHeader of the report and here this report will be printed by clicking the default print button of ReportViewer control.

But, I am unable to figure out, how to print this report page as many times as there will be the value in the No of Pages TextBox (as shown in the fig.)
(The Letterhead of the company to be shown in report is retrieved from database through a Stored Procedure)

I tried a lot of Googling but to no avail.

Irf
  • 4,285
  • 3
  • 36
  • 49
  • Your question is very hard to understand for me. You interchange "Number of Pages" and Number of Copies - perhaps you just mean the latter? Can you review your question, try to clarify a bit more? – Jeroen Sep 21 '12 at 09:42
  • @Jeroen I've changed the language of question and tried to make it simple, pls have a look at it – Irf Sep 22 '12 at 01:48
  • Aye, now it makes more sense! I'll second @JoaoLeal's answer then, that seems like it would do the trick. – Jeroen Sep 22 '12 at 08:43
  • @Jeroen But I tried Joao-Leal's answer but it is showing some errors – Irf Sep 24 '12 at 08:22

2 Answers2

3

Create a new report. This report should have 1 parameter called "number of copies" (or equivalent). It should also have a Tablix with 1 column and no borders, inside the cell insert a sub report pointing to the report with the letterhead.

Your dataset query should be something like this:

WITH dataset AS (
   SELECT 1 AS ID UNION ALL 
   SELECT ID + 1 FROM dataset WHERE ID < @Param
)
SELECT ID 
FROM dataset --edit: obviously I was missing the table
OPTION (MAXRECURSION 0)

Then on your tablix, use this dataset, group by ID and on the group properties select "Page Breaks"->"Between each instance of a group".

If I understood your question correctly, this should do the trick.

Joao Leal
  • 5,533
  • 1
  • 13
  • 23
  • Thanks for taking interest. I tried to do the same but I am getting an error : "Invalid column name 'ID'." – Irf Sep 24 '12 at 04:09
  • I even made on field name with name ID but to no avail. Can you suggest where I am going wrong ? – Irf Sep 24 '12 at 04:20
  • Where exactly is the error? can you run this query on management studio? – Joao Leal Sep 24 '12 at 10:59
  • Joao The error 'Invalid column name 'ID'. is in line itself "SELECT ID" – Irf Sep 25 '12 at 02:20
  • That's because I'm an idiot and made a syntax error, please see the updated query above. – Joao Leal Sep 25 '12 at 14:44
  • yes with some minor changes it **REALLY DID THE TRICK** Thank you very much ! – Irf Sep 26 '12 at 10:43
  • @JoaoLeal, I tried this trick and partly working for me. Do not know why it is generating the blank pages in between when exported or printed the report. Any idea ? – Aditya Feb 03 '16 at 09:02
  • Can you create a new question? The blank pages could be coming from the sub-report – Joao Leal Feb 09 '16 at 10:02
0

Expanding on Joao's solution (thanks for that) you can also do it without a subreport by joining the counter table to the actual data you want to display in the dataset.

  1. Add a Copies integer parameter with a default value of 1

  2. Update your dataset to include the counter and join

     -- Generate a table with @Count rows
 WITH dataset AS (SELECT 1 AS Copy UNION ALL SELECT Copy +   1 AS Expr1 FROM dataset AS dataset_2 WHERE  (Copy < @Copies))
 SELECT * FROM dataset INNER JOIN (

 -- The primary data to repeat

 SELECT * FROM MyTable WHERE Id = @IdParam

 -- End

 ) d ON 1=1 OPTION (MAXRECURSION 0)

  3. Add/update your row group to group on [Copy]

    enter image description here

  4. Set the row group page breaks to 'between each instance'

    enter image description here

James White
  • 2,062
  • 2
  • 24
  • 36