x
Solved

# I need to create a percentage column on my crystal report based on ratio of counts on reports

Posted on 2009-04-13
Medium Priority
2,361 Views
I need to create on my report two percentage columns that take a count from each row and divide it by the sum of counts below to produce each percentage value in the column. Would I  specifically need to create a crosstab report or a standard report ? Right now I have been given an excel spreadsheet that does this by way of formulas..I am not sure if Crystal operates exactly the same way in this regard.
0
Question by:Christopher_Arm1
• 42
• 25
• 13

LVL 101

Expert Comment

ID: 24131319
It can be done in a crosstab by changing the summary to be a percentage

It can also be done with formulas
Is it a single field or a summary?

Formula
{YourField} / Sum{YourField} * 100

Display with the %

mlmcc
0

Author Comment

ID: 24148557
Problem number one: before I even do that I am realizing I am placing three stored procedures in said crystal report to give me columns contains counts and financial sums. The crystal reports keeps asking for multiple sets of parameters based on each stored procedures parameter definition however they are the same  across the board start date and end date and productype. I need this to appear once before proceeding to setting up the percent formulas.
0

LVL 35

Expert Comment

ID: 24148855
You have 3 stored procedures in the main report?  Sounds messy, for lack of a better term.

I don't think I've ever tried to use more than one stored procedure in a single report, but the way CR automatically picks up the parameters, my guess is that the parameters from each procedure are going to be separate and that's that.  About the only way I can think of to "link" the parameters from the different procedures together in CR would be to do something like put two of the stored procedures in subreports and link the parameters in the main report to the parameters in the two subreports.  Of course whether or not using subreports is a viable option will depend on the structure of your report.

Forgive the obvious, but the best solution would probably be to somehow eliminate the need for 3 different stored procedures (combine them all into one procedure or something).

James
0

Author Comment

ID: 24151763
I understand that however the report needs multiple stored procedure for the calcuations involved unforunately
0

Author Comment

ID: 24151772
multiple stored procedures and unfortunately. Forgive the typoes. =)
0

LVL 35

Accepted Solution

James0628 earned 1000 total points
ID: 24154071
OK, I just tried creating a report that used two stored procedures and CR actually did consolidate the duplicated parameters, so the list of parameters only included each unique parameter once.  These two procedures each have 8 parameters.  Seven of those are the same in each procedure and each procedure has 1 unique parameter.  The report lists 9 parameters, the 7 duplicated ones and the 2 unique ones (1 from each report).

Which version of CR are you using?  That might be a factor.  I'm using CR 10.

What db are you using?  That might also be a factor.  I'm using stored procedures under SQL Server 2005.

Lastly, make sure that your parameters are defined the same in each procedure.  Not just the same name, but the same definition (eg. varchar (2) or datetime).  I'm assuming that if the definitions are different, including having the same data type, but with a different length, CR can not "consolidate" the parameters.

James
0

Author Comment

ID: 24157445
I am using Crystal XI and SQL Server Management Studio
0

LVL 101

Assisted Solution

mlmcc earned 1000 total points
ID: 24159106
Are the parameters named the same?

mlmcc
0

LVL 35

Expert Comment

ID: 24164339
Of course "SQL Server Management Studio" is just your interface to the server.  It doesn't tell us which version of SQL Server you're using.  But I doubt that the version will be a factor in this.

Are the parameters defined exactly the same in each stored procedure?

James
0

Author Comment

ID: 24216160
Yes they are defined exactly the same way @startdate1 and @enddate1 in each sp.
Now how do I begin this mapping multiple stored procedures so that the parameter request for selecting the dates appears once. And that once the grand totals are generated at the bottom of the report that afterwards percentages are calculated in two columns.
0

Author Comment

ID: 24216170
And I checked its SQL 2005.
0

Author Comment

ID: 24217899
Anything guys ? How would I do this step by step ?
0

LVL 35

Expert Comment

ID: 24221108
> Now how do I begin this mapping multiple stored procedures so
> that the parameter request for selecting the dates appears once.

When I tried using two stored procedures in one main report, CR automatically combined the identical parameters from each one, so those parameters only showed up once in the list of parameters.  I didn't have to do any "mapping".

In the "Field Explorer", under "Parameter Fields", do you see some parameters listed multiple times (once for each stored procedure that uses that parameter)?

As for the grand totals and %'s, what are you trying to do, exactly?  For example, let's say that you had a sales field, are you just trying to show on each detail line the % of the grand total that is represented by the sales on that line?

If so, then you could just create a formula similar to the following and put it in your detail section:

if Sum ({table.sales field}) <> 0 then
{table.sales field} % Sum ({table.sales field})

James
0

Author Comment

