Deserialization Failed: select

Showing posts with label select. Show all posts

Friday, March 30, 2012

ReportViewer in SSRS2000

Is it possible to restrict the ReportViewer export options to only a
select few ?
ThanksOn the SQL Reporting Server, assuming a default install, in the
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer directory, open the RSReportServer.config file.
In here there is a section called <RENDER> </RENDER> which lists all
the different export types available.
This will affect all reports on the ReportServer, but you should be
able to do something with this - comment out the entire line, or add a
visible=false; tag
HTH
Jason

Wednesday, March 28, 2012

ReportViewer Control and German-Swiss date-format

Direct select on my Server gives the following three all working:
select * from tblvehicle
where dtmyearofpurchase = '2/1/1980 3:36:07 PM'
select * from tblvehicle
where dtmyearofpurchase = '02/01/1980 15:36:07'
select * from tblvehicle
where dtmyearofpurchase = '1980-02-01 15:36:07'
the only one NOT working ist the GERMAN-SWISS date-format!!!
select * from tblvehicle
where dtmyearofpurchase = '01.02.1980 15:36:07'
Unfortunately I receive this format automatically from Report-Server!!
----
Additional Info:
----
I've embedded a ReportViewer Control in a Winforms app.
I get the parameters via ReportViewer1.ServerReport.GetParameters
and show values to be chosen from in a checked listbox from
ValidValues(i).Value (chosen the values, NOT the labels!)
I run the Winform-App one time from an external us-en-Client and
one time from the same de-ch-Swiss Server
US-client: Query DOES work
- XP SP2
- all suggested automatic updates installed incl. newest ones from today
(19.06.06)
- VS2005 V. 2.0.50727
- all english Software
Language Config:
Windows XP and all running programs are us-english
First Language in Internet Explorer: en-us
Regional Options: english-us with
time: h:mm:ss tt, e.g. 11:12:36 AM
short date: M/d/yyyy , e.g. 6/19/2006
long date: dddd, MMMM dd, yyyy, e.g. Monday, June 19, 2006
Server (and at the same time Swiss-Client): Query DOES NOT work!!!
- Enterprise Server 2003R2
- all suggested automatic updates installed incl. newest ones from today
(19.06.06)
- SQL Server 2005, SP1, cumulative post SP1 hotfixes
- all english Software
Language Config:
Windows 2003R2 and all running programs are us-english
First Language in Internet Explorer: de-ch (=german switzerland)
Regional Options: german switzerland
time: HH:mm:ss, e.g. 11:18:54
short date: dd.MM.yyyy, e.g. 19.06.2006
long date: dddd, d. MMMM yyyy, e.g. Montag, 19. Juni 2006
Via SQL Server Profiler I retrieved the following data:
either requested by the ReportViewer1.ServerReport.GetParameters
or sent by the SetParameters
1. DOES work:
from ReportServer (enumeration) to external en-us client
retrieved values via GetParameters
following values from Profiler:
<Value>2/1/1980 3:36:07 PM</Value>
<Label>02/01/1980 15:36:07</Label>
Sent Query from winforms via SetParameters
also retrieved from Profiler:
@.Parameters=N'strTableName=tblVehicle&strColumnName=dtmYearOfPurchase&Suchschlussel=2/1/1980 3:36:07 PM'
2. This DOES NOT work
to self server de-ch (german switzerland)
retrieved values via GetParameters
<Value>01.02.1980 15:36:07</Value>
<Label>02/01/1980 15:36:07</Label>
Sent Query from winforms via SetParameters
@.Parameters=N'strTableName=tblVehicle&strColumnName=dtmYearOfPurchase&Suchschlussel=01.02.1980 15:36:07'
Apparently it seems all to be right...
in 1. it works = one datarow is sent back to the winform app
in 2. it doesnt work = the report is rendered but empty.Hello Henry,
In Report Layout View you can select the Properties window. You can set the
language for the report which determines formatting according to language
preferences.You can use some expression like User!Language to make the
language selection dynamic. You may test default language/US-English of
report to see differences
The locale of the parameter section is based on the browser
Accept-Language. The formatting of dates in the report is based on the
language specified in the RDL.
Note that you need to deploy the report to server to test since there is
some issue in design time reportviewer regarding this.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Peter,
we are so involved in lots of other Reporting-Services areas, that we
couldn't resolve this issue yet, despite your valuable information.
I'll come back to this problem in the next four weeks.
Yours Henry
""privatenews"" wrote:
> Hello Henry,
> In Report Layout View you can select the Properties window. You can set the
> language for the report which determines formatting according to language
> preferences.You can use some expression like User!Language to make the
> language selection dynamic. You may test default language/US-English of
> report to see differences
> The locale of the parameter section is based on the browser
> Accept-Language. The formatting of dates in the report is based on the
> language specified in the RDL.
> Note that you need to deploy the report to server to test since there is
> some issue in design time reportviewer regarding this.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||Hello Henry,
Thank you for taking time to write in about the status of the issue. I
understand that you have other priorities right now. If you need any
assistance from us, please feel free to post back.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Dear Peter,
the problem was found to be:
(recap)
- Enduser works in localized Windows (e.g. Regional Settings Italian)
- Enduser may have different IE-Language Setting of e.g. us_english
- Enduser may like to work in our WinForm-Application (that includes a
ReportViewer-Control) in his German Language
So:
- we configure all text in our WinForm-App to be expressed in German
- we send an @.winform-language-Parameter to Reporting-Service
- we configure in the Report all the Body-Text, Dropdown-List content,
Column-Names etc. to be in German
- we do Selects to an underlying DB via Stored-Procedures that contain
Datetime-data-types in the Where-clause
- THE RESULT-SETS ARE EMPTY!!!
Problem:
- The explicit parameter for German will not work, since Dropdown Box
ValidValues are sent in the us_english-dateformat (mdy)
- the SQL-Server Server language is swiss-German (de-CH) with its inherent
dateformat of dmy
here's the solution:
- we define an internal Parameter @.language in the RDL
- we allow blank value
- NO available Values
- Default Value is a non-queried expression:
=Iif (user!Language.StartsWith("de"),"1",Iif
(user!Language.StartsWith("en"),"2",Iif
(user!Language.StartsWith("fr"),"3",Iif
(user!Language.StartsWith("it"),"4","2"))))
- we send this @.language Parameter to any Stored Procedure
- there we set the batch-level-language (means: in the SP):
if @.language = 1 set language German
if @.language = 2 set language English
if @.language = 3 set language French
if @.language = 4 set language Italian
Drawback:
- the user chose German for her WinForm-App
- she receives ALL text in German BUT us_english Dates in Dropdown Boxes
But finally the whole thing works.
Best Regards, Henry|||Hello Henry,
Thank you for taking time to write in about your detailed workaround to
solve this issue and this shall benefit the community!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

