Security vulnerabilities in Crystal Reports web apps

Saturday 18 February 2017 @ 6:27 pm

A Crystal Reports web development team has posted in the SAP forums about some security weaknesses in the .NET web deployment model. They are trying to get the attention of SAP, without success so far.

It appears that this team deployed a .NET web application using the Crytal Reports runtime engine. During a security audit they uncovered some serious vulnerabilities. It appears that they weren’t able to get the SAP support team to look at the problem because they do not have a support subscription, so they posted it to the forum as three question. From what I have read they are trying to walk the line between highlighting the seriousness of specific vulnerabilities, while not making these same vulnerabilities easier to exploit.

Those of you who do .NET web development might want to check out the following links.

Link 1 (131436)
Link 2 (130250)
Link 3 (133449)

And thanks to Ido Millet of Millet Software for pointing these posts out to me.

A simpler way to maintain formatting conditions

Wednesday 15 February 2017 @ 12:32 am

I have written before about the advantage of using CurrentFieldValue when applying a formatting condition. This is especially true when applying a similar format to multiple fields, because it allows you to ‘paint’ the format properties from one field to another using the format painter.  Related to this method is a way to make it easy to update all of these formulas at once, without having to change them individually.

For instance, say you have 12 columns and the font color formula for all of them is:

if CurrentFieldValue > 90
then CrGreen
else CrYellow

When someone wants to change the value from 90 to 95 or tweak one or both of the colors it would require updating one and repainting all the others. Instead you could create some feeder formulas for the different literal values. I would create three formula fields:

{@target} which contains the number 90.
{@LowColor} which contains the function CRYellow
{@HighColor} which contains the function CRGreen

If you have those three formulas, your formatting formula would be:

If current field value > {@target}
then {@HighColor}
else {@LowColor}

You can apply this formula to all 12 fields.  Then when someone needs to change yellow to orange you just change the {@LowColor} formula from CrYellow to Color(255 , 165 , 0). This way the change affects all the formatting formulas in one stroke.

Visual Cut adds RPT management capabilities

Tuesday 7 February 2017 @ 11:35 pm

Visual Cut has always been a solid desktop scheduler. I have used it for years to send out my invoices.

But Ido Millet of Millet software has recently added some new features that allow you to scan multiple reports. The scan reads in the database fields, text objects, SQL expressions and all formulas (including selection formulas and property expressions). You can group, sort, search, and export the results. You can also find & replace text and expressions and save the updated versions of these reports. Ido has posted a video demo of this.

Another video demo shows how you can deploy an entire series of new formulas in Excel and import these new formulas into multiple reports. With these new features, Visual Cut will now be included in my annual comparison of RPT management utilities.

Web based deployment options compared (2017)

Friday 27 January 2017 @ 1:00 am

There are many ways to deploy Crystal Reports to users. I normally lean toward the simpler and less expensive options, like locally installed viewers, or scheduled delivery of PDF output. But there are environments where a web based option is necessary. The “official” options from SAP are Crystal (Reports) Server and BO Enterprise. But there are other, less expensive products out there that also web delivery of Crystal Reports. These third party products allow your users to run and view reports from a browser. You can also centrally manage your report deployment from a browser.

I have created a page on my blog that lists and compares these products, and I update it every January. This year the list features 10 products, three of which are being listed for the first time:

Crystal Reports Server – a traditional Web portal
Report Runner Web Portal – a traditional Web portal
IntelliFront BI – a traditional Web portal
Ripplestone – a traditional Web portal
RVweb – a traditional Web portal
rePORTAL CR – a traditional Web portal
ReCrystallize Pro – a launch page generator for the web
ReCrystallize Server – a server-based web viewer
Report Launch – a bridge between BO server products and server based applications
RapidStack – Web Portal service built around Business Objects Enterprise

The blog page mentioned above contains a brief rundown on what each product does and provides links to all of the product web sites. I have also posted a feature matrix (PDF) that shows some of the specifics for comparison, including prices. This year there are several new lines in the matrix. They show which tools provide workflow/BPA support, Web APIs, password management and menu localization. If you have any feedback to share on these tools I would be happy to hear from you.

Renaming image objects

Sunday 22 January 2017 @ 12:50 pm

This was the first time I found a practical use for renaming a report object, a feature that has been available since 2002.

Every object placed on a report is given a unique “object name”. These names appear in the Report Explorer (View > Report Explorer) which lists all of the objects and where they are placed. The default object name for fields is the name of the field with a number after it. The number makes each object name unique, since the same field can be placed on the report multiple times. Graphic objects are also given object names. For instance, a series of lines would have the default names of Line1, Line2, etc.

In a report I did last week I needed to show the status of each transaction by displaying a small image that represented each status. This required stacking several images in the same place and suppressing most of them so that only one was visible at a time. The challenge was that once the images were stacked it was very difficult to determine if the correct one was selected. All the object names were Picture1, Picture2, etc.

Fortunately, you are allowed to modify the object name, as long as it is unique. To rename an object you right-click on the object, select “Format Object” and go to the common tab. The object name is at the top.  I renamed each image with the status it represented. Now when I select an object in the Report Explorer I know which one it is and can apply the correct suppress condition.