ID: 24251489
When I meant "mapping" I was talking about adding each individual stored procedure to the report in this care I needed to add four of them. They all share the same parameters so that is no problem. The parameters fields show only two parameters in this case startdate and enddate which is what I require.
Each stored procedure generates two columns and four rows of data. The first column being the count value and the second column being the sum for each category row outlined. The SP looks something like this:
select count(distinct NoDupes.AppID) as Received, sum((NoDupes.RequestedAmount))as Amount from(select v4.AppID, v8.RequestedAmount from [x].[dbo].CrTier as v1
inner join [x].[dbo].ApplicantCredit as v2
on v1.CrTierID = V2.CrTierID inner join [x].[dbo].ApplApplicants as v3 on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8
on v4.ApplicationID = v8.ApplicationID
WHERE v5.ProductDescription like '%Auto'
and v3.BorrowerTypeID = '1'
and v4.ApplDate between '2009-04-01 05:00:00.000' and '2009-04-17 05:00:00.000'
and v8.ActionTakenDateTime <=(select min(v8.ActionTakenDateTime) as Tier from [x].[dbo].ApplicationHistory as v8 where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes

Now imagine four unions after this with identical code except for the product description has been changed each time. Ok.. you get four rows of data. The count values need to be summed that can be done on the report itself. And so do the sum values displaying a dollar amount they too are also summed up. What I was referring to was say the total of the count values  is 14,018 for example
and say my individual counts I get are 509, 1209, 7011,5289..I need my next column to contain percentages that have the results of  509/14,018 * 100 1209/14,018 * 100 and so on....

0

LVL 35

Expert Comment

ID: 24256591

> When I meant "mapping" I was talking about adding each individual
> stored procedure to the report in this care I needed to add four of them.

Earlier you were saying that you had the same parameter(s) in two or more of your procedures and when you ran the report, CR was prompting you for each of those separately.

Then you said:

> Now how do I begin this mapping multiple stored procedures so that
> the parameter request for selecting the dates appears once.

Clearly there you're still talking about the same problem.  I don't see any other way to interpret that.

Anyway ...

Did you get the parameter issue fixed, so that when the same parameter is used in two or more of the procedures, you only have to enter it once?  Just curious.

As for your counts and %'s:

> ... say my individual counts I get are 509, 1209, 7011,5289.

Where/How do you get those counts?  If the report is getting all of the rows from the 4 stored procedures combined and you're getting a subtotal for each set, how do you do that?  Do you have the report grouped on something or ???

If you're getting those subtotals using a regular summary, you can add another summary for the same field and group and in the summary options, select the "Show as a percentage of" option and select the grand total.

James
0

Author Comment

ID: 24260475
I see two parameter fields on the report itself start date and end date checked off in field explorer. That looks fine as the parameter entry only seems to appear once.

Each stored procedure like the one above produces a count and a sum.
I could have shown you the other three stored procedures but they are identical save for the product description which in turn generates a different count and sum. Each SP has a group by to prevent duplication. Anyway.. the the four stored procedures have unions joining them together so it  produces something like this. The total of 1038, I can get by summing the field values upon them being generated on the report. My question is that to generate the  count percentage I need to grab the each individual count field and the total count field indicated below and show it in percentage format.

Count       Sum         Percent
-------      -------        ----------
509       1,400.00
239      15,000.00
80         1,000.00
210       5,000.00
------------------------------
1038   22,400.00
0

LVL 35

Expert Comment

ID: 24276047
So the 509, 239, etc. come directly from a field?  They're not totals?

In that case, create a formula like following (call it whatever you want) and put it on the report:

if Sum ({Count}) <> 0 then
{Count} % Sum ({Count})

Replace {Count} with whatever field that "Count" column is coming from.

James
0

Author Comment

ID: 24280007
Unfortunately the duplication using mutliple stored procedures is still occuring so I have decided to use subreports within the main report thereby giving my results. Howevr do I have to link the subreports to use a shared variable . I need one column of data in one subreport to generate the percentage data in another subreport.
0

LVL 101

Expert Comment

ID: 24280566
You don't have to link them to the main report just use this basic idea

WhilePrintingRecords;
Shared NumberVar MyValue;
''

In the first subreport
WhilePrintingRecords;
Shared NumberVar MyValue;
MyValue := 40;
'

In the second subreport
WhilePrintingRecords;
Shared NumberVar MyValue;
MyValue

mlmcc
0

LVL 35

Expert Comment

ID: 24284441
A very small correction - There should be two quotes at the end of mlmcc's second formula, instead of just one.  FYI, the quotes at the end of the first two formulas are simply so that they don't produce any output on the report.  You can also suppress those fields on the report, or the sections that they're in, in which case the output from the formulas won't matter.  Personally, I normally end formulas like those with the quotes, so I don't have to worry about suppressing them.

One other basic tip:
The subreport containing the formula that sets the variable has to be executed before the subreport that needs to "read" that variable, and the best (if not only) way to make sure that that happens is to put the two subreports in separate sections of the main report.

James
0

Author Comment

ID: 24295834
One quick question... the subreports have block fields/margins around them which seem to prevent from putting the column information generated by each subreport side by side. Is there a way to line them up instead of displaying the information vertically and also can I display the viewer in landscape mode as opposed to portrait since the information would be better displayed that way ?
0

LVL 101

Expert Comment

ID: 24296124
Landscape is controlled by your printer settings.
Click FILE --> PAGE SETUP

DO you want

Main report data         Sub1 data     Sub2 data

You should be able to do that but lining it up may be a problem.
Also if there are missing records or extra records in the subreports you may have to account for them in some way.

Not sure what you mean by block fields and margins.

mlmcc
0

Author Comment

ID: 24296214
Exactly what I want. What I meant were margins a rectangualr boundary around the subreports themselves when I toggle the mouse over it to move those results its movement on the report is limited.
0

Author Comment

ID: 24296293
Ok the main report data is line by line meaning
adding the subreport next to the main report data makes it look like this:
main report                 subreport
col 1       col 2                col3       col4
chris     85,000.00         scott     45,000
mark       33,000
jane         22,000
priscilla 44,000.00
everett  15,000.00
glenn     10,000.00
0

LVL 101

Expert Comment

ID: 24297234
What do you want?

Do you need CHRIS across line 1 of all?\ or just eliminate the spaces after CHRIS

mlmcc
0

Author Comment

ID: 24297835
The second one. the first set of results are the main report and subreport is moved next to it shows there is a field space that enlarges in that rowset.
0

LVL 101

Expert Comment

ID: 24298078
I sounds like you have the subreport in the details section.

You will need to link it to the main report on the date and the name/id field

mlmcc
0

Author Comment

ID: 24298397
Ok... another related question these percentage calculations I mentioned earlier in the thread is it easier to do them in SQL and insert everything into a temp table and then map the crystal report to the temp table ? Rather than on the report itself...
0

LVL 101

Expert Comment

ID: 24299095
Depends on your skill with MS SQL.

It generally is faster to do the calculations in the database if you can.  It definitely makes the report easier to write.

Since my skill with SQL is limited, I generally opt for using the report but most of my calculations are relatively easy

mlmcc
0

Author Comment

ID: 24307082
Yes this is my problem.   This is foreign territory for me using subreports with calculations in them and using shared variables to generate percentage columns dynamically on the report. This seems to be my only alternative since I was required to use multiple stored procedures by themselves rather than insert their results into a temp table that the report would pick up percentage, counts, summed up amounts and all the report would not have to calculate anything at all. But this way which I am committed to producing the same results is much harder.
0

Author Comment

ID: 24325811
No. There has to be simpler way using multiple stored porcedures on a crystal than usign subreports. The subreports are messy and they required added steps to use them like linking them and  such as  using shared variables to use a column in one subreport to generate calculations for another.
I have multiple stored procedures and using more than two on the report in Crystal XI and no results appear past that and I have four of them. Then if I can get them to display usign a simpler method what remains is calculating a percentage as indicated above. The users who want this project gave me an excel spreadsheet and I was thinking how simple this would be to have used DTS  to export my results to excel or csy file where the formulas are hardcoded and that would be it except SQL 2005 does not have that option.
0

LVL 101

Expert Comment

ID: 24326357
You can add multiple stored procedures to a report provided you can join them in some way.

mlmcc
0

Author Comment

ID: 24327145
Joining them through a primary key or foreign key no doubt common to all of the SP's being used. Unfortunately, Crystal Reports links the stored procedures by the field names I am using to count and sum which might be the problem when it returns no data on the report and removing all of the columns except for one stored procedure that returns count and sum values. How then would I be able to get around this problem ?
0

LVL 101

Expert Comment

ID: 24327305
Open the report
CLick FILE --> OPTION

Same for REPORT OPTIONS

You can then link the procedures as you want them

mlmcc
0

Author Comment

ID: 24328397
I turned off the smart linking by way of FILE-->OPTION and still see the same result. Nothing appears on the report. But then I am not creating my own links because there is no similiar foreign or primary key or similar field to link off of in the report
0

LVL 101

Expert Comment

ID: 24329702
You have to go in and change the linking.

To use multiple tables there has to be a common field (not by name) which relates the records.
If you don't then you either get no data or a cross join which creates lots of data but the records really aren't related.

mlmcc
0

Author Comment

ID: 24329745
Can I join my stored procedures so that the data lines up across like one big table and then map it to the report ? Is that possible via some outer join or left join ?

0

LVL 101

Expert Comment

ID: 24331095
If there is a common field between them then yes.

mlmcc
0

LVL 35

Expert Comment

ID: 24332623
> But then I am not creating my own links because there is
> no similiar foreign or primary key or similar field to link off
> of in the report

That's the key.  There have to be fields/columns in the output from each procedure, like a customer number, that can be used to link the records together.

I created a new report, added two stored procedures to it, and then on the Links tab in the "Database Expert", CR had automatically linked all of the fields with the same name in each procedure.  From there I could delete those links or add new ones (to link two fields that had a different name in each procedure).  All CR appears to be doing is executing the two stored procedures separately and then using the linked fields to join the two result sets together, creating one data set for the report.

James
0

Author Comment

ID: 24336499
Yeah.. that's what I thought I can't link a count of a field to a count of another field that might have the same customerid  number in both( counts) in four different stored procedures that determine those counts based on different decision categories that pull essentially different numbers because different individual ids are being pulled in each separate stored procedures. I  just realized that it won't work.

I may have no other option but  to use subreports to pull the data from the stored procedures separately and then do my calculations from there.
0

LVL 35

Expert Comment

ID: 24341322
Presumably the output from the stored procedures is connected in some way, like each procedure produces counts or sums for each customer.

In the code that you posted, you have

Group By v4.AppId, v8.RequestedAmount

So, it's producing totals for each v4.AppId and v8.RequestedAmount.  Those two columns are not actually included in the output.

Are the other procedures also producing totals for "AppId" and "RequestedAmount" (maybe under different names, but the same values)?

If so, if you add the "AppId" and "RequestedAmount" columns to the stored procedure output, you should then be able to link the procedures together using those fields.

James
0

Author Comment

ID: 24374408
ok. I seemed to have resolved that problem using the subreports that seems to rid me of the duplication problem on the report when i map the procedures. But the last problem is linking the subreports and  and comparing one column of data with another to generate  percentile columns. What sort of formula would I need to use that incorporates the fields ?
0

LVL 35

Expert Comment

ID: 24381063
So, you're using each stored procedure in a separate subreport, as opposed to using two or more procedures in the same report?

Where are the different subreports (Detail section?  Group header or footer?) and how do you want to "connect" the figures from them?  For example, subreports A and B are in the same detail section and you want to show a total from subreport A as a % of a total from subreport B.

James
0

Author Comment

ID: 24385153
They are all in the detail section the three sub reports and the main report. So I need to grab the count figures under the column approved which are in the main report and then grab the count values under received in the subreport directly beneath it and basically generate a percentage column in the main report that would look like this

Approved          Amount          %Approved

1,312               \$37,819,512       1,312/ 3,509 * 100  = 37.9
799                  \$5,675,890         799/2,561 * 100 = and so on going down the list
225                  \$1,812,490
120                    \$4,908

3,509            \$81,234,908
2,561            \$27,897,970
663               \$ 3,450,900
800                \$ 12,348

I need a formula that takes column a from the the main report and column b from the sub report and placed them in a formula to calculate the percentage and ideally so I can place the percentage in a column called % Approved next to Approved and Amount
0

Author Comment

ID: 24386200
This would require some sort of shared variable to pass data back and forth from the main report to the sub report correct ? Because I am not certain..
0

LVL 35

Expert Comment

ID: 24391582
Yeah, shared variables are the only way to get a value from a subreport to the main report or another subreport.  You can pass a value _to_ a subreport using the subreport links, but shared variables are the only way to get values _from_ a subreport.

mlmcc covered some of this in his post from 05/01, but the basic form is to put a formula in the main report header that declares the variable, put a formula in the subreport that sets the variable, and put a formula in the main report (or another subreport) that uses the variable.

However, I think you have a problem in that I think the lines you showed under "Received         Amount" are from the subreport.  Correct?  If so, then the problem is that the subreport has to be executed first, to set the variables.  For example, you'd have to have the subreport in one detail section, and then the "Approved          Amount          %Approved" columns in the main report in a separate detail section after that.  It needs to be a separate section, because if you put a subreport and a formula that uses a variable set by that subreport in the same section, there's no way to guarantee that the subreport will be executed first, so the variable may not be set when the formula is evaluated.

There's also the complication that you appear to be needing a list of values (3509, 2561, 663 and 800) from the subreport.  If you know that you're going to have a very finite list of values (eg. never more than 5), you could use a separate variable for each one.  Otherwise, you'd need to use one or more arrays.  You also need a way to connect the values to the correct line in the main report.  If there's always going to be a one to one correspondence, with the same number of "Approved" and "Received" counts, then that's all you need.  Otherwise, you'd need something like a customer ID for each count, so you could tell that this "Received" count went with that "Approved" count.

The first question is, where is the subreport in relation to where you need to use the value(s) from the subreport?  If the subreport is located after that point, can you move it?  Another option is to duplicate the subreport.  Insert another copy at a point before you need those values.  That copy won't produce any output.  It will just set the variables, so that the main report can use them.

Another question:

You said that the "Approved" column is coming from the main report.  Are the lines you showed (1312, 799, 225 and 120) separate records?

James
0

Author Comment

ID: 24396232
I don't understand what you are saying here can you please clarify James ?

I don't think I can movethe subreport in relation to the document as it stands right now. I realized that when you place subreports on a document there is a limited limited range of places you can place subreports without overwriting existing data for either the main report or another subreport.

I  need specific counts from approved and received to generate my percentages meaning I need  the first count value of Approved / first count value of received  * 100 and the second over the second and so on meaning I should have fiour percentage value that reflect how many were approved that were received in total.

The display of numbers represent each recordset of inormation how many of each were approved and the dollar amount attached to it. The first tier of information represents the data on the main report and the information below starting with Received is the subreport.
The main report and the subreport have stored procedures mapped to them tha performs counts and sums in their respective stored procedures. A copy of one said stored procedure can be seen above in a post I  made that you responded to afterwards.

Now except for the problem I have of entering parameters of start date and end date multiple times the report generates the subreport and the main report data for all intents and purposes at the same time from my direct observation.

Even so my main question is what would such a formula look like to generate the percentage values  that I needed ....that's number one and number two how can I put the results in the details section next to Approved and avoid duplication of the existing data of the main report also in the details section.
0

LVL 35

Expert Comment

ID: 24400733
Yeah, I knew I was covering a lot of ground there.  :-)

OK, the first problem is that you need a figure from the subreport that produces the "Received" lines and you want to use that on the "Approved" lines, which appear to come earlier in the report.  A subreport can use shared variables to pass values to the main report, but the variables won't be set until the subreport is executed.

What section of the main report produces those "Approved" lines?  Detail section?  Group header or footer (If so, which group?)?

What section of the main report is the "Received" subreport in?

James
0

LVL 35

Expert Comment

ID: 24400752
Of course I thought of this just after I posted that.  :-)

