Command ???? Crystal XI R2

[pungigis]

I have 2 sql commands that run in under 3 minutes in sql as follows, the problem I am having is when I pull them into Crystal the run and hang forever, I have killed it everytime after an hour, I am linking them together on the d.yearmonthnum, I have tried linking every possible way, can anybody see any glaring reason why this won't work??? Thanks in advance.

SQL 1
SELECT d.yearmonthnum,
SUM (Case when ph.batchtype IN ('PCR', 'PAR', 'PUR') then -PH.Paid1
when ph.batchtype IN ('PC', 'PA', 'PU') then ph.Paid1
else 0
end) AS Paid,
SUM (Case when ph.batchtype IN ('PCR', 'PAR', 'PUR') then -PH.Fee1
when ph.batchtype IN ('PC', 'PA', 'PU') then ph.Fee1
else 0
end) AS Fees,
SUM (Case when ph.batchtype = 'DAR' then -PH.Paid1
when ph.batchtype = 'DA' then ph.Paid1
else 0
end) AS Adjustments,
ccg.name, ccg.id, c.customer, c.cob
FROM dbo.Master M (NOLOCK)
INNER JOIN dbo.Fact F (NOLOCK)
ON M.Customer = F.Customerid
INNER JOIN dbo.Dim_Date D (NOLOCK)
ON convert(varchar,m.received,101)=convert(varchar,d.fulldate,101)
INNER JOIN dbo.Customcustgroups ccg
ON f.customgroupid = ccg.id
INNER JOIN dbo.PayHistory PH (NOLOCK)
ON M.Number = PH.Number
INNER JOIN dbo.Customer C (NOLOCK)
ON M.Customer = C.Customer
WHERE
d.yearmonthnum between {?Begin Date} and {?End Date}
GROUP BY
d.yearmonthnum,
ccg.name,
ccg.id,
c.customer,
c.cob

SQL 2
SELECT d.yearmonthnum, SUM(M.Original1) AS PlaceAmt,
Count(M.Number) AS PlaceAccts,
SUM(case when s.statustype <> '1 - Closed' Then m.current1 else 0 end) as activeamt,
SUM(case when s.statustype <> '1 - Closed' then 1 else 0 end) as activeaccts,
SUM(case when S.StatusType = '1 - CLOSED'
AND NOT S.Code IN ('CML', 'CIN', 'CHR', 'CCR', 'AIE','CAN','CMR','CBR','CBK', 'PIE') then m.current1 else 0 end) as closedamt,
SUM(case when S.StatusType = '1 - CLOSED'
AND NOT S.Code IN ('CML', 'CIN', 'CHR', 'CCR', 'AIE','CAN','CMR','CBR','CBK', 'PIE') then 1 else 0 end) as closedacct,
SUM(case when S.Code = 'PIF' then m.current1 else 0 end) as pifamt,
SUM(case when S.Code = 'PIF' then 1 else 0 end) as pifacct,
SUM(case when S.StatusType = '1 - CLOSED'
AND S.Code IN ('CML', 'CIN', 'CHR', 'CCR', 'AIE', 'CAN','CMR','CBR','CBK', 'PIE') then m.current1 else 0 end) as withdrawnamt,
SUM(case when S.StatusType = '1 - CLOSED'
AND S.Code IN ('CML', 'CIN', 'CHR', 'CCR', 'AIE', 'CAN','CMR','CBR','CBK', 'PIE') then 1 else 0 end) as withdrawnacct,
ccg.name, ccg.id, c.customer, c.cob
FROM dbo.Master M (NOLOCK)
INNER JOIN dbo.Fact F (NOLOCK)
ON M.Customer = F.Customerid
INNER JOIN dbo.Dim_Date D (NOLOCK)
ON convert(varchar,m.received,101)=convert(varchar,d.fulldate,101)
INNER JOIN dbo.Customcustgroups ccg
ON f.customgroupid = ccg.id
INNER JOIN dbo.Customer C (NOLOCK)
ON M.Customer = C.Customer
INNER JOIN dbo.Status S (NOLOCK)
ON M.Status = S.Code
WHERE
d.yearmonthnum between {?Begin Date} and {?End Date}
GROUP BY
d.yearmonthnum,
ccg.name,
ccg.id,
c.customer,
c.cob

 

[kray4660]

I haven't done very many commands, but I would think that you would have the date range on one command (since you are linking by d.yearmonthnum) and I am assuming that you are doing a inner join. We have noticed that the command in CR does not appear to be very efficient. So whenever possible we make stored procedures or views.

I hope this helps.

 

[lbass]

You should avoid linking commands to each other or to tables, since the links will occur locally and dramatically slow the report. You should always attempt to use one command as your sole datasource.

I'm not sure how you are using these results, but you could consider adding one or both in subreports in a main report, and then create the same parameters in the main report and link each sub to the main report by linking the parms to each other. I think you'll see the speed return. I almost always use commands when speed is an issue.

-LB

 

[pungigis]

Sorry lbass not sure what you mean so make each sql a subreport and on the main report create the prompts for begin date and end date and then link to sub reports??? I am sorry for appearing dense but how will that speed up the return???

 

