Report Paging

We are converting from Crystal Reports to Reporting Services. We are
encountering what appears to be an issue with the Report Viewer. If Table
rows in the body of the report are conditionally made Hidden (depending on
parameter input), the number of pages differs between the viewer and the
printed (either printer or pdf). In fact, some reports show a single page in
the viewer, but many pages when printed or exported to pdf. If the Table rows
are always visible, the pagination is consistent between viewer and printing.
Is there any way to work around this?Hello HK,
I would like to know the following things:
1. What's the SQL server version did you use? 2000 or 2005?
2. Since this issue only occured for the conditional Hidden, it is a known
issue in SQL 2005. And the product team is researching this issue. If I get
any update, I will let you know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you for your response.
We are using sql server 2005 sp2, visual studio 2005.
It appears to be occuring only with the conditional Hidden. If I remove the
conditional Hidden, we appear to get correct pagination. I (and our many
report users) would greatly appreciate any solution to this.
"Wei Lu [MSFT]" wrote:
> Hello HK,
> I would like to know the following things:
> 1. What's the SQL server version did you use? 2000 or 2005?
> 2. Since this issue only occured for the conditional Hidden, it is a known
> issue in SQL 2005. And the product team is researching this issue. If I get
> any update, I will let you know.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello HK,
Product Team confirmed this is a known issue and will be addressed in the
next version of Reporting Services.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||I'm glad to hear that - thanks for your response.
"Wei Lu [MSFT]" wrote:
> Hello HK,
> Product Team confirmed this is a known issue and will be addressed in the
> next version of Reporting Services.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>

Report not right in VB

I've got a report that works great in Crystal, but when I run the report in VB, I get a nearly blank report. (see attachments)
There are 3 sub reports in the main report.

Here is the code that I use for the report. I've used similar code before and it's worked before.

Dim CRXReport As CRAXDDRT.Report
Dim CRXDb As CRAXDDRT.Database
Dim CRXTbl As CRAXDDRT.DatabaseTable

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset

Dim init As Integer
Dim rptNum As Integer
Dim rptBranch As String
Dim rptDate As String

Set CRXReport = Report
Set CRXDb = CRXReport.Database
Set Report = app.OpenReport("D:\mgmntReport\mgmntReportLandscape.rpt")

Set con = New ADODB.Connection
Set rs = New ADODB.Recordset

con.Open "Driver={SQL Server};Server=TSGDEV07;Database=SEDONADW;Uid=sa;Pwd=psswrd;"
rs.Open "SELECT DISTINCT cono, officeno FROM management_YTD_Payroll", con

rptDate = Trim(Str(Month(Date))) & Trim(Str(Day(Date))) & Trim(Str(Year(Date)))
'MkDir "c:\MgmntReports\" & rptDate & "\"


rptNum = 1
Do While Not (rs.EOF Or rs.BOF)
Report.DiscardSavedData
frmMain.Show

'Debug.Print Report.Database.Tables.Count
Report.Database.Tables.Item(1).SetLogOnInfo "TSGDEV07", "sedonaDW", "sa", "psswrd"
Report.Database.Tables.Item(2).SetLogOnInfo "TSGDEV07", "sedonaDW", "sa", "psswrd"
Report.Database.Tables.Item(3).SetLogOnInfo "TSGDEV07", "sedonaDW", "sa", "psswrd"
Report.Database.Tables.Item(4).SetLogOnInfo "TSGDEV07", "sedonaDW", "sa", "psswrd"

Report.ParameterFields(1).AddCurrentValue Str(rs.Fields("cono"))
Report.ParameterFields(2).AddCurrentValue Str(rs.Fields("officeno"))

Report.ExportOptions.DestinationType = crEDTDiskFile
Report.ExportOptions.FormatType = crEFTPortableDocFormat
Report.ExportOptions.PDFExportAllPages = True
Report.ExportOptions.DiskFileName = "D:\mgmntReport\Reports\" & rs.Fields("cono") & rs.Fields("officeno") & ".pdf"
Report.Export False
rs.MoveNext
Loop
rs.Close
con.Close

Set rs = Nothing
Set con = Nothing
Set CRXReport = Nothing

Anyone have any suggestions?I think the problem may be with the sub-reports.
The 'Report.Database.Tables.Item(1).SetLogOnInfo ...' lines are only setting the logOnInfo for the tables in the main report. I need to login to about 6 other tables for the sub reports. I have no idea about how to do that though.|||I have an idea how to get to the sub report.

Report.OpenSubreport("billings.rpt").Database.Tables.Item(1).SetLogOnInfo..."

