Extract a string in a Stored Procedure

Is there anyway to extract part of a string in a stored procedure
using a parameter as the starting point?
For example, my string might read: x234y01zx567y07zx541y04z
My Parameter is an nvarchar and the value is: "x567y"
What I want to extract is the two charachters after the parameter, in
this case "07".
Can anyone shed some light on this problem?
Thanks,
lqLauren Quantrell (laurenquantrell@.hotmail.com) writes:
> Is there anyway to extract part of a string in a stored procedure
> using a parameter as the starting point?
> For example, my string might read: x234y01zx567y07zx541y04z
> My Parameter is an nvarchar and the value is: "x567y"
> What I want to extract is the two charachters after the parameter, in
> this case "07".
> Can anyone shed some light on this problem?

Looks like a combination of substring and charindex (or possibly
patindex) is what you need. I recommend that you use the SQL Server
Books Online to study all the string functions that SQL Server
offers. They are not that many, and not that extremely powerful, but
it's very useful to know them.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks.
I'm on a crash project using MDSE and don't have immediate access to
Books Online though...
lq

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns946FEC5AC5081Yazorman@.127.0.0.1>...
> Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> > Is there anyway to extract part of a string in a stored procedure
> > using a parameter as the starting point?
> > For example, my string might read: x234y01zx567y07zx541y04z
> > My Parameter is an nvarchar and the value is: "x567y"
> > What I want to extract is the two charachters after the parameter, in
> > this case "07".
> > Can anyone shed some light on this problem?
> Looks like a combination of substring and charindex (or possibly
> patindex) is what you need. I recommend that you use the SQL Server
> Books Online to study all the string functions that SQL Server
> offers. They are not that many, and not that extremely powerful, but
> it's very useful to know them.|||I figured out how to do this:

substring(mystring,charindex(@.parameter,myString)+ len(@.parameter),2)

where @.parameter = 'x' + [myUserID] + 'y'

Thanks for pointing me in the right direction.

lq

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns946FEC5AC5081Yazorman@.127.0.0.1>...
> Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> > Is there anyway to extract part of a string in a stored procedure
> > using a parameter as the starting point?
> > For example, my string might read: x234y01zx567y07zx541y04z
> > My Parameter is an nvarchar and the value is: "x567y"
> > What I want to extract is the two charachters after the parameter, in
> > this case "07".
> > Can anyone shed some light on this problem?
> Looks like a combination of substring and charindex (or possibly
> patindex) is what you need. I recommend that you use the SQL Server
> Books Online to study all the string functions that SQL Server
> offers. They are not that many, and not that extremely powerful, but
> it's very useful to know them.|||Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> I'm on a crash project using MDSE and don't have immediate access to
> Books Online though...

You have. Check my signature.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Externally Linked Reports

Let's say I have a report which displays employee details based on employee Id. The Employee Id is passed to the report as a parameter. Lets say you can select this from the dropdown list on the report. All of these reports are deployed on report server 2005. What I also have is another application where you manage the employee data.

What I want to do is put a link in the management application to the eomployee detail report and pass the employee id in the link, so that the user does not have to select the employee id on the report. I know that we can provide a link to the reports, but I don't know if we can also pass parameters to those reports in that link.

Any help is appreciated, thanks!!

This is pretty straight forward. We are doing the same thing like so:

http://domainname/reportserver/Pages/ReportViewer.aspx?%2fOurReports%2fReportName&rs:Command=Render&Parameter=InsertParamValueHere

Essentially you can just add &Parameter=Value to the URL to pass the parameter.

|||

All you need to use is a query string. For example:http://servername/appname/page.aspx?id=EMPLOYEE_ID

Cheers!

External Images (URL Based) don't display on deployed reports

Hello,
I have a RS 2005 report which uses a report parameter as the source for an
image external image. When in report designer, everything works fine. When
deployed, the image shows as red x.
I am not using unattended credentials since the image is accessible without
credentials. I even tried images from microsoft.com and cnn.com
Any help would be appreciated.
-PedroHi Pedro,
Probabily the image directory is not proper. Just right click on the Red x
or view source to see where the image points to and check the directory for
your image. Better to include it in your project so that the link is not lost.
Amarnath
"Pedro" wrote:
> Hello,
> I have a RS 2005 report which uses a report parameter as the source for an
> image external image. When in report designer, everything works fine. When
> deployed, the image shows as red x.
> I am not using unattended credentials since the image is accessible without
> credentials. I even tried images from microsoft.com and cnn.com
> Any help would be appreciated.
> -Pedro
>
>

