Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Crosstab report issue Post Reply Post New Topic
<< Prev  Page  of 3 Next >>
Author Message
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 21 Feb 2013 at 12:09pm
Information about the parameters is included below the code in my post above.
 
As for renaming, I've had a couple of things work:
 
1.  Select the command and press F2 - it may allow you to rename it.
 
2.  Click twice on the command - not as fast as a double-click, but not really slow either.  If you do it right, this will allow you to edit the name.
 
Bear in mind, however, that using multiple commands or a command with tables in a report is VERY inefficient!  When you have a command involved, Crystal ends up bringing ALL of the data into memory (the command is filtered but its Where clause, but nothing else is!) and then doing the joins and filtering in memory.  If you have very small unfiltered data sets, this is not a big deal.  However, if you're working with a large number of rows, this will significantly slow down the report and may cause it to fail.  Best practice is to have a single command that pulls ALL of the data for the report.
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
johnwsun
Senior Member
Senior Member


Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Quote johnwsun Replybullet Posted: 21 Feb 2013 at 12:17pm
Thanks Dell, sorry I missed the part about the parameter above.
Also thank you about how to rename the command.
I understand about the performance issue...but for my case this is OK as long as the client's requirement is met.
 
Regards,
 
JS
IP IP Logged
johnwsun
Senior Member
Senior Member


Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Quote johnwsun Replybullet Posted: 21 Feb 2013 at 12:44pm
Hi Dell,
 
I tried what you suggested about renaming the command, that works!
I also tried again about the date parameters-- actually the same way I did yesterday (somehow it was not working then) -- it works too!!
 
Thanks a lot!
 
Cheers,
 
JS
IP IP Logged
johnwsun
Senior Member
Senior Member


Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Quote johnwsun Replybullet Posted: 24 Feb 2013 at 2:37pm
Hi Dell,
 
I come back with new query, the previous setup works OK! my client would like to have more parameters:
1. Locations:  either ALL or one or more;
2. time breakup: day, week, moth, quarter, year, financial year which based on
   the setting Current or previous  
 
I wonder if the following code should be set up in record selection or in the SQL command ( to integrate into the SQL)?
Could you please advise? thanks in advance.
 
JS
 
if ?location = 'ALL' then (OND.LOC = * OR L1.LNK = *)
   else
       if ?location IN [3,4,44,80,88, 119, 291, 292,293, 294. 295,552,553] then
         join(OND.LOC = ?location) OR join(L1.LNK = ?location))
else
   if ?set = 'C'  then   //current
        if ?break = 'D' then  // day
            currentdate
       else
           if ?break = 'W' then // week
              currentweek
           else
                if ?break = 'M' then  // month
                   currentmonth
                else
                    if ?break = 'Q' then   //quarter
                        currentQuarter
                     else
                         if ?break = 'Y' then  //yearly
                             currentyear
                          else
                              if ?break = 'F'  then //financial year
                                  the currentfinancial year // 1/7/2012 - 30/06/2013
    else
        if ?set = 'P' then  //previous
                if ?break = 'D' then  // day
            currentdate - 1
       else
           if ?break = 'W' then // week
              previousweek
           else
                if ?break = 'M' then  // month
                   previousmonth
                else
                    if ?break = 'Q' then   //quarter
                        previousQuarter
                     else
                         if ?break = 'Y' then  //yearly
                             previousyear
                          else
                              if ?break = 'F'  then //financial year
                                  the previousfinancial year // 1/7/2011 - 30/06/2012
 
I could use SELECT CASE structure which is more clear.
 
How do I integrate 'ALL' when prompts for selecting locations:
1. All locations
2. xy
3.wu
etc
 
the following SQL works for one or more locations but not for 'ALL':
SELECT   'Issues' 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 OND
INNER JOIN BFS ON OND.IRN = BFS.IRN
LEFT OUTER JOIN LOCLNK L1 ON OND.LOC = L1.IRN
LEFT OUTER JOIN LTP LTP1 ON OND.LOC = LTP1.IRN
LEFT OUTER JOIN HEADING H1 ON L1.LNK = H1.IRN
LEFT OUTER JOIN HEADING H12 ON L1.IRN = H12.IRN
WHERE LDTE BETWEEN {?start_date} AND {?end_date} AND BFS.CODE = N'IDALBY'  AND( (OND.LOC IN {?location}) OR (L1.LNK IN {?location}))
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
 
Please advise, thanks in advance
 
JS


