| Author |
Message |
Squigy
Newbie
Joined: 23 Nov 2009
Location: Colombia
Online Status: Offline
Posts: 3
|
  
Topic: CHALLENGE - Can this be done?
Posted: 24 Nov 2009 at 8:48am |
< ="Content-" content="text/; charset=utf-8">< name="ProgId" content="Word.">< name="Generator" content="Microsoft Word 12">< name="Originator" content="Microsoft Word 12"> <>
I'm not a newbie to CR, but I'm not an expert
either. I'm creating a report using C# (VS2008).
No problem with connectivity, the data is there. My problem is with the desired format. My data is being supplied by MySQL and looks
like this.
|
DATE
|
ROUTE
|
PLATES
|
|
2009-11-23
|
R1
|
UAQ122
|
|
2009-11-23
|
R1
|
UAO304
|
|
2009-11-23
|
R2
|
UAN867
|
|
2009-11-23
|
R2
|
UAP539
|
|
2009-11-23
|
R3
|
UAP939
|
|
2009-11-23
|
R3
|
UAQ621
|
|
2009-11-23
|
R4
|
UAQ123
|
|
2009-11-23
|
R4
|
UAQ576
|
|
2009-11-24
|
R1
|
UAP095
|
|
2009-11-24
|
R1
|
UAO020
|
|
2009-11-24
|
R2
|
UAO298
|
|
2009-11-24
|
R2
|
UAP432
|
|
2009-11-24
|
R3
|
UAO383
|
|
2009-11-24
|
R3
|
UAP725
|
|
2009-11-24
|
R4
|
UAQ830
|
|
2009-11-24
|
R4
|
UAP025
|
|
2009-11-25
|
R1
|
UAO591
|
|
2009-11-25
|
R1
|
UAO516
|
|
2009-11-25
|
R2
|
UAP644
|
|
2009-11-25
|
R2
|
UAR784
|
|
2009-11-25
|
R3
|
UAN965
|
|
2009-11-25
|
R3
|
UAP073
|
|
2009-11-25
|
R4
|
UAP164
|
|
2009-11-25
|
R4
|
UAP564
|
For each day, there are four routes and each
route may have an unspecified number of vehicles (PLATES).
I am trying to get the report to look like
this, but to no avail.
|
ROUTE
SCHEDULE
|
|
2009-11-23
|
2009-11-24
|
2009-11-25
|
|
R1
|
R2
|
R3
|
R4
|
R1
|
R2
|
R3
|
R4
|
R1
|
R2
|
R3
|
R4
|
|
UAQ122
|
UAN867
|
UAP939
|
UAQ123
|
UAP095
|
UAO298
|
UAO383
|
UAQ830
|
UAO591
|
UAO644
|
UAN965
|
UAP164
|
|
UAO304
|
UAP539
|
UAQ621
|
UAQ576
|
UAO020
|
UAP432
|
UAP725
|
UAP025
|
UAO516
|
UAR784
|
UAP073
|
UAP564
|
The layout is important because the company
already uses this format and it is a requirement to produce an equal
report. The difficulty is that CR
displays data based on consecutive rows, whereas I want each row to display
data more in a tabular fashion.
So can it be done? And if so guide in the right direction, or
better yet, how would you do it?
|
 IP Logged |
|
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 25 Nov 2009 at 6:07am |
|
I haven't needed to use them, but it appears to be a fine candidate for a cross tabs report. SSRS calls them matrix and others Pivot. Sorry I can't help more.
|
| IP Logged |
|
Squigy
Newbie
Joined: 23 Nov 2009
Location: Colombia
Online Status: Offline
Posts: 3
|
Posted: 25 Nov 2009 at 6:38am |
|
I tried to use a cross tab, but the cross tab summarizes the data. I need the license plates to be placed below their corresponding route numbers (R1, R2, etc) without summary. That is the hard part.
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 25 Nov 2009 at 7:46am |
is the report for a fixed number of days? If so, it might be possible. The report would all be the footer, but that should be ok, and there are only 4 routes.
I would probably use arrays, I would have 1 for datetime to record the date, and I would have 4 string arrays 1 for each route.
shared datetimevar array goodDate;
shared stringvar array r1;
shared stringvar array r2;
shared stringvar array r3;
shared stringvar array r4;
in the report I would dimension the all the arrays in the report header with a distictcount({table.date}), I would then find the array member in the datetime array that matches the datetime in the record, then I would update the route array for the correct datetime index with a delimited string, something like: plate1|plate2|plate3|. This would be done in the detail section of the report.
In the footer of the report, print your datetimes, then for each route print the associated string from route array for the date in question, replacing the "|" with chr(13)+chr(10) (crlf). Set the route to can grow.
It should work. There is alot of coding in formulas, and if the dates are out of order, this doesn't address that issue.
I realize it is an outline, and kindof sketchy, but this will take a while....
I do hope to assist in finding a path to a solution, and I hope that this at least points in a direction that might work.
|
| IP Logged |
|
Squigy
Newbie
Joined: 23 Nov 2009
Location: Colombia
Online Status: Offline
Posts: 3
|
Posted: 25 Nov 2009 at 11:47am |
|
Unfortunately the number of days is not fixed. Thanks nonetheless. I guess a Cross Tab/Pivot Table that would allow me to use the actual values as opposed to a summary of the values would work. Is this possible?
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 30 Nov 2009 at 6:16am |
|
Sorry, I have never had to use a cross tab report in Crystal...
|
| IP Logged |
|
CoolEJ
Newbie
Joined: 30 Mar 2009
Location: Philippines
Online Status: Offline
Posts: 7
|
Posted: 07 Dec 2009 at 10:25pm |
you can do that using grouping>>>> by date then by route.
It will appear by row instead of by table. Thus you may just want to twist the page 90 degrees. Then you have it in a tabular form. The text though are still vertically inclined.
I guess you can do the twisting in VS?
|
| IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 08 Dec 2009 at 10:48am |
|
For what it worth, I created a data source using the data above and did not have any success.
|
| IP Logged |
|
|
|
