Can HTML-based reports be built in BLS via an SQL Query and XSLT?

[Former Member]

Hello xMII experts,

I have already built a report in xMII which uses XSLT to provide group/sum totals in a web browser. However in a new project, the report must run at certain times and possibly when certain signals become true in the process.

It appears that BLS is a good choice to achieve this and my proposed Transaction was:

1. SQL Query Action(uses the same QueryTemplate as in xMII)

2. XSLTransform Action on the resulting XML resultset (The .xsl file contains HTML which is the original used in xMII to produce the report there)

3. HTML Loader action with the resulting output of the transformation

I have now got some output in the resulting HTML file - however it omits all XSLT code - and I am left with an empty HTML shell but for a few images.

This indicates that perhaps no SQL data was ever returned.

I have therefore two questions:

1. How can I check if the SQL returned data?

2. Is it possible to deliver data to a HTML file directly after an XSLTransformation.

Looking forward to your responses

Best Regards

Robert Sales

[Former Member · Accepted solution]

Hi Robert

In your BLS you can use Tracer Action block and link it to sql output to check whether sql query is returning data or not.If sql query is returning more than one row, you need to use Repeater.

You can go through this thread.Hope this may help you.

https://forums.sdn.sap.com/click.jspa?searchID=26082298&messageID=6898717

[Former Member]

Hi Robert,

This was really good scenario to refresh my MII skills.

I just saw your initial question couple of days back and never visited your thread, since I wanted to find solution in my own way.

To be frank, I never used XSLT or sending email option much in my projects, so I thought to try this once.

To try this example, I opened the BLS and done following steps:

1. Inserted a Tag Query.

2. Then loaded the 'xsl' file from the server ... using XML_Loader.

3. Added one 'XSLTransformation' action with:

a. XSLT - Input = TagQuery - Output

b. XSLT - InputXSL = XML_Loader - Content

4. Then use Write_File or HTML_Saver action block, to save your this XSL transformed report on your server.

This was good experience to work on. I appreciate you for coming up with such requirement. When today I saw all replies to your thread, only your approach looked sensible.

[Former Member]

I have found a solution which works as follows:

1. SQL Query Action after the Schedule Editor triggers the Transaction

2. XSLT Action with the Results XML of the SQL Query Action as the Input XML

3. HTML Save Action with the XSLT Output as the source.

This gives me a report xxxx.html at a time triggered in the Schedule Editor.

Thank you for the tips.

Can someone show me how to reply to individuals and award points?

Best Regards

Robert Sales

[Former Member]

Robert,

Congrats! You did in a day or 2

You can reply to individuals in forum(here itself) like at edge there is reply link which gives reply to that person.Else u need to take there Email(Obviously ).

To award Points its same as you have done previously , you can change if you need, going to that and selecting diff options. For every reply u can award points since u get that radio options for every ans individual made.

Regards,

Padma

[Former Member]

Hello again,

Firstly I have not seen how you respond to individuals so apologies for that.

I think I should explain in more general terms what I need as what I have already completed may need changing.

I need to generate/create and print out HTML reports at certain specified times (via Schedule Editor perhaps) and when a signal in the PLC program becomes true. The plan is to automate creation and print outs of the reports via these two triggers.

Of this I have seen how to trigger a transaction at certain times and to generate the desired output in XML via an SQL Query.

What I don't know is how to automate XSLT application to the XML resultset so that I create my report with the correct HTML/Javascript embedded code executed together with the XSLT formatting. There should be no user interaction.

Can XSLT Transform action handle it if the .xsl file has HTML and JavaScript embedded inside it?

The user should be able to collect a print out the next day or open the report (saved as HTML or PDF)

The PDF approach is interesting as the user cannot of course see the report in the web browser at 2am - nor is there anyone around to close it. Javascript can automatically close the window after it has been printed (Intranet-Default printer).

The on-demand approach (user selects the date range and desired report in xMII) I have completed. Here one can of course use a servlet to build the report via QueryTemplate/DisplayTemplate.

I hope this is a little clearer now - please don't give up on me

[Former Member]

Thank you for the replies.

I am a little closer the result I need - however I think I need to explain what I had and what I need a little better.

Before BLS

• 1 xMII report page (.irpt extension) with two iCalendar applets (start/end date) and a set of buttons (1 for each report)

• Upon clicking on a button the two dates are passed into an .irpt file, and via a servlet an SQL QueryTemplate and a XSL DisplayTemplate are used to build the report.

• The HTML is embedded in the XSL file - thereby generating the report direct in the web browser.

- This all works fine

With BLS

• A transaction which uses a modified SQL QueryTemplate (no date parameters) passing the results to an XML file. This works.

• Now when I click on the button in my xMII screen the .irpt file is called with no Date parameters and the xAcute QueryTemplate called with the XSL DisplayTemplate. The irpt file has <html> and <body> tags with XSL file in the Servlet call providing the tables and data extraction from the XML.