ReportViewer Control - Toolbar - Export - Set other default ?

I would like to change the default setting of the export listbox in the toolbar of the reportviewer (webforms) control.

Now the default is : 'Select a format' and I would like to change it to Excel since in 90% of the cases my users export the reports to excel.

thanks in advance

1) To change order of export to options in SRS we need to change ReportServer.config file. Change the order and they will get reflected in the UI.

</Render>

Saturday, February 25, 2012

Reporting Stored Procedure on SQL Server 2000

I want to adapt a Stored Procedure like the following for reporting purposes.
select
frmInstId,
frmName,
frmDueDate,
country,
description,
frmLastStatus
flgStat1,
flgStat2,
flgStat3,
flgStat4,
flgStat5
FROM
tblFrmDef,
tblFrmInst,
tblFrmHist,
tblObjects
WHERE
tblFrmDef.frmDefId = tblFrmInst.frmDefId and
tblFrmInst.objId = tblObjects.objId and
tblFrmInst.frmInstId = tblFrmHist.frmInstId and
tblFrmInst.flgActive=1
The problem is that the report can potentially take numerous permutations of
numerous parameters, including:
-tblFrmInst.frmDueDate (range possible)
-tblObjects.user
-tblFrmInst.flgActive (1 or 0)
-tblDef.frmName,
-tblFrmHist.frmLastStatus,
-flgStat1,
-flgStat2,
-flgStat3,
-flgStat4
-flgStat5
Short of building a heavily nested if...then...else statement to build the
correct select statement, are there any better ideas?Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html
AMB
"Patrick" wrote:
> I want to adapt a Stored Procedure like the following for reporting purposes.
> select
> frmInstId,
> frmName,
> frmDueDate,
> country,
> description,
> frmLastStatus
> flgStat1,
> flgStat2,
> flgStat3,
> flgStat4,
> flgStat5
> FROM
> tblFrmDef,
> tblFrmInst,
> tblFrmHist,
> tblObjects
> WHERE
> tblFrmDef.frmDefId = tblFrmInst.frmDefId and
> tblFrmInst.objId = tblObjects.objId and
> tblFrmInst.frmInstId = tblFrmHist.frmInstId and
> tblFrmInst.flgActive=1
>
> The problem is that the report can potentially take numerous permutations of
> numerous parameters, including:
> -tblFrmInst.frmDueDate (range possible)
> -tblObjects.user
> -tblFrmInst.flgActive (1 or 0)
> -tblDef.frmName,
> -tblFrmHist.frmLastStatus,
> -flgStat1,
> -flgStat2,
> -flgStat3,
> -flgStat4
> -flgStat5
> Short of building a heavily nested if...then...else statement to build the
> correct select statement, are there any better ideas?|||Also, some of our parameters take in list
and I want to do filter like WHERE tblObjects.user in
('jblogg','helloWorld','smith')
how could I do that? I try to make the parameter a varchar(1000) parameter,
but SQL2000 doesn't seem to treat my comma seperated list as a list, but
rather treating it as a single string/value!?
"Alejandro Mesa" wrote:
> Dynamic Search Conditions in T-SQL
> http://www.sommarskog.se/dyn-search.html
>
> AMB
> "Patrick" wrote:
> > I want to adapt a Stored Procedure like the following for reporting purposes.
> > select
> > frmInstId,
> > frmName,
> > frmDueDate,
> > country,
> > description,
> > frmLastStatus
> > flgStat1,
> > flgStat2,
> > flgStat3,
> > flgStat4,
> > flgStat5
> > FROM
> > tblFrmDef,
> > tblFrmInst,
> > tblFrmHist,
> > tblObjects
> > WHERE
> > tblFrmDef.frmDefId = tblFrmInst.frmDefId and
> > tblFrmInst.objId = tblObjects.objId and
> > tblFrmInst.frmInstId = tblFrmHist.frmInstId and
> > tblFrmInst.flgActive=1
> >
> >
> > The problem is that the report can potentially take numerous permutations of
> > numerous parameters, including:
> > -tblFrmInst.frmDueDate (range possible)
> > -tblObjects.user
> > -tblFrmInst.flgActive (1 or 0)
> > -tblDef.frmName,
> > -tblFrmHist.frmLastStatus,
> > -flgStat1,
> > -flgStat2,
> > -flgStat3,
> > -flgStat4
> > -flgStat5
> >
> > Short of building a heavily nested if...then...else statement to build the
> > correct select statement, are there any better ideas?|||Hello Alejandro,
You shall use two single quotation for a string in dynamical query. For
example, you could run this in QA to test
exec ('select * from employees where fname=''test''')
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--
| Thread-Topic: Reporting Stored Procedure on SQL Server 2000
| thread-index: AcWcy8MVucGjmJIKT72QFaaxe01p2g==| X-WBNR-Posting-Host: 198.240.128.75
| From: "=?Utf-8?B?UGF0cmljaw==?=" <questions@.newsgroup.nospam>
| References: <5A2BD817-28A5-433D-9556-3E82983B39DD@.microsoft.com>
<915A2183-FE52-4005-A8A9-4CBF10BC8586@.microsoft.com>
| Subject: RE: Reporting Stored Procedure on SQL Server 2000
| Date: Tue, 9 Aug 2005 03:19:01 -0700
| Lines: 58
| Message-ID: <0C9CE207-7DD9-4EB3-9B8C-0091DFE447CF@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:66307
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Also, some of our parameters take in list
| and I want to do filter like WHERE tblObjects.user in
| ('jblogg','helloWorld','smith')
|
| how could I do that? I try to make the parameter a varchar(1000)
parameter,
| but SQL2000 doesn't seem to treat my comma seperated list as a list, but
| rather treating it as a single string/value!?
|
| "Alejandro Mesa" wrote:
|
| > Dynamic Search Conditions in T-SQL
| > http://www.sommarskog.se/dyn-search.html
| >
| >
| > AMB
| >
| > "Patrick" wrote:
| >
| > > I want to adapt a Stored Procedure like the following for reporting
purposes.
| > > select
| > > frmInstId,
| > > frmName,
| > > frmDueDate,
| > > country,
| > > description,
| > > frmLastStatus
| > > flgStat1,
| > > flgStat2,
| > > flgStat3,
| > > flgStat4,
| > > flgStat5
| > > FROM
| > > tblFrmDef,
| > > tblFrmInst,
| > > tblFrmHist,
| > > tblObjects
| > > WHERE
| > > tblFrmDef.frmDefId = tblFrmInst.frmDefId and
| > > tblFrmInst.objId = tblObjects.objId and
| > > tblFrmInst.frmInstId = tblFrmHist.frmInstId and
| > > tblFrmInst.flgActive=1
| > >
| > >
| > > The problem is that the report can potentially take numerous
permutations of
| > > numerous parameters, including:
| > > -tblFrmInst.frmDueDate (range possible)
| > > -tblObjects.user
| > > -tblFrmInst.flgActive (1 or 0)
| > > -tblDef.frmName,
| > > -tblFrmHist.frmLastStatus,
| > > -flgStat1,
| > > -flgStat2,
| > > -flgStat3,
| > > -flgStat4
| > > -flgStat5
| > >
| > > Short of building a heavily nested if...then...else statement to
build the
| > > correct select statement, are there any better ideas?
|

