dbr.toggle - Can I get the resulting columns displayed, to be the only ones exported to PDF?

(19 posts) (2 voices)

Started by shem
Latest reply from myDBR Team

Tags:

shem, Member

I just tried a very cool feature of yours, by adding to a report.
select 'dbr.toggle';
So if I uncheck three of the columns, so it will display in the browser say seven columns instead of ten.
Then when I click on Export to PDF I was hoping that only those seven columns would be exported to PDF, but alas all ten were exported.
Any suggestions?

I do not want to use select 'dbr.hidecolumn', 'My Column Name';
inside the report, because I do not want the columns to be hidden initially.
Only when the user Unchecks them.

In a separate but maybe related question:
Can I simulate the 'dbr.hidecolumn' by passing some parameter as part of a URL that references the myDBR report?

Thanks in advance.
myDBR Team, Key Master

The
dbr.toggle
is a client side functionality (JavaScript). The export functionality exports the original report, hence the client side modifications are not in the export.

Can I simulate the 'dbr.hidecolumn' by passing some parameter as part of a URL that references the myDBR report?

myDBR offers dynamic URL parameter functionality, allowing you to add dynamic parameters to the URL and then utilize them in the report. For instance, if you include your custom parameter in the URL like this: &myparam=123, you can then add a corresponding parameter to the report using the prefix GET_. For example, GET_myparam int can be added, and you can subsequently utilize this parameter within the report.

--
myDBR Team
shem, Member

Ok, I understand that the toggle functionality is Front end.
Is there a way to find which which of the toggle column headers have been UnChecked and then make a call the myDBR url using those fields as dynamic parameters ?
If its JavaScript would you be able to give me an example with code, as I am not really familiar with JavaScript.
Thanks
shem, Member

In a follow up to my above new question, please confirm the following so I understand what you are suggesting by dynamic parameters.

Is it true that for every one of the columns that I might want to hide (and there might be 20 of them), I have to include in my Store procedure signature a "GET_col1", GET_col2, etc..
That seems messy and time consuming when creating my reports.
shem, Member

In yet another follow up, I read the documentation about how
one defines a checkbox in the Settings-> Parameter queries page.
I apologize but I am having trouble understanding the example I saw there.
I don't understand the parameter called inCheckbox, and I don't understand where "inValue" comes from.
Can you somehow make it simpler with step by step how I instructions how I do this?
Thanks, in advance for answers to all of the above questions.
myDBR Team, Key Master
You can get the checked/unchecked values using JavaScript and pass them to the report as a paramerter. This is not a myDBR feature though, but your custom code.

You can get the toggled out columns as a comma separated list from table's hiddencols data attribute. To access it, add a CSS class to the result set:
```
select 'dbr.resultclass', 'mytable';
```
Then using JavaScript you can acces the attribute using:
```
$('table.mytable').data('hiddencols');
```
You will get a string "2,4" if second and fourth columns as hidden.

--
myDBR Team
shem, Member

Thank you for your response, but I only understand parts of your response and would love it if you could write out a full example for me.

Here are the things I am confused about:
1. Does your solution still require (or does the solution include) a select 'dbr.toggle'; ?

2) Am I correct that your solution is NOT using a checkbox?

3) Am I correct that in your statement
select 'dbr.resultclass', 'mytable';
you are suggesting I create a CSS class called "mytable"?
Well, what does the mytable CSS class contain? And is it to be located in userstyle.css ?

4) In the statement
$('table.mytable').data('hiddencols');
are the keywords "table" and "hiddencols" mydbr keywords? JavaScript keywords? CSS keywords? Where are they defined? Could you please be explicit in your answer?

5) Would this piece of JavaScript code be excecuted upon onLoad or something like that? I remind you that I do not really know JavaScript.

6) Is your solution suggesting I pass to the stored procedure a string (e.g. "2,4") containing the columns to be hidden, and then inside the stored procedure I would execute 'dbr.hidecolumn' for each of the comma separated numbers?

Thanks in advance, and again it would be great if you had a full example that I could copy.
I am still learning the ropes with myDBR, and I am sure that as I get more experienced I will be asking less and less questions.
myDBR Team, Key Master

1. Does your solution still require (or does the solution include) a select 'dbr.toggle'; ?

Your question pertains to detecting deselected columns when utilizing the dbr.toggle feature.

2) Am I correct that your solution is NOT using a checkbox?

Could you provide further clarification on this matter? The dbr.toggle entails a user interface that facilitates toggling the visibility of columns through checkboxes. The answer to your question does not have any checkboxes.

