can't get all the information from the stored procedure 1

[sue1127]

I have a Crystal 8.0 report that is running off a stored procedure(SqlServer 7.0). The stored proc is returning the period amount for the current fiscal year, but not for the previous fiscal year. I just get 0's for previous fiscal year amount when I add the condition that fiscal year=last_fiscal_year, or even when I hard code 2004 as the last fiscal year

I subtract 1 from the current fiscal year to get it to return last_fiscal_year, and it does do that, but it does not return the corresponding period amount. However, when I run the stored procedure through Infomaker, there is a separate row for the amount from the previous fiscal year(2004), so I know the stored proc is getting the information.

Does anyone have any ideas on why Crystal is not getting the last fiscal year amount?

Thanks very much,

Sue

 

[fredp1]

Hi Sue,

Unless you have some filters/suppression logic in your report, I would expect it it work the same.
Just to be sure, can you create a simple report with the SP in it to see what you get. Hopefully you will get the expected results.
If it doesn't try running the SP in SQL Server Query Analyser i.e exec [stored_procedure_name]
You should get the same result.

Check the original report by exporting to he Report Definition to see if there is any suppresion/filtering.

I have only used 8.5 and running sp from it works okay.

If that still does't work, look for any service packs and then you can trace the sp in Sql Server Profiler to see whats been sent to the db.



Cheers
Fred

 

[synapsevampire]

I'd have to look at the SP, but it sounds like te SP is returning the data correctly, but it may be that it returns 2 data sets, which CR does not support.

I'd have to see the SP to know, but I'd guess that it's designed that way and would need to be reworked.

-k

 

[vidru]

I agree with k - it sounds like you're getting two result sets. The application might be able to handle it, by CR can't. You could likely create a new stored proc that joins the two (PYTD and YTD) with a UNION query.

If you post the proc, I'm sure someone can help, if you need it.

-dave

 

[sue1127]

Thanks to all who responded. I think the 2 data sets may be the problem, but I'm not sure how to determine if that is the case, or how to join the two with a UNION query. I've posted the proc below. If anyone can point me in the right direction, I would very much appreciate it.

Thanks,

Sue

ALTER proc uc_cr_frs_dept_expense

@period smallint,
@fiscal_year char(5),
@user varchar(25)

As
declare @last_fiscal_year as numeric

SELECT
last_fiscal_year =(cast(@fiscal_year as numeric)) -1,
ledger_organizations.fiscal_year, ledger_organizations.organization_description,
ledger_accounts.fiscal_year,
ledger_balances.balance_name, ledger_balances.period, ledger_balances.ledger_account, ledger_balances.fiscal_year, ledger_balances.period_amount,
chart_of_accounts.account, chart_of_accounts.account_description,
budget_lists.approver
FROM
{ oj ((((ledger_organizations ledger_organizations(NOLOCK) RIGHT OUTER JOIN ledger_accounts ledger_accounts (NOLOCK)ON ledger_organizations.fiscal_year = ledger_accounts.fiscal_year AND
ledger_organizations.ledger = ledger_accounts.ledger AND ledger_organizations.organization_level = ledger_accounts.organization_level AND
ledger_organizations.organization = ledger_accounts.organization) INNER JOIN chart_of_accounts chart_of_accounts(NOLOCK) ON ledger_accounts.fiscal_year = chart_of_accounts.fiscal_year AND
ledger_accounts.coa = chart_of_accounts.coa AND ledger_accounts.account = chart_of_accounts.account) LEFT OUTER JOIN ledger_balances ledger_balances(NOLOCK) ON ledger_accounts.fiscal_year = ledger_balances.fiscal_year AND
ledger_accounts.ledger = ledger_balances.ledger AND
ledger_accounts.ledger_account = ledger_balances.ledger_account) INNER JOIN budget_list_accounts budget_list_accounts(NOLOCK) ON chart_of_accounts.fiscal_year = budget_list_accounts.fiscal_year) INNER JOIN budget_lists budget_lists(NOLOCK) ON budget_list_accounts.fiscal_year = budget_lists.fiscal_year AND
(substring(ledger_balances.ledger_account,1,8) >= budget_list_accounts.beginning_organization and substring(ledger_balances.ledger_account,1,8) <= budget_list_accounts.ending_organization) and budget_list_accounts.ledger = budget_lists.ledger AND
budget_list_accounts.signature_list = budget_lists.signature_list}
WHERE
len(ledger_accounts.ledger_account) ='12'and
ledger_balances.ledger = 'UC' AND
(ledger_balances.balance_name = 'ACTUAL' OR ledger_balances.balance_name = 'BUDGET') and
((substring(ledger_balances.ledger_account,1,4)>='2110' and
substring(ledger_balances.ledger_account,1,4) <='2999') or
substring(ledger_balances.ledger_account,1,4)='8001') and
/* Next 3 lines added to include last year's period amt*/
((ledger_balances.period <=@period) and
(ledger_balances.fiscal_year=@fiscal_year or
ledger_balances.fiscal_year='2004'))and
budget_lists.approver=@user

 

[synapsevampire]

The SP is odd, it declares @last_fiscal_year, but never uses it.

Try changing the last part to:
(
(ledger_balances.period <=@period) and
(
(ledger_balances.fiscal_year=@fiscal_year
or
ledger_balances.fiscal_year='2004')
)
)
and
budget_lists.approver=@user

or declare the @last_fiscal_year as a char(5), then cast @fiscal_year to a numeric, subtract 1 from it, then cast it back to a char(5).

I don't have a database here to test with, but that's the theory.

A shame that they store years as chars.

-k

 

[sue1127]

Thanks, -k, I'll give it a try and let you know if it works.

Sue

 

[sue1127]

I tried the 2 most recent suggestions, and I also tried changing ledger_balances.fiscal_year to budget_lists.fiscal_year, but so far nothing as worked. I had tried to use @last_fiscal_year instead of hard-coding '2004'. When that didn't work I tried '2004', which I will settle for at this point.

I couldn't figure out the correct syntax for casting fiscal_year back to char(5) after casting it as numeric.

It seems as though the Union query might be the way to go, but I'm not quite sure how to code it.

Sue

 

[fredp1]

To get a UNION query uyou can use this basic structure

SELECT
last_fiscal_year =(cast(@fiscal_year as numeric)) -1 as [Year Entered],
ledger_organizations.fiscal_year as [Fiscal_Year], ledger_organizations.organization_description as [Description],
ledger_accounts.fiscal_year,
ledger_balances.balance_name, ledger_balances.period, ledger_balances.ledger_account, ledger_balances.fiscal_year, ledger_balances.period_amount,
chart_of_accounts.account, chart_of_accounts.account_description,
budget_lists.approver
--The rest of the previous year query
UNION ALL
SELECT
last_fiscal_year =(cast(@fiscal_year as numeric)) as [Year_Entered],
ledger_organizations.fiscal_year as [Fiscal_Year], ledger_organizations.organization_description as [Description],
ledger_accounts.fiscal_year,
ledger_balances.balance_name, ledger_balances.period, ledger_balances.ledger_account, ledger_balances.fiscal_year, ledger_balances.period_amount,
chart_of_accounts.account, chart_of_accounts.account_description,
budget_lists.approver
--The rest of the current year query

Whats mandatory is to have the same number of columns in both queries and the columns are mapped 1 for 1
i.e. the 3rd column in the first and second query must be description.
Note: The column names come exclusively from the first query so its a good idea to name each column a 'generic' name like year(as apposed to previous year).
If you have columns in the top query thats not in the bottom query you will need to create faux columns and intialise the column with say a 0 or ''.

I hope this makes sense

Cheers
Fred

 

[sue1127]

Thanks, Fred. I'll give it a try, and I'll let you know if I have any questions.

Sue

 

[vidru]

I'll jump in and supply you with the CAST/re-CAST to get the previous year, so you don't have to hard-code it:

DECLARE @last_fiscal_year as CHAR(5)

SET @last_fiscal_year = CAST(CAST(@fiscal_year AS INT) - 1 AS CHAR(5))

Then you can drop '2004' from your WHERE clause and replace it with @last_fiscal_year.

-dave

 

[sue1127]

Thanks Dave. I was on the right track, but couldn't get the syntax quite right. I'll give it a try.

 

[sue1127]

Well, I finally got information displaying for last fiscal year. I couldn't get the union to work right. Apparently what did it was Dave's Cast/ReCast syntax, along with changing the Crystal running totals formula to 2004, instead of last fiscal year. Neither one of those worked alone, but together they brought back the correct information. I'm not crazy about hard-coding the last fiscal year in Crystal; I'll probably play around with it some more--I'm sure there must ba a way to avoid the hard-coding. But for right now---it's working.

Thanks to all who responded.

Sue