Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rhondac
Starting Member
1 Post |
 Posted - 2008-04-28 : 07:42:21
|
| Hello,I need to create a report that will count new cases based on the create date(create_date) and criteria for the groups(The only way to distinguish between the 2 major groups mts and bnb is area!= 'bnb" because everything else is MTS). The sample report I need to create below shows how it needs to be counted weekly, for a 4 month period, for the groups under MTS and BNB. The totals and grand totals can be achieved in the report tool. I want to create variables for the new cases (mts_newcases_sales, mts_newcases_salesd, bnb_newcases_salesd etc)Ex. MTS sales : (status = 'Calculated' OR status = 'REJECTED') and errorsource != 'marketing' and accountns is null and area != 'BNB'(everything else is MTS)MTS salesd ; Credit >= '1001' and (status = 'REJECTEDV' or status = 'ACCEPTEDS') and errorsource != 'marketing' and accountnr is nullBNB creditr: Credit < 101 and (status = 'SUBMITTED' OR status = 'REJECTEDS' OR status = 'REJECTEDA' OR STATUS = 'ACCEPTEDC') 12-Jan 19-Jan 26-JanMTS New Cases Received 85 84 79 Sales 30 32 27 SalesD 47 34 37 SalesV 3 7 - CreditR 44 29 26 CreditB 6 12 9 CreditS - - - CreditP 10 11 11 MTS Subtotal 140 125 110 Broadb New Cases Received 12 13 14 Sales - - - SalesD - - - SalesV - - - CreditR 12 11 12 CreditB 8 13 9 CreditS - - 2 CreditP 1 1 1 Broadb Subtotal 21 25 24 Total New Cases Received 97 97 93 Sales 30 32 27 SalesD 47 34 37 SalesV 3 7 - CreditR 56 40 38 CreditB 14 25 18 CreditS - - 2 CreditP 11 12 12 Grand Total 161 150 134 This is just a very brief bit of codeSELECT MTS_new_cases_sales, mts_new_cases_salesd …….FROM vwCreditN WHERE mts_sales_new_cases = ( )...and (status = 'Calculated' OR status = 'REJECTED')... Can you please show me how to accomplish this? I have never created a vertical type report before, can select statments be placed in textbox expressions? Can I do counts in there instead of the main query?Thank you in advance for your effort, Rhonda |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-28 : 10:49:41
|
| You need to return your entire data using a procedure or query to report dataset. The individual filterations and conditional expressions can be written inside report tablkes it self.ex:for the first table (MTS) place this filterFields!area.value != "bnb"and inside table MTS sales field count is taken as=SUM(IIF( (Fields!status.value = "Calculated" OR Fields!status.value = "REJECTED") and Fields!errorsource.value != 'marketing' and Len(Fields!accountns.value)=0,1,0))Similarly for BNB creditr=SUM(IIF(Cint(Fields!Credit.value) < 101 AND (Fields!status.value = "SUBMITTED" OR Fields!status.value = "REJECTEDS" OR Fields!status.value = "REJECTEDA" OR Fields!status.value = "ACCEPTEDC"),1,0)) |
 |
|
|
|
|
|
|
|
Need Help with report!!!