Can a blob field be included in a cross-tab in CR for Visual Studio

[Former Member]

We are heavily using Cross-Tabs in the v13 SP17 CR Designer under Visual Studio.  We have a blob field containing an image that we can easily place in the report, but appears to not be available in the field selector within the Cross-Tab Expert.

Is there a way to include a blob image field in a Cross-Tab?

I tried making the blob available through a formula, hoping that I could pick the formula within the "Cross-Tab Expert > Group Options... > Options > Customize Group Name > Use a Formula as Group Name", but when I try to create a formula with the blob field, it presents an error saying "A blob field cannot be used in a formula."

Is this just a limitation of CR Designer under VS.NET and maybe it's supported by the full CR Designer?

To provide some more context, here is a screen capture showing the blob field used in the report outside of the cross-tab:

And then a screen capture in the Cross-Tab expert showing the lack of the "image" field in the field explorer:

Thanks,

Pete

[ido_millet · Accepted solution]

CrossTabs are designed to aggregate (Sum, Max, Min, Avg, ...) so they can't use blobs as input.

[Former Member · Accepted solution]

Thanks for the quick reply Ido.

I absolutely understand that Cross-Tabs aggregate, and that it doesn't make sense to aggregate a blob.  But there could easily be a unique blob per row in the cross-tab, with the desire to render it.

For example, it would be really useful for us to be able to aggregate on the name of the image file, and then display it as the image itself (maybe via the "Use a Formula as Group Name" feature).

Our specific use case is a report of products, such as shirts that have multiple sizes.  A cross-tab presents the product with each row being a style of shirt and the columns as the sizes (there are more aggregations as well, such as the quantity ordered, extended price, etc).  Each product style (row) has an associated product image that we really would like to include in that cross-tab.  Currently we are having to display a distinct cross-tab for each product style, since we have to put the image outside the cross-tab.  This results in a less-than-ideal report layout.

We were able to quickly proof of concept including an image in a SQL Server Reporting Services (SSRS) "Matrix" widget (conceptually identical to a CR Cross-Tab).  The lack of this ability in CR Cross-Tabs is pushing us close to the point of switching our reports over to SSRS.  So, I wanted to double check to make sure there wasn't some obscure workaround for accomplishing this in CR that I just hadn't found yet.

[ido_millet · Accepted solution]

You can use a manual CrossTab approach to achieve the same thing.

Group the report, place the image in the group section, use boxes and lines...

Not as clean, but very doable.

[Former Member · Accepted solution]

Any chance you have a sample report for doing a manual cross tab with multiple row and multiple column groupings, and multiple summarizations?

[former_member11696 · Accepted solution]

Funny you asked.... Just posted examples in this thread:

Don

[Former Member · Accepted solution]

Don, our situation is exactly like Minerva Vazquez Bernal's final reply in the thread where she said

"my pivoted columns have to be dynamics. With a manual crosstab I can't achieve that, right?"

