Crystal MTD YTD Daily Report- as one PDF file

Posted on 2010-09-14
Last Modified: 2012-05-10
I am trying to mimic an existing excel report that has 3 tabs (Daily, MTD & YTD). All three tabs are similar but have different values based on report types MTD, Daily and YTD.  I have to recreate this report in Crystal with PDF as output format. I told the user that we can’t do multi tab reports in Crystal to PDF. So, they asked me to do one below the other
i.e;  PDF Page1: Daily, ;  PDF Page2: MTD, ;  PDF Page3: YTD

Crystal gets the data from a strored proc refcursor. The refcursor has all there (Daily, MTD, YTD) data. How to do this in Crystal?

I tried creating a report with Report Type (Daily, MTD, YTD) as user prompt and all column formulae based on the user prompt. The report is complex and has 2 summary sub reports. But the user didn’t like it because they need to run the report three times to get all three report types: Daily, MTD & YTD). They asked me to create one single report without asking for the report type (i.e Daily, MTD & YTD) prompt. It makes sense because the refcursor returns all necessary columns for three report types. By generating three different reports, we are hitting the database 3 times.

To be short, I want to create a report using a refcursor. But the report should have 3 different headers (Daily, MTD, YTD) and proper page breaks should be created after each report type and I also need to create sub-reports at each report type to show summary information.

Please help!!!!
Question by:Shamanth
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
LVL 101

Expert Comment

ID: 33677669
Crystal can only have 1 deep subreports.

Records can only be in one part of the report so to do this you will have to use a view/procedure that returns the records 3 times.  Once for each "group" you want.

Change the stored procedure to return data as DAILY union MTD union YTD
Then group on DAILY/MTD/YTD



Author Comment

ID: 33678307
Procedure is already built. I can't change it now. It will return all necessary data (Daily, MTD &YTD) around 40 columns at once. Whole data will be brought to the report. What I need is in the I have to show Daily, MTD and YTD data one below the other with different headings instead of 3 excel tabs.  
Report Parameter will be just Date and the Stored Proc returns Daily, MTD and YTD according to the user entered date.
Is it not at all possible?!!!!
LVL 101

Accepted Solution

mlmcc earned 500 total points
ID: 33678544
No, since that requires a record to be used in 3 different places in the report.

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users


Author Comment

ID: 33681678
I can use Cross Tab to avoid Sub Reports. But formatting and showing data in Cross tab is new to me. Can you please give me some tips on formatting crosstabs?
The report has 30+columns and have a group on Region.

At the bottom of the report I need to show the summary of all 4 regions and for all 30 columns. What I did was I saved the main report and used the same report as sub report for summary. It is easy for me to do like this for formatting changes.

Author Comment

ID: 33681792
I can't even adjust the size of the crosstab!!

Author Comment

ID: 33682074
Also, I am seeiing "Total" field 2 times. I am able to edit the text but not able to getrid if the fields completely. I browsed all the Crosstab Expert options.
The report is like this Group By "Region"

Territory  Region   State  Country  Total1  Toal2  Total3 .....  Total35
----------- ------       -----    -------     ------     -----    ------       -------
T1        West
T2        West
T3        West

T29       NEast
T30       NEast
T50       NEast

NEast Total:                                    Sum()1 Sum()2  Sum()3 ..       Sum()35
SWest Total:                                  Sum()1 Sum()2  Sum()3  ..       Sum()35
West Total:                                     Sum()1 Sum()2  Sum()3    ..    Sum()35
Central Total:                                  Sum()1 Sum()2  Sum()3   ..     Sum()35  

LVL 23

Expert Comment

by:Ido Millet
ID: 33687529
At least one of the 3rd-party Crystal Reports Desktop Scheduling tools listed at: allows you to automate the process of exporting Crystal reports to different tabs inside a single Excel Workbook, or export reports to different pdf files and then merge them, and then emailing the resulting files to desired recipients.
LVL 101

Expert Comment

ID: 33713586
A cross tab will grow to the size it requires bith vertically and horizontally.

What data do you have to work with?


Author Comment

ID: 33716806
Let me explore the Cross-Tab functionlity
LVL 101

Expert Comment

ID: 33727043
FYI - You can close this using the ACCEPT MULTIPLE SOLUTIONS in the question


Author Closing Comment

ID: 33738094

Featured Post

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Finding a job can be stressful - searches, resume tweaks, and networking events can be super boring. Luckily we're here to help you land your dream job!
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Notifications on Experts Exchange help you keep track of your activity and updates in one place. Watch this video to learn how to use them on the site to quickly access the content that matters to you.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question