search

baansconsulting / Wolfe

0 stars 0 forks source link

Customer_Yearly_Report #25

Closed TomGang closed 8 years ago

TomGang commented 8 years ago

We have a good start to this report, so I want to provide the below comments so that it can be useable.

overall comments:

  1. Can we remove the option to select a year when creating this report. I want it always to be for the current year. And also make it so we can search (type) the name of a customer in the dropdown
  2. any invoiced revenue should be based on payment date and any trading revenue should be based on trade date

under the "Revenue Summary Yearly" section:

  1. can you please always just show the most recent 5 years of invoiced and trading revenue.
  2. after the total column, can you show the "Avg Comm Rate" (which would be the commission/quantity for trading data)
  3. add a space between Revenue and Summary in the section heading

under the "Revenue Summary Quarterly" section:

  1. can you please always just show the most recent 8 quarters of invoiced and trading revenue.
  2. after the total column, can you show the "Avg Comm Rate" (which would be the commission/quantity for trading data)
  3. add a space between Revenue and Summary in the section heading
  4. no need to total this section

under the "Invoice history" section:

  1. can you limit this section to only show the last 25 invoices
  2. you can remove the customer name field (or make it subheading) to save space
  3. can you make the paying entity field wider
  4. you can remove the balance column
  5. can you add a "Days to Pay" Column (date paid - date sent)... if unpaid, can you show "unpaid XX days"?

under the "trading history" section:

  1. I am going to take a different approach to this section (so everything can be removed)
  2. please show the top 10 tickers (based on quantity) that they have traded in the last 12 months.
  3. for each record provide the: Ticker, Security Name (with the extra spaces removed)

Future sections: Just an FYI, Once we have the budget and Analyst Allocation pieces of the database built, that information will be added to this report

baansconsulting commented 8 years ago

we are working on this. We are planning to complete this by Tue/Wed.

baansconsulting commented 8 years ago

implemented on the server

TomGang commented 8 years ago

server error- image

Server Error in '/' Application.

Compilation Error

Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: CS0103: The name 'txtcustomername' does not exist in the current context

Source Error:

Line 34: DataSet1 dset = new DataSet1(); Line 35: ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/Reports/MainCustomerName_report.rdlc"); Line 36: string querymaincustomer = "SELECT tblMainCustomertoCustomer.CustomerCode, tblCustomerMaster.TypeofManager, tblCustomerMaster.FromDate as effectivedate, tblCustomerNamesMaster.CustomerShortName as quickbooksubaccount,tblPaymentLevel.PaymentLevel, tblSalesNamesMaster.SalesCoverage as covreagesale, tblTraderNamesMaster.TraderCoverage as coveragetrader , tblCustomerMaster.CustomerDisplayName,'" + txtcustomername.Text + "' as maincustomername FROM tblMainCustomer left outer JOIN tblMainCustomertoCustomer ON tblMainCustomer.MainCustomerCode = tblMainCustomertoCustomer.MainCustomerCode left outer JOIN tblCustomerMaster ON tblMainCustomertoCustomer.CustomerCode = tblCustomerMaster.CustomerCode left outer JOIN tblCustomerNamesMaster ON tblMainCustomertoCustomer.CustomerCode = tblCustomerNamesMaster.CustomerCode left outer JOIN tblPaymentLevel ON tblCustomerNamesMaster.PaymentLevelID = tblPaymentLevel.PaymentLevelID left outer JOIN tblSalesNamesMaster ON tblCustomerNamesMaster.SalesShortName = tblSalesNamesMaster.SalesShortName left outer JOIN tblTraderNamesMaster ON tblCustomerNamesMaster.TraderShortName = tblTraderNamesMaster.TraderShortName where (tblMainCustomertoCustomer.MainCustomerCode='" + lblcustomercode.Text + "' and YEAR(tblCustomerMaster.FromDate)='" + ddlyear.SelectedItem.Text + "') or tblMainCustomertoCustomer.MainCustomerCode='" + lblcustomercode.Text + "'"; Line 37: string queryRevenueSummaryyearly = "Select CustomerDisplayName,cYear,sum(Trading) as Trading,Sum(InvoiceAmount) as InvoiceAmount,(sum(Trading)+Sum(InvoiceAmount)) as Total,SUM( Avg_Comm_Rate) as Avg_Comm_Rate FROM (SELECT qryMainCustomerNameWithoutFirm.CustomerDisplayName,qryMainCustomerNameWithoutFirm.CustomerShortName,sum(tblInvoiceAndPayment.InvoiceAmount) as InvoiceAmount,year(tblPaymentDetail.PaymentDate) as cYear , 0 as Trading,'' as Avg_Comm_Rate FROM qryMainCustomerNameWithoutFirm INNER JOIN tblInvoiceAndPayment ON qryMainCustomerNameWithoutFirm.CustomerShortName = tblInvoiceAndPayment.SubAccount INNER JOIN tblPaymentDetail ON tblInvoiceAndPayment.InvoiceNumber = tblPaymentDetail.InvoiceNumber where qryMainCustomerNameWithoutFirm.CustomerCode in (select distinct CustomerCode from qryMainCustomerNameWithoutFirm WHERE MainCustomer = '" + txtcustomername.Text + "') and DATEPART(YYYY,tblPaymentDetail.PaymentDate)>=Cast('" + ddlyear.SelectedItem.Text + "' as int)-5 Group by qryMainCustomerNameWithoutFirm.CustomerShortName,Year(tblPaymentDetail.PaymentDate),qryMainCustomerNameWithoutFirm.CustomerDisplayName union all select qryMainCustomerNameWithoutFirm.CustomerDisplayName,qryMainCustomerNameWithoutFirm.CustomerShortName,0 as InvoiceAmount,year(tblTradeAccounting.TRD_DATE) as cYear ,tblTradeAccounting.COMMISSION as Trading,(tblTradeAccounting.COMMISSION/tblTradeAccounting.QUANTITY) as Avg_Comm_Rate from qryMainCustomerNameWithoutFirm,tblTradeAccounting where qryMainCustomerNameWithoutFirm.CustomerShortName=tblTradeAccounting.ACCOUNT_NAME and DATEPART(YYYY,tblTradeAccounting.TRD_DATE)>=Cast('"+ddlyear.SelectedItem.Text+"' as int)-5 and tblTradeAccounting.ACCOUNT_NAME in (select distinct CustomerShortName from qryMainCustomerNameWithoutFirm WHERE MainCustomer = '" + txtcustomername.Text + "') )vv group by vv.CustomerDisplayName,vv.cYear order by vv.cYear DESC"; Line 38:

Source File: d:\inetpub\wwwroot\WolfeTest\Reports\Customer_Yearly_Report.aspx.cs Line: 36

Show Detailed Compiler Output:

c:\windows\system32\inetsrv> "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\csc.exe" /t:library /utf8output /R:"C:\Windows\assembly\GAC_MSIL\Microsoft.ReportViewer.WebForms\11.0.0.089845dcd8080cc91\Microsoft.ReportViewer.WebForms.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.ServiceModel.Web\v4.0_4.0.0.031bf3856ad364e35\System.ServiceModel.Web.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Web.Services\v4.0_4.0.0.0b03f5f7f11d50a3a\System.Web.Services.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.ServiceModel.Activation\v4.0_4.0.0.031bf3856ad364e35\System.ServiceModel.Activation.dll" /R:"C:\Windows\assembly\GAC_MSIL\Microsoft.ReportViewer.ProcessingObjectModel\11.0.0.089845dcd8080cc91\Microsoft.ReportViewer.ProcessingObjectModel.dll" /R:"C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\6766643b\eedfb007\assembly\dl3\c116b86b\004cab43_254ad101\EPPlus.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Web.Extensions\v4.0_4.0.0.031bf3856ad364e35\System.Web.Extensions.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Activities\v4.0_4.0.0.031bf3856ad364e35\System.Activities.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\Microsoft.VisualStudio.Web.PageInspector.Loader\v4.0_1.0.0.0b03f5f7f11d50a3a\Microsoft.VisualStudio.Web.PageInspector.Loader.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Configuration\v4.0_4.0.0.0b03f5f7f11d50a3a\System.Configuration.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.ServiceModel.Activities\v4.0_4.0.0.0__31bf3856ad364e35\System.ServiceModel.Activities.dll" /R:"C:\Windows\assembly\GAC_MSIL\Microsoft.ReportViewer.DataVisualization.resources\11.0.0.0_es_89845dcd8080cc91\Microsoft.ReportViewer.DataVisualization.resources.dll" /R:"C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\6766643b\eedfb007\App_Web_qygsdpls.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Data.DataSetExtensions\v4.0_4.0.0.0b77a5c561934e089\System.Data.DataSetExtensions.dll" /R:"C:\Windows\Microsoft.NET\Framework64\v4.0.30319\mscorlib.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\Microsoft.CSharp\v4.0_4.0.0.0b03f5f7f11d50a3a\Microsoft.CSharp.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Xml.Linq\v4.0_4.0.0.0b77a5c561934e089\System.Xml.Linq.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_64\System.EnterpriseServices\v4.0_4.0.0.0b03f5f7f11d50a3a\System.EnterpriseServices.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Web.Entity\v4.0_4.0.0.0b77a5c561934e089\System.Web.Entity.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Web.DynamicData\v4.0_4.0.0.031bf3856ad364e35\System.Web.DynamicData.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.WorkflowServices\v4.0_4.0.0.031bf3856ad364e35\System.WorkflowServices.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Core\v4.0_4.0.0.0b77a5c561934e089\System.Core.dll" /R:"C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\6766643b\eedfb007\App_Code.guy-41dz.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.ComponentModel.DataAnnotations\v4.0_4.0.0.031bf3856ad364e35\System.ComponentModel.DataAnnotations.dll" /R:"C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\6766643b\eedfb007\assembly\dl3\4dc9f5fa\00765138_f565d001\Telerik.Web.UI.dll" /R:"C:\Windows\assembly\GAC_MSIL\Microsoft.ReportViewer.Common\11.0.0.089845dcd8080cc91\Microsoft.ReportViewer.Common.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Xml\v4.0_4.0.0.0b77a5c561934e089\System.Xml.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Web.ApplicationServices\v4.0_4.0.0.031bf3856ad364e35\System.Web.ApplicationServices.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Runtime.Serialization\v4.0_4.0.0.0b77a5c561934e089\System.Runtime.Serialization.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Web.WebPages.Deployment\v4.0_2.0.0.031bf3856ad364e35\System.Web.WebPages.Deployment.dll" /R:"C:\Windows\assembly\GAC_MSIL\Microsoft.ReportViewer.WinForms\11.0.0.089845dcd8080cc91\Microsoft.ReportViewer.WinForms.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_64\System.Web\v4.0_4.0.0.0b03f5f7f11d50a3a\System.Web.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\Microsoft.Build.Framework\v4.0_4.0.0.0b03f5f7f11d50a3a\Microsoft.Build.Framework.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_64\System.Data\v4.0_4.0.0.0b77a5c561934e089\System.Data.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Drawing\v4.0_4.0.0.0b03f5f7f11d50a3a\System.Drawing.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.IdentityModel\v4.0_4.0.0.0__b77a5c561934e089\System.IdentityModel.dll" /R:"C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\6766643b\eedfb007\assembly\dl3\e20829de\00492037_f565d001\Telerik.Web.UI.Skins.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System\v4.0_4.0.0.0b77a5c561934e089\System.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.ServiceModel\v4.0_4.0.0.0b77a5c561934e089\System.ServiceModel.dll" /out:"C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\6766643b\eedfb007\App_Web_wenatrwt.dll" /D:DEBUG /debug+ /optimize- /win32res:"C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\6766643b\eedfb007\wenatrwt.res" /w:4 /nowarn:1659;1699;1701;612;618 /warnaserror- "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\6766643b\eedfb007\App_Web_wenatrwt.12.cs" "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\6766643b\eedfb007\App_Web_wenatrwt.4.cs" "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\6766643b\eedfb007\App_Web_wenatrwt.14.cs" "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\6766643b\eedfb007\App_Web_wenatrwt.6.cs" "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\6766643b\eedfb007\App_Web_wenatrwt.1.cs" "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\6766643b\eedfb007\App_Web_wenatrwt.8.cs" "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\6766643b\eedfb007\App_Web_wenatrwt.13.cs" "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\6766643b\eedfb007\App_Web_wenatrwt.3.cs" "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\6766643b\eedfb007\App_Web_wenatrwt.5.cs" "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\6766643b\eedfb007\App_Web_wenatrwt.11.cs" "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\6766643b\eedfb007\App_Web_wenatrwt.7.cs" "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\6766643b\eedfb007\App_Web_wenatrwt.10.cs" "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\6766643b\eedfb007\App_Web_wenatrwt.9.cs" "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\6766643b\eedfb007\App_Web_wenatrwt.0.cs" "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\6766643b\eedfb007\App_Web_wenatrwt.2.cs"