And that assumption seemed to be confirmed by the last line of your (Don's) tutorial that says:

"In order to develop a cross-tab like report using ‘Manual crosstab or Mock crosstab’, we need to manually add the required number of columns (that holds key figures) beside the main column (that holds the characteristic)."

So, could you (Don) put in a feature request for including an image/blob in a cross-tab as a Rows or Columns field?  I completely understand that it makes sense to prevent it from being added as a "Summarized field."  I also understand that it may be necessary to only allow it via the "Group Options > Customize Group Name", since I could imagine grouping on the blob data itself may be prohibitively slow, and hence it might make sense to group on a proxy field for the blob, and then just identify the blob field for display only.

Hopefully the business case I described here was sufficient:

Pete

[ido_millet · Accepted solution]

Pete, if your report is grouped like the CrossTab, you could place the blob field in a group section, and underlay the Crosstab to create the desired effect.

The key is to use the section property of 'Underlay Following Sections'.
Haven't tried it, but I think it should work 

[Former Member · Accepted solution]

Thanks for the additional thought Ido.

That was one of the first workarounds we considered after noticing that the blob field just wasn't present in the cross-tab expert.  And we heavily use "Underlay Following Sections" for other purposes in our reports.

However, we have variable heights within our cross-tabs based on 4 levels of Row grouping. We would want the image to be associated with the first level of Row grouping, but each first level could have 1 or more elements in the next level grouping, and the same holds for at least another level of Row grouping after that.  Even if there was some way to dynamically position each image based on calculated coordinates, I don't know how we could calculate the correct position from the data being fed into the cross-tab.

I believe I've seen some forum posts about people hooking custom .NET code into events raised by either the report instantiation and/or the report generation, and then programmatically interacting with the report.  So, maybe there is some path down that route, but I haven't been able to locate relevant documentation.  And, even if I could find that documentation, I expect it would be a complex task with our time potentially better spent on the effort to reimplement the reports in SQL Server Reporting Services that supports images in cross-tabs.

If I hear there is a feature request in the pipeline to include images in the Row (and Column) groupings of Cross-Tabs, waiting some number of months for an update with that feature is also a viable option.  So, , can you submit a feature request or let me know a channel for doing so?

Pete

[ido_millet · Accepted solution]

Whatever CrossTab levels you have, you can mirror those using grouping levels in the report (and assign section heights to mirror the row heights in the CrossTab.

This, of course, would solve only row-wise blobs.

[Former Member · Accepted solution]

... I figured I might as well make this even more concrete with an excerpt from our current non-ideal implementation where we present a distinct cross-tab per image, with the image being included at a report level grouping, and with the cross-tab at that same level of report grouping.  This is one of our more simple examples, but you can likely see how we don't have even vertical spacing between the "styles" (the 6 digit numbers that are in the first column of the cross-tabs).

We would really like to be able to include the image in the cross-tab, and collapse this back to a single cross-tab that includes multiple styles/products. Hopefully this is good fodder to add to a feature request :

[Former Member · Accepted solution]

My reply above crossed paths in the ether with your additional thought about putting in additional report groupings to mirror the cross-tab, setting blank section heights for those groupings to match the cross-tab row heights, and then placing the image at the top level of that grouping...

I'll give that a little more thought and see if I can make it work.

Pete

[Former Member · Accepted solution]

So close, but yet so far...

I cringed while I did it, but I managed to prototype the approach you suggested, and I think we could get it working, albeit with a report design that is likely more fragile and likely not intuitive to someone looking at it months later (particularly since I haven't found any way to embed documentation in a report other than buried in comments in formulas, or as suppressed Text Objects scattered through the report design surface).

However, I think the show stopper is that this workaround appears to breakdown as soon as the cross-tab spans multiple pages.  It appears that the section containing the cross-tab that is flagged as "Underlay Following Sections" ends up starting its underlay only on the last of the pages that the cross-tab spans.

I gave a little thought to whether I could work around this by moving which piece is in the group header versus footer, but I don't think there is a solution there, because the cross-tab has to sit at a grouping level above the repeated image, so it has to be in it's group header, and then whether the repeated image and spacer are in their respective header or footer doesn't matter.

Here are some screen shots to make it concrete, along with some commentary...

Here is a high level view at the relevant part of the proof of concept report. The cross-tab's section is shown to "Underlay...", and then I have a spacer section with the yellow background to account for the cross-tab's header.  Then the image to be repeated, and then a single non-suppressed section for the level of grouping that represents the subrows (shoe widths) in the POC example.

When the cross-tab fits on a single page, it seems I could get something to work as shown below.  I would need to tweak spacing slightly, and then deal with cosmetics.

However, as soon as the cross-tab spills over to another page, the images don't start appearing until the last page that the cross-tab spans.  This screen capture shows enough content that the cross-tab covers 2 pages, and you can see the images only starting to appear on the second page.  If I put in enough content for the cross-tab to span 3 pages, the images start appearing on the 3rd page, etc...

I haven't seen a reply from you regarding what process to follow for making the inclusion of images in a Cross-Tab rows (and columns) an official feature request.  Can you provide any guidance on that?

I really appreciate both of your help, but I have to repeat that this is a very frustrating limitation.

Pete

[ido_millet · Accepted solution]

Pete, would delivering the report as PDF export work for you? If so, there's a 3rd-party tool that can cleanly solve your use case.

To submit feature requests, see:

SAP Crystal Reports: Home

[Former Member · Accepted solution]

, going straight to a PDF export is definitely a viable option.  Could you provide a pointer to the 3rd-party tool you are referencing?

Also, thanks for the pointer to the official "Idea Submission" channel.  It's a little discouraging that the page claims the next "Idea Review" is March 31st, 2016 (about 3 months ago), but at least that isn't years ago

Once I post the idea there, I'll cross-reference it here so hopefully anyone following/seeing this thread can upvote it so it gets to the apparent minimum of 20 votes to be evaluated.

Pete

[ido_millet · Accepted solution]

Pete, it's one of the Crystal Reports Desktop Scheduler listed at Crystal Reports Viewers, Schedulers and Related Products

[Former Member · Accepted solution]

, could you clarify your recommendation?

I do need the reports to be run on-demand by a user running an occassionally connected desktop .NET application, and on specific datasets that the user chooses at the time the run the report (e.g. a specific Order).  A "Desktop Scheduler" in general doesn't seem to align well with that usage model, although I could imagine creating a "single run, and right now" scheduled job on the fly.

But more importantly, I don't understand how a Desktop Scheduler would have anything to do with attempts to align images with the cross-tab rows, to workaround the lack of the ability to embed them in the cross-tab itself.

Are you suggesting that I generate 2 separate reports, one with the cross-tab and one with the images, and then overlay/merge the PDFs somehow, since I see things like "Visual Cut" under the "Crystal Reports schedulers/Email (desktop)" you deep linked me to.  But that really seems like a stretch, because, for one, we have an arbitrary number of these cross-tabs sprinkled throughout a 20 page report, interspersed with an arbitrary amount of additional content.

Could you clarify or point to the specific item you are talking about?

Thanks,

Pete

[ido_millet · Accepted solution]

You can call the process on a ad-hoc basis via a command line API. This includes the ability to specify parameter values or prompt the user for some or all of the parameter values.

The images would be added to the PDF based on Display String property of any desired CrossTab element (including the ability to resize and offset the image relative to the CrossTab element location. Since the location of the CrossTab element is an organic part of the CrossTab layout, the location of the inserted images would be precise.

[Former Member · Accepted solution]

Thanks for the additional clarification Ido. 

For anyone following this thread, I submitted a feature request here.  Please vote it up so it might get considered - Support inclusion of a blob/image field in the Rows or Columns of a cross-tab : View Idea

Ido, even though I'm still not quite understanding the actual approach you are describing, I'm pretty sure the effort involved (assuming it actually is possible) far exceeds the budget my client would invest in working around the lack of this feature in a CR cross-tab.

At this point, I'll include my further questions to you below simply to appease my curiosity of trying to wrap my head around the workaround approach you are suggesting. At the same time, I realize you are a good samaritan helping out people in this community, and given that I am giving up on any further attempt at a workaround to get images embedded/aligned with individual rows in a cross-tab, I completely understand if we just let this thread die at this point.  But here it goes...

We are already instantiating the report object directly in .NET code, and programmatically setting its data sources and values for multiple parameters.  I can hook into the three events the ReportDocument class raises: InitReport, RefreshReport, and Disposed.  I understand that a .NET object model is exposed for the report instance, with it's highest level structure exposed through auto-generated .NET code that provides handles on the various report sections and parameters.  Below is a screen capture showing that I can get a handle on the instance of the specific Cross-Tab in question, and can imagine programmatically injecting images as report objects into the report object hierarchy.  However, my understanding is that this represents the report definition, and not the specific manifestation of the cross-tab for a particular run of the report for a particular data set.

Specifically, I assume I can't get a handle on each row that is manifested in the cross-tab and extract its position information.  If I could do that, I can almost imagine being able to programmatically insert picture objects for the product images, and figuring out how to set absolute positions for them.  But my mental model of all this is still fragmented, and I'm not seeing the big picture of what features within CR that enable this control flow, and at what key points/events in its runtime lifecycle.

Anyway, if you choose not to answer, thanks for the time you already spent on this thread.  Here is the screen capture I mentioned above.  The key elements are highlighted in yellow:

[ido_millet · Accepted solution]

Pete, the direction you are considering (trying to do this via code and the Crystal runtime) is a dead-end.

[Former Member · Accepted solution]

Thanks for the affirmation .  That was my feeling all along, but it had seemed like the direction you were indicating as the only possible workaround.  Do you still see an avenue that I haven't explored that might have a fairly straightforward solution once I understand the path.  Every comment you made earlier in this thread made sense to me, but I still can't make heads or tails of your two most recent suggestions:

a 3rd-party tool that can cleanly solve your use case... one of the Crystal Reports Desktop Scheduler listed at Crystal Reports Viewers, Schedulers and Related Products

and

images would be added to the PDF based on Display String property of any desired CrossTab element (including the ability to resize and offset the image relative to the CrossTab element location. Since the location of the CrossTab element is an organic part of the CrossTab layout, the location of the inserted images would be precise.

[ido_millet · Accepted solution]

Pete, sorry but further discussion of the 3rd-party tool solution may violate forum etiquette. 

If you are interested, please contact the developer of that tool.