| Author |
Message |
johnwsun
Senior Member
Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
|
   Topic: Crosstab report issue
Posted: 18 Feb 2013 at 3:45pm |
Hi All, I wish to have a crosstal report designed in such a way where: reservations items added new membership location abc 65 250 12 def 35 200 5 ghi 15 100 6 based on a selection query o ad-hoc date range, week, quarterly, yearly The tables related to reservations, items and membership are not linked to each other, how can I make them available in such a desgin? should I use view on the databse or sql command in the reports iteself. Thanks in advance. JS
|
 IP Logged |
|
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 19 Feb 2013 at 3:45am |
I would write a SQL command that does the following: - Three queries unioned together - one for each table. - Add a "Type" field that would have values that are the same as the column headers that you have above. That should get you the data you're looking for in a format that will work with a crosstab. -Dell
|
|
|
|
IP Logged |
|
johnwsun
Senior Member
Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
|
Posted: 19 Feb 2013 at 12:09pm |
Hi Dell, Thanks for your quick response. my further query, as the above statistics such as 'reservation', 'items added', each of which comes from several tables linked. I know that two or more SQL UNION together need same column, but the above statistics are not same columns. Could you please elaborate a bit more by giving an example on the part of 'add a "Type" field that would have values that are the same as the column headers'? Thanks again. JS
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 19 Feb 2013 at 3:55pm |
Something like this: Select 'Reservation' as record_type, table1.Field1, table2.Reservation as item_count from table1 inner join table2 on .... where.... UNION ALL Select 'Items Added' as record_type, table1.field1, table3.items as item_count from table1 inner join table3 on... where... UNION ALL Select 'New Membership' as record_type, table1.field1 table4.members from table1 inner join table4 on... where ... The trick is to have the same number of fields, of the same type, in the same order for all three select statements. It doesn't matter if the field names are different - you can use "as" to alias them or the field names from the first query in the set will set the field names for all of the results. -Dell
|
|
|
|
IP Logged |
|
johnwsun
Senior Member
Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
|
Posted: 19 Feb 2013 at 4:00pm |
thanks Dell, Could I use UINION? I am aware that using UNION ALL will lead to duplicates...may be not in my case. is the 'type' goiig to be the heading of column? I will have a look into it and try your advices, and get back to you shortly. Cheers, JS
Edited by johnwsun - 19 Feb 2013 at 4:04pm
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 20 Feb 2013 at 3:14am |
You could use just union, but union all is more efficient when you can use it. There will be no duplicates between the Select statements, so, as long as there are no duplicates within the individual select statements, you should be able to use union all. Play with it and see what works best for you. -Dell
|
|
|
|
IP Logged |
|
johnwsun
Senior Member
Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
|
Posted: 20 Feb 2013 at 2:04pm |
Hi Dell, I have run the below SQL on the database...and found there is only one column heading...of course, this is 'UNIONed', how is this implemented in CR? the result set after running the SQL: (below is location) no_column_name reservation_placed ABC 123 DEF 345 GHI 645 ABC 34 <- from here should be membership DEF 10 GHI 5 I have listed the SQL here for your comment: SELECT CASE WHEN LTP1.TYP = N'LINE' THEN
(CASE WHEN H1.HEAD IS NULL THEN N'None' ELSE H1.HEAD END)
ELSE
(CASE WHEN H12.HEAD IS NULL THEN N'None' ELSE H12.HEAD END)
END, COUNT(*)AS Reservation_placed
FROM RVP
INNER JOIN RVC ON RVP.IRN = RVC.IRN
INNER JOIN BFS ON RVP.IRN = BFS.IRN
LEFT OUTER JOIN LOCLNK L1 ON RVP.PLOC = L1.IRN
LEFT OUTER JOIN LTP LTP1 ON RVP.PLOC = LTP1.IRN
LEFT OUTER JOIN HEADING H1 ON L1.LNK = H1.IRN
LEFT OUTER JOIN HEADING H12 ON L1.IRN = H12.IRN
WHERE RVP.DTE BETWEEN '2012-7-1' AND '2013-6-30' AND BFS.CODE = N'IDALBY'
GROUP BY
CASE WHEN LTP1.TYP = N'LINE' THEN
(CASE WHEN H1.HEAD IS NULL THEN N'None' ELSE H1.HEAD END)
ELSE
(CASE WHEN H12.HEAD IS NULL THEN N'None' ELSE H12.HEAD END)
END UNION ALL SELECT
CASE WHEN LTP1.TYP = N'LINE' THEN
(CASE WHEN H1.HEAD IS NULL THEN N'None' ELSE H1.HEAD END)
ELSE
(CASE WHEN H12.HEAD IS NULL THEN N'None' ELSE H12.HEAD END)
END, COUNT(*)as current_member
FROM BDR
INNER JOIN MAIN ON BDR.IRN = MAIN.IRN
INNER JOIN BFS ON BDR.IRN = BFS.IRN
LEFT OUTER JOIN BDB ON MAIN.IRN = BDB.IRN
LEFT OUTER JOIN LOCLNK L1 ON BDB.LNK = L1.IRN
LEFT OUTER JOIN LTP LTP1 ON BDB.LNK = LTP1.IRN
LEFT OUTER JOIN HEADING H1 ON L1.LNK = H1.IRN
LEFT OUTER JOIN HEADING H12 ON L1.IRN = H12.IRN
WHERE (MAIN.CREATED_DATE <= '2013-6-30' AND (MAIN.DEACT_DATE IS NULL OR MAIN.DEACT_DATE > '2013-6-30')) AND BFS.CODE = N'IDALBY'
GROUP BY
CASE WHEN LTP1.TYP = N'LINE' THEN
(CASE WHEN H1.HEAD IS NULL THEN N'None' ELSE H1.HEAD END)
ELSE
(CASE WHEN H12.HEAD IS NULL THEN N'None' ELSE H12.HEAD END)
END
Thanks in advace. JS
|
|
IP Logged |
|
johnwsun
Senior Member
Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
|
Posted: 20 Feb 2013 at 6:46pm |
Hi Dell, Further from above, the above query I entered into SQL command works for hard-coded date, but parameter driven not working and returns nothing for {?start_date}, and {?end_date}, the parameter prompt asks for the date format like dd/mm/yyyy, but the format in the date column of the databasee as 'yyyy-mm-dd' or '2010-05-01'. Do I need to convert this in the SQL command? Cheers, JS
Edited by johnwsun - 20 Feb 2013 at 6:47pm
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 21 Feb 2013 at 3:26am |
I would change it to something like this:
SELECT
'Reservations' as Record_Type,
CASE WHEN LTP1.TYP = N'LINE' THEN
(CASE WHEN H1.HEAD IS NULL THEN N'None' ELSE H1.HEAD END)
ELSE
(CASE WHEN H12.HEAD IS NULL THEN N'None' ELSE H12.HEAD END)
END as Head,
COUNT(*)AS Item_Count
FROM RVP
INNER JOIN RVC ON RVP.IRN = RVC.IRN
INNER JOIN BFS ON RVP.IRN = BFS.IRN
LEFT OUTER JOIN LOCLNK L1 ON RVP.PLOC = L1.IRN
LEFT OUTER JOIN LTP LTP1 ON RVP.PLOC = LTP1.IRN
LEFT OUTER JOIN HEADING H1 ON L1.LNK = H1.IRN
LEFT OUTER JOIN HEADING H12 ON L1.IRN = H12.IRN
WHERE RVP.DTE BETWEEN '2012-7-1' AND '2013-6-30' AND BFS.CODE = N'IDALBY'
GROUP BY
CASE WHEN LTP1.TYP = N'LINE' THEN
(CASE WHEN H1.HEAD IS NULL THEN N'None' ELSE H1.HEAD END)
ELSE
(CASE WHEN H12.HEAD IS NULL THEN N'None' ELSE H12.HEAD END)
END UNION ALL SELECT
"Current Member" as Record_Type,
CASE WHEN LTP1.TYP = N'LINE' THEN
(CASE WHEN H1.HEAD IS NULL THEN N'None' ELSE H1.HEAD END)
ELSE
(CASE WHEN H12.HEAD IS NULL THEN N'None' ELSE H12.HEAD END)
END,
COUNT(*)as Item_Count
FROM BDR
INNER JOIN MAIN ON BDR.IRN = MAIN.IRN
INNER JOIN BFS ON BDR.IRN = BFS.IRN
LEFT OUTER JOIN BDB ON MAIN.IRN = BDB.IRN
LEFT OUTER JOIN LOCLNK L1 ON BDB.LNK = L1.IRN
LEFT OUTER JOIN LTP LTP1 ON BDB.LNK = LTP1.IRN
LEFT OUTER JOIN HEADING H1 ON L1.LNK = H1.IRN
LEFT OUTER JOIN HEADING H12 ON L1.IRN = H12.IRN
WHERE (MAIN.CREATED_DATE <= '2013-6-30' AND (MAIN.DEACT_DATE IS NULL OR MAIN.DEACT_DATE > '2013-6-30')) AND BFS.CODE = N'IDALBY'
GROUP BY
CASE WHEN LTP1.TYP = N'LINE' THEN
(CASE WHEN H1.HEAD IS NULL THEN N'None' ELSE H1.HEAD END)
ELSE
(CASE WHEN H12.HEAD IS NULL THEN N'None' ELSE H12.HEAD END)
END
In your cross-tab the Head column becomes the "Row", the Record_Type column is the "Column" and Sum of "Item_Count" is the summary. For your dates, are the date columns you're using actual date types or are they dates converted to strings? In either case, you need to remove the parameters and the selection formula from the main report if that's where you've created them. Instead, you'll do this: 1. Create the Start_Date and End_Date parameters in the Command Editor. Make sure they're Date type parameters, not string parameters. 2. In the SQL command, delete one of the hard-coded dates, including the quotes around it. With your cursor in the spot where you want the parameter, double-click on the appropriate parameter. This will put the param in the query. If the fields are actual date types, Crystal should handle setting up the parameter format so that the database will read it. If the fields are strings that hold dates, you'll have to take care of the conversion where the parameters are used. -Dell
|
|
|
|
IP Logged |
|
johnwsun
Senior Member
Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
|
Posted: 21 Feb 2013 at 11:26am |
Oh,I see, Yes! that works! By the way, Could you offer me some solution for the parameter problem about the date as what I described last query? Another thing, sorry, forgive me for asking again...is there a way to rename the 'command' in the 'selected table' section. I have used SQL command before, but never able to rename when I had more than one command ... command, command1 don't look good at all Thanks a lot! JS
Edited by johnwsun - 21 Feb 2013 at 11:41am
|
|
IP Logged |
|
|
|