also

report.OpenSubreport("billings.rpt").ParameterFields(1).addCurrentValue..."|||Here is what i got to work. (adapted from http://support.businessobjects.com/communitycs/filesandupdates/scr8_vb_rdc_subreports.exe.asp)

Option Explicit
'this application illustrates how to set properties of a subreport at runtime
'although it specifically sets the database location and the value for a text object
'the logic is the same for formatting fields, passing log on information, setting parameter values, etc.
Dim app As New CRAXDDRT.Application
Dim Report As New CRAXDDRT.Report

'Dim CRReport As New CrystalReport1

Private Sub Form_Load()
Dim CRXReport As CRAXDDRT.Report
Dim CRXDb As CRAXDDRT.Database
Dim CRXTbl As CRAXDDRT.DatabaseTable

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim i As Integer
Dim j As Integer
Dim x As Integer

Set con = New ADODB.Connection
Set rs = New ADODB.Recordset

con.Open "Driver={SQL Server};Server=TSGDEV07;Database=SEDONADW;Uid=sa;Pwd=psswrd;"
rs.Open "SELECT DISTINCT cono, officeno FROM management_YTD_Payroll", con

Set CRXReport = Report
Set CRXDb = CRXReport.Database
Set Report = app.OpenReport(rptPath & "mgmntReportLandscape.rpt")


'this code works through the RDC object model to identify a subreport object
'in the main report
Dim crSecs As CRAXDRT.Sections
Dim crSec As CRAXDRT.Section
Dim crRepObjs As CRAXDRT.ReportObjects
Dim crSubRepObj As CRAXDRT.SubreportObject
Dim crSubReport As CRAXDRT.Report

Set crSecs = Report.Sections
For i = 1 To crSecs.Count
Set crSec = crSecs.Item(i)
Set crRepObjs = crSec.ReportObjects
For x = 1 To crRepObjs.Count
If crRepObjs.Item(x).Kind = crSubreportObject Then

Set crSubReport = Report.OpenSubreport(crRepObjs.Item(x).SubreportName)

'the following code sets the subreport table to a different database
For j = 1 To crSubReport.Database.Tables.Count
crSubReport.Database.Tables.Item(j).SetLogOnInfo "TSGDEV07", "sedonaDW", "sa", "psswrd"
Next j
'within this loop you can set other properties of the subreport and
'the field objects and sections in it.
End If
Next
Next
Do While Not (rs.EOF Or rs.BOF)
Report.DiscardSavedData
Report.ParameterFields(1).AddCurrentValue CStr(rs.Fields("cono"))
Report.ParameterFields(2).AddCurrentValue CStr(rs.Fields("officeno"))
Report.ExportOptions.DestinationType = crEDTDiskFile
Report.ExportOptions.FormatType = crEFTPortableDocFormat
Report.ExportOptions.PDFExportAllPages = True
Report.ExportOptions.DiskFileName = "D:\mgmntReport\Reports\" & rs.Fields("cono") & rs.Fields("officeno") & ".pdf"
Report.Export False
rs.MoveNext
Loop

rs.Close
con.Close
Set rs = Nothing
Set con = Nothing
End
End Sub

Private Sub Form_Resize()
CRViewer1.Left = 0
CRViewer1.Height = ScaleHeight
CRViewer1.Width = ScaleWidth
frmMain.WindowState = vbMaximized
End Sub

Report Layout

I have 2 tables in Access. I use VB to view a report in Crystal Report.

Table1: StaffMaster

EmpCode
EmpName

Table2: StaffHrs

EmpCode
Rfs
Project
Month_Year
ATH

I wish to display a report in Crystal Report as follows:

EmpCode | EmpName | Rfs | Prject | Month_Year | ATH

How do I accomplish this? Can this be done using crosstab query?

The criteria is for Month_Year. I tried using Crosstab query, but I have problems with the date format. The date should be displayed on the report as mmmm yyyy format. Can it be done using the following query?

"SELECT StaffMaster.EmpCode, StaffMaster.EmpName, StaffHrs.RFS, StaffHrs.Project, StaffHrs.Month_Year, " & _
"StaffHrs.ATH FROM StaffMaster LEFT OUTER JOIN StaffHrs ON StaffMaster.EmpCode=StaffHrs.EmpCode " & _
"WHERE StaffMaster.EmpCode=StaffHrs.EmpCode AND StaffMaster.Discipline='Architecture' AND " & _
"StaffHrs.Month_Year BETWEEN #July 2004# AND #September 2004#"

If so, how will I display the fields in the appropriate places?

