Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Copying data from a cross tab table Post Reply Post New Topic
Author Message
sanur13
Newbie
Newbie


Joined: 25 Apr 2016
Location: France
Online Status: Offline
Posts: 18
Quote sanur13 Replybullet Topic: Copying data from a cross tab table
    Posted: 20 Jun 2016 at 2:47am
I cannot figure out a way to copy data from a cross tab table and paste it elsewhere on the report.

Example :

Cross table :

          Jan   Feb   Mar
Client A   2     1     0
Client B   4     3     1
Client C   3     0     0


Is there a way to create a box dialog that shows whatever value is on Feb - Client A ? For this example the value on the box would be 1, but say the report's updated and now client A bought 5 units on february, I'd like the value on the box dialog to be automatically updated to 5.

Hope this is not too confusing, thanks for the help.

IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 20 Jun 2016 at 4:01am
There is no way to copy/paste this in the report. However, there may be a way to get the data.

How do you know which cross-tab cell has the data that you need elsewhere? What are the rules for getting this data?

-Dell
IP IP Logged
sanur13
Newbie
Newbie


Joined: 25 Apr 2016
Location: France
Online Status: Offline
Posts: 18
Quote sanur13 Replybullet Posted: 20 Jun 2016 at 8:35pm
Hi Dell,

Thanks for the help. I want to get the data from the two last months (two lasts columns) of the cross table. I would also like to make a formula that shows the percentage of change between this two months.

I'm using the cross table to show the percentage of orders that were sent on time to the clients on each month.

My parameteres:

Columns: order.delivery_date
Lines: client.name
summarized fields : average(good_ratio)

good_ratio is actually the percentage of orders on time, I get it by using a series of formulas, hope it doesn't get too confusing :

good_ratio = 100 - month_ratio

month_ratio = 100*(Sum ({@Retard}, {order.delivery_date}, "weekly")/DistinctCount ({order_line.ID_order_line}, {order.delivery_date}, "weekly"))

retard = if ({order.delivery_note_date} - {order.delivery_date}) > 0 then 1 else 0




-Santiago.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 21 Jun 2016 at 4:01am
How do you know which months are the last two? Are they based on the current date or on date parameters the user enters?

-Dell
IP IP Logged
sanur13
Newbie
Newbie


Joined: 25 Apr 2016
Location: France
Online Status: Offline
Posts: 18
Quote sanur13 Replybullet Posted: 22 Jun 2016 at 1:36am
The date parameters the user enters.


Santiago.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 22 Jun 2016 at 4:58am
What is the maximum date range? In order to write the query, you need to set the maximum number of months.

-Dell
IP IP Logged
sanur13
Newbie
Newbie


Joined: 25 Apr 2016
Location: France
Online Status: Offline
Posts: 18
Quote sanur13 Replybullet Posted: 22 Jun 2016 at 8:27pm
Ok, the maximum date range is the last day of the previous month. For example, if we used the report today, we would set a date range from January 1st up to May 31st.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 24 Jun 2016 at 3:43am
So, if I understand you correctly, that the first of the current year until the last day of the previous month, unless it's January in which case you want all of the previous year. Is that correct?

Would you please get the query that Crystal has generated by going to the Database menu and selecting "Show SQL". If you'll paste that here, I'll write a query for you. Also, what type of database are you connecting to?

-Dell
IP IP Logged
sanur13
Newbie
Newbie


Joined: 25 Apr 2016
Location: France
Online Status: Offline
Posts: 18
Quote sanur13 Replybullet Posted: 27 Jun 2016 at 4:29am
Hi Dell,

I actually found a soltuion!

I was using the same two date parameters (start and end) to limit both the data for the whole report and the data to be used on the formulas for the current and previous month.

Here's what I did:

I use a start_date and end_date to limit the data of the report.

{CLIENT.DELIVERY_DATE}>={?Start_Date} AND {CLIENT.DELIVERY_DATE}<={?End_Date}

I created a month_start_date and a month_end_date to select the data from the last month and all the formulas are parametered in order to use just the data between those two dates.

Example: every formula I created starts with this condition :

IF {CLIENT.DELIVERY_DATE}>={?Month_Start} AND {CLIENT.DELIVERY_DATE}<={?Month_End}...

That way the report shows the data from the whole year and the formulas use only the data from the last month.


Thanks a lot for your help these last days!


Edited by sanur13 - 27 Jun 2016 at 4:30am
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.