Reporting Sevices multiple resultsets in a dataset

Hi!

I have a question about SQL Reporting Services. I have a stored procedure which returns multiple resultsets (multiple select). In my reporting services project I have a dataset that connects to this procedure. But, when I execute it in the Data tab, I only get the first resultset. So can't I have more resultsets?
I can't use more datasets (that have the selects in the procedure) because the procedure is complicated and has many calculation. I've managed to get all the selects in only one and the reporting services in working in this way. How about many resultsets in a dataset?

Thank you!

Irina Stancai have usually had one resultset in my stored procs.. but i will give it a shot at this :
if you add a textbox in designer view, right click and go to properties..it opens a new window.
the last option says datasets. if you xpand it, it shows the list of fields returned by that dataset. you can bind the dataset field to the textbox from there.

see if you can bind your fields this way. i dont know if you already knew this.

hth|||As far as I know, Reporting Services can only handle Datasets containing ONE table at a time.
That's why I am not currently using it; just sounds like way too much overhead to do a round trip for each datatable.
I normally use one dataset with multiple tables and datarelations between the tables, but it doesn't look like reporting services can handle this. (And to denormalise the data into one table just sounds archaic and like too much effort.)

If anybody can prove me wrong I shall be delighted to hear more!|||>>And to denormalise the data into one table just sounds archaic and like too much effort