[TeyBrady]

Why don't you join those two queries in the command object? You should be able to put parenthesis around them and act like they are tables in the from part of a query.

Select sql1.*, sql2.*
from (Your sql1 above) sql1,
(Your sql2 above) sql2
where sql1.yearmonthnum = sql2.yearmonthnum

You may have to mess with the field names a bit because it might not like having two yearmonthnum names come across on the select. If you rename them to yearmonthnum1 and yearmonthnum2 and use them that way in the join you should be able to be as lazy as I was and it will work. So this is what the where clause would look like but you would have to name the fields this way in the sub queries too. You will need the to include the parenthesis I included above on the from. There were not there just to make it easier to read. I'm not sure if you have ever seen a query done like that before where you basically take a valid query put parenthesis around it and act like it was a table or view name in the query. It's fun to do.

where sql1.yearmonthnum1 = sql2.yearmonthnum2

 

[hilfy]

pungigis,

lbass' suggestion will speed up the return because all of the linking will be done on the database server instead of being done in memory (and disk swap!) on the machine where the report is running.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[lbass]

pungigis,

Yes, create separate reports, one for each command, and then add them as subreports to a new shell report (you would need to add a table with a few records in it so that it will execute). In the shell report, create the same date parms you are using in each subreport and link the subs to the main report on those parameters so you only get one prompt per parameter. Do this by using the dropdown in the lower left corner of the subreport linking screen to select the actual parameter, not the defaul {?pm-?parm} version of the parameter.

-LB

 

[pungigis]

Thank you TeyBrady, this is pretty cool and it works however it takes a very long time to run but if i am working with smaller data sets this could definately be used.

LBASS, I have the 2 sub reports in the report footer of the main report, 2 subs have same parameter, do I need to pull in data to the main report to add parameters and then link them??

THANKS to everyone, great help!

 

[TeyBrady]

I believe the code I gave you is essentially the Join that Crystal was doing for you. It finished in the database without you falling asleep so that proved it was faster than letting crystal do it.

You may be able to tune your query. Make sure you have an index on the yearmonthnum field in the dim_date table. You probably already have indexes on all the of fields you use in the joins. If not you might want to try it. I kind of wonder if having an index on the code field in the customer table might help too. You sure use it a lot in those summaries. You use statustype from the status table a bit in your summaries too so an index there might help too.

I've seen indexes do amazing things for queries.

 

[lbass]

As I said, I think you need to bring in one table that contains a few records in order for the subs to execute. Then, yes, create the parms in the shell report and link them to each of the subreport parms.

-LB

 

[pungigis]

ok, i have done this and linked like you said but it is not limiting the data.

main rpt sql
DATES
SELECT d.yearmonthnum, ccg.name, ccg.id, c.customer, c.cob
FROM dbo.Master M (NOLOCK)
INNER JOIN dbo.Fact F (NOLOCK)
ON M.Customer = F.Customerid
INNER JOIN dbo.Dim_Date D (NOLOCK)
ON convert(varchar,m.received,101)=convert(varchar,d.fulldate,101)
INNER JOIN dbo.Customcustgroups ccg
ON f.customgroupid = ccg.id
INNER JOIN dbo.Customer C (NOLOCK)
ON M.Customer = C.Customer
INNER JOIN dbo.Status S (NOLOCK)
ON M.Status = S.Code
WHERE
d.yearmonthnum between {?Begin Date} and {?End Date}
AND c.status <> 'Inactive'
GROUP BY
d.yearmonthnum,
ccg.name,
ccg.id,
c.customer,
c.cob

subreport 1 is sql 1 above and subreport 2 is sql 2 above.

main report parameters are begin date, end date, cob & company id, same parameters on subreport.

sub reports are in a group header section and the group header is dates.yearmonthnum on the main report.

Each subreport is linked as follows from main report to sub report

?Begin Date = ?Begin Date
?End Date = ?End Date
?COB = ?COB
?Company Id = ?Company Id

There is no data being shown on the main report and the subreport should be limiting based on above parameters, but its not limiting the sub report data at all. Can you tell what I am doing wrong???

 

[lbass]

First, why are you showing a SQL statement for the main report? The point is to use a shell report that has a small number of records--just to make the subs execute. Add one field to the report header and suppress it. Then add the subs in separate report footer sections.

Regarding the parameters, you must have the parameters built into the commands you using, and the only parameters you are currently showing are for begin date and end date. If you want to add others, build them into the SQL.

-LB

 

[pungigis]

ok so maybe I can't even do what I need, the reason I was using what i was is i need to be able to prompt for Begin Date, End Date, COB, & Company ID but cob & company id need to provide a list of choices for the user to select and in company id there can be more than one choice. The only way to make this happen was to pull that into the main report and set up a prompt showing values and allowing multiples.

 

[lbass]

If you create parameters in a command you can still create pick lists in the CR interface once the command compiles. However, commands won't accept multiple values parameters. There is an Faq that shows a workaround: faq766-6779.

-LB