Can you attach your report to a post here so that I (and anyone else) can take a look at it?  That would probably save a lot of time.  If you could save the report with some data and post that, that would probably be even better, as long as the data doesn't include anything "confidential".  To save data with the report, check the File > "Save Data with Report" option, refresh the report if necessary and then save it.

If you're going to u/l the report, note that EE only accepts certain file extensions and RPT is not one of them, so just change the extension to one that EE does accept, like TXT, and u/l that.  You might want to add a note to the file saying that the extension needs to be changed back to RPT.

James
0

Author Comment

ID: 24442760
Unfortunately not as this is confidental work I  would be disclosing... which would present a problem. Even the stored procedure written above had to be altered so database table names were not immediately identifiable. However, this begs the question could I combine the stored procedures that I have so they display the counts and sums of approved, decisioned, received and booked ? I know that UNION or UNION ALL do not work in this regard as they just make the counts and sums all part of one big resultset that is vertically displayed in query analyzer.
0

LVL 35

Expert Comment

ID: 24447809
I think it'd be unusual if a report (without data) included anything that was actually confidential, but, of course, that's your call.

Combining the stored procedures is a good idea.  Looking back, I see that I suggested that over a month ago.

If the procedures all produce the same basic columns, it is possible.  In its simplest form, you'd just combine the queries from each procedure using UNION ALL.  You'd keep the counts and sums from each query separate by putting them in separate columns.  For example, in the sample code that you posted earlier, you had:

select count(distinct NoDupes.AppID) as Received, sum((NoDupes.RequestedAmount))as Amount
from (etc., etc.)

That might become something like:

select
0 as Approved_Count,
0 as Approved_Amount,
0 as Third_Count,
0 as Third_Amount,
0 as Fourth_Count,
0 as Fourth_Amount
from (etc., etc.)

For the queries from the "Approved" stored procedure, you might have something like:

select
count(distinct NoDupes.AppID) as Approved_Count,
sum((NoDupes.RequestedAmount)) as Approved_Amount,
0 as Third_Count,
0 as Third_Amount,
0 as Fourth_Count,
0 as Fourth_Amount
from (etc., etc.)

You'd replace "Third" and "Fourth" with something that's appropriate for the values coming from the third and fourth stored procedure queries, and do the same kind of thing to fill those columns in those queries.

The other thing is that you need a way to tie the rows from each set of queries together (so that the report can combine the first row from each set, and the second row from each set, and so on).  Before, you said that a stored procedure had a set of queries with different product descriptions.  I'm guessing that you're referring to lines like the following:

WHERE v5.ProductDescription like '%Auto'

If those are the same for each query in each stored procedure (eg. the first query in each procedure looks for '%Auto', the second looks for '%value2', the third looks for '%value3', and so on), I'd probably use that.  Add a column to each query and put in the appropriate value.  For example:

select
'Auto' as Product,
0 as Approved_Count,
0 as Approved_Amount,
0 as Third_Count,
0 as Third_Amount,
0 as Fourth_Count,
0 as Fourth_Amount
from (etc., etc.)

In the second query in each set, you'd have 'value2' as Product, and so on.

So, the "Approved" queries would produce rows like:

Auto, 0, 0, 1312, 37819512, 0, 0, 0, 0
value2, 0, 0, 799, 5675890, 0, 0, 0, 0
value3, 0, 0, 225, 1812490, 0, 0, 0, 0
value4, 0, 0, 120, 4908, 0, 0, 0, 0

The "Received" queries would produce rows like:

Auto, 3509, 81234908, 0, 0, 0, 0, 0, 0
value2, 2561, 27897970, 0, 0, 0, 0, 0, 0
value3, 663, 3450900, 0, 0, 0, 0, 0, 0
value4, 800, 12348, 0, 0, 0, 0, 0, 0

In the report you would group on the "Product" column (Auto, value2, etc.), to combine the corresponding rows from each set of queries.  For example, the "Auto" group might contain records like this:

Auto, 0, 0, 1312, 37819512, 0, 0, 0, 0     (from the "Approved" queries)
Auto, 3509, 81234908, 0, 0, 0, 0, 0, 0     (from the "Received" queries)
Auto, 0, 0, 0, 0, 123, 456789, 0, 0     (from the third procedure's queries)
Auto, 0, 0, 0, 0, 0, 0, 987, 654321     (from the fourth procedure's queries)

A group total for each column would give you the different figures for that "Product".  Use the group totals in the group footer to output a single line for each "Product".

Make any sense?  :-)

Of course without seeing the stored procedures, there is a lot of conjecture in all of this.

James
0

Author Comment

ID: 24451281
Well that is just it. I mentioned UNION ALL won't work in this case and looking at the result set example that you described I don't think that the solution matches what I am looking for. A sample of one such stored procedure is in the thread from april 28, 2009 sent by me. Each one is identical to that one save the product changes and this...the decision changes to whatever approved, decsioned, received etcetera. There are four stored procedures looking like this with UNION  that attaches them them all..the only change is the product auto, visa, signature, etcetera... and then there are four of each of those blocs with the change being the decision process. For example all four of the stored procedures joined together for one bloc dealing with approved look like the one below with the change being after every UNION the product. So you are getting a count and sum for whatever product that the procedure is focusing on.

select count(distinct NoDupes.AppID) as Received, sum((NoDupes.RequestedAmount))as Amount from(select v4.AppID, v8.RequestedAmount from [x].[dbo].CrTier as v1
inner join [x].[dbo].ApplicantCredit as v2
on v1.CrTierID = V2.CrTierID inner join [x].[dbo].ApplApplicants as v3 on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8
on v4.ApplicationID = v8.ApplicationID
WHERE v5.ProductDescription like '%Auto'
and v7.DecisionLabel  not like '%approved'
and v3.BorrowerTypeID = '1'
and v4.ApplDate between '2009-04-01 05:00:00.000' and '2009-04-17 05:00:00.000'
and v8.ActionTakenDateTime <=(select min(v8.ActionTakenDateTime) as Tier from [x].[dbo].ApplicationHistory as v8 where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes
0

LVL 35

Expert Comment

ID: 24456186
I was looking at the procedure from 04/28 when I wrote that.

I still don't see why my idea won't work.  As I said, I can't say for sure without seeing the procedures, but, based on what I've seen, it seems reasonable.  Yes, you're combining all of the results into one result set, which is why you create separate count and sum columns for each "decision process".

Using the query that you just posted as a base, the first two "Received" queries would be:

select
'Auto' as Product,
0 as Approved_Count,
0 as Approved_Amount,
0 as Third_Count,
0 as Third_Amount,
0 as Fourth_Count,
0 as Fourth_Amount
from(select v4.AppID, v8.RequestedAmount from [x].[dbo].CrTier as v1
inner join [x].[dbo].ApplicantCredit as v2
on v1.CrTierID = V2.CrTierID inner join [x].[dbo].ApplApplicants as v3 on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8
on v4.ApplicationID = v8.ApplicationID
WHERE v5.ProductDescription like '%Auto'
and v7.DecisionLabel  not like '%approved'
and v3.BorrowerTypeID = '1'
and v4.ApplDate between '2009-04-01 05:00:00.000' and '2009-04-17 05:00:00.000'
and v8.ActionTakenDateTime <=(select min(v8.ActionTakenDateTime) as Tier from [x].[dbo].ApplicationHistory as v8 where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes

UNION ALL

select
'Visa' as Product,
0 as Approved_Count,
0 as Approved_Amount,
0 as Third_Count,
0 as Third_Amount,
0 as Fourth_Count,
0 as Fourth_Amount
from(select v4.AppID, v8.RequestedAmount from [x].[dbo].CrTier as v1
inner join [x].[dbo].ApplicantCredit as v2
on v1.CrTierID = V2.CrTierID inner join [x].[dbo].ApplApplicants as v3 on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8
on v4.ApplicationID = v8.ApplicationID
WHERE v5.ProductDescription like '%Visa'
and v7.DecisionLabel  not like '%approved'
and v3.BorrowerTypeID = '1'
and v4.ApplDate between '2009-04-01 05:00:00.000' and '2009-04-17 05:00:00.000'
and v8.ActionTakenDateTime <=(select min(v8.ActionTakenDateTime) as Tier from [x].[dbo].ApplicationHistory as v8 where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes

Those would produce two rows like:

Auto, 3509, 81234908, 0, 0, 0, 0, 0, 0
Visa, 2561, 27897970, 0, 0, 0, 0, 0, 0

If the same procedure included two similar queries for "Approved", they would produce two rows like:

Auto, 0, 0, 1312, 37819512, 0, 0, 0, 0
Visa, 0, 0, 799, 5675890, 0, 0, 0, 0

In the report, you'd group on "Product" (the first column).  In the "Auto" group, you'd get:

Auto, 3509, 81234908, 0, 0, 0, 0, 0, 0
Auto, 0, 0, 1312, 37819512, 0, 0, 0, 0

The group totals for columns 2 - 5 (Received_Count, Received_Amount, Approved_Count and Approved_Amount) would give you the combined totals from the "Received" and "Approved" queries for the "Auto" product (3509, 81234908, 1312 and 37819512).  Then you could use those in the "Product" group header or footer to do your calculations, etc.  For example, the "Auto" group total for Received_Count / the group total for Approved_Count would be 1312 / 3509.

On a different, but related, subject, if the difference between the 4 queries in your current stored procedures is something simple like what value you look for in v5.ProductDescription, it doesn't seem like you need 4 different queries.  I'd have one query look for any of the values and create a column to group by, based on the value (so you get a count and sum for each target value - Auto, Visa, etc.).

This is a separate issue from combining the stored procedures.  I just don't see the need for 4 different queries in each stored procedure, if the only difference is what they look for in v5.ProductDescription.  But this may also fall at least partly in the "personal preferences" category.  Some people might like having the separate queries.  I'd find one easier to deal with (eg. if you had to make changes).

James
0

LVL 35

Expert Comment

ID: 24456223
Re: My last comments about combining the 4 queries in one of your current stored procedures into one query (as opposed to combining the procedures)

One thing that combining the queries probably would change is the row order.  With separate queries for Auto, Visa, etc., you know that you'll get the row from the first query, then the row from the second query, and so on.  If you look for all of those values in one query, you don't know what order the rows will be in.

But, if you need the rows in a specific order, you could handle that with an ORDER BY, so it shouldn't really be a problem.  It's just an issue that I didn't think of until after I posted that.

James
0

Author Comment

ID: 24474566
Ultimately, i thought that if I had a temp table I could have merely inserted the results into it and had the report pick up the table itself and grab the data which is monthly by looking at date paramaters in the crystal report. But I am not allowed to do that. And then I thought maybe using DTS  and with the formulas harcoded in excel (percentages and totals) and this would be run monthly that would be no problem either however that option is not accessible to me either.

I have attached a sample excel file showing you what I need to achieve .I need the stored procedure to generate results exactly how this looks in the excel file.
The count is the first column Received, Decisioned, Approved and the sums are the \$ Received, \$ Decisioned etcetera and the percentages being generated on the SQl end I haven't figured that out yet or on the crystal end for that matter. Anyhow I developed stored procedures as you see spiltting the counts and sums for each decision on down. The problem was to line them up horizontally on the report in the format similar to the excel sheet not without either finding a way to combine the logic or using subreports which became way too complex for me.
sample-expert.xls
0

LVL 35

Expert Comment

ID: 24488954
I still think combining the stored procedures is the way to go, but without seeing the procedures, it's all just guesswork.  FWIW, I did look at your spreadsheet, but I don't really see anything in it that I didn't already know.  The gist of it seems to still be that you want to combine the output from different stored procedures on the same lines on the report, and combining the procedures still seems like the best way to do that.

However, putting that aside for the moment and going back to the subreport idea:

There was a basic problem because you wanted the output from the "Received" subreport to be after the "Approved" lines on the report, but you also wanted to use figures from the "Received" subreport on those "Approved" lines, which means that the "Received" subreport would have to be executed _before_ the "Approved" lines.

Perhaps the simplest way around that is to use two "Received" subreports, one that sets the variables and one that actually produces the "Received" lines.  The first "Received" subreport would be placed before the "Approved" lines and would set the variables, but produce no output.  The second "Received" subreport would produce the output and it would be placed after the "Approved" lines.

Using two subreports is not very efficient, but if the "Received" subreport is not reading much data, then reading that data twice probably isn't really a problem.  Of course, if, OTOH, the "Received" subreport would be reading a lot of data, then reading all that data twice would probably not be much of a solution.

You'll have to decide if you think using two subreports like that would be practical.

James
0

Author Comment

ID: 24493966
Ok. These are the stored procedures.
select count(distinct NoDupes.AppID) as Received, sum(( NoDupes.RequestedAmount))as Amount,'Total Auto' as LoanType from(select v4.AppID, v8.RequestedAmount from [x].[dbo].CrTier as v1 with(NOLOCK)
inner join [x].[dbo].ApplicantCredit as v2 with(NOLOCK)
on v1.CreditTierID = V2.CreditTierID inner join [x].[dbo].ApplApplicants as v3 with(NOLOCK)on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4 with(NOLOCK)
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5 with(NOLOCK)
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 with(NOLOCK)on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8 with(NOLOCK)
on v4.ApplicationID = v8.ApplicationID
WHERE v5.ProductDescription like '%Auto'
and v3.BorrowerTypeID = '1'
and v4.ApplDate between '2009-02-01 05:00:00.000' and '2009-02-28 05:00:00.000'
and v8.ActionTakenDateTime <=(select min(v8.ActionTakenDateTime) as Tier from [x].[dbo].ApplicationHistory as v8 WITH(NOLOCK) where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes
union
select count(distinct NoDupes.AppID) as Received, sum(( NoDupes.RequestedAmount))as Amount, 'Total Unsecured' as LoanType from(select v4.AppID, v8.RequestedAmount from [x].[dbo].ApplicantCredit as v2 with(NOLOCK)
inner join [x].[dbo].ApplApplicants as v3 with(NOLOCK)on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4 with(NOLOCK)
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5 with(NOLOCK)
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 with(NOLOCK)on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8 with(NOLOCK)
on v4.ApplicationID = v8.ApplicationID
WHERE v5.ProductDescription like 'Signature'
and v3.BorrowerTypeID = '1'
and v4.ApplDate between '2009-02-01 05:00:00.000' and '2009-02-28 05:00:00.000'
and v8.ActionTakenDateTime <=(select min(v8.ActionTakenDateTime) as Tier from [x].[dbo].ApplicationHistory as v8 where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes
union
select count(distinct NoDupes.AppID) as Received, sum(( NoDupes.RequestedAmount))as Amount, 'Total Visa' as LoanType from(select v4.AppID, v8.RequestedAmount from [x].[dbo].ApplicantCredit as v2 with(NOLOCK)
inner join [x].[dbo].ApplApplicants as v3 with(NOLOCK) on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4 with(NOLOCK)
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5 with(NOLOCK)
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 with(NOLOCK)on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8 with(NOLOCK)
on v4.ApplicationID = v8.ApplicationID
WHERE v5.ProductDescription like 'Visa%'
and v3.BorrowerTypeID = '1'
and v4.ApplDate between '2009-02-01 05:00:00.000' and '2009-02-28 05:00:00.000'
and v8.ActionTakenDateTime <=(select min(v8.ActionTakenDateTime) as Tier from [x].[dbo].ApplicationHistory as v8 where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes
union
select count(distinct NoDupes.AppID) as Received,  sum((NoDupes.RequestedAmount))as Amount,'Total OverDraft' as LoanType from(select v4.AppID, v8.RequestedAmount from  [x].[dbo].ApplicantCredit as v2 with(NOLOCK)
inner join [x].[dbo].ApplApplicants as v3 with(NOLOCK) on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4 with(NOLOCK)
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5 with(NOLOCK)
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 with(NOLOCK)on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8 with(NOLOCK)
on v4.ApplicationID = v8.ApplicationID
WHERE v5.ProductDescription like '%Overdraft%'
and v3.BorrowerTypeID = '1'
and v4.ApplDate between '2009-02-01 05:00:00.000' and '2009-02-28 05:00:00.000'
and v8.ActionTakenDateTime <=(select min(v8.ActionTakenDateTime) as Tier from [x].[dbo].ApplicationHistory as v8 with(NOLOCK) where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes

Decisioned-----

select count(distinct NoDupes.AppID) as Decisioned, sum((NoDupes.RequestedAmount))as Amount,'Total Auto' as LoanType from(select v4.AppID, v8.RequestedAmount from [x].[dbo].CrTier as v1 WITH(NOLOCK)
inner join [x].[dbo].ApplicantCredit as v2 with(NOLOCK)
on v1.CreditTierID = V2.CreditTierID inner join [x].[dbo].ApplApplicants as v3 with(NOLOCK) on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4 with(NOLOCK)
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5 with(NOLOCK)
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 WITH(NOLOCK)on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8 WITH(NOLOCK)
on v4.ApplicationID = v8.ApplicationID
WHERE v5.ProductDescription like '%Auto'
and v3.BorrowerTypeID = '1'
and v7.DecisionLabel not like '%pre-approved'
and v4.DecisionDate between '2009-02-01 05:00:00.000' and '2009-02-28 05:00:00.000'
and v8.ActionTakenDateTime <=(select min(v8.ActionTakenDateTime) as Tier from [x].[dbo].ApplicationHistory as v8 where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes
union
select count(distinct NoDupes.AppID) as Decisioned, sum((NoDupes.RequestedAmount))as Amount, 'Total Unsecured' as LoanType from(select v4.AppID, v8.RequestedAmount from [x].[dbo].ApplicantCredit as v2 WITH(NOLOCK)
inner join [x].[dbo].ApplApplicants as v3 WITH(NOLOCK) on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4 WITH(NOLOCK)
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5 WITH(NOLOCK)
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 WITH(NOLOCK) on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8 WITH(NOLOCK)
on v4.ApplicationID = v8.ApplicationID
WHERE v5.ProductDescription like 'Signature'
and v7.DecisionLabel not like '%pre-approved'
and v3.BorrowerTypeID = '1'
and v4.DecisionDate between '2009-02-01 05:00:00.000' and '2009-02-28 05:00:00.000'
and v8.ActionTakenDateTime <=(select min(v8.ActionTakenDateTime) as Tier from [x].[dbo].ApplicationHistory as v8 WITH(NOLOCK) where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes
union
select count(distinct NoDupes.AppID) as Decisioned, sum((NoDupes.RequestedAmount))as Amount,'Total Visa' as LoanType from(select v4.AppID, v8.RequestedAmount from [x].[dbo].ApplicantCredit as v2 WITH(NOLOCK)
inner join [x].[dbo].ApplApplicants as v3 WITH(NOLOCK) on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4 WITH(NOLOCK)
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5 WITH(NOLOCK)
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 WITH(NOLOCK) on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8 WITH(NOLOCK)
on v4.ApplicationID = v8.ApplicationID
WHERE v5.ProductDescription like 'Visa%'
and v7.DecisionLabel not like '%pre-approved'
and v3.BorrowerTypeID = '1'
and v4.DecisionDate between '2009-02-01 05:00:00.000' and '2009-02-28 05:00:00.000'
and v8.ActionTakenDateTime <=(select min(v8.ActionTakenDateTime) as Tier from [x].[dbo].ApplicationHistory as v8 WITH(NOLOCK)where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes
union
select count(distinct NoDupes.AppID) as Decisioned, sum((NoDupes.RequestedAmount))as Amount, 'Total Overdraft' as LoanType from(select v4.AppID, v8.RequestedAmount from  [x].[dbo].ApplicantCredit as v2 WITH(NOLOCK)
inner join [x].[dbo].ApplApplicants as v3 WITH(NOLOCK) on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4 WITH(NOLOCK)
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5 WITH(NOLOCK)
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 WITH(NOLOCK) on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8 WITH(NOLOCK)
on v4.ApplicationID = v8.ApplicationID
WHERE v5.ProductDescription like '%Overdraft%'
and v7.DecisionLabel  not like '%pre-approved'
and v3.BorrowerTypeID = '1'
and v4.DecisionDate between '2009-02-01 05:00:00.000' and '2009-02-28 05:00:00.000'
and v8.ActionTakenDateTime <=(select min(v8.ActionTakenDateTime) as Tier from [x].[dbo].ApplicationHistory as v8 WITH(NOLOCK)where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes

--APPROVED

select count(distinct NoDupes.AppID) as Approved, sum((NoDupes.RequestedAmount))as Amount,'Total Auto' as LoanType from(select v4.AppID, v8.RequestedAmount from [x].[dbo].CrTier as v1
inner join [x].[dbo].ApplicantCredit as v2 WITH(NOLOCK)
on v1.CreditTierID = V2.CreditTierID inner join [x].[dbo].ApplApplicants as v3 WITH(NOLOCK) on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4 WITH(NOLOCK)
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5 WITH(NOLOCK)
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 WITH(NOLOCK)on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8 WITH(NOLOCK)
on v4.ApplicationID = v8.ApplicationID
WHERE v5.ProductDescription like '%Auto'
and v3.BorrowerTypeID = '1'
and v7.DecisionLabel like 'Approved'
and v4.ApplDate between '2009-02-01 05:00:00.000' and '2009-02-28 05:00:00.000'
and v8.ActionTakenDateTime <=(select min(v8.ActionTakenDateTime) as Tier from [x].[dbo].ApplicationHistory as v8 where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes
union
select count(distinct NoDupes.AppID) as Approved, sum((NoDupes.RequestedAmount))as Amount,'Total Unsecured' as LoanType from(select v4.AppID, v8.RequestedAmount from [x].[dbo].ApplicantCredit as v2 WITH(NOLOCK)
inner join [x].[dbo].ApplApplicants as v3 WITH(NOLOCK) on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4 WITH(NOLOCK)
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5 WITH(NOLOCK)
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 WITH(NOLOCK) on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8 WITH(NOLOCK)
on v4.ApplicationID = v8.ApplicationID
WHERE v5.ProductDescription like '%Signature'
and v7.DecisionLabel like 'Approved'
and v3.BorrowerTypeID = '1'
and v4.ApplDate between '2009-02-01 05:00:00.000' and '2009-02-28 05:00:00.000'
and v8.ActionTakenDateTime <=(select min(v8.ActionTakenDateTime) as Tier from [x].[dbo].ApplicationHistory as v8 where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes
union
select count(distinct NoDupes.AppID) as Approved, sum((NoDupes.RequestedAmount))as Amount,'Total Visa' as LoanType from(select v4.AppID, v8.RequestedAmount from [x].[dbo].ApplicantCredit as v2 WITH(NOLOCK)
inner join [x].[dbo].ApplApplicants as v3 WITH(NOLOCK) on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4 WITH(NOLOCK)
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5 WITH(NOLOCK)
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 WITH(NOLOCK)on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8 WITH(NOLOCK)
on v4.ApplicationID = v8.ApplicationID
WHERE v5.ProductDescription like 'Visa%'
and v7.DecisionLabel like 'Approved'
and v3.BorrowerTypeID = '1'
and v4.ApplDate between '2009-02-01 05:00:00.000' and '2009-02-28 05:00:00.000'
and v8.ActionTakenDateTime <=(select min(v8.ActionTakenDateTime) as Tier from [x].[dbo].ApplicationHistory as v8 WITH(NOLOCK) where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes
union
select count(distinct NoDupes.AppID) as Received, sum((NoDupes.RequestedAmount))as Amount,'Total Overdraft' as LoanType from(select v4.AppID, v8.RequestedAmount from  [x].[dbo].ApplicantCredit as v2 WITH(NOLOCK)
inner join [x].[dbo].ApplApplicants as v3 on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4 WITH(NOLOCK)
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5 WITH(NOLOCK)
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 WITH(NOLOCK) on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8 WITH(NOLOCK)
on v4.ApplicationID = v8.ApplicationID
WHERE v5.ProductDescription like '%Overdraft%'
and v7.DecisionLabel like 'Approved'
and v3.BorrowerTypeID = '1'
and v4.ApplDate between '2009-02-01 05:00:00.000' and '2009-02-28 05:00:00.000'
and v8.ActionTakenDateTime <=(select min(v8.ActionTakenDateTime) as Tier from [x].[dbo].ApplicationHistory as v8 WITH(NOLOCK) where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes

BOOKED---

select count(distinct NoDupes.AppID) as Booked, sum((NoDupes.RequestedAmount))as Amount,'Total Auto' as LoanType from(select v4.AppID, v8.RequestedAmount from [x].[dbo].CrTier as v1
inner join [x].[dbo].ApplicantCredit as v2
on v1.CreditTierID = V2.CreditTierID inner join [x].[dbo].ApplApplicants as v3 on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8
on v4.ApplicationID = v8.ApplicationID inner join [x].dbo.vWorkFlowStatusDesc as v9
on v4.WorkFlowStatusID = v9.WorkFlowStatusID
WHERE v5.ProductDescription like '%Auto'
and v3.BorrowerTypeID = '1'
and v9.WorkFlowStatusDesc like 'Booked'
and v4.WorkFlowStatusDate  between '2009-02-01 05:00:00.000' and '2009-02-28 05:00:00.000'
and v8.ActionTakenDateTime >=(select max(v8.ActionTakenDateTime) as Tier from [x].[dbo].ApplicationHistory as v8 where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes
union
select count(distinct NoDupes.AppID) as Booked, sum((NoDupes.RequestedAmount))as Amount,'Total Unsecured' as LoanType from(select v4.AppID, v8.RequestedAmount from [x].[dbo].ApplicantCredit as v2
inner join [x].[dbo].ApplApplicants as v3 on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8
on v4.ApplicationID = v8.ApplicationID inner join [x].dbo.vWorkFlowStatusDesc as v9
on v4.WorkFlowStatusID = v9.WorkFlowStatusID
WHERE v5.ProductDescription like 'Signature'
and v3.BorrowerTypeID = '1'
and v9.WorkFlowStatusDesc like 'Booked'
and v4.WorkFlowStatusDate  between '2009-02-01 05:00:00.000' and '2009-02-28 05:00:00.000'
and v8.ActionTakenDateTime >=(select max(v8.ActionTakenDateTime) as Tier from [x].[dbo].ApplicationHistory as v8 where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes

0

Author Comment

ID: 24494057
Now that the procedures have been presented in their entirety, how do I combine all of these as you have mentioned into something that produces the results as outlined in the spreasheet ? As you can you can see they are governed by a decision process and product otherwise they are identical in every way.

By themselves they generate besides the aliased column..two columns apiece of counts and sums. A typical crystal report would only house one stored procedure but not four such as these and that's why I went the route of the subreports. But if you are telling me that your process can help me..I would appreciate any and all suggestions in the matter.
0

LVL 35

Expert Comment

ID: 24499789
First of all, a couple of comments on the procedures:

The WHERE in the 'Total Unsecured' query in the 'Approved' procedure has "like '%Signature'" (with a "%").  The WHERE in the 'Total Unsecured' query in the other procedures has "like 'Signature'" (no "%").

I assume that they should all be the same, for consistency, if nothing else.  It may not actually affect the results.  It depends on your data.  If the target value always starts with 'Signature', then the two tests will get the same results.  But if you had something like 'Co-signer Signature', '%Signature' would match that, but 'Signature' would not.

Your 'Booked' procedure at the end only has two queries.  There is no 'Total Visa' or 'Total Overdraft' query in that procedure.  I'm assuming that that's correct, but wanted to check, just in case there was more and it was cut off.

I noticed that all of the WHERE's have hard-coded dates (ie. "between '2009-02-01 05:00:00.000' and '2009-02-28 05:00:00.000' ").  I don't know where those came from, but I assume that they will ultimately need to be replaced by parameters.

Most of the queries use  WITH(NOLOCK) on pretty much every table, but there are some exceptions.  I noticed that some queries use it on ApplicationHistory and some don't.  And the 'Booked' queries don't use NOLOCK on anything.  I don't know if any of that is a problem.  I just noticed some apparent inconsistencies and wanted to mention them.

FWIW, I think at least some of the queries from the same procedure could be merged into one query.  For example, I think the 'Total Unsecured', 'Total Visa' and 'Total OverDraft' queries for 'Received' could probably be handled by one SELECT.  It might also be possible to merge some of the queries from different procedures (eg. have one SELECT that handles both 'Decisioned' and 'Approved').  I'm not as sure about that one, but I think it might be possible.

But I decided not to worry about trying to merge the queries at this point and just concentrate on getting them all into one procedure.  If that works, you can always look at refining the queries later if you like.

Technically, what you posted are not complete stored procedures.  They're just the queries.  And what I'm posting is just a combination of those queries.  To make it a stored procedure, you'll need to add a CREATE or ALTER PROCEDURE statement and parameter declarations.

Anyway, on to the combined queries.  My combination should be in a "code" window below this.  The columns that it's supposed to produce are:

Category   ('Received', 'Decisioned', 'Approved' and 'Booked')
Decisioned_Count
Decisioned_Amount
Approved_Count
Approved_Amount
Booked_Count
Booked_Amount
LoanType   ('Total Auto', 'Total Unsecured', 'Total Visa' and 'Total OverDraft')

Technically, at this point I'm thinking that the Category column isn't really necessary (the count and amount for each category are kept in separate columns anyway), but I think it's good to have it there for reference, if nothing else.

I'd suggest creating a new stored procedure, rather than trying to change your old one (at least for the time being).  Once you've created the procedure, try executing it in a SQL query window or create a new CR report and have it use this procedure and basically just put all of the columns on the report, so that you can see what you're getting.  It should be something like the following:

'Received'    321   654321   0      0      0      0      0      0     'Total Auto'
'Received'    123   123456   0      0      0      0      0      0     'Total Unsecured'
'Received'    234   234567   0      0      0      0      0      0     'Total Visa'
'Received'    345   345678   0      0      0      0      0      0     'Total OverDraft'

'Decisioned'   0      0     456   456789   0      0      0      0     'Total Auto'
'Decisioned'   0      0     567   567890   0      0      0      0     'Total Unsecured'
'Decisioned'   0      0     678   678901   0      0      0      0     'Total Visa'
'Decisioned'   0      0     789   789012   0      0      0      0     'Total Overdraft'

'Approved'     0      0      0      0     890   890123   0      0     'Total Auto'
'Approved'     0      0      0      0     901   901234   0      0     'Total Unsecured'
'Approved'     0      0      0      0     123   123456   0      0     'Total Visa'
'Approved'     0      0      0      0     456   234567   0      0     'Total Overdraft'

'Booked'       0      0      0      0      0      0     567   345678  'Total Auto'
'Booked'       0      0      0      0      0      0     678   456789  'Total Unsecured'

For your actual report, you'd group on the LoanType column ("Total Auto", "Total Unsecured", etc.), suppress the details and put your report lines in the group header or footer.  Summaries of the columns for that group would give you the figures you need for your report.

For example, looking at the first columns in your spreadsheet example, if you want the "\$ Received", "Decisioned", "\$ Decisioned" and "Approved" for "Total Auto", in the footer (or header) for the LoanType group, you'd use:

Sum of Received_Amount for the LoanType group
Sum of Decisioned_Count for the LoanType group
Sum of Decisioned_Amount for the LoanType group
Sum of Approved_Count for the LoanType group

If it's still not making much sense, try the procedure and look at the output.  Hopefully seeing the data it produces will start to clear things up.  Have fun.  :-)

James

``````-- Received-----
0 as Decisioned_Count,
0 as Decisioned_Amount,
0 as Approved_Count,
0 as Approved_Amount,
0 as Booked_Count,
0 as Booked_Amount,
'Total Auto' as LoanType
from(select v4.AppID, v8.RequestedAmount
from [x].[dbo].CrTier as v1 with(NOLOCK)
inner join [x].[dbo].ApplicantCredit as v2 with(NOLOCK)
on v1.CreditTierID = V2.CreditTierID inner join [x].[dbo].ApplApplicants as v3 with(NOLOCK)on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4 with(NOLOCK)
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5 with(NOLOCK)
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 with(NOLOCK)on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8 with(NOLOCK)
on v4.ApplicationID = v8.ApplicationID
WHERE v5.ProductDescription like '%Auto'
and v3.BorrowerTypeID = '1'
and v4.ApplDate between '2009-02-01 05:00:00.000' and '2009-02-28 05:00:00.000'
and v8.ActionTakenDateTime <=(select min(v8.ActionTakenDateTime) as Tier
from [x].[dbo].ApplicationHistory as v8 WITH(NOLOCK)
where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes

union

0 as Decisioned_Count,
0 as Decisioned_Amount,
0 as Approved_Count,
0 as Approved_Amount,
0 as Booked_Count,
0 as Booked_Amount,
'Total Unsecured' as LoanType
from(select v4.AppID, v8.RequestedAmount
from [x].[dbo].ApplicantCredit as v2 with(NOLOCK)
inner join [x].[dbo].ApplApplicants as v3 with(NOLOCK)on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4 with(NOLOCK)
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5 with(NOLOCK)
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 with(NOLOCK)on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8 with(NOLOCK)
on v4.ApplicationID = v8.ApplicationID
WHERE v5.ProductDescription like 'Signature'
and v3.BorrowerTypeID = '1'
and v4.ApplDate between '2009-02-01 05:00:00.000' and '2009-02-28 05:00:00.000'
and v8.ActionTakenDateTime <=(select min(v8.ActionTakenDateTime) as Tier
from [x].[dbo].ApplicationHistory as v8 where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes

union

0 as Decisioned_Count,
0 as Decisioned_Amount,
0 as Approved_Count,
0 as Approved_Amount,
0 as Booked_Count,
0 as Booked_Amount,
'Total Visa' as LoanType
from(select v4.AppID, v8.RequestedAmount
from [x].[dbo].ApplicantCredit as v2 with(NOLOCK)
inner join [x].[dbo].ApplApplicants as v3 with(NOLOCK) on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4 with(NOLOCK)
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5 with(NOLOCK)
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 with(NOLOCK)on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8 with(NOLOCK)
on v4.ApplicationID = v8.ApplicationID
WHERE v5.ProductDescription like 'Visa%'
and v3.BorrowerTypeID = '1'
and v4.ApplDate between '2009-02-01 05:00:00.000' and '2009-02-28 05:00:00.000'
and v8.ActionTakenDateTime <=(select min(v8.ActionTakenDateTime) as Tier
from [x].[dbo].ApplicationHistory as v8 where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes

union

0 as Decisioned_Count,
0 as Decisioned_Amount,
0 as Approved_Count,
0 as Approved_Amount,
0 as Booked_Count,
0 as Booked_Amount,
'Total OverDraft' as LoanType
from(select v4.AppID, v8.RequestedAmount
from  [x].[dbo].ApplicantCredit as v2 with(NOLOCK)
inner join [x].[dbo].ApplApplicants as v3 with(NOLOCK) on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4 with(NOLOCK)
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5 with(NOLOCK)
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 with(NOLOCK)on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8 with(NOLOCK)
on v4.ApplicationID = v8.ApplicationID
WHERE v5.ProductDescription like '%Overdraft%'
and v3.BorrowerTypeID = '1'
and v4.ApplDate between '2009-02-01 05:00:00.000' and '2009-02-28 05:00:00.000'
and v8.ActionTakenDateTime <=(select min(v8.ActionTakenDateTime) as Tier
from [x].[dbo].ApplicationHistory as v8 with(NOLOCK) where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes

UNION

-- Decisioned-----

select 'Decisioned' as Category,
count(distinct NoDupes.AppID) as Decisioned_Count,
sum((NoDupes.RequestedAmount)) as Decisioned_Amount,
0 as Approved_Count,
0 as Approved_Amount,
0 as Booked_Count,
0 as Booked_Amount,
'Total Auto' as LoanType
from(select v4.AppID, v8.RequestedAmount
from [x].[dbo].CrTier as v1 WITH(NOLOCK)
inner join [x].[dbo].ApplicantCredit as v2 with(NOLOCK)
on v1.CreditTierID = V2.CreditTierID inner join [x].[dbo].ApplApplicants as v3 with(NOLOCK) on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4 with(NOLOCK)
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5 with(NOLOCK)
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 WITH(NOLOCK)on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8 WITH(NOLOCK)
on v4.ApplicationID = v8.ApplicationID
WHERE v5.ProductDescription like '%Auto'
and v3.BorrowerTypeID = '1'
and v7.DecisionLabel not like '%pre-approved'
and v4.DecisionDate between '2009-02-01 05:00:00.000' and '2009-02-28 05:00:00.000'
and v8.ActionTakenDateTime <=(select min(v8.ActionTakenDateTime) as Tier
from [x].[dbo].ApplicationHistory as v8 where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes

union

select 'Decisioned' as Category,
count(distinct NoDupes.AppID) as Decisioned_Count,
sum((NoDupes.RequestedAmount)) as Decisioned_Amount,
0 as Approved_Count,
0 as Approved_Amount,
0 as Booked_Count,
0 as Booked_Amount,
'Total Unsecured' as LoanType
from(select v4.AppID, v8.RequestedAmount
from [x].[dbo].ApplicantCredit as v2 WITH(NOLOCK)
inner join [x].[dbo].ApplApplicants as v3 WITH(NOLOCK) on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4 WITH(NOLOCK)
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5 WITH(NOLOCK)
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 WITH(NOLOCK) on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8 WITH(NOLOCK)
on v4.ApplicationID = v8.ApplicationID
WHERE v5.ProductDescription like 'Signature'
and v7.DecisionLabel not like '%pre-approved'
and v3.BorrowerTypeID = '1'
and v4.DecisionDate between '2009-02-01 05:00:00.000' and '2009-02-28 05:00:00.000'
and v8.ActionTakenDateTime <=(select min(v8.ActionTakenDateTime) as Tier
from [x].[dbo].ApplicationHistory as v8 WITH(NOLOCK) where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes

union

select 'Decisioned' as Category,
count(distinct NoDupes.AppID) as Decisioned_Count,
sum((NoDupes.RequestedAmount)) as Decisioned_Amount,
0 as Approved_Count,
0 as Approved_Amount,
0 as Booked_Count,
0 as Booked_Amount,
'Total Visa' as LoanType
from(select v4.AppID, v8.RequestedAmount
from [x].[dbo].ApplicantCredit as v2 WITH(NOLOCK)
inner join [x].[dbo].ApplApplicants as v3 WITH(NOLOCK) on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4 WITH(NOLOCK)
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5 WITH(NOLOCK)
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 WITH(NOLOCK) on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8 WITH(NOLOCK)
on v4.ApplicationID = v8.ApplicationID
WHERE v5.ProductDescription like 'Visa%'
and v7.DecisionLabel not like '%pre-approved'
and v3.BorrowerTypeID = '1'
and v4.DecisionDate between '2009-02-01 05:00:00.000' and '2009-02-28 05:00:00.000'
and v8.ActionTakenDateTime <=(select min(v8.ActionTakenDateTime) as Tier
from [x].[dbo].ApplicationHistory as v8 WITH(NOLOCK)where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes

union

select 'Decisioned' as Category,
count(distinct NoDupes.AppID) as Decisioned_Count,
sum((NoDupes.RequestedAmount)) as Decisioned_Amount,
0 as Approved_Count,
0 as Approved_Amount,
0 as Booked_Count,
0 as Booked_Amount,
'Total Overdraft' as LoanType
from(select v4.AppID, v8.RequestedAmount
from  [x].[dbo].ApplicantCredit as v2 WITH(NOLOCK)
inner join [x].[dbo].ApplApplicants as v3 WITH(NOLOCK) on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4 WITH(NOLOCK)
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5 WITH(NOLOCK)
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 WITH(NOLOCK) on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8 WITH(NOLOCK)
on v4.ApplicationID = v8.ApplicationID
WHERE v5.ProductDescription like '%Overdraft%'
and v7.DecisionLabel  not like '%pre-approved'
and v3.BorrowerTypeID = '1'
and v4.DecisionDate between '2009-02-01 05:00:00.000' and '2009-02-28 05:00:00.000'
and v8.ActionTakenDateTime <=(select min(v8.ActionTakenDateTime) as Tier
from [x].[dbo].ApplicationHistory as v8 WITH(NOLOCK)where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes

UNION

--APPROVED

select 'Approved' as Category,
0 as Decisioned_Count,
0 as Decisioned_Amount,
count(distinct NoDupes.AppID) as Approved_Count,
sum((NoDupes.RequestedAmount)) as Approved_Amount,
0 as Booked_Count,
0 as Booked_Amount,
'Total Auto' as LoanType
from(select v4.AppID, v8.RequestedAmount
from [x].[dbo].CrTier as v1
inner join [x].[dbo].ApplicantCredit as v2 WITH(NOLOCK)
on v1.CreditTierID = V2.CreditTierID inner join [x].[dbo].ApplApplicants as v3 WITH(NOLOCK) on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4 WITH(NOLOCK)
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5 WITH(NOLOCK)
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 WITH(NOLOCK)on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8 WITH(NOLOCK)
on v4.ApplicationID = v8.ApplicationID
WHERE v5.ProductDescription like '%Auto'
and v3.BorrowerTypeID = '1'
and v7.DecisionLabel like 'Approved'
and v4.ApplDate between '2009-02-01 05:00:00.000' and '2009-02-28 05:00:00.000'
and v8.ActionTakenDateTime <=(select min(v8.ActionTakenDateTime) as Tier
from [x].[dbo].ApplicationHistory as v8 where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes

union

select 'Approved' as Category,
0 as Decisioned_Count,
0 as Decisioned_Amount,
count(distinct NoDupes.AppID) as Approved_Count,
sum((NoDupes.RequestedAmount)) as Approved_Amount,
0 as Booked_Count,
0 as Booked_Amount,
'Total Unsecured' as LoanType
from(select v4.AppID, v8.RequestedAmount
from [x].[dbo].ApplicantCredit as v2 WITH(NOLOCK)
inner join [x].[dbo].ApplApplicants as v3 WITH(NOLOCK) on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4 WITH(NOLOCK)
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5 WITH(NOLOCK)
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 WITH(NOLOCK) on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8 WITH(NOLOCK)
on v4.ApplicationID = v8.ApplicationID
WHERE v5.ProductDescription like '%Signature'
and v7.DecisionLabel like 'Approved'
and v3.BorrowerTypeID = '1'
and v4.ApplDate between '2009-02-01 05:00:00.000' and '2009-02-28 05:00:00.000'
and v8.ActionTakenDateTime <=(select min(v8.ActionTakenDateTime) as Tier
from [x].[dbo].ApplicationHistory as v8 where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes

union

select 'Approved' as Category,
0 as Decisioned_Count,
0 as Decisioned_Amount,
count(distinct NoDupes.AppID) as Approved_Count,
sum((NoDupes.RequestedAmount)) as Approved_Amount,
0 as Booked_Count,
0 as Booked_Amount,
'Total Visa' as LoanType
from(select v4.AppID, v8.RequestedAmount
from [x].[dbo].ApplicantCredit as v2 WITH(NOLOCK)
inner join [x].[dbo].ApplApplicants as v3 WITH(NOLOCK) on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4 WITH(NOLOCK)
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5 WITH(NOLOCK)
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 WITH(NOLOCK)on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8 WITH(NOLOCK)
on v4.ApplicationID = v8.ApplicationID
WHERE v5.ProductDescription like 'Visa%'
and v7.DecisionLabel like 'Approved'
and v3.BorrowerTypeID = '1'
and v4.ApplDate between '2009-02-01 05:00:00.000' and '2009-02-28 05:00:00.000'
and v8.ActionTakenDateTime <=(select min(v8.ActionTakenDateTime) as Tier
from [x].[dbo].ApplicationHistory as v8 WITH(NOLOCK) where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes

union

select 'Approved' as Category,
0 as Decisioned_Count,
0 as Decisioned_Amount,
count(distinct NoDupes.AppID) as Approved_Count,
sum((NoDupes.RequestedAmount)) as Approved_Amount,
0 as Booked_Count,
0 as Booked_Amount,
'Total Overdraft' as LoanType
from(select v4.AppID, v8.RequestedAmount
from  [x].[dbo].ApplicantCredit as v2 WITH(NOLOCK)
inner join [x].[dbo].ApplApplicants as v3 on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4 WITH(NOLOCK)
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5 WITH(NOLOCK)
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 WITH(NOLOCK) on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8 WITH(NOLOCK)
on v4.ApplicationID = v8.ApplicationID
WHERE v5.ProductDescription like '%Overdraft%'
and v7.DecisionLabel like 'Approved'
and v3.BorrowerTypeID = '1'
and v4.ApplDate between '2009-02-01 05:00:00.000' and '2009-02-28 05:00:00.000'
and v8.ActionTakenDateTime <=(select min(v8.ActionTakenDateTime) as Tier
from [x].[dbo].ApplicationHistory as v8 WITH(NOLOCK) where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes

UNION

-- BOOKED---

select 'Booked' as Category,
0 as Decisioned_Count,
0 as Decisioned_Amount,
0 as Approved_Count,
0 as Approved_Amount,
count(distinct NoDupes.AppID) as Booked_Count,
sum((NoDupes.RequestedAmount)) as Booked_Amount,
'Total Auto' as LoanType
from(select v4.AppID, v8.RequestedAmount
from [x].[dbo].CrTier as v1
inner join [x].[dbo].ApplicantCredit as v2
on v1.CreditTierID = V2.CreditTierID inner join [x].[dbo].ApplApplicants as v3 on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8
on v4.ApplicationID = v8.ApplicationID inner join [x].dbo.vWorkFlowStatusDesc as v9
on v4.WorkFlowStatusID = v9.WorkFlowStatusID
WHERE v5.ProductDescription like '%Auto'
and v3.BorrowerTypeID = '1'
and v9.WorkFlowStatusDesc like 'Booked'
and v4.WorkFlowStatusDate  between '2009-02-01 05:00:00.000' and '2009-02-28 05:00:00.000'
and v8.ActionTakenDateTime >=(select max(v8.ActionTakenDateTime) as Tier
from [x].[dbo].ApplicationHistory as v8 where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes

union

select 'Booked' as Category,
0 as Decisioned_Count,
0 as Decisioned_Amount,
0 as Approved_Count,
0 as Approved_Amount,
count(distinct NoDupes.AppID) as Booked_Count,
sum((NoDupes.RequestedAmount)) as Booked_Amount,
'Total Unsecured' as LoanType
from(select v4.AppID, v8.RequestedAmount
from [x].[dbo].ApplicantCredit as v2
inner join [x].[dbo].ApplApplicants as v3 on
v2.ApplicantID = v3.ApplicantID inner join [x].[dbo].Application as v4
on v3.ApplicationID = v4.ApplicationID inner join [x].[dbo].OrgProducts as v5
on v4.ProductID = v5.ProductID inner join [x].[dbo].CustomDecision AS v7 on
v4.DecisionID = v7.CustomDecisionID inner join [x].[dbo].ApplicationHistory as v8
on v4.ApplicationID = v8.ApplicationID inner join [x].dbo.vWorkFlowStatusDesc as v9
on v4.WorkFlowStatusID = v9.WorkFlowStatusID
WHERE v5.ProductDescription like 'Signature'
and v3.BorrowerTypeID = '1'
and v9.WorkFlowStatusDesc like 'Booked'
and v4.WorkFlowStatusDate  between '2009-02-01 05:00:00.000' and '2009-02-28 05:00:00.000'
and v8.ActionTakenDateTime >=(select max(v8.ActionTakenDateTime) as Tier
from [x].[dbo].ApplicationHistory as v8 where v4.ApplicationID = v8.ApplicationID
and v8.RequestedAmount <> '')
Group By v4.AppId, v8.RequestedAmount) As NoDupes
``````
0

Author Comment

ID: 24502642
There are a few problems with this that I see already. I  was about to say how would I differentiate Received Auto from Received Visa, but I see your point. =) But  this begs the question how would I get the exact percentages now in crystal  reports for  % approved which is equal to  count values of approved/ count  values of decisioned * 100 without duplication on the report itself. Or % booked which is count value of booked/ count value of approved * 100 ?
0

Author Comment

ID: 24503549
I understand that "crosstab formatted" crystal reports have indexed cells that can be used to specifically pull numerators and denominator value for percentages. How would I go about that exactly ? Everytime in the past I have attempted to insert a cross tab into a blank report it alters the apperance of the output in  a  bizarre pyramid of columns and rows that I did not create or specify. I will attempt to filter on the zeroes from the report from the output on the report leaving the data in the format similar to the spreadsheet.
0

LVL 35

Expert Comment

ID: 24507032
The details would be suppressed.  The report would be grouped on LoanType and your report lines would be in the group footer, so you'd get one line for each type (Auto, Visa, etc.).

% approved

Create a formula like the following and put it in the LoanType group footer:

if Sum ({proc_name.Decisioned_Count}, {proc_name.LoanType}) <> 0 then
Sum ({proc_name.Approved_Count}, {proc_name.LoanType}) /
Sum ({proc_name.Decisioned_Count}, {proc_name.LoanType}) * 100

The "if" is to make sure you don't get a "divide by zero" error.  If the total Decisioned_Count for a LoanType will never be 0, you can leave off the "if".

% booked

Very much the same thing.  Create a formula like the following and put it in the LoanType group footer:

if Sum ({proc_name.Approved_Count}, {proc_name.LoanType}) <> 0 then
Sum ({proc_name.Booked_Count}, {proc_name.LoanType}) /
Sum ({proc_name.Approved_Count}, {proc_name.LoanType}) * 100

Obviously, in both formulas you'd replace proc_name with your stored procedure name.

> I will attempt to filter on the zeroes from the report from the output
> on the report leaving the data in the format similar to the spreadsheet.

If you're talking about trying to "suppress" the count and amount columns in each record that are 0, I don't think you need to do anything like that.  See the above.  The idea is to not show the individual records at all and instead add the records together, so you end up with a figure for every column (the group totals).

As for the cross-tab idea, that might be possible.  I'm not sure at this point.  Cross-tabs are very good at doing certain things, but they're also fairly limited.  If you need to do anything beyond fairly straightforward summaries, you can start to run into walls pretty quickly.  We can look at the cross-tab idea, but at this point I don't think it's necessary.

James
0

Author Comment

ID: 24520191
I tried the code you outliend and it worked. The question becomes can I suppress the zero counts and the 0.00'S on the report the have the numerical data line up next to each other perfectly like in the spreadsheet or not ? That is a minor technicality however but one I thought I would ask nonetheless.
0

LVL 101

Expert Comment

ID: 24522849
If they are numeric fields you can use the formatting to suppress 0s

Right click the field
Click FORMAT FIELD
Click the NUMBER TAB
Click CUSTOMIZE
Set the SUPPRESS os option

mlmcc
0

LVL 35

Expert Comment

ID: 24523437
I'm glad it worked.  It made sense to me, but you never know.  :-)

I'm not sure what you're trying to suppress.  The records from the procedure contain lots of 0's, because there are 8 different number columns and each query only produces 2 of those columns, leaving the other 6 columns as 0.  You "suppress" those 0's by suppressing the details and producing your output in the group footer, where every column that had a non-zero value in the group will show that value, by using a group total for each column.

If you're talking about suppressing 0's in the group footer totals, what, exactly, are you trying to do?  Are you trying to suppress specific fields/columns that are 0, or whole lines that are 0, or ... ?

If you're not sure how to answer my questions, the simplest thing might be to post a message showing what you're seeing (change the values if you like) and what you'd like to see.

James
0

Author Comment

ID: 24526526
James,

The formula here
if Sum ({proc_name.Decisioned_Count}, {proc_name.LoanType}) <> 0 then
Sum ({proc_name.Approved_Count}, {proc_name.LoanType}) /
Sum ({proc_name.Decisioned_Count}, {proc_name.LoanType}) * 100

gives me an error it reports that the result of formulas must be a boolean.
0

LVL 101

Expert Comment

ID: 24532237
Where did you put the formula?

mlmcc
0

LVL 35

Expert Comment

ID: 24532511
That's a formula to calculate a %, so you'd be creating a new formula (in the "Field Explorer", right-click on "Formula Fields" and select New).  If you entered what you posted (and nothing else) in a formula and CR said that the result had to be a boolean, you must be putting it somewhere else, like a field/section suppression formula.

James
0

Author Comment

ID: 24546768
correct. It seems to be working now. I will be testing it to confirm everything works appropriately however and let you guys know my results.
0

Author Comment

ID: 24551223
Question: I need to generate percentages for each field in the column received as the numerator and the sum field at the bottom being the denominator now I figured I  could use a for loop for this however  I only seem to get one number which translates to one pass in the loop and it exits:
The formulas above I used were active fields whereas the denominator in this one will be a static total  value from the report
numbervar counter;
numbervar p;
for counter := 1 to count({sp_vr_Auto_reports;1.Category}) do
(
if Sum ({sp_v__Tier_Auto_reports;1.Total_Received_Count}, {sp_v__Tier_Auto_reports;1.Category}) <> 0 then
);
counter := counter + 1;
p

I should get a column output that looks like
21%
9%
10%
34%
26%
and the last amount equals 100% at the bottom when summed
0

LVL 35

Expert Comment

ID: 24553313
OK, there are a few problems with what you tried.

You are trying to execute the loop once for each Category.  You can do that, in the sense that if there are 4 values for the field Category, DistinctCount () will return 4 and the loop will be executed 4 times.  But every time you execute Sum ({sp_v__Tier_Auto_reports;1.Total_Received_Count}, {sp_v__Tier_Auto_reports;1.Category}), you get the total for the current value in Category.  You're not looping through the totals for different Category's.  So, you're going to get the same results every time through the loop.

count({sp_vr_Auto_reports;1.Category}) should actually return a record count for the whole report.  DistinctCount would give you a count of the different values in Category.

In your Sum () and Count () functions you referred to Category, but I _think_ you're actually interested in the total for each LoanType (Visa, Auto, etc.), not Category (Received, Decisioned, etc.).

The formula you posted should actually give you an endless loop.  You increment counter outside of the for loop, so unless count({sp_vr_Auto_reports;1.Category}) returns 1 (and I don't think it should), counter should never reach that value and that should be an endless loop.

p is just a simple numeric variable.  You put a value (the %) in p every time through the loop, which replaces whatever was already in p.  Even if the rest of the formula worked, when you got to the end of the loop, p would just have the value from the last iteration.

If I'm following what you're after (and I may not be), try a formula like the following:

if Sum ({sp_v_Tier_Auto_reports;1.Total_Received_Count}) <> 0 then

Put that in the LoanType group footer.  It divides the received count for each LoanType (Visa, Auto, etc.) by the grand total received count.  The % operator is just a CR shortcut.  (A % B) is the equivalent of (A / B * 100).  You can use the (A/ B * 100) form if you prefer.  As far as I'm concerned, that's just a matter of personal preference.

James
0

Author Comment

ID: 24556281
The problem is when I place this formula field in the group footer as you mentioned I get zeroes in each grouping and then at the bottom level I get 100 percent.
It doesn't make sense considering there are number values populating received and the grand total received. it makes me think something is wrong with the formula somehow like for example looking at the formula the numerator and the denominator look exactlyy the same. If I looked at it in crystal they use sum of..to calculate the grand total.
0

Author Comment

ID: 24557643
I tried this again I got the same result which i wasn't looking for.... Ideally I would not want the resulting column posted in the group footer where it will split them up one per row. I wanted to present the column of results below in the report footer since I ran out of room even shrinking the font size down based on the amount of information condensed into the report itself.
0

Author Comment

ID: 24557845
so if I want  this to appear in the footer:

7%                  88%
9%                  72%
19%                51%
21%                40%
15%                28%
28%                13%
-----------------------------

99%              49%

the first total  past the line exacted by summing the percentages whereas the seccond summation is an a /b  * 100 resultant value based on the approved total/ received total
0

LVL 35

Expert Comment

ID: 24561083
I know you said that you can't post the report, but being able to see your formulas and where they are in the report would sure make things easier.

> The problem is when I place this formula field in the group footer
> as you mentioned I get zeroes in each grouping and then at the
> bottom level I get 100 percent.

You put it in the LoanType group footer?

If you change the formula to just the following, do you get the total for each LoanType?

> it makes me think something is wrong with the formula somehow
> like for example looking at the formula the numerator and the
> denominator look exactlyy the same.

They're not the same.  What I posted was:

if Sum ({sp_v_Tier_Auto_reports;1.Total_Received_Count}) <> 0 then

Sum ({sp_v__Tier_Auto_reports;1.Total_Received_Count}, {sp_v__Tier_Auto_reports;1.LoanType}) is the total of Total_Received_Count for the LoanType group (ie. for each LoanType).

But that formula would only work correctly somewhere in the LoanType group (eg. the group footer).  That one Sum gives you the total for the current LoanType, so the formula has to be evaluated for each LoanType.  If that formula was in the report footer, the formula would only be evaluated at the end of the report, and that Sum would only return the total for the final LoanType group on the report.

If you want to show the %'s in the report footer, you need to save the required values in variables, which will then be used in the report footer to do the calculations.  I'm not sure exactly which values you need, but we'll start with the received count for each LoanType.

Create a formula like the following and put it in the report header.  This formula declares the variable.  We're going to store the counts in an array with one element for each LoanType.  You have 4 types (Correct?), so there are 4 elements in the array.  The "" at the end is just so the formula produces no output on the report.  You could also suppress the field or the section that the field is in.

""

Create a formula like the following and put it in the LoanType group footer (or header).  This formula saves the total received count for each LoanType in the array.  If you've changed the values that the stored procedure puts in LoanType, change the formula to look for the new values.

Local NumberVar i;

Select {sp_v__Tier_Auto_reports;1.LoanType}
Case "Total Auto" :
i := 1;
Case "Total Unsecured" :
i := 2;
Case "Total Visa" :
i := 3;
Case "Total Overdraft" :
i := 4;

if i <> 0 then

""

To see if the above formula worked, create a formula like the following and put it in the report footer.  It should output the total received count for a LoanType.  Select different totals by replacing the 1 with 2, 3 or 4.

If that works, then we've got the basic idea working.  We just have to figure out which figures you need to save and exactly how you want to use them in the report footer.

Also, I just happened to realize that a field name that you used in the first % formula you posted was not the name I used.  You used Total_Received_Count, but the name I used in the stored procedure I posted was Received_Count.  Did you change the column name?  It's fine if you did.  I just hadn't noticed until just now that the name was different and I wanted to make sure we were talking about the same field.

James
0

Author Comment

ID: 24572276
So this same logic would work for producing each percentage then ?
0

Author Comment

ID: 24572303
or rather based on the logic above could I have two arrays saving both counts approved and received and then put them into variables that calculate each  perecentage and store it in the footer ?
0

LVL 35

Expert Comment

ID: 24577305
Exactly.  Create an array to save each count or amount you need (received, approved, etc.).  If you like, you can have one formula update all of the arrays.  For example, you could change the end of the second formula I posted (the one that updates received_count), from:

if i <> 0 then

""

to

if i <> 0 then
(
approved_count [ i ] :=
Sum ({sp_v__Tier_Auto_reports;1.Total_Approved_Count}, {sp_v__Tier_Auto_reports;1.LoanType});
);

""

Of course you would also need to add any new array to the first formula (in the report header), and if, as suggested above, you're going to add a new array to the second formula (the one that updates the array), you'd also need to add a declaration for that array to that formula.

If you prefer, you can create a separate formula to update each array, rather than use the same formula.  I'd use one formula, but there's something to be said for keeping things "compartmentalized".

One other detail I wanted to mention:

The first formula I posted (the one that declares the array) was:

""

I said that the "" at the end was so that the formula produces no output on the report, and that is true, but in this case it also serves another purpose.  Without that, the last thing in the formula would be the Redim, and the result of a formula can not be an array, so without the "" at the end, CR will actually give you an error on that formula.  So in this case you have to have the "" (or some other simple value) at the end, or you'll get an error.

James
0

Author Closing Comment

ID: 31569539
Thanks guys your help was invaluable here.
0

LVL 35

Expert Comment

ID: 24691587
You're welcome.  Glad we were finally able to get it sorted out.

James
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.