Microsoft (R) Visual C# Compiler version 4.0.30319.34209

for Microsoft (R) .NET Framework 4.5 Copyright (C) Microsoft Corporation. All rights reserved.

d:\inetpub\wwwroot\WolfeTest\Reports\CustomerSummary_Report.aspx.cs(23,30): warning CS0168: The variable 'ex' is declared but never used d:\inetpub\wwwroot\WolfeTest\Reports\CustomerSummary_Report.aspx.cs(29,30): warning CS0168: The variable 'ex' is declared but never used d:\inetpub\wwwroot\WolfeTest\Reports\Customer_Yearly_Report.aspx.cs(36,407): error CS0103: The name 'txtcustomername' does not exist in the current context d:\inetpub\wwwroot\WolfeTest\Reports\Customer_Yearly_Report.aspx.cs(36,1219): error CS0103: The name 'lblcustomercode' does not exist in the current context d:\inetpub\wwwroot\WolfeTest\Reports\Customer_Yearly_Report.aspx.cs(36,1372): error CS0103: The name 'lblcustomercode' does not exist in the current context d:\inetpub\wwwroot\WolfeTest\Reports\Customer_Yearly_Report.aspx.cs(37,866): error CS0103: The name 'txtcustomername' does not exist in the current context d:\inetpub\wwwroot\WolfeTest\Reports\Customer_Yearly_Report.aspx.cs(37,1809): error CS0103: The name 'txtcustomername' does not exist in the current context d:\inetpub\wwwroot\WolfeTest\Reports\Customer_Yearly_Report.aspx.cs(39,1834): error CS0103: The name 'txtcustomername' does not exist in the current context d:\inetpub\wwwroot\WolfeTest\Reports\Customer_Yearly_Report.aspx.cs(42,1095): error CS0103: The name 'lblcustomercode' does not exist in the current context d:\inetpub\wwwroot\WolfeTest\Reports\Customer_Yearly_Report.aspx.cs(45,1475): error CS0103: The name 'txtcustomername' does not exist in the current context d:\inetpub\wwwroot\WolfeTest\Reports\Customer_Yearly_Report.aspx.cs(45,2839): error CS0103: The name 'txtcustomername' does not exist in the current context d:\inetpub\wwwroot\WolfeTest\Reports\Customer_Yearly_Report.aspx.cs(113,34): error CS0103: The name 'lblcustomercode' does not exist in the current context d:\inetpub\wwwroot\WolfeTest\Reports\Customer_Yearly_Report.aspx.cs(141,17): error CS0103: The name 'txtcustomername' does not exist in the current context d:\inetpub\wwwroot\WolfeTest\Reports\Customer_Yearly_Report.aspx.cs(142,17): error CS0103: The name 'lblcustomercode' does not exist in the current context d:\inetpub\wwwroot\WolfeTest\Reports\QBYTDReceipt_Report.aspx.cs(25,30): warning CS0168: The variable 'ex' is declared but never used d:\inetpub\wwwroot\WolfeTest\Reports\QBYTDReceipt_Report.aspx.cs(31,30): warning CS0168: The variable 'ex' is declared but never used d:\inetpub\wwwroot\WolfeTest\Reports\QBPriorDayRecieptByTraderCoverage_Report.aspx.cs(23,31): warning CS0168: The variable 'ex' is declared but never used d:\inetpub\wwwroot\WolfeTest\Reports\DailyRevenueByClient-AdHoc_Report.aspx.cs(23,30): warning CS0168: The variable 'ex' is declared but never used d:\inetpub\wwwroot\WolfeTest\Reports\DailyRevenueByClient-AdHoc_Report.aspx.cs(57,26): warning CS0168: The variable 'ex' is declared but never used d:\inetpub\wwwroot\WolfeTest\Reports\DailyRevenueByClient-AdHoc_Report.aspx.cs(100,30): warning CS0168: The variable 'ex' is declared but never used d:\inetpub\wwwroot\WolfeTest\Reports\QBPriorDayReceipt-AdHoc_Report.aspx.cs(23,30): warning CS0168: The variable 'ex' is declared but never used

Show Complete Compilation Source:

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.34248

baansconsulting commented 8 years ago

can you try again please