• I have no additional HTML file so I placed the iframe tag inside the XSL file - but it refers to the .irpt file - this doesn't sound right!

I do get a little output in the web browser but it still omits all XSLT code.

One more point - The Transaction can be scheduled and run as required - but I need the entire report to created and stored for viewing at a later date. Will a servlet tag running inside an .irpt file achieve this?

Sorry for the chaotic writing here - but I must leave the office.

Regards

Robert Sales

[Former Member]

Hi Robert,

Sorry for calling you "rsales" in my post - for some reason I didn't see your name

I can't quite grasp what you are trying to do - I have run some tests and I can return html code and also an iframe with a .irpt file as the source from within the stylesheet. Are you calling the same irpt page from the stylesheet (creating a kind of endless loop)?

- Diana

Edited by: Diana Hoppe on May 14, 2009 10:05 AM

[Former Member]

Robert,

Pls correct me if my understanding is wrong.

I understand that ,

You have Date Selection screen which has StartDate and EndDate and Submit, After Selection you need to generate report on click of submit button right? That you are getting now.

Now you need to save this Report data for later view in som folder correct?

If this is the requirement, then in the Transaction itself map the document output to PDF document and save as PDF doc the report in some folder using Image saver.

Thus it gives result to grid and also save the Report in PDF in some folder.

Regards,

Padma

[Former Member]

Hello Diana,

Now I've found the Reply link! Very wide page......

No probs with rsales - I didn't realise it couldn't be changed! Otherwise it would show my full name...

I had in effect set up an endless loop which isn't good of course - I preferred to automate the entire process in BLS - therefore I pursued my original plan of retrieving the SQL, then applying XSLT to the XML with embedded HTML so that the report could be built entirely in BLS.

I think I had something wrong with the Ingoing/Outgoing sequence.

Many thanks for all the help

Regards

Robert Sales

[Former Member]

Rao,

The submit button solution is only for on-demand reports - what I need in BLS is the process to be run automatically.

The PDF solution could be useful for me - but the specification is so vague at the moment I don't know how it will be delivered. I'll satisfy myself with HTML and PDF for now!

Many thanks.

Regards

Robert Sales

[jcgood25]

Have you tried calling the IRPT page with the HTMLLoader action block? If the SERVLET section that you have for running the query with xslt for text output includes the date tokens like:

<PARAM NAME="StartDate" VALUE="">

<PARAM NAME="EndDate" VALUE="">

Then you should be able to do the date determination you need within your BLS TRX and formulate the URL passed to the HTMLLoader as such: ....Page.irpt?SD=05/15/2009 08:00:00&ED=05/15/2009 09:00:00

Use the Tracer to see the text output received by the loader action, and you should also be able to use the HTML or Text saver to publish this 'finished' report to your project web folder.

[Former Member]

Jeremy,

In my "non-BLS" reports, I run two servlets in an .irpt file.

Servlet 1 uses two date parameters derived from my main xMII form where the dates are selected (iCalendar). These are then passed to an SQL stored procedure to determine if the default dates have been overriden (other dates selected in iCalendar). These results are then added to the .xsl DisplayTemplate which builds the report header in HTML/XSLT.

Servlet 2 includes the main SQL Query and .xsl DisplayTemplate, again the SQL query makes use of the date range selected (either default or those selected by the user in the iCalendar applets).

My solution which I have used in the previous post above is running entirely in BLS because I do not need date parameters there. The reports will run at a certain time or signal change - and therefore no user interaction will occur - therefore the SQL query will hard code the required date range (e.g. last shift or last 30 days etc...) according to the customer's requirement - which is as of yet unspecified!

This has not been an easy scenario to convey - my apologies if I've been leading you and others up the garden path!

Best Regards

Robert Sales

[Former Member]

Hi Robert,

You could create transaction which gives your report without XSL transform.And create Xacute query for this transaction.

Then create an xsl file for the report using XSLT.

Now you can create irpt page which will have Servlet tag with your xacute query and xsl page.

<servlet name="Illuminator"> 
<param name="QueryTemplate" value="XacuteQry" >
<param name="StyleSheet" value="web://Folder/.xsl" >
<param name="Param.1" value="{}" >
<param name="Content-Type" value="text/html" >
</servlet>

Thus in you main page use iframe.

<iframe name="report" src="Report.irpt" marginwidth="1" marginheight="1" scrolling="no" border="0" frameborder="0" width="950" height="220"></iframe>

Try this.

Regards,

Padma

[Former Member]

I should mention that I am testing in 11.5 but that the final production will probably be in 12.0.

[Former Member]

Hi rsales,

Have you looked into using the xMII Report Servlet which builds the dynamic content is server-side and returned to the page, using special servlet tags? You would need to change your page extension from .html to .irpt to use this server. Please refer to the xMII 11.5 Help Documentation on "Report Generation". I encourage you to read up on all the features of using the .irpt extension. You should be able to migrate this version 12.0 without difficulty.

Kind Regards,

Diana Hoppe