Excel report

[benaznbe]

Hi all.

I use excel report required to retrieve 1 month The data I extracted it into a single sheet all 30 days. But I want to give you the information I pulled out 1 day 1 sheet, it will get all 30 sheets. Can I set a preference?

Thank you

[phildunlap]

Hi benaznbe,

I am not sure what you're asking. The /excel-reports.shtm page enables you to create or run Excel Reports which are templated using named ranges in Excel documents. So, where data is and on which sheet is within your control. If, however, you are downloading data from one of the many places you could be doing so, then how many points are on what kind of sheet will vary based on where you get the Excel document. So, can you post a screenshot of what you are talking about?

[benaznbe]

@phildunlap

This image is made from Excel Reports template. I set a date of 5 to 6. I want the name sheet match the date I chose.
The other way i want Day 5 of 1 Sheet and Day 6 of 1 Sheet . You see me at the yellow strip.

[phildunlap]

If I had to guess, you have two time periods defined doing five minute rollups over individual days.

Can you export your excel report template from the /ui/administration/import-export page to JSON and share that here? Perhaps also a screenshot of its configuration?

It sounds like you could define named ranges on the second sheet, and then reference the named ranges on the second sheet in the second time period.

[benaznbe]

@phildunlap configuration excel report template. I need to edit settings.

{
  "excel-report-templates": [
    {
      "schedule": true,
      "timeSeries": [
        {
          "periodType": "DAYS",
          "dateRangeType": "RELATIVE",
          "periodCount": 1,
          "name": "Time period 1",
          "rollupPeriodType": "MINUTES",
          "relativeDateType": "PAST",
          "namedRange": "Timestamp",
          "rollupPeriods": 5,
          "events": [],
          "rollup": "AVERAGE",
          "points": [
            {
              "pointXid": "PJST-SMA_Ambient_TEMP",
              "namedRange": "AIR1"
            },
            {
              "pointXid": "PJST-SMA_PVTEMP",
              "namedRange": "Air_Temp2"
            },
            {
              "pointXid": "XID_SMA_CAL_Irradiation1",
              "namedRange": "Radiation"
            },
            {
              "pointXid": "XID_SMA_CAL_Irradiation2",
              "namedRange": "Rad2"
            },
            {
              "pointXid": "PM2100#3_Active_Power_Total",
              "namedRange": "PQKW"
            },
            {
              "pointXid": "PJST-SMA_Total_yield",
              "namedRange": "PQKWH"
            }
          ]
        }
      ],
      "xid": "XID_Report",
      "emailTemplateFilename": "",
      "filename": "Solar.xls",
      "reportReadPermission": "user",
      "name": "Report",
      "scheduleCron": "0 0 * * * ?",
      "preventPurge": false,
      "user": "admin",
      "editPermission": "user",
      "email": false
    }
  ]
}

[phildunlap]

It sounds like you probably want the 'Ago' time period type and to use multiple timed ranges referencing different named ranges on separate sheets. For instance, this configuration exported a five minute average from three days ago on the first sheet, and a five minute average from four days ago on the second sheet.

[benaznbe]

@phildunlap I scheduled through the dashboard page in the attached image. Then press the button next to it. Can it be combined with what you say?

<div layout="row">
    <mdp-date-picker mdp-placeholder="From date" mdp-open-on-click="" mdp-format="ll" ng-model="from"></mdp-date-picker>
    <mdp-time-picker mdp-placeholder="From time" mdp-open-on-click="" mdp-auto-switch="true" ng-model="from" mdp-format="LTS"></mdp-time-picker>
    </div>
    
    <div layout="row">
    <mdp-date-picker mdp-placeholder="To date" mdp-open-on-click="" mdp-format="ll" ng-model="to"></mdp-date-picker>
    <mdp-time-picker mdp-placeholder="To time" mdp-open-on-click="" mdp-auto-switch="true" ng-model="to" mdp-format="LTS"></mdp-time-picker>
    </div>


    <ma-excel-report-run id="de5e6db9-19c3-480e-a5f3-d0c00a3d32ca" style="position: absolute; left: 470px; top: 45px;" xid="XID_Report" from="from" to="to"></ma-excel-report-run>

[Jared Wiltshire]

The <ma-excel-report-run> component doesn't support multiple time periods. Its something we can probably add if you need it though.

The harder part will probably be splitting the selected date range up into daily increments and passing it through to the component as separate time periods.

This all of course requires you to setup 31 time periods in an Excel report. This sounds pretty cumbersome.

I think maybe a better approach would be to create a single sheet with a named range for the whole month which Mango writes the data into. Then you could create a sheet in the Excel workbook for every day and insert formulas into them which pulls the data from the "whole month" sheet.

[psysak]

@jared-wiltshire said in Excel report:

hink maybe a better approach would be to create a single sheet with a named range for the whole month which Mango writes the data into.

Is it still the case that the <ma-excel-report-run> does not support multiple ranges? I have a report which has several ranges because I'm exporting multiple points and just ran into the problem that if I try to run the report from a dashboard I only get the first range correctly :(

[Jared Wiltshire]

@psysak said in Excel report:

Is it still the case that the <ma-excel-report-run> does not support multiple ranges?

There are three ways of running it, by default it only sets the date range for one time series. You can however get it to set the dates for all the time series by setting all-time-series="true" or only certain time series by passing time-series-names="['Time series 1', 'Time series 3']"

[psysak]

@jared-wiltshire said in Excel report:

@psysak said in Excel report:

Is it still the case that the <ma-excel-report-run> does not support multiple ranges?

There are three ways of running it, by default it only sets the date range for one time series. You can however get it to set the dates for all the time series by setting all-time-series="true" or only certain time series by passing time-series-names="['Time series 1', 'Time series 3']"

I suspected you might say that, I was curious what the option was for. That's perfect Jared!