you dont need to denormalize your data. as long as your stored proc returns only one RESULTSET you are fine. the resultset can be a result of a query joining any number of tables. i have used queries with multiple joins. so there is no limit of any table only. but the only thing i am not sure of is if you can return multiple result sets ( more than one select stmt ) from one stored proc and use both resultsets in the report.
xample :

create proc ...
as

select col1,col2...etc from table1,table2 where ... --<-- this will work

select * from table3 -- <-- i dont know abt this

hth|||I've read that one data region cannot support more than one dataset.

so, i've tried to do that (procedure with multiple resultsets in one dataset). When I run it in Query Analyzer, the exec of the procedure shows all the selects.
When i create a dataset in Reporting services, with the procedure, when I run it i only get the first select and in the Fields! area i only get the fields in the first select.
So, to solve the problem, cause i needed a table that expands and this can be done in only one table (or am i wrong??), i made the stored procedure to send the last selects in some temporary tables and i've made more datasets and used subreports. it's working fine now. but i was wondering if this could be done easier (cause i have now a few reports, rather than one). If you have another idea please tell me.

Thanks.|||That was my problem too. I basically wanted to display hierarchical data in one report (parent-children detail) and I always create two (or more) tables for that in one dataset, with a relation linking the two. It doesn't look like it could be done in reporting services. Not with multiple selects in one SP anyway. And that's what I wanted - to limit round trips to the server. So I have basically given up on reporting services and started writing my own custom reports. If you are not forced to use Reporting services have a look at the Report Starter Kit on this site.

If you are forced to use RS then maybe thisusergroup will be able to help you...