Extended Stored Procedures 7.0 - 2000

I have a problem with passing parameters into an extended stored procedure in sql 2000 that was not present in sql 7.0.

I pass in a parameter of type varchar(8000) but sql 2000 truncates this value to 255 characters.

I was using srv_paramdata(sproc,1) for example to get the pertinent data. Microsoft says that srv_paramdata has been superceded by srv_paraminfo, but this function is crashing my dll when I run it....

BYTE bType;
unsigned long cbMaxLen;
unsigned long cbActualLen;
BOOL fNull;
BYTE data;

// Use srv_paraminfo to get data type and length information.
srv_paraminfo(sproc, 2, &bType, &cbMaxLen, &cbActualLen,&data, &fNull);

Any ideas?

This worked in sql 7.0.

wsprintf(string,"%s",(const char*) srv_paramdata(sproc,1));

Umm help appreciated.

WintermuteUmm actual guys... thanks to anyone who has looked to help me out, but I think I have (*this).sorted->muchos[gracias++];

umm in keeping with Open Source Software paradigms and stuff if anyone is curious

PBYTE*data;
BYTE type;
unsigned long Maxlen;
unsigned long Reallen;
int nParams;
BOOL boolnull;
char head_descriptor[24];
FILE*file;

nParams=srv_rpcparams(sproc);


data=new PBYTE[1];
if(data==NULL)
{
ServerErrorMsg(sproc,"Was unable to allocate the requisite memory for this data operation");
return -1;
};
memset(data,0,nParams*sizeof(PBYTE));


srv_paraminfo(sproc,2,&type,&Maxlen,&Reallen,NULL,&boolnull);

sprintf(head_descriptor,"Parameter 2: Input");
srv_describe(sproc,2,head_descriptor,SRV_NULLTERM, type,Reallen,type,Reallen,NULL);

if(boolnull==0)
{
data[0]=(unsigned char*)malloc(Reallen);
if(data[0]==NULL)
{
ServerErrorMsg(sproc,"Unable to allocate memory for this variable!");
delete data;
return -1;
};

srv_paraminfo(sproc,2,&type,&Maxlen,&Reallen,data[0],&boolnull);
}
else
{
ServerErrorMsg(sproc,"There Seems to be no data present for parameter 2");
return -1;
};

file=fopen("C:/bod.txt","a+");
if(file==NULL)
{
ServerErrorMsg(sproc,"Unable to access the specified filename for file input, Please check the filename and try again!");
return -1;
};

fwrite(data[0],Reallen,1,file);
fclose(file);


return 0;
};

Regards
Wintermute.sql

Expressions for parameter values?

