sql server reporting services: Getting data from other source -- sql server reporting services

You're in the

sql server reporting services

group:

Getting data from other source

Re: Getting data from other source

Bruce L-C [MVP]
1/6/2006 9:07:50 AM

sql server reporting services:

Where are you doing this. If your SQL is getting changed then my guess is
you are using the graphical query designer (4 panes) instead of the generic
query designer (2 panes). The button to switch to the generic is to the
right of the ...

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]

Getting data from other source

Jack
1/6/2006 12:02:25 PM

I need to make a report which is getting data from Microsoft CRM and
Informix.

The way I intend to do it is making a view in the CRM database with data
from CRM and a linked Informix server.

Now I have a little problem getting data from informix, or rather the right
data.

In the sql query analyzer I can get data from Informix but I need to use a
parameter to get sales for the last year.

Like this

SELECT stam.kontonr AS Kontonr,
post.firmanr,
post.fakturanr,
stam.udskrevet,
stam.fakturatype,
post.beloeb,
post.linietype,
post.kontonr AS Varenr,
post.tekst AS Varenavn,
post.antkurpct as Antal

FROM informix.economi.root.fakpost post, informix.economi.root.fakstam stam

WHERE post.firmanr = stam.firmanr AND
post.fakturanr = stam.fakturanr AND
(post.firmanr = 7) AND
(stam.fakturatype <> 9) AND
(post.linietype <> 5) AND
(post.linietype <> 9) AND
(stam.udskrevet >= dateadd(YEAR,-1,getdate())) AND
(stam.udskrevet <= getdate())

ORDER BY post.kontonr DESC

The problem is that the odbc driver can't use the dateadd function, I've
tried using af @ parameter but it can't use that either.

Then I tried doing an exec ( sqlstatement '+@parameter+' sqlstatement ) but
that didn't work either.

It just can't get the date right, when I use ' in the sqlstatement I can use
@ variable but the variable need to be in ' to be recognized as a date. I'm
kinda lost here

Anyone got an idea ?

Jack

Re: Getting data from other source

Jack
1/6/2006 12:08:37 PM

Tried like this too and the results are below, it's doesn't sort right

declare @test DATETIME
declare @dato nvarchar(23)
declare @tekst nvarchar

set @test = dateadd(year,-1,getdate())
set @dato = convert (nvarchar(23),@test,121)
select @dato
set @tekst = '20050106 00:00:00'

exec ('SELECT stam.kontonr AS Kontonr,
post.firmanr,
post.fakturanr,
stam.udskrevet,
stam.fakturatype,
post.beloeb,
post.linietype,
post.kontonr AS Varenr,
post.tekst AS Varenavn,
post.antkurpct as Antal

FROM informix.economi.root.fakpost post, informix.economi.root.fakstam stam

WHERE post.firmanr = stam.firmanr AND
post.fakturanr = stam.fakturanr AND
(post.firmanr = 7) AND
(stam.fakturatype <> 9) AND
(post.linietype <> 5) AND
(post.linietype <> 9) AND
(stam.udskrevet >= '+@tekst+' ) AND
(stam.udskrevet <= getdate())

ORDER BY post.kontonr DESC')

-----------------------
2005-01-06 12:04:23.063

(1 row(s) affected)

Kontonr firmanr fakturanr udskrevet
fakturatype beloeb linietype Varenr Varenavn
Antal
----------- ------- ----------- ------------------------------------------------------
----------- ----------------- --------- ----------- --------------------------------------------------
-------------
8501000 7 7333 2003-01-31 00:00:00.000
1 -941.00 1 99999999 1,75% af 53809
..0000
55121500 7 10767 2005-10-26 00:00:00.000
3 -4591.17 4 99999999 Invoice
-10.0000
32101500 7 9104 2004-07-12 00:00:00.000
3 100.04 2 99999920 One-way pallet
2.0000

Re: Getting data from other source

Jack
1/6/2006 2:09:47 PM

Seems like i've solved it with set quote_identifier to off :)

"Jack" <jackdSPAM@jackd.dk> skrev i en meddelelse
news:eBhY5ErEGHA.3856@TK2MSFTNGP12.phx.gbl...
[quoted text, click to view]

Re: Getting data from other source

Jack
1/6/2006 3:02:18 PM

Now I have a problem getting it to work in the view, isn't it possible to do
the same things in a view like in the query analyzer.

All the double quotes get changed to [, how come ?

Now it think of the statement as an identifier and says it can't be greater
than 128, please help me out !

Here is what it converts it to

SET quoted_identifier OFF DECLARE @test DATETIME DECLARE @dato
nvarchar(25)
SET @test = dateadd(year, - 1, getdate())
SET @dato = (['] + CONVERT(nvarchar(23), @test, 121) + ['])
EXEC ([SELECT stam.kontonr AS Kontonr,
post.firmanr,
post.fakturanr,
stam.udskrevet,
stam.fakturatype,
post.beloeb,
post.linietype,
post.kontonr AS Varenr,
post.tekst AS Varenavn,
post.antkurpct as Antal

FROM informix.economi.root.fakpost post, informix.economi.root.fakstam stam

WHERE post.firmanr = stam.firmanr AND
post.fakturanr = stam.fakturanr AND
(post.firmanr = 7) AND
(stam.fakturatype <> 9) AND
(post.linietype <> 5) AND
(post.linietype <> 9) AND
(stam.udskrevet >= ]
+ @dato + [ ) AND
(stam.udskrevet <= getdate())

Re: Getting data from other source

Jack Nielsen
1/7/2006 9:34:45 AM

It is in a view in Enterprise manager and here it changes " to [

I need to do it in a view to get data from different sources because I can
only have one source in reporting services !

"Bruce L-C [MVP]" <bruce_lcNOSPAM@hotmail.com> skrev i en meddelelse
news:e2A82LtEGHA.716@TK2MSFTNGP09.phx.gbl...
[quoted text, click to view]

----------------------------------------
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at fÃ¥ 47933 spam-mails.
Betalende brugere fÃ¥r ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dk

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.

home · blog · groups

Questions? Comments? Contact the dn