Is possibly for groups to stack horizontally instead of vertically?

[former_member279138]

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.

[DellSC · Accepted solution]

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.

-Dell

[DellSC]

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?

-Dell

[former_member279138]

My data looks like &paint_chipping≤aking&broken&, &paint_chipping&, &paint_chipping&defective≤aking∂s_missing&, etc.

We're using ODBC

We're one crystal reports 2011

[DellSC]

ODBC to what type of database? Is there a limit to the type of of defects that might be in the field? What are the specific ones your report is looking for?

How is the data for your report set up - are you linking tables in Crystal, using a stored procedure, or using a command?

-Dell

[former_member279138]

I'm not sure the type of database.

There is no limit.

We're looking for all 20 options

The tables are linked in crystal.

[DellSC]

I have some ideas about how to do this, but I need to see how your data is laid out and what you're looking for with each option.

Would it be possible for you to save the report with data and upload it here? You'll have to change ".rpt" to ".txt" in the filename in order to get it to upload. I specifically need it with data in order to see what's happening as I work with it. I assume that you have a formula for each of the 20 options so that I can see how you're pulling them.4

Also, how many months are on the report?

Thanks!

[former_member279138]

report-example.txt

Ok. Here it is

[DellSC]

Not quite 🙂

Please save the report as a .rpt file with data. Then go to Windows Explorer and navigate to the .rpt file. If you don't see file extensions, you'll need to turn them on for the next step. Select the file, press F2 to edit the file name, delete ".rpt" and enter ".txt". So, you're NOT exporting the report to a text file, you're renaming it as a text file. This is because the routines for uploading files don't recognize the .rpt file extension so you can't upload files with .rpt extensions.

Thanks!

[former_member279138]

I'm trying but when I change it over to a txt file it tells me it can't be uploaded even when other txt files upload.

[former_member279138]

Sent. Just checking to make sure you got it.

[DellSC]

Yes, I did - Thanks for sending it. I haven't had a chance to look at it yet.