Report with Multiple Tables Join

I am designing a Receipt using Crystal Reports 8.5 Developer, MS SQL 2000 Server and MS Visual Basic 6.0 sp6. My Receipt will grab some data from RCP, RCPGL, RCPDetail and Customer tables.

SQL = SELECT RCP.RCPID, RCPGL.RCPGLID, RCPDetail.RCPDetailID, RCP.RCPDate, RCP.RCPID, RCPGL.GLDate, RCPGL.ChequeNo, RCPGL.GrossAmount, RCPDetail.InvoiceNo, RCPDetail.AmountPaid, Customer.CustomerName FROM RCP, RCPDetail, RCPGL, Customer WHERE RCP.RCPID=RCPDetail.RCPID AND RCP.RCPID=RCPGL.RCPID AND RCP.CustomerID=Customer.CustomerID AND RCP.RCPID= glngRCPID

Let say RCPDetail has 3 Invoices data and RCPGL has 2 cheque transactions, my SQL query will returns 6 records.

What I want is to display a section with 3 records of Invoice and another section to display 2 lines of cheque transactions.

Anyone can give me some recommendation?Group on the record type. If this is implicit from the data (e.g. ChequeNo is non-0 for cheques) then create a formula to return a type and group on the formula.|||Hi JaganEllis,
Thanks for your suggestion. I have found an idea and it is using grouping too. I will create a temporary table call TempReceipt. I'll SELECT data from RCPGL table and INSERT "RCPGL" as the key into TempReceipt and then SELECT data from RCPDetail table and INSERT "RCPDetail" as the key. After that join the TempReceipt table with Customer table using ADO. When display the data in Report, I'll use Group by the key of TempReceipt to separate the data.sql

Report Viewer

Hi,

I am new to using Report Viewer.

I created a basic report (Local Mode) using Report vIewr control in VS 2005 and typed dataset and it works fine.

But I would like to view a report with a parameter.

Can anyone explain me how to do that and bind it with the report viewer?

Thanks & Regards,

Lavanya.

Do you want to set parameters from your code?

|||

In the ASPX right after the

see the object source code and replace with your stuff

<asp:ObjectDataSourceID="ObjectDataSource2"runat="server"

SelectMethod="GetData"

TypeName="DataSetOTDTableAdapters.DataTableOTDFamilyTableAdapter">

<selectparameters>

<asp:querystringparametername="FromDate"querystringfield="FromDate"defaultvalue="01/01/2007"/>

<asp:querystringparametername="ToDate"querystringfield="ToDate"defaultvalue="07/01/2007"/>

</selectparameters>

</asp:ObjectDataSource>

that coresponds to a getdate method in the dataset that has 2 parameters @.FromDate and @.ToDate

You can also change the values of parameters in the PageInit event.

|||

I want to set the parameters in the report.rdlc.

I got one parameter working.

I have two textboxes. I have to see the report for a specific date range.

How can I do that?

For a single date, I set the operator in the filter as =.

What if I want to use two parameters (date range).

How do I set it in report.rdlc?

|||

I am assuming you are not using Crystal Reports. How do you fill the dataset? Can you supply the range parameters to the dataset instead of reportviewer? Also see if filtering works for you:

http://msdn2.microsoft.com/en-us/library/ms252125(VS.80).aspx

As I suggested, filtering data before report is rendered is a better option.

|||

Hi, I have a matrix report with rows as a name and the column as a date field.

When i run the report it works the way i want.

08/03/2007 07/31/200708/01/2007Adams , William484Anderson , David480Asseng , Steve440Bangerd , Michael048Barnaskas , Joe000Barry , Timothy084Berry , Jerry404Bowen , Nels000Breakall , Jeff4012Brockel , Robert048Campbell , Bryan000Castro , Carlos000Childs , Michael8124Conklin , Terry888

But when I give the parameter as the date(08/03/2007), I am not gettin the rows with 0. What am i doin wrong?

|||

Are you doing the filtering as suggested in that article?

|||

