March 16, 2010 at 12:25 pm
Hi guys, I need to do a report in SSRS to print bills from an ERP.
I just can't figure out how the heck to build this thing.
I need to be able to have more than 1 bill print in the report execution, Also the report must also repeat on a couple page when there are more than 15 items.
Any paper or tutorial is welcomed.
VS 2008
Report will be shown in the web with reportviewer.
Clients will be able to either print or export to pdf.
TIA.
March 17, 2010 at 8:24 am
Seriously, anyone has any remotly usefull guideline???
I don't want you to do it for me, I just want someone to guide me like :
make dataset for header.
make second ds that takes order no and page no as parameter
then add subreport that is linked to the main report.
Any google serch for subreport will guide you.
HTH.
TIA.
March 17, 2010 at 8:39 am
You're on the right track.
You'll need a dataset for the header (and any footer you have planned).
You'll need a dataset for the body of the report. Most likely, this will be part of the same dataset. You can set some columns to appear in a header and some in the details, and enforce a page-break when the header data changes. That's the easiest way to accomplish what it sounds like you're looking for.
When you state you need it to repeat when there's more than 15 items, do you mean 15 line items for one bill, or something else? Because that sounds like a formatting issue with specifying when to have a page break.
You shouldn't need to manually paginate the thing in the query. Format it correctly, and SSRS will page-break it for you.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2010 at 8:48 am
when I did a simmillar thing I encased all these datasets that build the "body" of the bill into a List Tool and this allowed repeating fields based on conditions.
March 17, 2010 at 8:56 am
Vishal Mehta (3/17/2010)
when I did a simmillar thing I encased all these datasets that build the "body" of the bill into a List Tool and this allowed repeating fields based on conditions.
I've never heard of such tool. Do you have any recommendations with links?
March 17, 2010 at 8:59 am
I'm with Gus and Vishal on this one. Do one dataset to take care of header/footer info and then create subreports based on that. You can even embed the subreports in rectangles and/or list controls to have a bit more control over your pagination. Your subreports will most likely just accept a single parameter of Client ID or Bill ID or whatnot which can be passed from your main dataset. Not sure if it was fixed with SSRS08 or not but when exporting to Excel subreports within a table won't be exported, hence the reliance on list/rectangles.
For anything more I'm kinda an image person. If you can draw me a picture of what you want I might be able to give you some more specific information.
-Luke.
March 17, 2010 at 9:01 am
Ninja's_RGR'us (3/17/2010)
Vishal Mehta (3/17/2010)
when I did a simmillar thing I encased all these datasets that build the "body" of the bill into a List Tool and this allowed repeating fields based on conditions.I've never heard of such tool. Do you have any recommendations with links?
From BOL:
Adding List Data Regions (Visual Studio Report Designer)
-Luke.
March 17, 2010 at 11:02 am
Luke L (3/17/2010)
I'm with Gus and Vishal on this one. Do one dataset to take care of header/footer info and then create subreports based on that. You can even embed the subreports in rectangles and/or list controls to have a bit more control over your pagination. Your subreports will most likely just accept a single parameter of Client ID or Bill ID or whatnot which can be passed from your main dataset. Not sure if it was fixed with SSRS08 or not but when exporting to Excel subreports within a table won't be exported, hence the reliance on list/rectangles.For anything more I'm kinda an image person. If you can draw me a picture of what you want I might be able to give you some more specific information.
-Luke.
Thanks a lot. One more requirement since those are basically bills and credit notes sent to our clients I'll expose only the print button and the export to PDF so excel export is not an issue here. Is there anything I need to worry about with that in mind with pdf and print?
Also I'm going to be exposing those reports using asp.net (c#) using the reportviewer control since this is a public website without windows authentication. I have a working model that takes a single or even multiple datasets for a single report. But it's not obvious to me that I have load a subreport using the same method. I'd love to know if anyone has even done this and has recommendations for or againts. At this point I've not researched subreports with reportviewer control but I'd love to hear any hints and tips.
I'll also do my research and post my findings.
If anyone is interested here's the c# code used to load the report (found on this site in the articles section).
private void LoadReport()
{
#warning this will be passed trough the cache or session while comming to this page.
//Declarations and get data for report.
ViewStateManager _temp = new ViewStateManager(this.ViewState);
Queries Q = new Queries(false, Queries.SqlServer.Production, "Remi", "Pneus Supérieurs Inc_", ref _temp);
SqlConnection conReport = Q.GetConnection(false);
SqlCommand cmdReport = new SqlCommand();
SqlDataReader drReport;
//we need an object copy of the dataset to load as the report's datasource.
DataSet dsReport = new DataSet1();
try
{
//open connection
conReport.Open();
//prepare connection object to get the data through
//reader and populate into dataset
cmdReport.CommandType = CommandType.Text;
cmdReport.Connection = conReport;
cmdReport.CommandText = Q.GetListeFacturesCredits("DUBUC").CommandText;
//read data from command object
drReport = cmdReport.ExecuteReader();
//new cool thing with ADO.NET... load data directly
//from reader to dataset
dsReport.Tables[0].Load(drReport);
//close reader and connection
drReport.Close();
conReport.Close();
//provide local report information to viewer
ReportViewer1.LocalReport.ReportPath = Server.MapPath("ListeFacturesEtCredits.rdlc");
ReportDataSource rds = new ReportDataSource();
//Format is DataSource_Datasetname
rds.Name = "DataSet1_Company";
rds.Value = dsReport.Tables[0];
ReportViewer1.LocalReport.DataSources.Add(rds);
ReportViewer1.LocalReport.Refresh();
}
catch (Exception)
{
}
finally
{
if (conReport.State == ConnectionState.Open)
{
conReport.Close();
}
conReport.Dispose();
Q.Dispose();
}
March 17, 2010 at 11:06 am
GSquared (3/17/2010)
You're on the right track.You'll need a dataset for the header (and any footer you have planned).
You'll need a dataset for the body of the report. Most likely, this will be part of the same dataset. You can set some columns to appear in a header and some in the details, and enforce a page-break when the header data changes. That's the easiest way to accomplish what it sounds like you're looking for.
When you state you need it to repeat when there's more than 15 items, do you mean 15 line items for one bill, or something else? Because that sounds like a formatting issue with specifying when to have a page break.
You shouldn't need to manually paginate the thing in the query. Format it correctly, and SSRS will page-break it for you.
Does that help?
So to recapitulate what you told me
So DS header is like :
Select Cie info, ship to, bill to, taxe numbers, order date, order no.
Then the body is where I put the details :
DsItems :
Select itemNo, Description, Price, Discount, Qty, LineAmount from...
I need to show the footer only on the last page of the bill or credit note... but obviously for each bill that I have in the header dataset.
There's also something the client asked that I have no idea how to do. Basically at the end of page 1 and if there's a page 2 I need to put the total for the page. Then the first line of page 2 needs to show the $ amount reported from page 1. Repeat untill you're on the last page.
What they want is a new item that reads something like : "Page total 876.45$" for the last row in details for the first page, then on the first row of the 2nd page : "Reported from page 1 : 876.45$"
Any pointers for that one? I can't find anything obvious in the groupings and totals sections... but then again I was not using a subreport on my first few tries...
March 17, 2010 at 11:09 am
That goes beyond what I know on SSRS. There's probably a way to do it, but I wouldn't know where to start.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2010 at 11:17 am
Ok I just hit google and I found those 3 usefull links :
Subreports with reportviewer control :
http://support.microsoft.com/kb/919157/en-us
The gist of the solution which I've just skimmed through seems that we need to add a new event handler for the reportviewer control : ReportViewer1 . LocalReport . SubreportProcessing
When the even fires we need to rebind the data for the subreport. So it looks like I'll have to do some formatting myself after all in the datasets assuming ssrs doesn't take care of that for me.
General tutorials with reportviewer control :
http://www.gotreportviewer.com/[/url]
SubReports demo with SSRS :
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply