Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: CrystalReports
Hello To All,
 
I am making a Crystal report with the use of Parameter and S.P.
now, there is two parameter in Crystal report as Company Name and Days.
 
When i Select one company, the database of that company will be executed in S.P.
 
i use the query like below:
SELECT     'FMCPL'as company, itmast.itcode, sl.itname AS itemname, SUM(sl.qtyin) AS qtyin, SUM(sl.qtyout) AS qtyout, SUM(sl.qtyin)- SUM(sl.qtyout) as qty, sl.unit AS unit, MAX(sl.docdt) AS maxdt, munim002.dbo.itmast.material, 
                      munim002.dbo.itgroup.dtldesc, munim002.dbo.gdmast.gdname
FROM         munim002.dbo.StockLederView sl INNER JOIN
                      munim002.dbo.itmast ON sl.itcode = munim002.dbo.itmast.itcode INNER JOIN
                      munim002.dbo.itgroup ON munim002.dbo.itmast.itgroup = munim002.dbo.itgroup.code INNER JOIN
                      munim002.dbo.gdmast ON sl.gdcode = munim002.dbo.gdmast.gdcode
GROUP BY sl.itname, sl.unit, munim002.dbo.itmast.material, munim002.dbo.itgroup.dtldesc, munim002.dbo.gdmast.gdname
HAVING      SUM(sl.qtyin)- SUM(sl.qtyout) > 0 and (MAX(sl.DOCdt) <= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE() - @dys)))
 

it databas will automatically change as per the selection parameter of company.
 
so,please help me...
 

Thanks and Regards...
Mitesh
Posted 11-Oct-12 20:47pm

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Please try this
 
declare @dbname nvarchar(50)
DECLARE @EXECQ AS VARCHAR(MAX)
 
set @dbname=''
 
if @comapny='companyname'
Begin
set @dbname='dbname'
End
 

SET @EXECQ='SELECT '''+@comapny+''' as company, sl.itname AS itemname, SUM(sl.qtyin) AS qtyin, SUM(sl.qtyout) AS qtyout, SUM(sl.qtyin)- SUM(sl.qtyout) as qty, sl.unit AS unit, MAX(sl.docdt) AS maxdt, ' + @dbname + '.dbo.itmast.material,
                      ' + @dbname + '.dbo.itgroup.dtldesc, ' + @dbname + '.dbo.gdmast.gdname
FROM         ' + @dbname + '.dbo.StockLederView sl INNER JOIN
                      ' + @dbname + '.dbo.itmast ON sl.itcode = ' + @dbname + '.dbo.itmast.itcode INNER JOIN
                      ' + @dbname + '.dbo.itgroup ON ' + @dbname + '.dbo.itmast.itgroup = ' + @dbname + '.dbo.itgroup.code INNER JOIN
                      ' + @dbname + '.dbo.gdmast ON sl.gdcode = ' + @dbname + '.dbo.gdmast.gdcode
GROUP BY sl.itname, sl.unit, ' + @dbname + '.dbo.itmast.material, ' + @dbname + '.dbo.itgroup.dtldesc, ' + @dbname + '.dbo.gdmast.gdname
HAVING      SUM(sl.qtyin)- SUM(sl.qtyout) > 0 and (MAX(sl.DOCdt) <= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE() - @dys)))
 
ORDER BY sl.itname'
--print @EXECQ
Exec (@ExecQ)
 
Thanks
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 8,963
1 OriginalGriff 6,706
2 Peter Leow 4,167
3 Zoltán Zörgő 3,714
4 Kornfeld Eliyahu Peter 2,525


Advertise | Privacy | Mobile
Web03 | 2.8.150123.1 | Last Updated 12 Oct 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100