3) Am I correct that in your statement
select 'dbr.resultclass', 'mytable';
you are suggesting I create a CSS class called "mytable"?
Well, what does the mytable CSS class contain? And is it to be located in userstyle.css ?

The dbr.resultclass is primarily used for identifying the source of the toggled table. It's important to note that a report may contain multiple tables with toggleable columns. It's unnecessary to specify the class anywhere; the JavaScript call uses the class.

4) In the statement
$('table.mytable').data('hiddencols');
are the keywords "table" and "hiddencols" mydbr keywords? JavaScript keywords? CSS keywords? Where are they defined? Could you please be explicit in your answer?

In this context, 'table' refers to the HTML table, 'mytable' represents the CSS class defined with dbr.resultclass, and 'hiddencols' pertains to myDBR's internal structure, which holds a comma-separated list of hidden columns. This can be observed by opening the browser debugger.

5) Would this piece of JavaScript code be excecuted upon onLoad or something like that? I remind you that I do not really know JavaScript.

You invoke the script when you require information about hidden columns. When a user accesses the report, they can toggle the visibility of columns as desired. If you intend to transmit the list of hidden columns for a PDF export, the call should be performed when the PDF export call is made so that you can add the string to the URL as a parameter.

6) Is your solution suggesting I pass to the stored procedure a string (e.g. "2,4") containing the columns to be hidden, and then inside the stored procedure I would execute 'dbr.hidecolumn' for each of the comma separated numbers?

Yes.

If you lack familiarity with JavaScript, would recommend dedicating some time to learning the basics. Alternatively, you could seek assistance from a colleague who is well-versed in the subject.

--
myDBR Team
myDBR Team, Key Master
We made this bit easier. The dbr.toggle-command now adds parameter toggleN to the export to PDF URL. The N being the number for the resultset in the report.

If you have just one resultset in the query and the first and third column are hidden via the toggle., the parameter added to the URL will be &toggle1=0,2.

You can then add following command to the report (inExportFormat and GET_toggle1 being parameters for the report):
```
if (inExportFormat='pdf') then

 create temporary table toggles (i int);

 insert into toggles values (0), (1),(2),(3),(4),(5),(6),(7),(9),(10);

 select 'dbr.hidecolumn', i+1

 from toggles

 where find_in_set(i,GET_toggle1 );

end if;
```
--
myDBR Team
shem, Member

Thank you very much.
Indeed when I uncheck a couple of the columns and click on the PDF link which is part of the MyDBR export dropdown, the code you gave me works.

However, our reports are embedded in our PHP application, and we have our own button to export PDF.
Our PDF button does not add to the end of the URL the toggle part
e.g. &toggle1=1%2C3
Would you be able to tell me how I could extract that part of the URL from your link, and add it to my PDF button link?
Thanks
myDBR Team, Key Master

It depends how your application embeds the report. You can access the URL in myDBR object and use that in your app's PDF call.

--
myDBR Team
shem, Member

My PHP application embeds the report within an <object> tag.
Can you please tell me how from within my PHP program I "access the URL in myDBR object and use that in your app's PDF call."?
myDBR Team, Key Master
Assume your object tag has ID 'obj' and your application uses jQuery:
```
$('object').contents().find('li.pdf>a').attr('href')
```
Without jQuery, in plain JavaScript, you can use:
```
document.getElementById('obj').contentDocument.querySelector('li.pdf a').getAttribute('href')
```
--
myDBR Team
shem, Member

Thank you so much.
I was able to get it to work by coding my PDF button as follows

'javascript:void(0);', // Replacing the URL with JavaScript void to prevent default navigation
[
'class' => 'btn btn-default',
'onclick' => 'var url = document.getElementById("mydbr_obj_id").contentDocument.querySelector("li.pdf a").getAttribute("href"); url = url.replace("&rs=1", ""); window.location.href = url; return false;'
]

I have two follow up questions:
1) The filter toolbar from which I extracted the PDF URL (which has the "toggle" information) has a parameter "&rs=1"
What is its purpose? I noticed that only when I programmatically removed that parameter (see my code above) would my customized PDF header display !
Is it possible for you to generate the filter URLs to be without the "&rs=1" parameter?

2) Can you please get the toggle information to also be on the Excel and CSV Filter buttons as well?
myDBR Team, Key Master

