Solved

Crystal MTD YTD Daily Report- as one PDF file

Posted on 2010-09-14
13
1,005 Views
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!!!!
0
Comment
Question by:Shamanth
[X]
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
13 Comments
 
LVL 100

Expert Comment

by:mlmcc
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

mlmcc

0
 

Author Comment

by:Shamanth
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?!!!!
0
 
LVL 100

Accepted Solution

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

mlmcc
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

Author Comment

by:Shamanth
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.
0
 

Author Comment

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

Author Comment

by:Shamanth
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  

0
 
LVL 22

Expert Comment

by:Ido Millet
ID: 33687529
At least one of the 3rd-party Crystal Reports Desktop Scheduling tools listed at: http://www.kenhamady.com/bookmarks.html 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.
0
 
LVL 100

Expert Comment

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

What data do you have to work with?

mlmcc
0
 

Author Comment

by:Shamanth
ID: 33716806
Let me explore the Cross-Tab functionlity
0
 
LVL 100

Expert Comment

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

mlmcc
0
 

Author Closing Comment

by:Shamanth
ID: 33738094
Thanks!
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

696 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