?
Solved

Crystal MTD YTD Daily Report- as one PDF file

Posted on 2010-09-14
13
Medium Priority
?
1,010 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 101

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 101

Accepted Solution

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

mlmcc
0
More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

 

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 23

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 101

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 101

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

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

Question has a verified solution.

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

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In this article, we’ll look at how to deploy ProxySQL.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

741 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