Hello all... im trying to figure out... how can i use an expression in a
parameter formula?
I have a report which takes a date parameter. I want to make a subscription
using last week as the date. So for example, i want to set up the
subscription to run every Sunday, and use a value of TODAY() - 7. If i enter
that as a value though, it says its an invalid type. I also tried it with an
equal sign on the front ( =TODAY()-6 ).
Is there a way to do this?
Thanks in advance,
- Arthur Dent.You can use the DateAdd function to add a day/hour/minute/month etc to a
date.
--
Rajeev Karunakaran [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
news:u7NwrsrSFHA.3140@.TK2MSFTNGP14.phx.gbl...
> Hello all... im trying to figure out... how can i use an expression in a
> parameter formula?
> I have a report which takes a date parameter. I want to make a
> subscription using last week as the date. So for example, i want to set up
> the subscription to run every Sunday, and use a value of TODAY() - 7. If i
> enter that as a value though, it says its an invalid type. I also tried it
> with an equal sign on the front ( =TODAY()-6 ).
> Is there a way to do this?
> Thanks in advance,
> - Arthur Dent.
>|||Thanks for the reply. Unfortunately, that doesnt seem to work either. When i
type in "DATEADD(d,-7,TODAY())" for my parameter, i get an error as so:
The value provided for the report parameter 'ForWeekOf' is not valid for its
type. (rsReportParameterTypeMismatch)
TIA-
"Rajeev Karunakaran" <rajeevkarunakaran@.online.microsoft.com> wrote in
message news:edMnSbsSFHA.3672@.TK2MSFTNGP10.phx.gbl...
> You can use the DateAdd function to add a day/hour/minute/month etc to a
> date.
> --
> Rajeev Karunakaran [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
> news:u7NwrsrSFHA.3140@.TK2MSFTNGP14.phx.gbl...
>> Hello all... im trying to figure out... how can i use an expression in a
>> parameter formula?
>> I have a report which takes a date parameter. I want to make a
>> subscription using last week as the date. So for example, i want to set
>> up the subscription to run every Sunday, and use a value of TODAY() - 7.
>> If i enter that as a value though, it says its an invalid type. I also
>> tried it with an equal sign on the front ( =TODAY()-6 ).
>> Is there a way to do this?
>> Thanks in advance,
>> - Arthur Dent.
>|||It sounds like you are trying to type an expression into the date field in
report manager. This won't work - you can only type date constants there.
You should rather load the report in report designer and set the default
value of the date parameter to something like =Today.AddDays(-7). Before
publishing to the report server, make sure to delete the existing report
*before* publishing.
Then, you can create a subscription which will use an expression-based
default value for the date parameter.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
news:eYecTDtSFHA.3184@.TK2MSFTNGP14.phx.gbl...
> Thanks for the reply. Unfortunately, that doesnt seem to work either. When
> i type in "DATEADD(d,-7,TODAY())" for my parameter, i get an error as so:
> The value provided for the report parameter 'ForWeekOf' is not valid for
> its type. (rsReportParameterTypeMismatch)
> TIA-
>
> "Rajeev Karunakaran" <rajeevkarunakaran@.online.microsoft.com> wrote in
> message news:edMnSbsSFHA.3672@.TK2MSFTNGP10.phx.gbl...
>> You can use the DateAdd function to add a day/hour/minute/month etc to a
>> date.
>> --
>> Rajeev Karunakaran [MSFT]
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
>> news:u7NwrsrSFHA.3140@.TK2MSFTNGP14.phx.gbl...
>> Hello all... im trying to figure out... how can i use an expression in
>> a parameter formula?
>> I have a report which takes a date parameter. I want to make a
>> subscription using last week as the date. So for example, i want to set
>> up the subscription to run every Sunday, and use a value of TODAY() - 7.
>> If i enter that as a value though, it says its an invalid type. I also
>> tried it with an equal sign on the front ( =TODAY()-6 ).
>> Is there a way to do this?
>> Thanks in advance,
>> - Arthur Dent.
>>
>|||Ah, yes, that sounds like it would do exactly what i need.
Thanks!!
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:O69jArtSFHA.2556@.TK2MSFTNGP12.phx.gbl...
> It sounds like you are trying to type an expression into the date field in
> report manager. This won't work - you can only type date constants there.
> You should rather load the report in report designer and set the default
> value of the date parameter to something like =Today.AddDays(-7). Before
> publishing to the report server, make sure to delete the existing report
> *before* publishing.
> Then, you can create a subscription which will use an expression-based
> default value for the date parameter.
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
> news:eYecTDtSFHA.3184@.TK2MSFTNGP14.phx.gbl...
>> Thanks for the reply. Unfortunately, that doesnt seem to work either.
>> When i type in "DATEADD(d,-7,TODAY())" for my parameter, i get an error
>> as so:
>> The value provided for the report parameter 'ForWeekOf' is not valid for
>> its type. (rsReportParameterTypeMismatch)
>> TIA-
>>
>> "Rajeev Karunakaran" <rajeevkarunakaran@.online.microsoft.com> wrote in
>> message news:edMnSbsSFHA.3672@.TK2MSFTNGP10.phx.gbl...
>> You can use the DateAdd function to add a day/hour/minute/month etc to a
>> date.
>> --
>> Rajeev Karunakaran [MSFT]
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
>> news:u7NwrsrSFHA.3140@.TK2MSFTNGP14.phx.gbl...
>> Hello all... im trying to figure out... how can i use an expression in
>> a parameter formula?
>> I have a report which takes a date parameter. I want to make a
>> subscription using last week as the date. So for example, i want to set
>> up the subscription to run every Sunday, and use a value of TODAY() -
>> 7. If i enter that as a value though, it says its an invalid type. I
>> also tried it with an equal sign on the front ( =TODAY()-6 ).
>> Is there a way to do this?
>> Thanks in advance,
>> - Arthur Dent.
>>
>>
>

Expression to Parameter

I would like my header text not to be hard coded

I took a very big table which include texts i need from my DB to DS

How can i pick a single row with single data in it to be bind

to my Paramter ?

Could you explain this in more detail ?

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Expression Problem Using a Multi-Select Parameter

I have a rectangle region in a report that contains a graph and a table. I want to display that list region only when the user selects a "Select All" from a multi-select report parameter. This rectangle region is used only to display summary data for All Agencies.

My report also contains a list region with graphs and tables, where I display data for each agency (my detail group), and page-break on each agency.

The problem I am experiencing occurs when using the Expression Builder for the Visibility property for my rectangle and list regions. Since a multi-select parameter is an array, I am forced to select an element in my paramater such as =Parameters!Agency.Value(0). When the user chooses "(Select All)", the first element is the first agency in the list. I don't want that.

How can I get Reporting Services to display a rectangle or list region when "Select All" is chosen, and to hide that rectangle or list region when one or more agencies are chosen from a multi-select parameter?

I have tried using Agency.Label and I've tried other expressions such as Parameters!Agency.Count = Count(Agency.Value), etc, without success.

If you're on SP0 or SP2 or later, the Select All option is always there. It's not really a checkbox you can detect. It's just a shortcut way for selecting/deselecting all options.

I've reported this as an enhancement. You should be able to tell whether they've selected all possible options:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124515

At that link I describe a couple of possible workarounds. Hope that helps.

|||

There is currently no built-in functionality, but here are some ideas to achieve what you are looking for:

* if the multi value parameter has a pre-defined (constant) list of valid values, you know how many values are available for selection. The report parameters in RS 2005 expose a new property called .Count which tells you the count of selected parameter values (e.g. =Parameters!P1.Count). Hence, you could compare the count of the selected values with the count of the total values.

* if the multi value parameter has a dataset-based valid values list, you could just use the same field in a CountDistinct aggregate function to determine how many valid values are available, e.g. =CountDistinct(Fields!A.Value) and compare it again with the Count of selected values (e.g. =Parameters!P1.Count).

-- Robert

expression in Visibility>Hidden field = No output to csv

Hi all,
I have a problem with a report I have created. It has around 52 columns and each column is shown or hidden based on a boolean parameter. Simple huh? I though so.

Each column has an expression similar to =IIF(Parameters!showfirstname.Value,False,True) for the Hidden field. This is not the hidden field for the 'cell' or 'header' but for the entire column.

The problem is, the report is correctly displayed as a pdf, tiff, excel file (possibly others), but all columns with an expression as the hidden value are not displayed in the xml or csv output regardless of the parameter value. This also applies if the expression is =IIF(True,False,True) or =IIF(1=1,False,True).

As soon as I change this field back to a simple 'True' or 'False' it displays correctly. I've tried playing around with setting the output options to values other than the default Auto setting to no avail.

There are numerous comments about this on newsgroups online going back to the first release of reporting services but none of them have solutions.

Regards

John Burns

John,

You can't conditionally hide and show data in data renderers (CSV, XML). This is by design. If you have an expression in the Hidden field and 'Auto' in DataOutput tab for text boxes in the column, your data will not be rendered into CSV or XML.

You can set DataElementOutput to Output for textboxes in the cells and in the header, and the coulmn will always be in the output file.

Thanks!

|||

You could in SQL 2000!

I've just upgraded to 2005 and exports to csv format no longer work whereas they did in SQL 2000. I've tracked the culpit down to the visibility statement. If there is an expression

e.g.

=IIf(1=1, false, true)

against the table then the data will render in all formats except csv\xml. This is a breaking change that has been introduced in 2005 so I would expect that microsoft would consider fixing it or producing a hot fix for affected systems. Note that I haven't applied any service packs but haven't seen anything related to this issue in the SP's.

Try this example report which queries sysobjects in the master database (change the data source first)

<?xml version="1.0" encoding="utf-8"?>

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

<DataSources>

<DataSource Name="DataSource1">

<ConnectionProperties>

<IntegratedSecurity>true</IntegratedSecurity>

<ConnectString>Data Source=SQLServer;Initial Catalog=master</ConnectString>

<DataProvider>SQL</DataProvider>

</ConnectionProperties>

<rd:DataSourceID>145254b5-d42f-46f6-a007-7898e2fb93b4</rd:DataSourceID>

</DataSource>

</DataSources>

<BottomMargin>2.5cm</BottomMargin>

<RightMargin>2.5cm</RightMargin>

<PageWidth>21cm</PageWidth>

<rd:DrawGrid>true</rd:DrawGrid>

<InteractiveWidth>8.5in</InteractiveWidth>

<rd:GridSpacing>0.25cm</rd:GridSpacing>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<ColumnSpacing>1cm</ColumnSpacing>

<ReportItems>

<Textbox Name="textbox1">

<rd:DefaultName>textbox1</rd:DefaultName>

<ZIndex>1</ZIndex>

<Style>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontFamily>Tahoma</FontFamily>

<FontWeight>700</FontWeight>

<FontSize>20pt</FontSize>

<Color>SteelBlue</Color>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Height>0.91429cm</Height>

<Value>Report1</Value>

</Textbox>

<Table Name="table1">

<DataSetName>DataSource1</DataSetName>

<Top>0.91429cm</Top>

<Visibility>

<Hidden>=IIf(1=1, false, true)</Hidden>

</Visibility>

<Width>5.07936cm</Width>

<Details>

<TableRows>

<TableRow>

<TableCells>

<TableCell>

<ReportItems>

<Textbox Name="name">

<DataElementOutput>Output</DataElementOutput>

<rd:DefaultName>name</rd:DefaultName>

<ZIndex>1</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontFamily>Tahoma</FontFamily>

<BorderColor>

<Default>LightGrey</Default>

</BorderColor>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Fields!name.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="id">

<DataElementOutput>Output</DataElementOutput>

<rd:DefaultName>id</rd:DefaultName>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontFamily>Tahoma</FontFamily>

<BorderColor>

<Default>LightGrey</Default>

</BorderColor>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Fields!id.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

<Height>0.53333cm</Height>

</TableRow>

</TableRows>

</Details>

<Header>

<TableRows>

<TableRow>

<TableCells>

<TableCell>

<ReportItems>

<Textbox Name="textbox2">

<rd:DefaultName>textbox2</rd:DefaultName>

<ZIndex>3</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontFamily>Tahoma</FontFamily>

<FontWeight>700</FontWeight>

<FontSize>11pt</FontSize>

<BorderColor>

<Default>LightGrey</Default>

</BorderColor>

<BackgroundColor>SteelBlue</BackgroundColor>

<Color>White</Color>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>name</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="textbox3">

<rd:DefaultName>textbox3</rd:DefaultName>

<ZIndex>2</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontFamily>Tahoma</FontFamily>

<FontWeight>700</FontWeight>

<FontSize>11pt</FontSize>

<BorderColor>

<Default>LightGrey</Default>

</BorderColor>

<BackgroundColor>SteelBlue</BackgroundColor>

<Color>White</Color>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>id</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

<Height>0.55873cm</Height>

</TableRow>

</TableRows>

<RepeatOnNewPage>true</RepeatOnNewPage>

</Header>

<TableColumns>

<TableColumn>

<Width>2.53968cm</Width>

</TableColumn>

<TableColumn>

<Width>2.53968cm</Width>

</TableColumn>

</TableColumns>

</Table>

</ReportItems>

<Height>2.00635cm</Height>

</Body>

<rd:ReportID>10fa5646-bbb8-452c-b281-7f6cb1fc3a6c</rd:ReportID>

<LeftMargin>2.5cm</LeftMargin>

<DataSets>

<DataSet Name="DataSource1">

<Query>

<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

<CommandText>select * from sysobjects</CommandText>

<DataSourceName>DataSource1</DataSourceName>

</Query>

<Fields>

<Field Name="name">

<rd:TypeName>System.String</rd:TypeName>

<DataField>name</DataField>

</Field>

<Field Name="id">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>id</DataField>

</Field>

<Field Name="xtype">

<rd:TypeName>System.String</rd:TypeName>

<DataField>xtype</DataField>

</Field>

<Field Name="uid">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>uid</DataField>

</Field>

<Field Name="info">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>info</DataField>

</Field>

<Field Name="status">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>status</DataField>

</Field>

<Field Name="base_schema_ver">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>base_schema_ver</DataField>

</Field>

<Field Name="replinfo">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>replinfo</DataField>

</Field>

<Field Name="parent_obj">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>parent_obj</DataField>

</Field>

<Field Name="crdate">

<rd:TypeName>System.DateTime</rd:TypeName>

<DataField>crdate</DataField>

</Field>

<Field Name="ftcatid">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>ftcatid</DataField>

</Field>

<Field Name="schema_ver">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>schema_ver</DataField>

</Field>

<Field Name="stats_schema_ver">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>stats_schema_ver</DataField>

</Field>

<Field Name="type">

<rd:TypeName>System.String</rd:TypeName>

<DataField>type</DataField>

</Field>

<Field Name="userstat">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>userstat</DataField>

</Field>

<Field Name="sysstat">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>sysstat</DataField>

</Field>

<Field Name="indexdel">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>indexdel</DataField>

</Field>

<Field Name="refdate">

<rd:TypeName>System.DateTime</rd:TypeName>

<DataField>refdate</DataField>

</Field>

<Field Name="version">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>version</DataField>

</Field>

<Field Name="deltrig">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>deltrig</DataField>

</Field>

<Field Name="instrig">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>instrig</DataField>

</Field>

<Field Name="updtrig">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>updtrig</DataField>

</Field>

<Field Name="seltrig">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>seltrig</DataField>

</Field>

<Field Name="category">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>category</DataField>

</Field>

<Field Name="cache">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>cache</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

<Width>12.69841cm</Width>

<InteractiveHeight>11in</InteractiveHeight>

<Language>en-US</Language>

<TopMargin>2.5cm</TopMargin>

<PageHeight>29.7cm</PageHeight>

</Report>

expression in Visibility>Hidden field = No output to csv

Hi all,
I have a problem with a report I have created. It has around 52 columns and each column is shown or hidden based on a boolean parameter. Simple huh? I though so.

Each column has an expression similar to =IIF(Parameters!showfirstname.Value,False,True) for the Hidden field. This is not the hidden field for the 'cell' or 'header' but for the entire column.

The problem is, the report is correctly displayed as a pdf, tiff, excel file (possibly others), but all columns with an expression as the hidden value are not displayed in the xml or csv output regardless of the parameter value. This also applies if the expression is =IIF(True,False,True) or =IIF(1=1,False,True).

As soon as I change this field back to a simple 'True' or 'False' it displays correctly. I've tried playing around with setting the output options to values other than the default Auto setting to no avail.

There are numerous comments about this on newsgroups online going back to the first release of reporting services but none of them have solutions.

Regards

John Burns

John,

You can't conditionally hide and show data in data renderers (CSV, XML). This is by design. If you have an expression in the Hidden field and 'Auto' in DataOutput tab for text boxes in the column, your data will not be rendered into CSV or XML.

You can set DataElementOutput to Output for textboxes in the cells and in the header, and the coulmn will always be in the output file.

Thanks!

|||

You could in SQL 2000!

I've just upgraded to 2005 and exports to csv format no longer work whereas they did in SQL 2000. I've tracked the culpit down to the visibility statement. If there is an expression

e.g.

=IIf(1=1, false, true)

against the table then the data will render in all formats except csv\xml. This is a breaking change that has been introduced in 2005 so I would expect that microsoft would consider fixing it or producing a hot fix for affected systems. Note that I haven't applied any service packs but haven't seen anything related to this issue in the SP's.

Try this example report which queries sysobjects in the master database (change the data source first)

<?xml version="1.0" encoding="utf-8"?>

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

<DataSources>

<DataSource Name="DataSource1">

<ConnectionProperties>

<IntegratedSecurity>true</IntegratedSecurity>

<ConnectString>Data Source=SQLServer;Initial Catalog=master</ConnectString>

<DataProvider>SQL</DataProvider>

</ConnectionProperties>

<rd:DataSourceID>145254b5-d42f-46f6-a007-7898e2fb93b4</rd:DataSourceID>

</DataSource>

</DataSources>

<BottomMargin>2.5cm</BottomMargin>

<RightMargin>2.5cm</RightMargin>

<PageWidth>21cm</PageWidth>

<rd:DrawGrid>true</rd:DrawGrid>

<InteractiveWidth>8.5in</InteractiveWidth>

<rd:GridSpacing>0.25cm</rd:GridSpacing>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<ColumnSpacing>1cm</ColumnSpacing>

<ReportItems>

<Textbox Name="textbox1">

<rd:DefaultName>textbox1</rd:DefaultName>

<ZIndex>1</ZIndex>

<Style>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontFamily>Tahoma</FontFamily>

<FontWeight>700</FontWeight>

<FontSize>20pt</FontSize>

<Color>SteelBlue</Color>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Height>0.91429cm</Height>

<Value>Report1</Value>

</Textbox>

<Table Name="table1">

<DataSetName>DataSource1</DataSetName>

<Top>0.91429cm</Top>

<Visibility>

<Hidden>=IIf(1=1, false, true)</Hidden>

</Visibility>

<Width>5.07936cm</Width>

<Details>

<TableRows>

<TableRow>

<TableCells>

<TableCell>

<ReportItems>

<Textbox Name="name">

<DataElementOutput>Output</DataElementOutput>

<rd:DefaultName>name</rd:DefaultName>

<ZIndex>1</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontFamily>Tahoma</FontFamily>

<BorderColor>

<Default>LightGrey</Default>

</BorderColor>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Fields!name.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="id">

<DataElementOutput>Output</DataElementOutput>

<rd:DefaultName>id</rd:DefaultName>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontFamily>Tahoma</FontFamily>

<BorderColor>

<Default>LightGrey</Default>

</BorderColor>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Fields!id.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

<Height>0.53333cm</Height>

</TableRow>

</TableRows>

</Details>

<Header>

<TableRows>

<TableRow>

<TableCells>

<TableCell>

<ReportItems>

<Textbox Name="textbox2">

<rd:DefaultName>textbox2</rd:DefaultName>

<ZIndex>3</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontFamily>Tahoma</FontFamily>

<FontWeight>700</FontWeight>

<FontSize>11pt</FontSize>

<BorderColor>

<Default>LightGrey</Default>

</BorderColor>

<BackgroundColor>SteelBlue</BackgroundColor>

<Color>White</Color>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>name</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="textbox3">

<rd:DefaultName>textbox3</rd:DefaultName>

<ZIndex>2</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontFamily>Tahoma</FontFamily>

<FontWeight>700</FontWeight>

<FontSize>11pt</FontSize>

<BorderColor>

<Default>LightGrey</Default>

</BorderColor>

<BackgroundColor>SteelBlue</BackgroundColor>

<Color>White</Color>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>id</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

<Height>0.55873cm</Height>

</TableRow>

</TableRows>

<RepeatOnNewPage>true</RepeatOnNewPage>

</Header>

<TableColumns>

<TableColumn>

<Width>2.53968cm</Width>

</TableColumn>

<TableColumn>

<Width>2.53968cm</Width>

</TableColumn>

</TableColumns>

</Table>

</ReportItems>

<Height>2.00635cm</Height>

</Body>

<rd:ReportID>10fa5646-bbb8-452c-b281-7f6cb1fc3a6c</rd:ReportID>

<LeftMargin>2.5cm</LeftMargin>

<DataSets>

<DataSet Name="DataSource1">

<Query>

<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

<CommandText>select * from sysobjects</CommandText>

<DataSourceName>DataSource1</DataSourceName>

</Query>

<Fields>

<Field Name="name">

<rd:TypeName>System.String</rd:TypeName>

<DataField>name</DataField>

</Field>

<Field Name="id">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>id</DataField>

</Field>

<Field Name="xtype">

<rd:TypeName>System.String</rd:TypeName>

<DataField>xtype</DataField>

</Field>

<Field Name="uid">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>uid</DataField>

</Field>

<Field Name="info">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>info</DataField>

</Field>

<Field Name="status">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>status</DataField>

</Field>

<Field Name="base_schema_ver">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>base_schema_ver</DataField>

</Field>

<Field Name="replinfo">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>replinfo</DataField>

</Field>

<Field Name="parent_obj">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>parent_obj</DataField>

</Field>

<Field Name="crdate">

<rd:TypeName>System.DateTime</rd:TypeName>

<DataField>crdate</DataField>

</Field>

<Field Name="ftcatid">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>ftcatid</DataField>

</Field>

<Field Name="schema_ver">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>schema_ver</DataField>

</Field>

<Field Name="stats_schema_ver">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>stats_schema_ver</DataField>

</Field>

<Field Name="type">

<rd:TypeName>System.String</rd:TypeName>

<DataField>type</DataField>

</Field>

<Field Name="userstat">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>userstat</DataField>

</Field>

<Field Name="sysstat">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>sysstat</DataField>

</Field>

<Field Name="indexdel">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>indexdel</DataField>

</Field>

<Field Name="refdate">

<rd:TypeName>System.DateTime</rd:TypeName>

<DataField>refdate</DataField>

</Field>

<Field Name="version">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>version</DataField>

</Field>

<Field Name="deltrig">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>deltrig</DataField>

</Field>

<Field Name="instrig">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>instrig</DataField>

</Field>

<Field Name="updtrig">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>updtrig</DataField>

</Field>

<Field Name="seltrig">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>seltrig</DataField>

</Field>

<Field Name="category">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>category</DataField>

</Field>

<Field Name="cache">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>cache</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

<Width>12.69841cm</Width>

<InteractiveHeight>11in</InteractiveHeight>

<Language>en-US</Language>

<TopMargin>2.5cm</TopMargin>

<PageHeight>29.7cm</PageHeight>

</Report>

Expression help on dataset parameter value

I am trying to use and expression in the parameters tab on the dataset. I
have a parameter called END_DATE and I want it to be equal to the expression
=DATEADD(DateInterval.Day, 1, Parameters!END_DATE.Value)
The expression comes out fine on the report. I tested it there to see if it
would generate the proper date and it did. Then I moved the expression from
the report and into the Value side of the Parameter on the Parameter tab of
the dataset and
I get an error CLI0111E Numeric value out of range SQLSTATE=22003
on this (I am using DB2)It probably has to be a DB2 function in the dataset...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
news:596479BF-E8BE-4475-A7A8-912BAAE61CCE@.microsoft.com...
>I am trying to use and expression in the parameters tab on the dataset. I
> have a parameter called END_DATE and I want it to be equal to the
> expression
> =DATEADD(DateInterval.Day, 1, Parameters!END_DATE.Value)
> The expression comes out fine on the report. I tested it there to see if
> it
> would generate the proper date and it did. Then I moved the expression
> from
> the report and into the Value side of the Parameter on the Parameter tab
> of
> the dataset and
> I get an error CLI0111E Numeric value out of range SQLSTATE=22003
> on this (I am using DB2)|||Sorry Wayne ... I am not sure what you mean by your reply. The dataset
consists of just a stored procedure. In the dataset tab this is all there is
- -
GRSINST1.SP_RPT_RES_UPTIME
how would I make this a DB2 function in the dataset?
"Wayne Snyder" wrote:
> It probably has to be a DB2 function in the dataset...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
> news:596479BF-E8BE-4475-A7A8-912BAAE61CCE@.microsoft.com...
> >I am trying to use and expression in the parameters tab on the dataset. I
> > have a parameter called END_DATE and I want it to be equal to the
> > expression
> > =DATEADD(DateInterval.Day, 1, Parameters!END_DATE.Value)
> >
> > The expression comes out fine on the report. I tested it there to see if
> > it
> > would generate the proper date and it did. Then I moved the expression
> > from
> > the report and into the Value side of the Parameter on the Parameter tab
> > of
> > the dataset and
> > I get an error CLI0111E Numeric value out of range SQLSTATE=22003
> > on this (I am using DB2)
>
>|||I must have forgotten to say that I am using a db2 stored procedure and
passing it parameters so I dont know where else I can manipulate the parm
since it is used for the query. I thought I had read that you can use
expressions on the parameter tab of the dataset. So why cant I get this
expression to work. It is fairly simple and it works when I put it on the
report (which I did just to verify syntax).
"Wayne Snyder" wrote:
> It probably has to be a DB2 function in the dataset...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
> news:596479BF-E8BE-4475-A7A8-912BAAE61CCE@.microsoft.com...
> >I am trying to use and expression in the parameters tab on the dataset. I
> > have a parameter called END_DATE and I want it to be equal to the
> > expression
> > =DATEADD(DateInterval.Day, 1, Parameters!END_DATE.Value)
> >
> > The expression comes out fine on the report. I tested it there to see if
> > it
> > would generate the proper date and it did. Then I moved the expression
> > from
> > the report and into the Value side of the Parameter on the Parameter tab
> > of
> > the dataset and
> > I get an error CLI0111E Numeric value out of range SQLSTATE=22003
> > on this (I am using DB2)
>
>