help.axcms.netAxinom Logo
Save Save Chapter Send Feedback

Generating reports in Excel

 

Using data generated by Reporting Template Project cube, you will be able to create and present various reports and charts in Excel.

 

Connecting to Reporting Template Cube in Excel

The example Excel file provided with Reporting Template Project shows some basic reports using the data warehouse.

C:\Projects\AxCMS_ReportingSample\Resources_ReportingSample\Reporting\Sample.xlsx

or

C:\Projects\AxCMS_ReportingSample\Resources_ReportingSample\Reporting\SampleMC.xlsx

if you have MailCenter integrated

If you are running Vista,  find Start/Programs/Microsoft Office/Excel  and  right click/ Run as Administrator.
Then in Excel, open the excel file instead of opening it directly. This is needed for cube connection to work.



By default, it is configured to work with Cube_ReportingSample on localhost. To change this (if Cube is located on a different server than localhost or if you need to use the Cube_ReportingSample_MC for MailCenter reports), follow this instructions:

  • Open the sheet in Excel
  • Select the ribbon 'Data'
  • Select 'Connections'
  • Select the connection 'Reporting_Cube'
  • Click the button 'Properties'
  • Switch to tab 'Definition'
  • Click "Browse" and select the connection file with correct cube to connect to


    If you don't have MailCenter integrated:
    C:\Projects\AxCMS_ReportingSample\Resources_ReportingSample\Reporting\Configuration\ReportingSample_Cube.odc

    If you have MailCenter integrated:
    C:\Projects\AxCMS_ReportingSample\Resources_ReportingSample\Reporting\Configuration\ReportingSample_Cube_MC.odc
  • If the cube(s) are located on server different than localhost, you can change the connection details either in .odc files or directly in Connection string window

 

Now, press Refresh in Workbook Connections to retrieve new data from cube.

Creating a report in Excel file

  • Insert a PivotTable (or PivotChart if a chart should be shown with the table). Select to use an external data source and choose the Cube_reportingSample (Cube_reportingSampleMC for MailCenter reports) connection. Next select if you want to create the table on new worksheet or a existing worksheet.

 

The Privot Table Field List is opened to select values to the Table (and Chart)

  • Select action value(s) for the report from Privot Table Field List i.e. LS Banner Click Count and LS Banner Impression Count.

 

Numbers of Banner impressions and Banner clicks are shown in table.

 

  • Select action info value(s) for the report from Privot Table Field List i.e. Create Date, HourOfDay

 

Number of Banner impressions and Banner clicks are shown in table and also what hour of the day the actions were made.

  • Left click + hold and drag the HourOfDay option from the Column Labels area to the Row Labels area

 

The table shows the hour when the action where made as rows.