Working with reports can be a little frustrating. Consider this listing of the General report category.
And then try and answer the following questions
- Which reports have charts?
- Which reports show a Gauge?
- Which reports are Cross Tabs?
- Which reports were created by the user Susan Maye?
But I don't have this problem now as you can see from the screen below.
I created a new administration screen using a pair of classic ASP pages.
How did I do this?
1) The first thing I did was to read remind myself about the way in which the reports are defined in the system.
You should read two earlier articles to understand what I did.
2) The second thing I did was to then create a new view that returned the information that I wanted.
I created this as a system view called vCustomReportAdmin
And it had the following structure.
But the SQL can be seen below.
[code language="sql"]
CREATE VIEW vCustomReportAdmin
AS
SELECT dbo.Custom_Reports.Repo_ReportId, dbo.Custom_Reports.Repo_Name, dbo.Users.User_FirstName + ' ' + dbo.Users.User_LastName AS user_fullname,
dbo.Custom_Reports.Repo_Title, dbo.Custom_Reports.Repo_PrintOptions, dbo.Custom_Reports.Repo_CreatedBy, dbo.Custom_Reports.Repo_CreatedDate,
dbo.Custom_Reports.Repo_UpdatedDate, dbo.Custom_ReportCharts.ReCh_Style, dbo.Custom_Reports.Repo_Category, CASE WHEN (reba_crosstabfield IS NOT NULL)
THEN 'CrossTab' WHEN reba_inrangefield IS NOT NULL THEN 'Historical' ELSE 'List' END AS repo_type
FROM dbo.Custom_Reports LEFT OUTER JOIN
dbo.Users ON dbo.Custom_Reports.Repo_CreatedBy = dbo.Users.User_UserId LEFT OUTER JOIN
dbo.Custom_ReportBands ON dbo.Custom_Reports.Repo_ReportId = dbo.Custom_ReportBands.ReBa_ReportId LEFT OUTER JOIN
dbo.Custom_ReportCharts ON dbo.Custom_ReportBands.ReBa_ReportBandId = dbo.Custom_ReportCharts.ReCh_ReportBandID
WHERE (dbo.Custom_Reports.Repo_PrintOptions = 63) AND (dbo.Custom_Reports.Repo_Category <> N'Account')
[/code]
NB: I am using a system that does not use the Accounts entity so I have suppressed this using the where clause. You may decide to changed the SQL in anyway you need.
3) The third task that I did was to create a new table connection. This is because I wanted to have control over defining captions and field types for the data returned by the view.
I did this within the Advanced Customization area.
I created my new Table with the same name as the view (although I think it would have been better to have given it a more distinct name).
4) My fourth job was to then ensure the fields returned by the view were defined correctly in meta data. The new 'Table' was accessible through the Customization screens.
And this allowed me to change captions and to make sure that fields were defined as selection lists where necessary.
For example in my case:
- rech_style needed to be defined as a selection list and be given a caption 'Chart Style'.
- repo_type needed to be defined as a selection list and be given a caption 'Report Type'.
- repo_category just needed to be defined as a selection list.
5) The fifth thing that I then did was to then create a list for the new custom table.
And I added the fields that I needed.
NB: I have added here also a hyperlink to an ASP page that will redirect to the Report Editing screen. I'll cover that later below but its page was "AdminExtras/CustomReportsEdit.asp".
6) My sixth task was to create a new screen to allow the filtering of the reports.
NB: This was defined as a search screen linked to the list I created as my fifth task.
The screen held the fields that I wanted to use for searching the different reports in my system.
7) The seventh action I then did was to create the link to the Customization menu. I did this in the area
Administration -> Advanced Customisation -> System Menus
The menu I changed was 'AdminCustomization'.
This referenced the ASP file that I would then need to create.
"AdminExtras/CustomReports.asp"
8) As my eighth task I turned to creating the ASP pages.
This had the structure of a simple search page.
[code language="javascript"]
<%
CRM.SetContext("Find");
var listBlock = CRM.GetBlock("vCustomReportAdminList");
var searchBlock = CRM.GetBlock("vCustomReportAdminSearchBox");
searchBlock.Title = CRM.GetTrans("Tabnames","reports");
var strSearchFormClearButton = CRM.Button("Clear", "clear.gif", "javascript:document.EntryForm.em.value='6';document.EntryForm.submit();");
var myBlockContainer = CRM.GetBlock("Container");
with (myBlockContainer)
{
AddBlock(searchBlock);
AddBlock(listBlock);
AddButton(strSearchFormClearButton);
}
listBlock.ArgObj = searchBlock;
CRM.AddContent(myBlockContainer.Execute());
Response.Write(CRM.GetPage());
%>
[/code]
9) The ninth and penultimate task was to create the redirect page.
[code language="javascript"]
<%
var intRecordId = CRM.GetContextInfo("user","user_userid");
Response.Redirect(CRM.URL(1401)+"&Key0=41&Key4="+intRecordId+"&Act2=update&Key41="+Request.Querystring("repo_reportid"));
%>
[/code]
10) The tenth, last and final task was to make sure that everything works as intended.
I can filter to find Bar charts associated with Cross Tab reports.
And then I can click to edit the report.