ThanksI landed up in the crosstab query, without the date criteria, which I think will give me the result I want. I ignored the date criteria, so that I can retrieve all the records in the StaffMaster table, which is what I want. But one problem is that, the recordset contains all the dates that are there in the StaffHrs table. I would like to find out, if the Month_Year field of the recordset is the same as that I want, and then display it into a field. The Month_Year field is not fixed, as it depends on the user's selection. So it cannot be specified in the code. I know this can be done with formula fields. But how?

This is the crosstab query:

strsql = "TRANSFORM Sum(StaffHrs.ATH) AS SumOfATH " & _
"SELECT StaffMaster.EmpCode, StaffMaster.EmpName, StaffHrs.RFS, StaffHrs.Project " & _
"FROM StaffMaster LEFT JOIN StaffHrs ON StaffMaster.EmpCode = StaffHrs.EmpCode " & _
"Where (([StaffMaster]![Discipline] = 'Architecture')) " & _
"GROUP BY StaffMaster.EmpCode, StaffMaster.EmpName, StaffHrs.RFS, StaffHrs.Project " & _
"ORDER BY StaffMaster.EmpName " & _
"PIVOT StaffHrs.Month_Year"

The Field Count is 18. But this may also vary depending on the Discipline that the user selects.

If, for example, the Discipline is Architecture, then the field count is 18. I did a looping just to see what the fields are. This is the code:

For I = 1 To Report.Database.Tables(1).Fields.Count
K = Report.Database.Tables(1).Fields(I).DatabaseFieldDisplayName
MsgBox K
Next I

The result I got is as follows:

<>
01/01/05
02/01/05
03/01/05
04/01/05
05/01/05
05/01/04
06/01/04
07/01/04
08/01/04
09/01/04
10/01/04
11/01/04
12/01/04
EmpCode
EmpName
Project
RFS

If the result is this, how will I find out the appropriate data for the corresponding date and display it in the corresponding fields? The user needs to view the data for 6 months from the month he selects.

Thanks

Report help please...

Hey guys,

Im kind of new to crystal, well enough to be dangerous anyway... but I have a report that I am trying to build. let me give you a little background on what the database is like so you have an idea of what Im trying to do. I am reporting against a SQL database that is our CRM system, which we track calls in. so you open a ticket, and then there are journal entries that are linked to that object in another table.

so what i want to do is display the open calls for a particular company, and the last two journal entries.

what i have so far, is everythign but the last two journal entries. i can get it to display all of them, but i only want the last two.. otherwise the report is way to big. i was thinking there must be a "TOP" type function or somethign kind of like in sql, where i could say only top 2 or something like that.. any help would be good... thanks!Have you tried this:

"SELECT TOP 2 * FROM YourTable ORDER BY OneField Desc"

I think this SQL will extract last 2 items.|||I have thought about doing that, but not really sure where to put it... i mean there are so many spots in crystal to put formulas... do i put it right on the field? on the section of the report? or where? right now im trying to use the suppress function with a formula, thinking it should work.. but its not... either it suppresses everythign or nothing... i cant get it to work if the formula condition is met, which the help files says that it should work like that...

im trying

{mytable.datecolumn} < Last7Days

figured ok if it is less then the last 7 days then suppress it... but that doesnt seam to work..|||I have thought about doing that, but not really sure where to put it...

I use Vb code to execute SQL. Have you tried SQL Expression Fields on CR?

Report generates incorrect results

Hey, after tinkering around with this VB 2005/Crystal setup for a while, I finally narrowed down the issue with the problem I'm having.

Using the SQL query Crystal gives me, I am trying to use VB.NET to pull data from a DSN server and into a DataSet, which I am trying to merge with the rpt file using the report.SetDataSource option. The problem is, with that particular query, the results generated pages in the report in the THOUSANDS. Typically, this report never goes over 10 pages. The thing is that when I generate the report within Crystal itself, it's fine... 10 pages or less.

After some fooling around with the VB code, I found that the problem was in the query! It seems that when I pull certain fields and inject them into the report, it systematically loads every possible value from those fields into each group (company tickets), and with so many fields this happens with, there becomes a compound effect. So what was 50+ bloat pages then becomes thousands.

I am wondering what the issue could possibly be with the query Crystal gives me (which works in Crystal) that when I try to bind the same data from a DataSet with the rpt template, it inflates the report with garbage data. Anyone have any similar occurrances or suggestions?Bump|||Maybe is how Crystal evaluates the records (whilereading, beforerading??) or maybe somehow one field was moved a detail section and before was on a header. I assume queries are the same...

Robert