1) Lines and boxes do not have a common tab, so there is no way to change the object names.
2) The Report Explorer was introduced with version 9 and that is the first version that allowed us to view and edit object names. If you open a report from CRv8 or older the object names will be more generic (Field1, Field2, etc). However if you copy and paste one of these objects while in a later version the newly created object will be named like current objects.

Group-specific subreport that only hits the database once

Thursday 12 January 2017 @ 10:44 pm

Group-specific subreports are always a last resort for me, because they hit the database multiple times and usually that slows things down. Sometimes, though, they are necessary.

I recently had to create a report with about 100 groups, and each group needed a subreport that ran a separate query returning a very large dataset. I was concerned about the time it would take to hit the database 100 extra times. I was trying to find a way to read the data just once for all the groups and yet still have each subreport provide group-specific data.

I knew that when you move an un-linked subreport from the report header to the group header it only refreshes in the first group and is simply repeated for every other group. If I could fool Crystal into thinking that my subreport was the same query for all groups, then it should only run once. So I changed the SQL in the subreport to return the data for all groups, and then added a parameter to select one group value at a time. I found that when I placed the parameter in the Record Selection Formula Crystal would refresh the subreport with every group. But if I put the same parameter in the “Saved Data” selection formula, which forces it to be evaluated locally, the subreport would not refresh for each group. I still get group-specific data for each group, but only one hit to the database.

Note – the “Saved-Data” record selection formula was introduced in Crystal Reports 2008 (v12).  If you are using Crystal Reports XI (v11) or an older version you will have to use an alternative method that is a bit more complicated.  For instance you could pass the group parameter to a conditional formula and use that to ensure that your totals are group specific.  Then use group selection to eliminate all the zero groups.  Not nearly as elegant but workable.  Call to schedule a session if you would like more details on this option.

Reduced prices for the “Expert” series

Sunday 8 January 2017 @ 11:29 pm

Starting this month, the prices for the “Expert” series of educational PDFs have all been cut in half. So if you were hesitant, maybe the new prices will make it easier to try them out.

Check out the full list of titles here along with the new prices.

Putting a watermark under lines or boxes

Wednesday 28 December 2016 @ 12:04 am

Crystal will allow you to layer or superimpose images and text. Sometimes this is done intentionally using a faint image which creates a watermark effect behind the text. But if you are using lines or boxes with your text, the watermark won’t behave the same way. For some reason, text objects can be set to appear “in front of” an image or can be “moved to the back”. But Crystal lines and boxes will always appear “behind” an image. So if you put a watermark image behind a section that has both text and lines, the text can be moved to the front and will appear but the lines will always be hidden behind the image. Here are the 4 workarounds:

1) Use an empty text object with a border instead of a line or a box.
An empty text object can have a border on one side to make a line or on all four sides to make a box. And because this is not a true line or box object it will still give you the option to keep it in front of an image. The downside is that borders only come in one thickness which is thicker than the hairline people often want for their lines and boxes.

2) Put the lines and boxes in a subreport.
For some reason, if the image is in the main report and the lines/boxes are in a subreport, the lines/boxes can be kept in front of the image. Some users put both the text and the lines/boxes in the subreport. Of course subreports add another layer of complexity. And if the subreport requires a repeating query it can be a real performance killer.

3) Use an OLE object for the lines or boxes
You can add an OLE object like an Excel spreadsheet or a Word Doc, and draw your lines and boxes in the OLE document. The OLE object can be moved to be in front of the watermark image and then it will be visible.

4) Modify the image itself to include the lines and boxes.
OK, this is punting, but it works in cases where the lines and boxes are always in the same place relative to the image.

Adding comments to formulas

Friday 23 December 2016 @ 12:58 am

Most programming languages allow you to add comments in your code. This is useful to explain a calculation or to document changes. Creating these comments usually involves some special punctuation at the beginning and/or end of the comment which tells the program to skip over those lines.  There are several different syntax patterns that indicate comments and these vary by language.

In Crystal Reports formula syntax you insert a comment by adding two forward slashes [//] at the beginning of the comment. Crystal will ignore those slashes and also ignore anything to the right of the slashes for the rest of that line. This means you can add a comment in the middle of a line like this:

The {table.code} = 'abc' //code 'abc' is for special cases
then 'special'
else  ' '

Or you can start a line with two slashes and Crystal will ignore the entire line. In either case the commented text will turn green to show that Crystal recognizes the comment.

But one warning, I don’t recommend that you put any comments in the selection formulas (record or group). The reason is that the selection formulas can be rewritten by the select expert. Any time you use the select expert to change the criteria, Crystal will regenerate the selection formula from scratch and all the comments will disappear. So if you need to add a comment to the report criteria you can write that criteria in a separate formula field, and include the comments in that formula. Then you can use the formula field as part of the selection criteria.

New web portal from ChristianSteven Software

Thursday 15 December 2016 @ 10:48 pm

ChristianSteven Software has released a new web portal product in 2016 called IntelliFront BI. It can run Crystal Reports, SSRS Reports and MS Power BI dashboards.  It also allows you to create and run proprietary KPI reports and scorecards.

As a web portal, it allows users to run reports on demand.  But it can also run them automatically, either on a fixed schedule or based on data driven events. This allows you to automate business processes and workflows.

It even includes a web service API for integration with other tools and systems. The license is $50,000 which includes unlimited users, unlimited servers and unlimited cores.  They offer a free live demo for those who are interested.

«« Previous Posts
Versa Reports

remiCrystal reporting solution