| |
Пример настраивомого отчета
Для вызова отчета используется форма
в которой указываются необходимые уровни группировки и условия отбора
Код формы :
Private Const RepName = "Svod1"
Private Sub closeform_Click()
DoCmd.Close acForm, Me.Name
End Sub
Private Sub Show_report_Click()
Dim sql As String 'Условие
Dim SelectName As String 'Заголовок отчета
Dim YearWhere As String 'Условие по дате
'На основе комбобокса собираем условие
If Me.fid_curator <> 0 Then sql = " and id_curator = " & fid_curator
If Me.fid_curator <> 0 Then SelectName = vbCrLf & " Руководитель - " & fid_curator.Column(1)
If Me.fid_partner <> 0 Then sql = sql & " and id_partner = " & fid_partner
If Me.fid_partner <> 0 Then SelectName = SelectName & vbCrLf & " Партнер - " & fid_partner.Column(1)
If Me.fyearwhere = 1 Then YearWhere = " and plan_year = 2004 "
If Me.fyearwhere = 2 Then YearWhere = " and plan_year * 12 + plan_month between 2004 * 12 + 7 and 2005 * 12 + 7 "
If Me.fyearwhere <> 0 Then SelectName = SelectName & vbCrLf & " на " & Me.fyearwhere.Column(1)
Dim RepName
RepName = "Svod1"
If CurrentProject.AllReports(RepName).IsLoaded Then DoCmd.Close acReport, RepName
On Error Resume Next
'Условия группировки - флажки собираются в аргументы открытия
DoCmd.OpenReport RepName, acViewPreview, , , , sql & "<next>" & SelectName & "<next>" &
Me.itog_partner & "<next>" & Me.itog_curator & "<next>" & Me.itog_total & "<next>" & Me.itog_month & "<next>" & YearWhere
DoCmd.RunCommand acCmdZoom100
End Sub
Код отчета :
Option Compare Database
Option Explicit
Public sql As String
Public SelectName
Public Itogpartner As Boolean
Public Itogcurator As Boolean
Public ItogTotal As Boolean
Public ItogMonth As Boolean
Public YearWhere As String
Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
'Разбор аргументрв открытия отчета
If Nz(Me.OpenArgs, "") = "" Then Exit Sub
Dim Tmparr
Tmparr = Split(Nz(Me.OpenArgs, ""), "<next>", , vbTextCompare)
sql = Tmparr(0)
If Tmparr(1) <> "" Then SelectName = "Выборка:" & Tmparr(1)
Itogpartner = Tmparr(2)
'Если группировка по партнеру не требуется - ставим константу в контролсоурс
If Not Itogpartner Then Me.GroupLevel(2).ControlSource = "=2"
Itogcurator = Tmparr(3)
If Not Itogcurator Then Me.GroupLevel(3).ControlSource = "=3"
ItogTotal = Tmparr(4)
ItogMonth = Tmparr(5)
YearWhere = Tmparr(6)
If ItogMonth Then
Me.capfoot.ControlSource = "=""Всего "" & monthname([plan_month]) & "" "" & plan_year "
Me.caphead.ControlSource = "=monthname([plan_month], )& "" "" & plan_year "
End If
Dim YearSubWhere As String
YearSubWhere = " and idpartners_fplan_prop in ( select id_partners_fplan_prop FROM dbo.partners_fplan_summ fps1 where 1=1 " & YearWhere & " )"
Dim sqlstr(-1 To 0)
sqlstr(0) = " SELECT 'Все периоды' as plan_monthyear , 'Все годы' as plan_year ,fc.Savedfio AS fiocurator, fp.Savedfio AS fiopartner," & _
" (SELECT isnull(SUM(sumplat), 0) FROM dbo.partners_fplan_summ fps1 WHERE id_partners_fplan_prop = idpartners_fplan_prop AND fps1.id_strash = 64 " & YearWhere & " ) AS sum64, " & _
" (SELECT isnull(SUM(sumplat), 0) FROM dbo.partners_fplan_summ fps1 WHERE id_partners_fplan_prop = idpartners_fplan_prop AND fps1.id_strash = 66 " & YearWhere & " ) AS sum66, " & _
" (SELECT isnull(SUM(sumplat), 0) FROM dbo.partners_fplan_summ fps1 WHERE id_partners_fplan_prop = idpartners_fplan_prop AND fps1.id_strash = 96 " & YearWhere & " ) AS sum96, " & _
" (SELECT isnull(SUM(sumplat), 0) FROM dbo.partners_fplan_summ fps1 WHERE id_partners_fplan_prop = idpartners_fplan_prop AND fps1.id_strash = 86 " & YearWhere & " ) AS sum86, " & _
" (SELECT isnull(SUM(sumplat), 0) FROM dbo.partners_fplan_summ fps1 WHERE id_partners_fplan_prop = idpartners_fplan_prop AND fps1.id_strash = 85 " & YearWhere & " ) AS sum85, " & _
" p.plat_description, p.saved_clientname, p.prj FROM dbo.partners_fplan_prop p INNER JOIN dbo.Used_partners fc ON p.id_curator = fc.id_sotr INNER JOIN dbo.Used_partners fp " & _
" ON p.id_partner = fp.id_sotr WHERE (1 = 1)" & sql & YearSubWhere
sqlstr(-1) = " Select * from ( SELECT months.mm as plan_month , yyyy as plan_year , months.mm + years.yyyy * 12 as plan_monthyear, fc.Savedfio AS fiocurator, fp.Savedfio AS fiopartner," & _
" (SELECT isnull(SUM(sumplat), 0) FROM dbo.partners_fplan_summ fps1 WHERE id_partners_fplan_prop = idpartners_fplan_prop AND fps1.id_strash = 64 and plan_month = months.mm and plan_year = years.yyyy " & YearWhere & " ) AS sum64, " & _
" (SELECT isnull(SUM(sumplat), 0) FROM dbo.partners_fplan_summ fps1 WHERE id_partners_fplan_prop = idpartners_fplan_prop AND fps1.id_strash = 66 and plan_month = months.mm and plan_year = years.yyyy " & YearWhere & " ) AS sum66, " & _
" (SELECT isnull(SUM(sumplat), 0) FROM dbo.partners_fplan_summ fps1 WHERE id_partners_fplan_prop = idpartners_fplan_prop AND fps1.id_strash = 96 and plan_month = months.mm and plan_year = years.yyyy " & YearWhere & " ) AS sum96, " & _
" (SELECT isnull(SUM(sumplat), 0) FROM dbo.partners_fplan_summ fps1 WHERE id_partners_fplan_prop = idpartners_fplan_prop AND fps1.id_strash = 86 and plan_month = months.mm and plan_year = years.yyyy " & YearWhere & " ) AS sum86, " & _
" (SELECT isnull(SUM(sumplat), 0) FROM dbo.partners_fplan_summ fps1 WHERE id_partners_fplan_prop = idpartners_fplan_prop AND fps1.id_strash = 85 and plan_month = months.mm and plan_year = years.yyyy " & YearWhere & " ) AS sum85, " & _
" p.plat_description, p.saved_clientname, p.prj FROM dbo.partners_fplan_prop p INNER JOIN dbo.Used_partners fc ON p.id_curator = fc.id_sotr INNER JOIN dbo.Used_partners fp " & _
" ON p.id_partner = fp.id_sotr cross join (select distinct plan_month as mm FROM dbo.partners_fplan_summ where 1=1 " & YearWhere & ") months " & _
" cross join (select distinct plan_year as yyyy FROM dbo.partners_fplan_summ where 1=1 " & YearWhere & ") years " & _
" WHERE (1 = 1)" & sql & " ) src where sum64 <> 0 or sum66 <> 0 or sum96 <> 0 or sum86 <> 0 or sum85 <> 0 "
Me.RecordSource = sqlstr(ItogMonth)
End Sub
Private Sub ЗаголовокГруппы0_Format(Cancel As Integer, FormatCount As Integer)
Me.ЗаголовокГруппы0.Visible = Me.Itogpartner
End Sub
Private Sub ЗаголовокГруппы2_Format(Cancel As Integer, FormatCount As Integer)
Me.ЗаголовокГруппы2.Visible = Me.Itogcurator
End Sub
Private Sub ПримечаниеГруппы1_Format(Cancel As Integer, FormatCount As Integer)
Me.ПримечаниеГруппы1.Visible = Me.Itogpartner
End Sub
Private Sub ПримечаниеГруппы3_Format(Cancel As Integer, FormatCount As Integer)
Me.ПримечаниеГруппы3.Visible = Me.Itogcurator
End Sub
Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
GroupFooter1.Visible = ItogMonth
End Sub
Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
GroupHeader0.Visible = ItogMonth
End Sub
Private Sub grouptotal_Format(Cancel As Integer, FormatCount As Integer)
Me.grouptotal.Visible = ItogTotal
End Sub
Private Sub Report_NoData(Cancel As Integer)
MsgBox "Нет проектов по выбранному условию"
Cancel = True
End Sub
| |
