Report Portal

quote:

---

Originally posted by juliom

Hi Igor...
I'm creating an SQL Report which has 22 filters.
Some of those filters (I'll refer to this kind of filter as Type A) are based on the table that has the main data for the report, so the SQL for those filters looks like this:

[code]
SELECT DISTINCT t.c432, t.c432
FROM T56 t
UNION
SELECT TOP 1 'TODOS', 'TODOS'
FROM T56
[/code]

The value returned by filters such as the one above is a string, I'm handling this as a varchar(20)

Other filters (Type B) are based on other tables and I'm selecting a code and a description to create the filter, those filters' code looks like this

[code]
SELECT f.FRE_CODIGO, f.FRE_DESCRIPCION
FROM FRE_FREGADO f
UNION
SELECT TOP 1 100, 'TODOS'
FROM FRE_FREGADO
[/code]

The value returned by filters such as the one above depends on the datatype of the first column selectd, I'm handling this according to its datatype, some are int, some are numeric and some are float.

I created an store procedure that receives the parameters (the results from the filters) and creates the WHERE part of the select and executes it.

The problem is with Type B filters when the index of the name is greater than 9 (so they have to be referred as ::10, ::11, etc...

I'm calling the store procedure and this, for example works fine:

[code]
EXEC dbo.[CONSULTA PERFILES PERSONAS] '::1','::2','::3',::4,::5,::6,::7,::8,::9,'::10',::9,'::12','::13',::9,'::15',::9,'::17',::9,'::19','::20',::9,'::22'
[/code]

(I'm using the defalult value for every filter which is either 'TODOS' OR 'TODAS' - this is ALL in spanish - so the store procedure doesn´t generate the WHERE part of the select)

As you can see the highest filter result, not between qotation marks is 9 (which appears several times) if I change any of those for ::11, ::14, ::16, ::18 or ::21 I get this error message:

[code]
Error converting data type nvarchar to numeric
[/code]

I can enclose the reference to the filter results in quotation marks but i get the same error.

I gess this is a bug...

Sorry.. there was an error in a code tag so some of the Post wasn't shown... I'm going to try an get the Sentence with the SQLProfiler any ways..

Thanks

Julio

---

quote:

---

It seems to me that some how the system is taking the filter's display value (not the return value) and attaches the last digit of the name to it and returns that.. This only happens for those filters above the 9th (10th, 11th, 12th, etc.)

---

quote:

---

Originally posted by admin

Please try to put quotes around 11th parameter like so:

EXEC dbo.[CONSULTA PERFILES PERSONAS] '::1','::2','::3',::4,::5,::6,::7,::8,::9,'::10','::9','::12','::13',::9,'::15',::9,'::17',::9,'::19','::20',::9,'::22'

---

quote:

---

Originally posted by admin

Julio,

I understand the problem now. It happens when there more that 9 filter in a query. The problem is that filter ::1 interferes with ::10 ,::11, ::12 and so on.

The fix is quite simple (SqlReport.vb line 210) and will be available in the next release.

---