1) The filter toolbar from which I extracted the PDF URL (which has the "toggle" information) has a parameter "&rs=1"
What is its purpose? I noticed that only when I programmatically removed that parameter (see my code above) would my customized PDF header display !
Is it possible for you to generate the filter URLs to be without the "&rs=1" parameter?

The rs parameter refers to the object within the report. for example, if you have two tables in the report, the eyword]rs[/keyword] parameter for the first one is 1 and 2 for the second.

2) Can you please get the toggle information to also be on the Excel and CSV Filter buttons as well?

The logic for that would be the same. Replace the filetype in the query with the one you are interested in. Also, you can use the browser's debugger tools to inspect the DOM.

--
myDBR Team

shem, Member

You wrote above:
The rs parameter refers to the object within the report. for example, if you have two tables in the report, the eyword]rs[/keyword] parameter for the first one is 1 and 2 for the second.

The question is:
Why is the rs parameter preventing the output of my customized PDF header, for example in the following piece of myDBR code:
# Handle PDF special functionality. PDF export should display a special header. Also Have the PDF export automatically download a file instead of overwriting the web page.

    IF (inExportFormat = 'pdf') THEN

        # Automatic download

        select 'dbr.export.options', 'content-disposition.pdf', 'attachment';

        # The next section is for our customized display of our PDF header

        select 'dbr.title', ''; # Remove the report title since it's part of the header

        SELECT `name` into @report_name FROM mydbr_reports WHERE proc_name = inAutoReportProcedure;

        select 'dbr.wkhtmltopdf', "--header-html ''"; # Disable default PDF header.

        CALL sp_DBR_pdf_header(clientdb, @report_name);

    END IF;

    # The next section would ideally Handle not only PDF but also Excel, and CSV Exports, but the toggle parameter is only on PDF

    IF (inExportFormat = 'pdf') THEN

        # The next section is for the feature of toggling the checkboxes for the column

        create temporary table toggles (i int);

        # The next line assumes that the most number of columns any of our reports will have is 25

        insert into toggles values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20);

        select 'dbr.hidecolumn', i + 1

        from toggles

        where find_in_set(i,GET_toggle1 );

    END IF;

The proof of what I am asking is when clicking on two different buttons within the myDBR portal itself.
The button to export PDF on the "Filter/toggle" toolbar has the rs=1 parameter, and when clicked will only display my grid and not my customized PDF header.
However, if I click on the button to export PDF on the Administrator toolbar at the top right, it does not contain the rs=1 parameter, and when I click on it it displays both my customized PDF header and the (toggled) grid.
Since I have no control over the "Filter/toggle" toolbar what can be done to resolve the situation? Is there a way to hide the button to export (Excel,PDF,CSV) on the "Filter/toggle" toolbar?

myDBR Team, Key Master

Why is the rs parameter preventing the output of my customized PDF header

Your custom header is not a header, but a result set in the report. When you specify the rs paramerer in the PDF export, it exports the resultset you specify in the rs-parameter. If you want to treat your custom header as a header, make it a real header (see export_header_pdf.php).

if you want the extra resultset to appear in your PDF, leave out the rs-parameter from the URL and you will get the full report.

Since I have no control over the "Filter/toggle" toolbar what can be done to resolve the situation? Is there a way to hide the button to export (Excel,PDF,CSV) on the "Filter/toggle" toolbar?

You can use CSS style to hide it.

--
myDBR Team
shem, Member

Thanks, I was able to do that.

Now I have a follow up question.
If my report had many result sets, and lets say the URL for the report looks like this

http://localhost/mydbr/report.php?r=sp_DBR_kpi_productivity&h=64723d358dc98fb3a610c7bf2120d8e1a65a6295e253b101f429f163a216799c&p1=%60az-ark%60&u2=ACTIVE&u3=2023-01-01&u4=2024-01-01&hdr=0&export=pdf&toggle1=1%2C2&toggle2=3%2C4

so I know that I need GET_toggle1 to get the parameter &toggle1=1%2C2
and I need GET_toggle2 to get the parameter &toggle2=3%2C4
So my plan was to use the values for toggle1 for the first result set and the values for toggle2 for the second result set.

But when I ran the report using the two calls to the IF statement you showed me above (containing create temporary table toggles etc..)
it only exported the first results set.
How do I get it to export all the result sets?
myDBR Team, Key Master

If you do not intentionally pick only one result set from the report, either by setting the rs-parameter in the URL or in the report logic, myDBR will export all the result sets in the report.

Your logic is otherwise correct.

--
myDBR Team

Reply

You must log in to post.