I have a high level question around Tabular Report developme...

[Kristi Carter]

I have a high level question around Tabular Report development for Versions 8207 and lower.

We sometimes struggle when developing a simple tabular reports using App Studio or Info Assist. There seems to be quite a bit of code and/or knowledge needed to make a report based on our companies standards.

In one of my past employers we actually used SSRS (Microsoft Reporting Services) for development of tabular reports and then used WebFOCUS solely for visuals and dashboards.

Does anyone else do this type of thing where you are using more than one reporting tool to provide what is needed to the business

[Debra Waybright]

Not usually. When you say Tabular Report do you mean a spreadsheet or a PDF report that looks like this:

col1 col2 col3

123 ab xyz

234 cd pdq

We use InfoAssist and AppStudio to do both charts and tabular reports. We are running 8.2.03.

And our data isnt exactly in the dimensions/measures layout that would be best. Im wondering what it is about your companys standards that makes it difficult for you.

Deb

[Dawn Thurber]

Good morning Kristi! We havent had a need to use other reporting tools to date. Im a power user in InfoAssist and solely use InfoAssist for visual and data reporting also in the insurance industry. Im intrigued with your struggle and question but Im not sure how to provide anything helpful to you beyond that answer without more details of what you are looking for.

[Kristi Carter]

Thanks for your responses.

To clarify, when I am talking tabular reports, it is in the spreadsheet format type. Columns and rows.

Few things that I have not been able to find that the tool easily provides:

 

 

Getting a list of valid parameter choices in a drop down listing and being able to sort that listing in descending order. Most of my struggle has been with dates as our data for dates comes in an Number format.

 

 

Adding images on an AHTML version report. Currently in order to get this available, I have to create a Compound Document to have the image included when the user runs he report.

 

 

Dynamic column headers based on a Variable selected.

 

 

When exporting from an AHTML format, it does not export the formatting (like colors, fonts, totals, etc.), nor does it open in the newer version of Excel.

 

 

These are just a few things I have come across on a recent report that I have developed.

Now, I have been able to figure out how to accomplish what is needed mostly by using App Studio, however it took some review, Researching the IBI Knowledge Base/Forum and entering IBI tickets to accommodate.

Thanks,

Kristi

[Stephen Blais]

For #1, you could convert the numerical dates to a date field before you display them.

For #4, if you set WEBVIEWER to ON, CACHELINES to ON and AREXPEXCEL to XLSX, you can get the export from AHTML to XLSX format. For example:

TABLE FILE CAR

PRINT *

ON TABLE SET WEBVIEWER ON

ON TABLE SET CACHELINES ON

ON TABLE SET AREXPEXCEL XLSX

ON TABLE PCHOLD FORMAT AHTML

END

Im not sure youll be able to export formatting, though.

[Debra Waybright]

For item#2 - If you are using AppStudio to create your AHTML report, it doesnt have to be a compound document, you can use HTML code to put the image in:

-HTMLFORM BEGIN NOEVAL

<style>

body {

background-image: url('/approot/enterprise_analytics/image.png');

background-repeat: no-repeat;

background-attachment: fixed;

background-position: left top;

background-size: 500px 250px;

}

</style>

-HTMLFORM END

 

As for #1 - parameters are easy to add via InfoAssist. Drag the field to the filter box and where it wants a value, choose Parameter for the type. If you choose Dynamic, you can then choose the field that has the values the user should choose from. There is a radio button to sort the values Ascending or Descending. If you choose Static, you enter the values that will be in the drop down to choose from. There is a tick box if you want the users to be able to choose multiple values from the list or not.

Hope that helps!

Deb

[Toby Mills]

Hi Kristi

I dont know how much authority youve got there at Farmers, but it sounds like youd be greatly helped out by asking your Database guys to make you some Views to read with dates you can use right out of the box. You might also might consider building your Masters with DEFINEs in them to create the Dates you need rather than having to do DEFINEs inside every report.

Dynamic columns probably will take you down the trail of Dialog Manager. I dont think SSRS would handle that one very well either.

We have both WebFOCUS and Tableau at my current location. Ive converted a couple of clients from Business Objects to WebFOCUS in the past. I think you guys used BO in the past right Maybe try that to see how that goes

Whichever tool youre going to use for your BI reports, having your data lined up nicely in the first place really helps.

Maybe write up something for your DBA types to show what would really help your application work go more smoothly

Keep the questions coming - we like questions:)

Toby Mills, CISSP

[Kristi Carter]

Debra thanks for your response.

Item #2. I am able to actually get an image to show in the header area for an AHTML format report when it is done in legacy design, however now that we have the designer version (SET ARVERSION=2) the same option does not work. I currently have a ticket in with this and the response was that they were able to replicate the issue and are reporting it to the Product Management team as a potential bug.

I have tried your logic to add HTML, however I am unsuccessful as well. I created a fex file using WFRetail. Below is the code I have. Can you review and confirm that I have this noted corrected

I have taken the image an moved to the EDASERVE in the location it notes below.

I have tried adding the HTML logic before and after the END of the ENDSTYLE.