Edited by johnwsun - 25 Feb 2013 at 1:15am
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 25 Feb 2013 at 3:28am
Try something like this:
SELECT   
 'Issues' 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 OND 
  INNER JOIN BFS ON OND.IRN = BFS.IRN 
  LEFT OUTER JOIN LOCLNK L1 ON OND.LOC = L1.IRN 
  LEFT OUTER JOIN LTP LTP1 ON OND.LOC = LTP1.IRN 
  LEFT OUTER JOIN HEADING H1 ON L1.LNK = H1.IRN 
  LEFT OUTER JOIN HEADING H12 ON L1.IRN = H12.IRN 
WHERE LDTE BETWEEN {?start_date} AND {?end_date} 
  AND BFS.CODE = N'IDALBY'  
  AND( ({?location} = 'ALL') OR 
   (OND.LOC IN {?location}) OR 
   (L1.LNK IN {?location}))
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
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
johnwsun
Senior Member
Senior Member


Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Quote johnwsun Replybullet Posted: 25 Feb 2013 at 12:30pm
Hi Dell,
 
Your suggestion works for 'ALL', but now if I choose more than one location, it geenrates an error ( two or three locations):
Failed to retrieve data from the database
Source: Microsoft OLE DB Provider for SQL server
Description: An expression of no-boolean type specified in a context where a condition is expected, ear ','.
SQL state: 42000
Native Error:4145 [Datbase Vendor Code:4145]
 
Could you advise me please?
It seems to me that I need to use concatenation for SQL command equevalent to the 'join' function for string array?
 
Thanks again,
 
JS
 
 
 


Edited by johnwsun - 25 Feb 2013 at 12:31pm
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 25 Feb 2013 at 12:48pm
Instead of ({?location} = 'ALL') try ('ALL' in {?location}).
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
johnwsun
Senior Member
Senior Member


Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Quote johnwsun Replybullet Posted: 25 Feb 2013 at 1:08pm
Hey, it works now!! Smile
 
Thanks a lot!
 
JS
IP IP Logged
johnwsun
Senior Member
Senior Member


Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Quote johnwsun Replybullet Posted: 27 Feb 2013 at 5:51pm
Hi Dell,
 
I come back with another query:
 
I wish to display 0 for the following column which has currently no data for all the locations:
 
                   Medical Devices issued
(locatios)
abc                 0
def                  0
ghi                  0
...
...
...
 
I have put the following SQL in the SQL command, but the following column won't show as I expected that there are no data currently in the system ( I have set 'convert Database NULL values to default', but it won't help). Could you advise if there are some other ways to achieve the goal:
 
UNION ALL
SELECT   'Media Devices Issued' 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 OND
INNER JOIN BFS ON OND.IRN = BFS.IRN
INNER JOIN ICO ON OND.ITM = ICO.IRN
INNER JOIN MAIN ON ICO.COL = MAIN.IRN
LEFT OUTER JOIN LOCLNK L1 ON OND.LOC = L1.IRN
LEFT OUTER JOIN LTP LTP1 ON OND.LOC = LTP1.IRN
LEFT OUTER JOIN HEADING H1 ON L1.LNK = H1.IRN
LEFT OUTER JOIN HEADING H12 ON L1.IRN = H12.IRN
WHERE LDTE BETWEEN '2012-07-01' AND '2013-06-30' AND BFS.CODE = N'IDALBY'  AND MAIN.XCODE = ('MDEVICE') AND (( 'ALL' IN {?location} ) OR (OND.LOC IN {?location}) OR (L1.LNK IN {?location}))
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 adance.
 
JS
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 28 Feb 2013 at 6:43am
I don't know your data well enough to know whether this would work for sure, but you could try something like this:
 
1.  Change the to Main to something like this:
 
Left Outer Join Main on ICO.COL = MAIN.IRN and MAIN.XCODE = 'MDEVICE'
 
2.  Take the reference to "MAIN.XCODE" out of the where clause - it's now handled by the join.
 
3.  Change "Count(*)" to "Count(some field)" where the field is not in OND, BFS, or ICO and is something that will only have a value if there is an MDEVICE record.
 
You're getting no data because of the explicit "MAIN.XCODE = 'DEVICE'" in the where clause.  So, by using a left outer join AND looking for the appropriate XCODE in the join, you should get data even when there is no device record.
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
<< Prev  Page  of 3 Next >>
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

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | BAEZL

© 2004-2026 Brian Bischof. All Rights Reserved.