Skip to Content

Is possibly for groups to stack horizontally instead of vertically?

I'm putting together a report based on a multiple choice field of 20 possible entries. I'm trying to show numbers across months. So I have a formula to parse each individual entry from responses and created 20 of them for each possible answer.

Here's an example:

if isnull({return:reason}) then 0
else if instr({return:reason}, "Paint Chipping")>0 then 1 
else 0

Each incident is recorded with the date it was reported. I made the date a group and had the group set for each month.

So my results look like this:

Month     Paint Chipping        Leak         Broken
May 2018      2                  4              5
June 2018     1                  4               0
July 2018      0                 1               2

Here I used 3 examples but there are twenty categories (most with longer names) and even in the landscape orientation it is too much.

Is there a way to get groups (and really the whole report) to stack horizontally instead of vertically so I can get this instead

Reason            May 2018    June 2018    July 2018 
Paint Chipping        2            1           0
Leak                  4            4           1
Broken                5            0           2

One of the parameters is a date range and it won't go beyond a year's time so I won't run into the same as with the other orientation.

I did consider formatting the fields so that the table could be read in a portrait orientation while the report still was in landscape orientation but that is a pain for our users plus the report viewer we use with our software does not allow you to change orientation.

I have also tried a crosstab but given that I have multiple fields to place in rows and all those fields have null fields, the cross tab produces a hot mess.

Is this possible or if there is another way to get what I'm after let me know.

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Best Answer
    Posted on Oct 11, 2018 at 05:11 PM

    Ok, I think I've go this figured out.

    In order to get the format you want, you're going to have to use a Command, which is a SQL Select statement to get the data into the format you need it. This is necessary because any given record can have multiple reason codes in the same field, so you need to union several queries together, each one looking for a single reason code.

    The "CHARINDEX" syntax, which does the same thing as the Crystal "Instr" function, will probably be different if you're using a different type of database. I used a "With" statement as part of this. With statements pull data into memory a single time as a temporary cursor that you can base other queries off of instead of going to the actual tables for every query that's been joined with a UNION. It is a faster way of doing things, but again, it doesn't work with all database types. I know for sure that it works in SQL Server and Oracle, and I'm pretty sure that it works in Sybase.

    You'll have to delete the start date and end date parameters from your report and then create them in the Command Editor when you add the query to the command. Parameters that have been created in the Command Editor have some additional internal properties that parameters created in the report don't have and the Command Editor can't see the parameters that have been created in the report. Once you save the command, the parameters are available in the report's Field Explorer so that you can use them on the report. For more information about how to work with commands, see my blog post here.

    In your report, create a cross-tab that has the new reasonName field as the rows, the new reviewMonth field as the column, and count of PatID as the summary.


    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 04, 2018 at 08:16 PM

    What does the data in {return:reason} look like? What type of database are you getting the data from? What version of Crystal are you using?


    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.