Solved

Crystal MTD YTD Daily Report- as one PDF file

Posted on 2010-09-14
13
1,000 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
  • 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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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!
Read about achieving the basic levels of HRIS security in the workplace.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now