Excel solution to Crystal Cross Tab repeating row labels - video attached
Excel macro:
Sub DistributeRowHeadings()
' Distribute row headings Macro
Columns("C:C").Select
Selection.Cut
Columns("A:A").Select
ActiveSheet.Paste
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(Array(1, 2), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True
End Sub------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642
------------------------------
Original Message:
Sent: 04-23-2020 16:46
From: Dan Burleson
Subject: Crystal Reports columns
If you are okay with having the user do two Excel commands (or use a small macro that opens the exported file and does the two commands) You could use a Cross Tab feature for your inner most row called "Group Options", select the "Customize Group Name" option then select "Use a formula for group name". Enter all your row values separated by a delimiter such as a pipe symbol ("|"). For example:
{AR_InvoiceHistoryHeader.ARDivisionNo} & "|" &{AR_InvoiceHistoryHeader.CustomerNo}& "|" &{AR_InvoiceHistoryHeader.SalespersonNo}
After you export your report to Excel format, cut the inner most row column created with the formula above and paste into column A. Then use the "Text to Columns" command to break out each row label into it's own column. See how this works in the attached 24 second video.
------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642
------------------------------
Original Message:
Sent: 04-22-2020 21:05
From: Robert Osborn
Subject: Crystal Reports columns
HI Jeff,
Agreed but in this client's case she has to RPD to get to Sage and on that server, Excel keeps crashing on her. If they ever upgrade it, I will change up the way I do it.
Thank you,
Bob Osborn
ACI Consulting
p 714.282.0378 ext. 402 f 714.282.0235
Bob@ACIconsulting.com
This communication, including attachments, is confidential and may contain proprietary information intended only for the proposed recipient. Please notify the sender and delete this message if you believe that you have received this message in error or if you are not the proposed recipient. Unauthorized disclosure, copying, or distribution of the information is strictly prohibited.
Original Message------
@Robert Osborn I think we all have clients like that. If you haven't ever done the pivot route, do yourself a favor and dig into it. Easier to learn than riding a bike and the possibilities are nearly endless. You could set the spreadsheet up for her and then write detailed instructions on how to click the two refresh buttons. If she was able to learn how to export the Crystal into Excel, she surely can learn the two button tango.
------------------------------
Jeff Schwenk
FORMER 90M Board Member
Bottomline Software, Inc.
Waynesboro VA
540-221-4444
------------------------------