Conditional images in reports based upon data

Toad Data Point
1

All,

Our team recently decided to adopt Toad for Data Analysts to manage some clinical trials data. One more complex Excel report needs to have a red, yellow, or green circle in the rows of one column (“Column B”). The color of the circle is dependent upon the data in another column in the report (“Column A”), such that if the value of Column A for a given row is 0-0.25 that row’s Column B would have a green circle, if Column A is 0.25-0.5 Column B would have a yellow circle, and so forth.

I’ve seen what appears to be functions that would allow me to implement this data-driven image/shape placement in an Excel report, but I haven’t been able to figure out how to piece these functions together correctly.

  1. Does TDA support this conditional image/shape insertion and if so what are the steps to set this up?

  2. If each time this report is generated, the query will need to pull out a different set of clinical trials, would it be smarter to save this query with variables?

My apologies if this questions was posed in a previous thread; I was not able to use the right search terms to find it.

Lauren

2

You could try doing this one of two ways.

  1. In Excel you can use a macro. In our next version of TDA you will be able to fire the macro in our automation scripts.

  2. You can build a Toad Data report that uses a formatting rule based on the value of a column.

Debbie

3

The ability to run an excel macro from automation would be great. Do you mean the next beta version or 2.8?

Thanks,
Paul

4

Thanks for the feedback, Debbie! I found the PictureBox and conditional features, which may be what you were talking about in your point 2. What would be most helpful is detailed documentation on formatting rules (and most other reporting features) so I can determine which are appropriate and actually understand what all of the input parameters do. As a new user, I’ve found the documentation that ships with the application & online tutorial videos to be very high-level and thus not that helpful.

Does such detailed documentation exist? My apologies if I’ve overlooked it.

5

Actually it is in the current 2.7 Beta right now. You would need to write the macro in your Excel file, Then when exporting data, choose the file with the macro. The Export Wizard will know it has a macro in it and a new page in the the Wizard will display showing all macros. You choose whether to fire the macro before or after the data is exported.

Debbie

6

If you want to try and use the Toad Data Reports and the formatting rules here is a help topic that might help. This is from DevExpress which is the component we use.

http://documentation.devexpress.com/#XtraReports/CustomDocument5167

Debbie

7

Following the information found via the link to devexpress.com on formatting rules.

I’m looking at using the PictureBox control to display a coloured image for the RAG status (Red, Amber, Green), based on the value in a field named [RAGStatus].

The permitted values of this field are Red; Amber; Green.

I have created three PictureBox controls in my report, each with the required coloured image.

Then I have created three FormattingRules with condition:

[RAGStatus] == Red

[RAGStatus] == Amber

[RAGStatus] == Green

I have set data Binding to the [RAGStatus] field.

But still don’t seem to get this to work…

How do I set conditional visibility of these picturebox controls so only the correct image will display?

Any help and advise would be welcome.

Thank you in advance.

Regards

David

8

}

}

}

After a bit of playing about I found the solution.

For each pictureBox you need to enter OnBeforePrint scripts:
private void pictureBox1_BeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e) {pictureBox1.Visible = false;private void pictureBox2_BeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e) {pictureBox2.Visible = false;private void pictureBox3_BeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e) {pictureBox3.Visible = false;
Then with the pictureBox control selected in the Properties window set the Behavior > Script > OnBeforePrint property to the same name.

Also make sure your Formatting Rules have Visible = True and the DataMember and DataSource set.

Mine are Table1 and dataSet1.

That way the pictureBox should only display in Preview when the value in RAGStatus matched your Formatting Rule.

9

Thanks for finding this

Debbie