Note: we are currently on Version 8206.

TABLE FILE WF_RETAIL

SUM

WF_RETAIL.WF_RETAIL_SALES.COGS_US

BY WF_RETAIL.WF_RETAIL_PRODUCT.PRODUCT_CATEGORY

ON TABLE SET PAGE-NUM NOLEAD

ON TABLE SET ASNAMES ON

ON TABLE NOTOTAL

ON TABLE PCHOLD FORMAT AHTML

ON TABLE SET HTMLCSS ON

ON TABLE SET STYLE *

INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,

$

ENDSTYLE

END

-HTMLFORM BEGIN NOEVAL

 

body {

background-image: url('/approot/test_myibi/images/logohorizontal_transparent_xsmall.png');

background-repeat: no-repeat;

background-attachment: fixed;

background-position: left top;

background-size: 500px 250px;

}

 

-HTMLFORM END

Item #1. I agree with what you are suggesting will work, however I have come across scenarios where the values in the listing are not working as expected. I have another ticket in on this and they are currently reviewing.

My issue is this: We are using a master file for Dates. We link to the Date Dimension table to then use the formatted date in the filter listing. What is happening is the listing will then show all the dates within the Date Dimension table and not just any valid dates that are truly in the main fact table.

I have currently done a workaround on this that does seem to be working, however this workaround would then not be able to be accomplished easily by dragging and dropping filters to easily use.

Hopefully that all makes sense.

Thanks,

Kristi Carter

[Kristi Carter]

Thanks for your response Stephen. I was excited to see that we could export to XLSX by adding some additional logic to the fex file, so I tried your sample and it worked just fine, however when I tried to incorporate it into anything else it did not seem to work.

Below is a sample I tried next using WF_RETAIL and adding SUM and BY fields. Below is an error I get. Any thoughts on what I could be doing wrong

TABLE FILE WF_RETAIL

SUM

WF_RETAIL.WF_RETAIL_SHIPMENTS.SHIPMENT_UNITY

WF_RETAIL.WF_RETAIL_SALES.COGS_US

BY WF_RETAIL.WF_RETAIL_PRODUCT.PRODUCT_CATEGORY

BY WF_RETAIL.WF_RETAIL_GEOGRAPHY_VENDOR.BUSINESS_REGION

ON TABLE SET WEBVIEWER ON

ON TABLE SET CACHELINES ON

ON TABLE SET AREXPEXCEL XLSX

ON TABLE PCHOLD FORMAT AHTML

END

 

Thanks,

Kristi

[Debra Waybright]

Kristi,

you are missing the style tags in your HTML, I think that is why it isnt working for you. Add before body and after the close bracket (}) before the -HTMLFORM END in your HTML code and see if that works for you.

As for your parameters, you are joining your main table to a dates table and you put the dates table field in the list box, not a field from the main table. I think that is why you are getting all the dates table values and not just the ones in the main table. Have you verified that your join is INNER It may be that you need to put a define field on the main table to format the date and use that in your parameter list.

Deb

[Kristi Carter]

Thanks for your response Debra. Below is a print screen of what the code looks like. I do have the style tags in there, they just disappeared in the notes for some reason when I pasted the logic.

For the dates, you are correct on all of it. That is the reason why all the dates show even when it has an INNER join noted. To get around this I had actually did the date formatting directly within the main table in stead of using our Date Dimension table. Which is too bad, because that defeats the purpose of having the Date Dimension table.

 

image.png759197 4.8 KB

 

Thanks again for the insights.

Kristi

[Debra Waybright]

When you post code, first put three backticks (```) on a line then paste the code then on the next line put another three backticks and it should show all the code just like your screen shot. This is new for myibi (compared to focal point) and is pretty slick. But if you dont know about it

 

That HTML code will put an image in the background, so if you have something on top of it, that could be why it isnt showing.

You could also try this in the code in your WebFOCUS style section add:

TYPE=REPORT, ALPHA=50, $

TYPE=REPORT, OBJECT=IMAGE, IMAGE=filename.png,POSITION=(0 0), DIMENSION=(6.5 2.5), $

My notes say this will work for AHTML output but it has to be a png image. It still puts the image behind the report and the ALPHA has to do with the opacity of the report, so if your image isnt behind the report (I used this to put a watermark on a report) you could omit that or put 100 instead of 50.

WebFocus joins can be a bit of a pain.

Good luck!

Deb

[Kristi Carter]

Thanks for the information on the backticks. I will try to remember that for next time.

I tried what you suggested on the image portion, and it works ok with the legacy design of AHTML, however when forcing it to be the new Designer AHTML the image still does not show.

I force this to be the Designer version by adding: SET ARVERSION=2

Any other ideas that may work

Thanks,

Kristi

[Debra Waybright]

Kristi,

Im afraid I cant help you with that. We are still running 8.2.03. If you havent opened a case with IBs tech support, you may want to do that.

Good luck!

Deb

[Kristi Carter]

Thanks for all of your insight. I have already opened a case with IBI and they say it can be done, but I was hoping maybe you knew something else.

Thanks again for the help,

Kristi