add a formula to column when exporting to csv

(10 posts) (2 voices)
  1. cayasanchez, Member

    MyDBR Team:

    I'm trying to export a myDBR report to CSV, the report has currency symbols and also percentages.
    I used the option:

    select 'dbr.export.options', 'csv.skip_formatting', 1;

    It works well for the currency columns the problem is with the percentages columns. For example if the percentage was 9.5% I want it to export as 0.095, now it is exporting as 9.5

    I don't want to change the way the report shows, it is right to show the 9.5% value, I just want to change the value when exporting to CSV.
    Reviewing the documentation there are some export options for excel to add formulas, but didn't find anything for CSV.

    Please help me, how to convert the 9.5 to 0.095 when exporting to CSV.

  2. myDBR Team, Key Master

    if you want to show different value for CSV export as in other formats, use the inExportFormat automatic parameter to determine which format the user is requexting and then show the calculation you wish to show.

    myDBR Team

  3. cayasanchez, Member

    Could you send me an example of how to use the command inExportFormat to change the percentage column?

  4. myDBR Team, Key Master

    You can for example do:

    declare v_divider int;
    if (inExportFormat='csv') then
    set v_divider = 100;
    set v_divider = 1;
    end if; select percent / v_divider as 'Percent'
    from yourdata;

    myDBR Team

  5. cayasanchez, Member

    I tried your code and I got error:
    Undeclared variable: inExportFormat

    Then I added the inExportFormat as input variable and it worked.
    But I don't want to add an additional parameter, it should be easy for the user.
    Is there anyother way to do it? MyDBR doesn't have any function to know if the user clicked on export CSV? My user will always look at the report in HTML and then will download the data to CSV.

    The documentation for using inExportFormatSet is so poor.

  6. myDBR Team, Key Master

    The inExportFormat is an automatic parameter. This means that when you add this parameter to a report, myDBR will automatically populate it with the correct value. The parameter is not shown to the user.

    The string value for the parameter will be the URL's export-parameter value (empty string for HTML, csv, xlsx, pdf or sql).

    MyDBR doesn't have any function to know if the user clicked on export CSV? My user will always look at the report in HTML and then will download the data to CSV.

    This is excatly what the inExportFormat parameter is for.
    myDBR Team

  7. cayasanchez, Member

    If I do not declare the inExportFormat as input parameter then I get this error:
    Undeclared variable: inExportFormat

    If this is an automatic parameter why I'm getting that error?

    I'm using exactly the code you sent me.

  8. myDBR Team, Key Master

    Automatic parameters are the parameters you define for the report and they will, as said, be automatically populated by myDBR. The reason you are getting the error is that you are using a variable/parameter that is not defined.

    See a demo.

    myDBR Team

  9. cayasanchez, Member

    I cannot open the demo, I got:

    Unfortunately, we could not complete your request. Please contact your system admin.

    Do you have any other documentation with examples so I can see how to define the inExportFormat?

  10. myDBR Team, Key Master

    You can try the demo again.

    myDBR Team


You must log in to post.