Yeah I create report parameters and then I include filters in the matrix.

|||

Am just curious, 0 implies there is no record or value for that date, right? So that means filter works as it should. Or, what do those values mean?

|||

Yeah... 0 means the count of the entries for that date is 0. If it is 4 it means the count of entries for that date and for that tech is 4.

Now when i dont include any parameters, I get the 0 entries also.

I need the same thing when i include my date parameter.. is it possible?

|||

I am sorry but maybe I am not getting it right. What is the purpose of a filter if you want to display all the values. Let me look into it and I will get back to you if I find something.

|||

It is just similar to the one without parameter. I add an parameter So that I can see the data only for that date. Now it is shoeing all dates...

|||

Lets backtrack. Is filtering working for you or not?

|||

Yeah it works. But the problem is it doesn not show me the columns with 0 .

|||

Let me put it crisp.

What I am trying to do is a Compliance report. If the tech comes in for that day he is compliant, if not he is not compliant.

So what I did was I did a matrix report with rows as Technician Name , Columns as Date and data as Count(Date)

It worked perfect. The result is shown below.

07/31/200708/01/200708/02/2007Adams , William848Anderson , David804Asseng , Steve404Bangerd , Michael488Barnaskas , Joe000Barry , Timothy844Berry , Jerry044Bowen , Nels000Breakall , Jeff0124Brockel , Robert4812Campbell , Bryan000Castro , Carlos000Childs , Michael1244Conklin , Terry888Contractor , Contractor000Cooke , Jackie000DeLeon , Carlos8120Dorn , Craig400Ehrlich , Chase000Elswick , James000English , Mike400Ewing , David000Favalora , Anthony41212Fierke , Bruce000Figueroa , Richard000

Now I need to include parameter to show the report for a specific date. In our example let us take 7/31/2007

I included report parameters for the report and filters for the matrix where Fields!Date.Value=Parameter!Date.Value

I have a textbox and a button in my aspx page.

On button click

Dim pubAs Microsoft.Reporting.WebForms.ReportParameter

Dim pubdateAsString = TextBox1.Text

pub =New Microsoft.Reporting.WebForms.ReportParameter("Arrival_Time", pubdate)Me.ReportViewer1.LocalReport.SetParameters(New Microsoft.Reporting.WebForms.ReportParameter() {pub})

ReportViewer1.LocalReport.Refresh()

And then I run the report with 07/31/2007 in the textbox and hit the button. I dont get

Barnaskas , Joe

Berry , Jerry

Bowen , Nels

Breakall , Jeff

Campbell , Bryan

Castro , Carlos

Contractor , Contractor

Cooke , Jackie

Ehrlich , Chase

Elswick , James

etc..,

because the count is 0.

Hope I explained it at my best. Please help.

report server refresh question

Hello

I'm new at creating reports in reporting services and I have a basic question.
My problem is that for some reason my reports don't refresh right away, even after I press the recycle button.

I change a value on the table on a database and click on the refresh button of the report. But I have to give it sometime and click the refresh in order for the report to show the changed data.

I have some charts in the report as well, so maybe I'm missing a configuration setting somewhere.

Please help a newbie out.

Thanks
Peter

For clarification,

1. Is your reports pointing to the same table to which you are doing the update?

Another thing to try is to go back and refresh the page and now click on the report name again to reload. See, if this works.

|||

Please make sure that the recycle button you are pushing is the small green one in the top of the ReportViewer control and not the browser refresh.

Check the Properties of the deployed report under Execution and see if caching has been turned on.

Larry

|||The rendered report is cached based on the CleanUpCycle config entry and it is not advisable to modify that from the default 10 [mins]. Try to use add "&rs:ClearSession" to the url of the report and check if the report is refreshed with latest data.|||Hi,
You can do one thing,After any modification in the Database.Come to VS 2005,refresh the Dataset Window(you will get from View Tool Bar).
Probably the changed will be reflected.

Cheers,
Shri|||great thank you for all the responses. All the tips have been usefull.