datasets and parameters

I am trying to put up together a report with several parameters. The first
parameter is independent, the second one, though depends of this first one.
Is there a way to link them somehow, so that when i pick my first parameter,
the second dataset I'm using for my second parameter will be populated based
on the value of my first parameter.
Simple example if it's still not clear: i have a table with 3 columns, 10
rows. My 1st param is the 1st col, my 2nd param is the 2nd col. Initially the
report brings back all 10 rows. There are 2 distinct values in the 1st col,
and 3 distinct values in the 2nd col - one distinct value on the 1st col is
associated with 2 values in 2nd col, one with the other remaining.
I NEED TO HAVE MY SECOND PARAM POPULATED WITH 2 values WHEN I PICK one value
of the first parameter and 1 value when I pick the other one.
Thank you,
kowalskylook up Cascading Parameters in books online or searching the same in this
newsgroup.
"kowalsky" wrote:
> I am trying to put up together a report with several parameters. The first
> parameter is independent, the second one, though depends of this first one.
> Is there a way to link them somehow, so that when i pick my first parameter,
> the second dataset I'm using for my second parameter will be populated based
> on the value of my first parameter.
> Simple example if it's still not clear: i have a table with 3 columns, 10
> rows. My 1st param is the 1st col, my 2nd param is the 2nd col. Initially the
> report brings back all 10 rows. There are 2 distinct values in the 1st col,
> and 3 distinct values in the 2nd col - one distinct value on the 1st col is
> associated with 2 values in 2nd col, one with the other remaining.
> I NEED TO HAVE MY SECOND PARAM POPULATED WITH 2 values WHEN I PICK one value
> of the first parameter and 1 value when I pick the other one.
> Thank you,
> kowalsky

dataset using stored proc with multi select params

I have a stored proc that I'm using to run a report. It works fine, but currently I'm using a parameter that is a single selection from a dropdown. I'd like to use multi select, but have not been able to get it to work.

In the data tab I'm currently using "text" for command type and :

Code Snippet

declare @.sql nvarchar(2000)

set @.sql = '
EXEC [Monitor] '' + @.p_OfferStatus + '''

exec sp_executesql @.sql, N'@.p_OfferStatus VARCHAR(100)', @.p_OfferStatus = @.p_OfferStatus

when I run this in the data tab, it works fine, returning data, but when I try to preview it it tells me there are syntax errors. Anyone know the correct way to use multi selects with stored procs?

That depends on your code within the proc. You will normally have to split the values up to a table variable to make them searchable as they will be passed as 01,2,3,4. COuld you send your code over ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Hey, thanks for the reply. I can send the code, but you probobly dont need it, I'm just using
WHERE field IN(@.parameter)
|||

Code Snippet

SET @.v_sql = '
SELECT
a.clientname AS Client,
s.Suptype_ID,
s.[name] AS [The Name],
convert(int, GETDATE() - COALESCE(s.datelastprocessed, s.datecreated)) AS [Days Overdue],
CASE
WHEN ol. success_flag = 0 THEN ''FAILED''
WHEN ol. success_flag = 1 THEN ''SUCCESS''
ELSE ''''
END AS Processed,
s.datelastprocessed AS [Date Processed],
o.offerstatus_id AS [Offer Status],
CASE
WHEN s.advertiserlink LIKE ''%suptype_id=%'' THEN 2
WHEN ods.suptype_id IS NULL THEN 0
ELSE 1
END AS Auto
--ft.[File Name],
FROM #temp_suppressiontype s
LEFT OUTER JOIN #temp_1 ods ON ods.suptype_id = s.suptype_id
LEFT OUTER JOIN #temp_2 ol ON ol.suptype_id = s.suptype_id
LEFT OUTER JOIN #temp_3 p ON p.subtype_id = s.suptype_id
LEFT OUTER JOIN #temp_4 a ON a.clientkey_id = p.outsideadvertiser_clientkey_id
LEFT OUTER JOIN #temp_5 o ON o.suptype_id = s.suptype_id
LEFT OUTER JOIN #temp_6 os ON os.offerstatus_id = o.offerstatus_id
WHERE 1 = 1 ' +
CASE WHEN @.p_DaysOverdue IS NULL THEN
''
ELSE '
AND CONVERT(INT, GETDATE() - COALESCE(s.datelastprocessed, s.datecreated)) >= ' + CONVERT(VARCHAR, @.p_DaysOverdue)
END +
CASE WHEN @.p_OfferStatus IS NULL THEN
''
ELSE '
AND o.offerstatus_id IN (' + CONVERT(VARCHAR, @.p_OfferStatus) + ')'
END +
CASE WHEN @.p_Auto IS NULL THEN
''
ELSE '
AND CASE
WHEN s.advertiserlink LIKE ''%suptype_id=%'' THEN 2
WHEN ods.suptype_id IS NULL THEN 0
ELSE 1
END IN (' + CONVERT(VARCHAR, @.p_Auto) + ')'
END + '
ORDER BY 8 DESC, 1, 3 ASC'
PRINT @.v_sql
EXEC sp_executesql @.v_sql

|||

You cannot do this in your procedure, the parameters will be passed as a delimited string, this works if you are using directly a query in Reporting Service, but won′t work using the sttored procedure interface. See my Split function which you can use inside your SQL Server to chop the values and join them to the original query.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1331765&SiteID=1

Jens K. Suessmeyer

http://www.sqlserver2005.de

Dataset Results

I have created adataset to make a drop down list for a parameter. As shown:

SELECT distinct [Table] FROM [Database]

The results i get back are A, B & C. What i am trying to do is show a result as a different word.

i.e instead of displaying A is shows in drop down list as Apple, B shows Banana. etc.

Can anyone help please.

Thanks

Lookup the usage of case(sql server). This should get you your desired result.|||The parameter can use two columns of the returned dataset, one for the value column and one for the display column, getting this back from the query and binding it to the appropiate properties in the parameter setting should help.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Ok got it working.

select distinct

case [COLUMN NAME]

when 'A' then 'Apple'

when 'B' then 'Banana'

else 'Unknown'end

from [TABLE NAME]

where [COLUMN NAME] in ('A', 'B', 'C')

Dataset Query using Parameters

OK. I've got a tough one here. I am attempting to create a parameter .aspx
page that will pass in start date, end date and multiple storeIDs to the
report. A section of my query in the report dataset looks like this:
WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN @.paramStartDate AND
@.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN (@.paramStore))
The problem is at the end with the @.paramStore. It works if you pass it
just one StoreID. The syntax becomes ...StoreID IN ('1')
When you try to pass it more than one storeid, it blows up. The syntax
becomes ...StoreID IN ('1,2') and an error comes up saying that it cannot
convert '1,2' to datatype int. Is there a way to take these leading and
trailing apostrophes off or can you think of a workaround? Thanks.You have to do a dynamically generated SQL statement, like this:
= "SELECT ... WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN
@.paramStartDate AND > @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN
(" & @.paramStore & "))"
It builds the SQL statement on the fly, so you won't be able to use the
query designer after this. I may not have put the quotes in properly, but I
hope you get the idea.
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"BrianW" <BrianW@.discussions.microsoft.com> wrote in message
news:9FF1DC3A-463D-41BB-9130-631A5D0600FF@.microsoft.com...
> OK. I've got a tough one here. I am attempting to create a parameter
> .aspx
> page that will pass in start date, end date and multiple storeIDs to the
> report. A section of my query in the report dataset looks like this:
> WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN @.paramStartDate AND
> @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN (@.paramStore))
> The problem is at the end with the @.paramStore. It works if you pass it
> just one StoreID. The syntax becomes ...StoreID IN ('1')
> When you try to pass it more than one storeid, it blows up. The syntax
> becomes ...StoreID IN ('1,2') and an error comes up saying that it cannot
> convert '1,2' to datatype int. Is there a way to take these leading and
> trailing apostrophes off or can you think of a workaround? Thanks.|||Thanks Jeff but I can't seem to get this to work. It changes my quotation
marks to brackets and comes up with error "Identifier expected."
"Jeff A. Stucker" wrote:
> You have to do a dynamically generated SQL statement, like this:
> = "SELECT ... WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN
> @.paramStartDate AND > @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN
> (" & @.paramStore & "))"
> It builds the SQL statement on the fly, so you won't be able to use the
> query designer after this. I may not have put the quotes in properly, but I
> hope you get the idea.
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "BrianW" <BrianW@.discussions.microsoft.com> wrote in message
> news:9FF1DC3A-463D-41BB-9130-631A5D0600FF@.microsoft.com...
> > OK. I've got a tough one here. I am attempting to create a parameter
> > .aspx
> > page that will pass in start date, end date and multiple storeIDs to the
> > report. A section of my query in the report dataset looks like this:
> >
> > WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN @.paramStartDate AND
> > @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN (@.paramStore))
> >
> > The problem is at the end with the @.paramStore. It works if you pass it
> > just one StoreID. The syntax becomes ...StoreID IN ('1')
> >
> > When you try to pass it more than one storeid, it blows up. The syntax
> > becomes ...StoreID IN ('1,2') and an error comes up saying that it cannot
> > convert '1,2' to datatype int. Is there a way to take these leading and
> > trailing apostrophes off or can you think of a workaround? Thanks.
>
>|||My advice in this situation is to back up and make sure you can create the
appropriate string.
Create a report that has the report parameters and a textbox and nothing
else. In the textbox put in the expression. Now you should be able to copy
and paste the result into query analyzer and it should work. Sometimes just
seeing the result will let you know what you are doing wrong.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"BrianW" <BrianW@.discussions.microsoft.com> wrote in message
news:7236F404-0AB1-44C6-97A1-A601A1E2B738@.microsoft.com...
> Thanks Jeff but I can't seem to get this to work. It changes my quotation
> marks to brackets and comes up with error "Identifier expected."
> "Jeff A. Stucker" wrote:
> > You have to do a dynamically generated SQL statement, like this:
> >
> > = "SELECT ... WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN
> > @.paramStartDate AND > @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID
IN
> > (" & @.paramStore & "))"
> >
> > It builds the SQL statement on the fly, so you won't be able to use the
> > query designer after this. I may not have put the quotes in properly,
but I
> > hope you get the idea.
> >
> > Cheers,
> >
> > '(' Jeff A. Stucker
> > \
> >
> > Business Intelligence
> > www.criadvantage.com
> > ---
> > "BrianW" <BrianW@.discussions.microsoft.com> wrote in message
> > news:9FF1DC3A-463D-41BB-9130-631A5D0600FF@.microsoft.com...
> > > OK. I've got a tough one here. I am attempting to create a parameter
> > > .aspx
> > > page that will pass in start date, end date and multiple storeIDs to
the
> > > report. A section of my query in the report dataset looks like this:
> > >
> > > WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN @.paramStartDate AND
> > > @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN (@.paramStore))
> > >
> > > The problem is at the end with the @.paramStore. It works if you pass
it
> > > just one StoreID. The syntax becomes ...StoreID IN ('1')
> > >
> > > When you try to pass it more than one storeid, it blows up. The
syntax
> > > becomes ...StoreID IN ('1,2') and an error comes up saying that it
cannot
> > > convert '1,2' to datatype int. Is there a way to take these leading
and
> > > trailing apostrophes off or can you think of a workaround? Thanks.
> >
> >
> >

Dataset Query Parameter

I have a dataset query that contains the expression:
Cost * (1 - ABS(SIGN(DATEPART(mm, OrderDate) - DATEPART(mm,
DATEADD(DateInterval.Month, - 11, { fn NOW() })))))
I have had no luck replacing the { fn NOW() } with a parameter and was
wondering if it was at all possible.To use parameters in a dataset query expression, use @.ParameterName.
"Todd Simmons" wrote:
> I have a dataset query that contains the expression:
> Cost * (1 - ABS(SIGN(DATEPART(mm, OrderDate) - DATEPART(mm,
> DATEADD(DateInterval.Month, - 11, { fn NOW() })))))
> I have had no luck replacing the { fn NOW() } with a parameter and was
> wondering if it was at all possible.
>|||I tried that and get a "Syntax error or access violation"
"Harolds" wrote:
> To use parameters in a dataset query expression, use @.ParameterName.
> "Todd Simmons" wrote:
> > I have a dataset query that contains the expression:
> >
> > Cost * (1 - ABS(SIGN(DATEPART(mm, OrderDate) - DATEPART(mm,
> > DATEADD(DateInterval.Month, - 11, { fn NOW() })))))
> >
> > I have had no luck replacing the { fn NOW() } with a parameter and was
> > wondering if it was at all possible.
> >

dataset query based on a global parameter

I need to create a report that displays data based on the userid. all the
records have a userid field filled with many userid values. how can I use the
global paramerer userid in the criteria for my dataset. so when a user run
the report olny records with his or her userid are showncreate a parameter with default value the global parameter. then use that
parameter in the query. ofcourse you can't use caching for those reports
"DJJIII" wrote:
> I need to create a report that displays data based on the userid. all the
> records have a userid field filled with many userid values. how can I use the
> global paramerer userid in the criteria for my dataset. so when a user run
> the report olny records with his or her userid are shown|||You do not have to have a 1:1 mapping between query parameters and report
parameters. Doing it the way suggested here means you have to muck around
with hiding it (since you don't want people to change it).
Have your query parameter, let's call it @.UserID. RS will automatically
recreate a report parameter called UserID but we won't use it. Click on the
..., go to parameters. Map the query parameter @.UserID to the global . The
parameter dialog box has a name and value columns. On the value side switch
it to expression. This brings you to the expression builder where you can
pick the User!UserID global variable. One thing to note, this variable has
the domain as well as the user id, so if you don't want this you will have
to strip it off.
Now, go you your layout, Report->Parameters and delete the now unneeded
UserID report parameter.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Antoon" <Antoon@.discussions.microsoft.com> wrote in message
news:04C477F1-17E5-47C5-BB11-9EF31278F1CE@.microsoft.com...
> create a parameter with default value the global parameter. then use that
> parameter in the query. ofcourse you can't use caching for those reports
> "DJJIII" wrote:
>> I need to create a report that displays data based on the userid. all
>> the
>> records have a userid field filled with many userid values. how can I use
>> the
>> global paramerer userid in the criteria for my dataset. so when a user
>> run
>> the report olny records with his or her userid are shown|||great tip, that is indeed a lot more practical
"Bruce L-C [MVP]" wrote:
> You do not have to have a 1:1 mapping between query parameters and report
> parameters. Doing it the way suggested here means you have to muck around
> with hiding it (since you don't want people to change it).
> Have your query parameter, let's call it @.UserID. RS will automatically
> recreate a report parameter called UserID but we won't use it. Click on the
> ..., go to parameters. Map the query parameter @.UserID to the global . The
> parameter dialog box has a name and value columns. On the value side switch
> it to expression. This brings you to the expression builder where you can
> pick the User!UserID global variable. One thing to note, this variable has
> the domain as well as the user id, so if you don't want this you will have
> to strip it off.
> Now, go you your layout, Report->Parameters and delete the now unneeded
> UserID report parameter.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Antoon" <Antoon@.discussions.microsoft.com> wrote in message
> news:04C477F1-17E5-47C5-BB11-9EF31278F1CE@.microsoft.com...
> > create a parameter with default value the global parameter. then use that
> > parameter in the query. ofcourse you can't use caching for those reports
> >
> > "DJJIII" wrote:
> >
> >> I need to create a report that displays data based on the userid. all
> >> the
> >> records have a userid field filled with many userid values. how can I use
> >> the
> >> global paramerer userid in the criteria for my dataset. so when a user
> >> run
> >> the report olny records with his or her userid are shown
>
>

Dataset filtering... how?

Hi all,

I want to create a summary page for one of my report, depending on a
parameter, I would like to filter the dataset before creating the summary
fields.

I cannot figure out how to perform this filtering, my approach has been to
insert a Table control and map it to my dataset, then try and do some
filtering via the properties. When i then put something like

=sum(Fields!Sales.Value)

into the header (such that it is shown only once, not repeated), I get the
sum for the full dataset, not the filtered one.

Please help, is there any easier way todo this?

Lastly, I can't filter at the SQL level as the dataset is being used to
populate a chart below the summary.

Advice welcome.

Please help
Taz

Put the filter directly on the table (by opening the table properties dialog and selecting the filter tab).

Small sample report is attached at the bottom.

-- Robert

============================================================

<?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="AdventureWorks">
<DataSourceReference>AdventureWorks</DataSourceReference>
<rd:DataSourceID>c2dc0e71-020b-48e3-9005-53286c923eb2</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Table Name="table1">
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<rd:DefaultName>textbox7</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Sum</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="value_1">
<rd:DefaultName>value_1</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!value.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Footer>
<Filters>
<Filter>
<Operator>NotEqual</Operator>
<FilterValues>
<FilterValue>=2</FilterValue>
</FilterValues>
<FilterExpression>=Fields!id.Value</FilterExpression>
</Filter>
</Filters>
<DataSetName>DataSet1</DataSetName>
<Top>0.125in</Top>
<Width>4.33333in</Width>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="id">
<rd:DefaultName>id</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!id.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="value">
<rd:DefaultName>value</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!value.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>id</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
</TableColumns>
<Height>0.75in</Height>
</Table>
</ReportItems>
<Height>1in</Height>
</Body>
<rd:ReportID>427ef1de-ff4a-42ab-96eb-9b4fbdc898e0</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select 1 as id, 5 as value union
select 2, 6 union
select 3, 7</CommandText>
<DataSourceName>AdventureWorks</DataSourceName>
</Query>
<Fields>
<Field Name="id">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>id</DataField>
</Field>
<Field Name="value">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>value</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>4.5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>

Dataset filtering... how?

Hi all,
I want to create a summary page for one of my report, depending on a
parameter, I would like to filter the dataset before creating the summary
fields.
I cannot figure out how to perform this filtering, my approach has been to
insert a Table control and map it to my dataset, then try and do some
filtering via the properties. When i then put something like
=sum(Fields!Sales.Value)
into the header (such that it is shown only once, not repeated), I get the
sum for the full dataset, not the filtered one.
Please help, is there any easier way todo this?
Lastly, I can't filter at the SQL level as the dataset is being used to
populate a chart below the summary.
Advice welcome.
Please help
TazIn the table properties you need to go to the Filter tab. You will then be
able to select the dataset field you want from the drop-down list and then
set what value(s) you want to include in the table.
HTH, Magendo_man
"Tarun Mistry" wrote:
> Hi all,
> I want to create a summary page for one of my report, depending on a
> parameter, I would like to filter the dataset before creating the summary
> fields.
> I cannot figure out how to perform this filtering, my approach has been to
> insert a Table control and map it to my dataset, then try and do some
> filtering via the properties. When i then put something like
> =sum(Fields!Sales.Value)
> into the header (such that it is shown only once, not repeated), I get the
> sum for the full dataset, not the filtered one.
> Please help, is there any easier way todo this?
> Lastly, I can't filter at the SQL level as the dataset is being used to
> populate a chart below the summary.
> Advice welcome.
> Please help
> Taz
>
>|||Thanks for ther reply.
I found that actually filtering directly on the dataset was my best option
(within the Data tab). My charts are working correctly on the condensed
data.
Taz
"magendo_man" <magendoman@.discussions.microsoft.com> wrote in message
news:C9DCDE28-059B-472C-BC4B-EDEC09C4E1B7@.microsoft.com...
> In the table properties you need to go to the Filter tab. You will then be
> able to select the dataset field you want from the drop-down list and then
> set what value(s) you want to include in the table.
> HTH, Magendo_man
> "Tarun Mistry" wrote:
>> Hi all,
>> I want to create a summary page for one of my report, depending on a
>> parameter, I would like to filter the dataset before creating the summary
>> fields.
>> I cannot figure out how to perform this filtering, my approach has been
>> to
>> insert a Table control and map it to my dataset, then try and do some
>> filtering via the properties. When i then put something like
>> =sum(Fields!Sales.Value)
>> into the header (such that it is shown only once, not repeated), I get
>> the
>> sum for the full dataset, not the filtered one.
>> Please help, is there any easier way todo this?
>> Lastly, I can't filter at the SQL level as the dataset is being used to
>> populate a chart below the summary.
>> Advice welcome.
>> Please help
>> Taz
>>

Dataset Dynamic Query

I am trying to implement the setting of a parameter to the default value of
'-All-' using the IIF function in the WHERE clause.
I received a processing error then backed off and checked a basic 'SELECT
select list FROM tablename WHERE columnname = value' query. It ran in
Preview just fine.
Then I wrapped the query in ' = " query w/o changes" ' When this runs in
Preview I get the following message in a pop-up box .. "Processing Error ..
An error has occurred during report processing. Query execution failed for
data set 'TestDS'. Line 1: Incorrect syntax error'.' "
The only change is the ' = " ..." '
The Datatset Command type is 'Text'
Any idea what is happening here'
--
Thanks in advance
GHHi Vakar,
Based on my knowledge, the Generic Query Designer Execute button will grey
out when the query begins with an '='
This is expected behavior since dynamic query cannot be executed in Generic
Query Designer, which also means you are not able to Perview the data now.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Correct the i cannot execute the query from the Query Designer in the Data tab.
I get the error when I go to the the Preview tab.
Thanks
"Michael Cheng [MSFT]" wrote:
> Hi Vakar,
> Based on my knowledge, the Generic Query Designer Execute button will grey
> out when the query begins with an '='
> This is expected behavior since dynamic query cannot be executed in Generic
> Query Designer, which also means you are not able to Perview the data now.
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||If you get an error when you go to Preview it is because something is wrong
with your string. If you have to (and I avoid it) use an expression then do
these steps.
1. Create a report with report parameters and a text box (nothing else)
2. Set the textbox to the expression that will end up being the source for
the dataset. Preview and look at what is in the textbox and make sure that
it is valid SQL (copy and paste into query analyzer and validate you have it
correctly).
3. create a query that will have the same fields returned to get you field
list populated
4. change to the expression that you now know (from #2) is correct.
These are the steps I always take.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GH" <vakar@.community.nospam> wrote in message
news:799D9ABF-1386-40B8-81C4-80D4327A2D87@.microsoft.com...
> Correct the i cannot execute the query from the Query Designer in the Data
tab.
> I get the error when I go to the the Preview tab.
> Thanks
> "Michael Cheng [MSFT]" wrote:
> > Hi Vakar,
> >
> > Based on my knowledge, the Generic Query Designer Execute button will
grey
> > out when the query begins with an '='
> >
> > This is expected behavior since dynamic query cannot be executed in
Generic
> > Query Designer, which also means you are not able to Perview the data
now.
> >
> > Thank you for your patience and corporation. If you have any questions
or
> > concerns, don't hesitate to let me know. We are always here to be of
> > assistance!
> >
> >
> > Sincerely yours,
> >
> > Michael Cheng
> >
> > Online Partner Support Specialist
> > Partner Support Group
> > Microsoft Global Technical Support Center
> > ---
> > Get Secure! - http://www.microsoft.com/security
> >
> > This posting is provided "as is" with no warranties and confers no
rights.
> > Please reply to newsgroups only, many thanks!
> >
> >|||Great suggustion ... I was going to ask for debugging suggestions ... I
The following testing was just completed.
I created a dataset (query 2) that is a subset of my problem query (query 1)
which is populating another table. The IIF function is the SAME for both
queries (I cut and pasted from Query 2 to Query 1). Query 2 works but Query
1 gets the error. Query 1 runs when commenting out the IIF function and
removing the =" ".
Here are the queries:
Query 1
="SELECT DISTINCT
a.EID
,CONVERT(VARCHAR(15),a.EventStartDate,107) AS EventStartDate
,a.PassageDistrictID
,Region = CASE
WHEN e.PassageRegionFullName IS NULL THEN 'Unknown'
WHEN e.PassageRegionFullName = '' THEN 'Unknown'
ELSE e.PassageRegionFullName
END
,a.EventName
,RegionalMgrName = CASE
WHEN b.LastName IS NULL THEN 'N/A'
WHEN b.LastName = '' THEN 'N/A'
ELSE b.LastName
END
,OperationsMgrName = CASE
WHEN d.LastName IS NULL THEN 'N/A'
WHEN d.LastName = '' THEN 'N/A'
ELSE d.LastName
END
,CONVERT(VARCHAR(15),c.LetterFirstSendDate,107) AS AssignmentLtrSentDt
FROM dbo.Event a
LEFT OUTER JOIN dbo.REPS b ON a.RegionalManagerID = b.RepID
LEFT OUTER JOIN dbo.AssignmentLetters c ON c.Event_EID = a.EID
LEFT OUTER JOIN dbo.REPS d ON a.OperationsManagerID = d.RepID
LEFT OUTER JOIN dbo.GeographicDim_PE e ON a.PassageDistrictID=e.PassageDistrictID "
& IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
Parameters!Region.Value & "")
Query 2
="SELECT a.EID, a.EventName
FROM dbo.Event a
JOIN GeographicDim_PE b
ON a.PassageDistrictID = b.PassageDistrictID "
& IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
Parameters!Region.Value & "")
"Bruce L-C [MVP]" wrote:
> If you get an error when you go to Preview it is because something is wrong
> with your string. If you have to (and I avoid it) use an expression then do
> these steps.
> 1. Create a report with report parameters and a text box (nothing else)
> 2. Set the textbox to the expression that will end up being the source for
> the dataset. Preview and look at what is in the textbox and make sure that
> it is valid SQL (copy and paste into query analyzer and validate you have it
> correctly).
> 3. create a query that will have the same fields returned to get you field
> list populated
> 4. change to the expression that you now know (from #2) is correct.
> These are the steps I always take.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "GH" <vakar@.community.nospam> wrote in message
> news:799D9ABF-1386-40B8-81C4-80D4327A2D87@.microsoft.com...
> > Correct the i cannot execute the query from the Query Designer in the Data
> tab.
> >
> > I get the error when I go to the the Preview tab.
> >
> > Thanks
> >
> > "Michael Cheng [MSFT]" wrote:
> >
> > > Hi Vakar,
> > >
> > > Based on my knowledge, the Generic Query Designer Execute button will
> grey
> > > out when the query begins with an '='
> > >
> > > This is expected behavior since dynamic query cannot be executed in
> Generic
> > > Query Designer, which also means you are not able to Perview the data
> now.
> > >
> > > Thank you for your patience and corporation. If you have any questions
> or
> > > concerns, don't hesitate to let me know. We are always here to be of
> > > assistance!
> > >
> > >
> > > Sincerely yours,
> > >
> > > Michael Cheng
> > >
> > > Online Partner Support Specialist
> > > Partner Support Group
> > > Microsoft Global Technical Support Center
> > > ---
> > > Get Secure! - http://www.microsoft.com/security
> > >
> > > This posting is provided "as is" with no warranties and confers no
> rights.
> > > Please reply to newsgroups only, many thanks!
> > >
> > >
>
>|||I have heard that sometimes there is a problem with put in a hard carriage
return in the expression box. Make sure the iif part is on the same line.
I.e. let any line wrapping in the expression box occur on its own, don't do
a carriage return to put the iif on another line. Just a guess.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GH" <vakar@.community.nospam> wrote in message
news:00F718AB-5FCC-4667-B8D2-24512860F5E0@.microsoft.com...
> Great suggustion ... I was going to ask for debugging suggestions ... I
> The following testing was just completed.
> I created a dataset (query 2) that is a subset of my problem query (query
1)
> which is populating another table. The IIF function is the SAME for both
> queries (I cut and pasted from Query 2 to Query 1). Query 2 works but
Query
> 1 gets the error. Query 1 runs when commenting out the IIF function and
> removing the =" ".
> Here are the queries:
> Query 1
> ="SELECT DISTINCT
> a.EID
> ,CONVERT(VARCHAR(15),a.EventStartDate,107) AS EventStartDate
> ,a.PassageDistrictID
> ,Region => CASE
> WHEN e.PassageRegionFullName IS NULL THEN 'Unknown'
> WHEN e.PassageRegionFullName = '' THEN 'Unknown'
> ELSE e.PassageRegionFullName
> END
> ,a.EventName
> ,RegionalMgrName => CASE
> WHEN b.LastName IS NULL THEN 'N/A'
> WHEN b.LastName = '' THEN 'N/A'
> ELSE b.LastName
> END
> ,OperationsMgrName => CASE
> WHEN d.LastName IS NULL THEN 'N/A'
> WHEN d.LastName = '' THEN 'N/A'
> ELSE d.LastName
> END
> ,CONVERT(VARCHAR(15),c.LetterFirstSendDate,107) AS AssignmentLtrSentDt
> FROM dbo.Event a
> LEFT OUTER JOIN dbo.REPS b ON a.RegionalManagerID = b.RepID
> LEFT OUTER JOIN dbo.AssignmentLetters c ON c.Event_EID = a.EID
> LEFT OUTER JOIN dbo.REPS d ON a.OperationsManagerID = d.RepID
> LEFT OUTER JOIN dbo.GeographicDim_PE e ON a.PassageDistrictID=> e.PassageDistrictID "
> & IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
> Parameters!Region.Value & "")
> Query 2
> ="SELECT a.EID, a.EventName
> FROM dbo.Event a
> JOIN GeographicDim_PE b
> ON a.PassageDistrictID = b.PassageDistrictID "
> & IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
> Parameters!Region.Value & "")
> "Bruce L-C [MVP]" wrote:
> > If you get an error when you go to Preview it is because something is
wrong
> > with your string. If you have to (and I avoid it) use an expression then
do
> > these steps.
> > 1. Create a report with report parameters and a text box (nothing else)
> > 2. Set the textbox to the expression that will end up being the source
for
> > the dataset. Preview and look at what is in the textbox and make sure
that
> > it is valid SQL (copy and paste into query analyzer and validate you
have it
> > correctly).
> > 3. create a query that will have the same fields returned to get you
field
> > list populated
> > 4. change to the expression that you now know (from #2) is correct.
> >
> > These are the steps I always take.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "GH" <vakar@.community.nospam> wrote in message
> > news:799D9ABF-1386-40B8-81C4-80D4327A2D87@.microsoft.com...
> > > Correct the i cannot execute the query from the Query Designer in the
Data
> > tab.
> > >
> > > I get the error when I go to the the Preview tab.
> > >
> > > Thanks
> > >
> > > "Michael Cheng [MSFT]" wrote:
> > >
> > > > Hi Vakar,
> > > >
> > > > Based on my knowledge, the Generic Query Designer Execute button
will
> > grey
> > > > out when the query begins with an '='
> > > >
> > > > This is expected behavior since dynamic query cannot be executed in
> > Generic
> > > > Query Designer, which also means you are not able to Perview the
data
> > now.
> > > >
> > > > Thank you for your patience and corporation. If you have any
questions
> > or
> > > > concerns, don't hesitate to let me know. We are always here to be of
> > > > assistance!
> > > >
> > > >
> > > > Sincerely yours,
> > > >
> > > > Michael Cheng
> > > >
> > > > Online Partner Support Specialist
> > > > Partner Support Group
> > > > Microsoft Global Technical Support Center
> > > > ---
> > > > Get Secure! - http://www.microsoft.com/security
> > > >
> > > > This posting is provided "as is" with no warranties and confers no
> > rights.
> > > > Please reply to newsgroups only, many thanks!
> > > >
> > > >
> >
> >
> >|||I just tried that ...
I removed all cr/lf making on long line of code ... nastly ... but it
worked ...
My mind thinks TSQL/Query Analyzer and forgets that in RS to think VB!!
Thanks for you help!!
"Bruce L-C [MVP]" wrote:
> I have heard that sometimes there is a problem with put in a hard carriage
> return in the expression box. Make sure the iif part is on the same line.
> I.e. let any line wrapping in the expression box occur on its own, don't do
> a carriage return to put the iif on another line. Just a guess.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "GH" <vakar@.community.nospam> wrote in message
> news:00F718AB-5FCC-4667-B8D2-24512860F5E0@.microsoft.com...
> > Great suggustion ... I was going to ask for debugging suggestions ... I
> >
> > The following testing was just completed.
> >
> > I created a dataset (query 2) that is a subset of my problem query (query
> 1)
> > which is populating another table. The IIF function is the SAME for both
> > queries (I cut and pasted from Query 2 to Query 1). Query 2 works but
> Query
> > 1 gets the error. Query 1 runs when commenting out the IIF function and
> > removing the =" ".
> >
> > Here are the queries:
> > Query 1
> > ="SELECT DISTINCT
> > a.EID
> > ,CONVERT(VARCHAR(15),a.EventStartDate,107) AS EventStartDate
> > ,a.PassageDistrictID
> > ,Region => > CASE
> > WHEN e.PassageRegionFullName IS NULL THEN 'Unknown'
> > WHEN e.PassageRegionFullName = '' THEN 'Unknown'
> > ELSE e.PassageRegionFullName
> > END
> > ,a.EventName
> > ,RegionalMgrName => > CASE
> > WHEN b.LastName IS NULL THEN 'N/A'
> > WHEN b.LastName = '' THEN 'N/A'
> > ELSE b.LastName
> > END
> > ,OperationsMgrName => > CASE
> > WHEN d.LastName IS NULL THEN 'N/A'
> > WHEN d.LastName = '' THEN 'N/A'
> > ELSE d.LastName
> > END
> > ,CONVERT(VARCHAR(15),c.LetterFirstSendDate,107) AS AssignmentLtrSentDt
> > FROM dbo.Event a
> > LEFT OUTER JOIN dbo.REPS b ON a.RegionalManagerID = b.RepID
> > LEFT OUTER JOIN dbo.AssignmentLetters c ON c.Event_EID = a.EID
> > LEFT OUTER JOIN dbo.REPS d ON a.OperationsManagerID = d.RepID
> > LEFT OUTER JOIN dbo.GeographicDim_PE e ON a.PassageDistrictID=> > e.PassageDistrictID "
> > & IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
> > Parameters!Region.Value & "")
> >
> > Query 2
> > ="SELECT a.EID, a.EventName
> > FROM dbo.Event a
> > JOIN GeographicDim_PE b
> > ON a.PassageDistrictID = b.PassageDistrictID "
> > & IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
> > Parameters!Region.Value & "")
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > If you get an error when you go to Preview it is because something is
> wrong
> > > with your string. If you have to (and I avoid it) use an expression then
> do
> > > these steps.
> > > 1. Create a report with report parameters and a text box (nothing else)
> > > 2. Set the textbox to the expression that will end up being the source
> for
> > > the dataset. Preview and look at what is in the textbox and make sure
> that
> > > it is valid SQL (copy and paste into query analyzer and validate you
> have it
> > > correctly).
> > > 3. create a query that will have the same fields returned to get you
> field
> > > list populated
> > > 4. change to the expression that you now know (from #2) is correct.
> > >
> > > These are the steps I always take.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "GH" <vakar@.community.nospam> wrote in message
> > > news:799D9ABF-1386-40B8-81C4-80D4327A2D87@.microsoft.com...
> > > > Correct the i cannot execute the query from the Query Designer in the
> Data
> > > tab.
> > > >
> > > > I get the error when I go to the the Preview tab.
> > > >
> > > > Thanks
> > > >
> > > > "Michael Cheng [MSFT]" wrote:
> > > >
> > > > > Hi Vakar,
> > > > >
> > > > > Based on my knowledge, the Generic Query Designer Execute button
> will
> > > grey
> > > > > out when the query begins with an '='
> > > > >
> > > > > This is expected behavior since dynamic query cannot be executed in
> > > Generic
> > > > > Query Designer, which also means you are not able to Perview the
> data
> > > now.
> > > > >
> > > > > Thank you for your patience and corporation. If you have any
> questions
> > > or
> > > > > concerns, don't hesitate to let me know. We are always here to be of
> > > > > assistance!
> > > > >
> > > > >
> > > > > Sincerely yours,
> > > > >
> > > > > Michael Cheng
> > > > >
> > > > > Online Partner Support Specialist
> > > > > Partner Support Group
> > > > > Microsoft Global Technical Support Center
> > > > > ---
> > > > > Get Secure! - http://www.microsoft.com/security
> > > > >
> > > > > This posting is provided "as is" with no warranties and confers no
> > > rights.
> > > > > Please reply to newsgroups only, many thanks!
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Great. Wasn't sure if it would solve the problem but I couldn't see anything
wrong (having the second query with the iif work but not the first was the
clue for me).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GH" <vakar@.community.nospam> wrote in message
news:FF5CFDA6-8144-482A-BA91-DB35B53ECE25@.microsoft.com...
> I just tried that ...
> I removed all cr/lf making on long line of code ... nastly ... but it
> worked ...
> My mind thinks TSQL/Query Analyzer and forgets that in RS to think VB!!
> Thanks for you help!!
> "Bruce L-C [MVP]" wrote:
> > I have heard that sometimes there is a problem with put in a hard
carriage
> > return in the expression box. Make sure the iif part is on the same
line.
> > I.e. let any line wrapping in the expression box occur on its own, don't
do
> > a carriage return to put the iif on another line. Just a guess.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "GH" <vakar@.community.nospam> wrote in message
> > news:00F718AB-5FCC-4667-B8D2-24512860F5E0@.microsoft.com...
> > > Great suggustion ... I was going to ask for debugging suggestions ...
I
> > >
> > > The following testing was just completed.
> > >
> > > I created a dataset (query 2) that is a subset of my problem query
(query
> > 1)
> > > which is populating another table. The IIF function is the SAME for
both
> > > queries (I cut and pasted from Query 2 to Query 1). Query 2 works but
> > Query
> > > 1 gets the error. Query 1 runs when commenting out the IIF function
and
> > > removing the =" ".
> > >
> > > Here are the queries:
> > > Query 1
> > > ="SELECT DISTINCT
> > > a.EID
> > > ,CONVERT(VARCHAR(15),a.EventStartDate,107) AS EventStartDate
> > > ,a.PassageDistrictID
> > > ,Region => > > CASE
> > > WHEN e.PassageRegionFullName IS NULL THEN 'Unknown'
> > > WHEN e.PassageRegionFullName = '' THEN 'Unknown'
> > > ELSE e.PassageRegionFullName
> > > END
> > > ,a.EventName
> > > ,RegionalMgrName => > > CASE
> > > WHEN b.LastName IS NULL THEN 'N/A'
> > > WHEN b.LastName = '' THEN 'N/A'
> > > ELSE b.LastName
> > > END
> > > ,OperationsMgrName => > > CASE
> > > WHEN d.LastName IS NULL THEN 'N/A'
> > > WHEN d.LastName = '' THEN 'N/A'
> > > ELSE d.LastName
> > > END
> > > ,CONVERT(VARCHAR(15),c.LetterFirstSendDate,107) AS
AssignmentLtrSentDt
> > > FROM dbo.Event a
> > > LEFT OUTER JOIN dbo.REPS b ON a.RegionalManagerID = b.RepID
> > > LEFT OUTER JOIN dbo.AssignmentLetters c ON c.Event_EID = a.EID
> > > LEFT OUTER JOIN dbo.REPS d ON a.OperationsManagerID = d.RepID
> > > LEFT OUTER JOIN dbo.GeographicDim_PE e ON a.PassageDistrictID=> > > e.PassageDistrictID "
> > > & IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
> > > Parameters!Region.Value & "")
> > >
> > > Query 2
> > > ="SELECT a.EID, a.EventName
> > > FROM dbo.Event a
> > > JOIN GeographicDim_PE b
> > > ON a.PassageDistrictID = b.PassageDistrictID "
> > > & IIF(Parameters!Region.Value = 0,""," WHERE b.PassageRegionID = " &
> > > Parameters!Region.Value & "")
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > If you get an error when you go to Preview it is because something
is
> > wrong
> > > > with your string. If you have to (and I avoid it) use an expression
then
> > do
> > > > these steps.
> > > > 1. Create a report with report parameters and a text box (nothing
else)
> > > > 2. Set the textbox to the expression that will end up being the
source
> > for
> > > > the dataset. Preview and look at what is in the textbox and make
sure
> > that
> > > > it is valid SQL (copy and paste into query analyzer and validate you
> > have it
> > > > correctly).
> > > > 3. create a query that will have the same fields returned to get you
> > field
> > > > list populated
> > > > 4. change to the expression that you now know (from #2) is correct.
> > > >
> > > > These are the steps I always take.
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "GH" <vakar@.community.nospam> wrote in message
> > > > news:799D9ABF-1386-40B8-81C4-80D4327A2D87@.microsoft.com...
> > > > > Correct the i cannot execute the query from the Query Designer in
the
> > Data
> > > > tab.
> > > > >
> > > > > I get the error when I go to the the Preview tab.
> > > > >
> > > > > Thanks
> > > > >
> > > > > "Michael Cheng [MSFT]" wrote:
> > > > >
> > > > > > Hi Vakar,
> > > > > >
> > > > > > Based on my knowledge, the Generic Query Designer Execute button
> > will
> > > > grey
> > > > > > out when the query begins with an '='
> > > > > >
> > > > > > This is expected behavior since dynamic query cannot be executed
in
> > > > Generic
> > > > > > Query Designer, which also means you are not able to Perview the
> > data
> > > > now.
> > > > > >
> > > > > > Thank you for your patience and corporation. If you have any
> > questions
> > > > or
> > > > > > concerns, don't hesitate to let me know. We are always here to
be of
> > > > > > assistance!
> > > > > >
> > > > > >
> > > > > > Sincerely yours,
> > > > > >
> > > > > > Michael Cheng
> > > > > >
> > > > > > Online Partner Support Specialist
> > > > > > Partner Support Group
> > > > > > Microsoft Global Technical Support Center
> > > > > > ---
> > > > > > Get Secure! - http://www.microsoft.com/security
> > > > > >
> > > > > > This posting is provided "as is" with no warranties and confers
no
> > > > rights.
> > > > > > Please reply to newsgroups only, many thanks!
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Dataset dependant on who's running the report...

I've got a parameter that I am populating from sql, but I'd like to be
able to add some functionality that does something like check for
permissions of the logged in user and use one dataset over another.
Is there anwyay I can achieve this or simulated it in any way?
Thanks in advance
Weston WeemsHi Weston
We are handling this by passing a userID as a parameter and based on
the userID passed will show or hide fields. we haven't created
different datasets, rather one large one and only display what is
necessary.
I don't know if this will help you at all.
Jennifer

Dataset Data is not the same as Database Data

Hi,

Thanks in advance. I have a problem in my dataset. Today morning we updated some of the descriptions to our parameter tables. But these are not getting updated to our reports. I checked the dataset. Even that shows the same values. How do I go about this? Please help. Thank You.

Regards,

Das.

You might have turned on caching on the reports. Reports execution can be cached, which is mostly done in environments where data retrieval would take a tremendous time comparing to the benefit of having realtime / cached data. Look in the properties of the report. Caching of the report should be disabled, otherwise the reportserver will take a copy from the Report Server Temp database to render the report.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi,

Thanks for the reply. I tried to find how to disable the cache in report and dataset properties. But I couldn't find any option. Could you please tell me where can I find the option? Thanks for your help. Bye.

Regards,

Das.

dataset connection string defined by a parameter?

Is there any way to dynamically set the connection string for a dataset
based on the value of a parameter?
Would I have to write a custom data extension to accomplish this?
thanks, AndrewHello,
Datasource connection string is not part of your report. It is provided by
ReportServer.
So, datasource is not aware about parameter's reports.
Jerome BERTHAUD MCSD, MCT
http://www.winsight.fr
"Andrew" <nospam@.nospam.com> wrote in message
news:#m8p1veWEHA.1144@.TK2MSFTNGP10.phx.gbl...
> Is there any way to dynamically set the connection string for a dataset
> based on the value of a parameter?
> Would I have to write a custom data extension to accomplish this?
> thanks, Andrew
>|||Hi Jerome,
Thanks for the info. So basically, it would be impossible to set the
connection string from within the designer based on a parameter.
However, wouldn't it still be possible to get hold of the parameters
collection within a custom data extension and use that to determine the
connection string prior to actually querying the datasource?
Thanks, Andrew
"Jerome BERTHAUD" <jerome.berthaud@.winsight.fr> wrote in message
news:uigOlEfWEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hello,
> Datasource connection string is not part of your report. It is provided by
> ReportServer.
> So, datasource is not aware about parameter's reports.
> Jerome BERTHAUD MCSD, MCT
> http://www.winsight.fr
> "Andrew" <nospam@.nospam.com> wrote in message
> news:#m8p1veWEHA.1144@.TK2MSFTNGP10.phx.gbl...
> > Is there any way to dynamically set the connection string for a dataset
> > based on the value of a parameter?
> >
> > Would I have to write a custom data extension to accomplish this?
> >
> > thanks, Andrew
> >
> >
>|||I have released a DPE that achiveves this. It can be downloaded at
http://workspaces.gotdotnet.com/appworld
Regards
Toby
"Andrew" <nospam@.nospam.com> wrote in message
news:%23m8p1veWEHA.1144@.TK2MSFTNGP10.phx.gbl...
> Is there any way to dynamically set the connection string for a dataset
> based on the value of a parameter?
> Would I have to write a custom data extension to accomplish this?
> thanks, Andrew
>

Dataset

When I make a dataset as parameter how can I make an ALL value which
doesn't
select the single values but all values instead.
Like
Month: all, jan, feb, etc.
when I choose all it's supposed to ignore it in the where clause, I tried
with case but it didn't work out, any clues on this one ?
Jack
--
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at få 45077 spam-mails.
Betalende brugere får ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dkgive it a null value and adapt your query so it says : "SELECT ... WHERE
month LIKE '@.parameterMonth'"
"Jack Nielsen" wrote:
> When I make a dataset as parameter how can I make an ALL value which
> doesn't
> select the single values but all values instead.
> Like
> Month: all, jan, feb, etc.
> when I choose all it's supposed to ignore it in the where clause, I tried
> with case but it didn't work out, any clues on this one ?
> Jack
>
> --
> Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
> Den har indtil videre sparet mig for at fÃ?Â¥ 45077 spam-mails.
> Betalende brugere fÃ?Â¥r ikke denne besked i deres e-mails.
> Hent gratis SPAMfighter her: www.spamfighter.dk
>
>|||Similarly...
"Select * from mytable where (col1 = @.parm or @.parm is null)"
This can also be used w/ multiple parms
"Select * from mytable where (col1 = @.parm or @.parm is null) and (col2 =@.parm2 or @.parm2 is null)"
I've used this method in many queries/procs to avoid using dynamic sql...
Cheers!
--
Ben Sullins
"Koen" wrote:
> give it a null value and adapt your query so it says : "SELECT ... WHERE
> month LIKE '@.parameterMonth'"
> "Jack Nielsen" wrote:
> > When I make a dataset as parameter how can I make an ALL value which
> > doesn't
> > select the single values but all values instead.
> >
> > Like
> >
> > Month: all, jan, feb, etc.
> >
> > when I choose all it's supposed to ignore it in the where clause, I tried
> > with case but it didn't work out, any clues on this one ?
> >
> > Jack
> >
> >
> > --
> > Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
> > Den har indtil videre sparet mig for at fÃ?Â¥ 45077 spam-mails.
> > Betalende brugere fÃ?Â¥r ikke denne besked i deres e-mails.
> > Hent gratis SPAMfighter her: www.spamfighter.dk
> >
> >
> >

Dataset

Hi,
I have 30 datasets in my report. But I will be using these datasets based on
the input parameter. Say if the input parameter value is 10 then I will use
first 10 datasets. if the parameter value is 15 i will be using first 15
datasets and so on.
If the parameter value is 10. Then I am excuting the remaining 20 datasets
for no use. which i am not going to use.
Is there a way that I can execute the datasets conditionally based on the
input parameters.
/
SNope, they all will execute.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"ERS Developer" <ERSDeveloper@.discussions.microsoft.com> wrote in message
news:6F6EDF32-CFD7-4757-9E34-E50BD29C0B3E@.microsoft.com...
> Hi,
> I have 30 datasets in my report. But I will be using these datasets based
> on
> the input parameter. Say if the input parameter value is 10 then I will
> use
> first 10 datasets. if the parameter value is 15 i will be using first 15
> datasets and so on.
> If the parameter value is 10. Then I am excuting the remaining 20 datasets
> for no use. which i am not going to use.
> Is there a way that I can execute the datasets conditionally based on the
> input parameters.
> /
> S|||On Apr 9, 12:36 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> Nope, they all will execute.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "ERS Developer" <ERSDevelo...@.discussions.microsoft.com> wrote in message
> news:6F6EDF32-CFD7-4757-9E34-E50BD29C0B3E@.microsoft.com...
> > Hi,
> > I have 30 datasets in my report. But I will be using these datasets based
> > on
> > the input parameter. Say if the input parameter value is 10 then I will
> > use
> > first 10 datasets. if the parameter value is 15 i will be using first 15
> > datasets and so on.
> > If the parameter value is 10. Then I am excuting the remaining 20 datasets
> > for no use. which i am not going to use.
> > Is there a way that I can execute the datasets conditionally based on the
> > input parameters.
> > /
> > S
In short, Bruce is correct; however:
The best work around would be to include an input parameter in the
stored procedures/queries that are sourcing the datasets and if the
parameter value is not set, don't execute the remainder of the stored
procedure. This could be accomplished by using a conditional statement
for each stored procedure that bypasses all actions in the stored
procedure, based on the parameter value passed to it.
Regards,
Enrique Martinez
Sr. Software Consultantsql

Datareader insted of dataset Sored Procedure

i m writing a stored procudrue to update my data that is onthertable.and i pass the parameter in my vb code,when i pass the data thatis insert only first record of data but second record insert the eroorwill come is data reader is colsed. now insted of data reade i have touse data set how can i use that and update my data is ontehrtable.?below i written my vb.net2005 code.

Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("Project1connectionString").ToString())
' con.Open()
' Dim ggrnid As String
' Dim acceptqty As String
' Dim itemid As String

' Dim grnid As TextBox = CType(GRNDetailsView.FindControl("fldgrnid"), TextBox)
' ggrnid = grnid.Text

' Dim sWhere As String = grnid.Text
' If (Not String.IsNullOrEmpty(sWhere)) Then
' For Each s As String In sWhere '
' 'Dim iRowIndex As Integer = Convert.ToInt32(s)
' Dim sqldtr As SqlDataReader
' sqlcmd = New SqlCommand
' sqlcmd.Connection = con
' sqlcmd.CommandType = CommandType.Text
' sqlcmd.CommandText = "select acceptqty,itemid fromgrndetail where grnid='" & Trim(ggrnid) & "'"

' datacommand = CommandType.StoredProcedure
' 'datacommand("aaceptqtygrn", con)

' Dim cmd As New SqlCommand("aaceptqtygrn", con)

' sqldtr = sqlcmd.ExecuteReader()

' 'dataset = datacommand.
' 'sqldtr = sqlcmd.ExecuteScalar

' If sqldtr.HasRows = True Then
' While sqldtr.Read()
' acceptqty = sqldtr.Item("acceptqty")
' itemid = sqldtr.Item("itemid")
' cmd.CommandType = CommandType.StoredProcedure
' cmd.Parameters.AddWithValue("@.acceptqty", acceptqty)
' cmd.Parameters.AddWithValue("@.itemid", itemid)
' sqldtr.Close()
' cmd.ExecuteNonQuery()

' End While
' 'sqldtr.Close()
' 'cmd.ExecuteNonQuery()
' 'Next sqldtr.HasRows
' End If
' Next s
' sqldtr.Close()
' con.Close()
' End If
' End If
Catch ex As Exception

MsgBox(ex.Message)

End Try

prajapatiamit2003:

' While sqldtr.Read()
' acceptqty = sqldtr.Item("acceptqty")
' itemid = sqldtr.Item("itemid")
' cmd.CommandType = CommandType.StoredProcedure
' cmd.Parameters.AddWithValue("@.acceptqty", acceptqty)
' cmd.Parameters.AddWithValue("@.itemid", itemid)
' sqldtr.Close()
' cmd.ExecuteNonQuery()

' End While

It looks like you are closing your data reader before exiting you while loop. And you probably do this because your stored proc wont execute while you have the data reader open.

You can either ececute the stored procedure ona different connection, or you can store the results of the reader in some kind of list/array/collection close the reader and then iterate through the collection to execute the sproc.

Because the datareader maintains an open connection to the database you cannot use that connection while the reader is open.

|||But how can i store in array of my data!

DataReader

Hi all,

I read somewhere that we have to close the data reader explicitly before getting an output parameter from a stored procedure. I experimented, and this retrieves the right value:

sqlConnection1.Open();
SqlDataReader myReader = sc.ExecuteReader();
lblCatName.Text = "CatName: " + sc.Parameters["@.CatName"].Value;
lblReturn.Text = "Return: " + sc.Parameters["@.RETURN_VALUE"].Value;
myReader.Close();
sqlConnection1.Close();

I found the following in MSDN: " You must explicitly call the Close method when you are through using the SqlDataReader (...) The Close method fills in the values for output parameters (...)"

Am I missing something here? And if both ways work, what is better practice?

Thanks,you don't need a reader for output parameters.

simply sc.ExecuteNonReader();
lblCatName.Text = "CatName: " + sc.Parameters["@.CatName"].value;
...|||Hi,

Of course, you are correct!!! How did I miss that??!!
I tried returning some values from my stored procedure so that it would make sense to use the ExecuteReader, and it is true that the DataReader has to be closed before getting the output and return values!! (ExecuteNonQuery does not raise the problem I described because doesn't have to be closed)

Thanks for your help!!

Bea|||well here's the truth behind it on SQL Server side at least...

If you execute some command, text or a stored procedure, it sort of looks like this (depending on the provider)

DECLARE @.p1 DATATYPE
DECLARE @.p2 DATATYPE
SET @.p1 = <VALUE FOR PARAMETER p1>
SET @.p2 = <VALUE FOR PARAMETER p2>
-- do the command here i.e.
EXEC StoredProcedure @.p1, @.p2 OUTPUT
SELECT @.p1, @.p2 -- this is how it gets its output parameters

tricky eh... well this means that you can't get the output parameters until you close the data reader that is executing from the EXEC StoredProcedure @.p1, @.p2 OUTPUT ...

does this make sense? yes... this is because it does it in order of operation, and you may get zero or more datareaders from the StoredProcedure, while in other providers (Oracle, Odbc) the implementation may be different and thus it may be able to return the output parameters before it streams the readers ...

imagine:

CREATE PROCEDURE StoredProcedure (@.p1 INT, @.p2 INT OUTPUT) AS
BEGIN
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND @.p1 = 1
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), N'IsView') = @.p1

SET @.p2 = 5
END

execute this in a stream reader or soemthing like it ... if you pause on the first datareader ... assuming it returns something ... it will not execute the rest of the procedure until you close the stream or ask for the next reader... then it will stream you the next data reader and then you ask for another or close ... once you close you go outside the procedure and it finishes executing ...

so essentially what it should/we would like it to do is

EXECUTE COMMAND:
- GET ALL READERS
- REMOVE THE LAST READER FROM THE LIST OF AVAILABLE READERS AND STORE THE OUTPUT PARAMETERS CHANGES
- ALLOW ACCESS TO THE REMAINDING AVAILABLE READERS

You can see proof of all this if you execute SQL Profiler and an application in debug mode... try stepping through the executing of the procedure and the retrieval of the data reader(s)... also add watches to each of the parameters... to see when their values change...|||Thank you so much for your very complete post! It was extremely helpful!!

Bea

DataFile and Log File

I want to know the datafile and log file for the given input parameter. I am
basically creating function and the input parameter would be database name
and that will return me DataFile and the LogFile.
Can anybody tell me which query I have to used in the function. I have to
used to get the DataFile and LogFile
Have a look at the sys.database_files catalog view in BOL.
Andrew J. Kelly SQL MVP
"Rogers" <naissani@.hotmail.com> wrote in message
news:OsKzR7QiHHA.5008@.TK2MSFTNGP02.phx.gbl...
>I want to know the datafile and log file for the given input parameter. I
>am basically creating function and the input parameter would be database
>name and that will return me DataFile and the LogFile.
> Can anybody tell me which query I have to used in the function. I have to
> used to get the DataFile and LogFile
>
|||In SQL Server 2000?
Thanks
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OGIt1IRiHHA.392@.TK2MSFTNGP06.phx.gbl...
> Have a look at the sys.database_files catalog view in BOL.
> --
> Andrew J. Kelly SQL MVP
> "Rogers" <naissani@.hotmail.com> wrote in message
> news:OsKzR7QiHHA.5008@.TK2MSFTNGP02.phx.gbl...
>
|||On Sat, 28 Apr 2007 10:42:15 -0400, Roger wrote:

>In SQL Server 2000?
Hi Roger,
A more polite way to write this would have been; "Oh, I'm sorry I forgot
to include in my original post that I'm using SQL Server 2000. Is there
an alternative method for that version?"
The answer to that question would have been to check the sysfiles system
table in Books Online.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Data-driven subscription: put parameter in the filename text

Hi all,

I've developed a data driven subscription report. I have a paramete (named "Data") that is a result of my query to the current date. It is working fine.

Now I would like to make one change: In the step4 of the creation of the data-driven report we have the option to give a name to the filename.The name that I gave was TestFile. In this option i'm also able to select the parameter instead of giving the name to the filename. Can I make something like TestFile_ & @.Data? Wich would result in TestFile_01-02-2007? Or the only way is to make, in the query of the paramenter another field with this result?

Thanks in advance.

Marco.

I tried the other option, making it by a query and this is what I want.

Anyway, instead of making this in my query, I couldn't have done this in the text? Like I said TestFile_ & @.Data? This would be interpreted as a string am I right?

Data-driven subscription with multi-select parameter

I'm trying to create a data-driven subscription with the ability to pass
multiple values using a multi-select parameter. Is this possible, and if so
how does one store the data in the database?
Thanks,
JoshI need this ability also, and according to this post, it is really not
possible. The solution proposed here is not feasable for my needs as I have
potentially from 1 to many many values for each parameter. Also, have no idea
how to use the SOAP API.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=518858&SiteID=1
"JSI" wrote:
> I'm trying to create a data-driven subscription with the ability to pass
> multiple values using a multi-select parameter. Is this possible, and if so
> how does one store the data in the database?
> Thanks,
> Josh

Data-driven Subscription with Date Parameter from DB

I'm not having fun anymore... I have done this in the past and now I am
unable to get this to work.
I have a report that accepts a date as a parameter. When called through the
web-page or in a subscription with static values for the date, this works. It
also works if the default value for the parameter is used. The default value
is a call to a datasource that simply calls "SELECT GETDATE()".
When I set up a data-driven subscription, give it the same datasource, give
the query of "SELECT GETDATE() AS REPORTDATE", and set the parameter to use
the REPORTDATE value; an error occurs stating that a type mismatch has been
encountered.
I have created a test report (attached below) that exhibits the same issue.
Set up a data-driven subscription with the "SELECT GETDATE() AS REPORTDATE"
query on any datasource.
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<PageHeader>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
<Style />
<Height>0.25in</Height>
</PageHeader>
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox1</rd:DefaultName>
<Width>2.6875in</Width>
<CanGrow>true</CanGrow>
<Value>=Parameters!STARTDATE.Value</Value>
<Left>1.625in</Left>
</Textbox>
</ReportItems>
<Style />
<Height>0.375in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="CPR_commerce">
<rd:DataSourceID>ec9dccfe-38c9-45ca-a0c0-7364bfc665e8</rd:DataSourceID>
<DataSourceReference>CPR_commerce</DataSourceReference>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="DEFAULTDATE">
<Fields>
<Field Name="ID">
<DataField />
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>CPR_commerce</DataSourceName>
<CommandText>SELECT GETDATE()</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>2549c4c5-9946-4a66-abda-7784310a7b58</rd:ReportID>
<PageFooter>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
<Style />
<Height>0.25in</Height>
</PageFooter>
<BottomMargin>1in</BottomMargin>
<ReportParameters>
<ReportParameter Name="STARTDATE">
<DataType>DateTime</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>DEFAULTDATE</DataSetName>
<ValueField>ID</ValueField>
</DataSetReference>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>STARTDATE</Prompt>
</ReportParameter>
</ReportParameters>
<Language>en-US</Language>
</Report>Well... I've solved it. It seems that the locale setting was set to en-CA and
it was messing up the times. Not sure why or how this got set to that but I'm
sure I'll be able to figure it out or avoid it in the future... man, what a
pain.
"Rixmann" wrote:
> I'm not having fun anymore... I have done this in the past and now I am
> unable to get this to work.
> I have a report that accepts a date as a parameter. When called through the
> web-page or in a subscription with static values for the date, this works. It
> also works if the default value for the parameter is used. The default value
> is a call to a datasource that simply calls "SELECT GETDATE()".
> When I set up a data-driven subscription, give it the same datasource, give
> the query of "SELECT GETDATE() AS REPORTDATE", and set the parameter to use
> the REPORTDATE value; an error occurs stating that a type mismatch has been
> encountered.
> I have created a test report (attached below) that exhibits the same issue.
> Set up a data-driven subscription with the "SELECT GETDATE() AS REPORTDATE"
> query on any datasource.
> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <PageHeader>
> <PrintOnLastPage>true</PrintOnLastPage>
> <PrintOnFirstPage>true</PrintOnFirstPage>
> <Style />
> <Height>0.25in</Height>
> </PageHeader>
> <RightMargin>1in</RightMargin>
> <Body>
> <ReportItems>
> <Textbox Name="textbox1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <Width>2.6875in</Width>
> <CanGrow>true</CanGrow>
> <Value>=Parameters!STARTDATE.Value</Value>
> <Left>1.625in</Left>
> </Textbox>
> </ReportItems>
> <Style />
> <Height>0.375in</Height>
> </Body>
> <TopMargin>1in</TopMargin>
> <DataSources>
> <DataSource Name="CPR_commerce">
> <rd:DataSourceID>ec9dccfe-38c9-45ca-a0c0-7364bfc665e8</rd:DataSourceID>
> <DataSourceReference>CPR_commerce</DataSourceReference>
> </DataSource>
> </DataSources>
> <Width>6.5in</Width>
> <DataSets>
> <DataSet Name="DEFAULTDATE">
> <Fields>
> <Field Name="ID">
> <DataField />
> <rd:TypeName>System.DateTime</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>CPR_commerce</DataSourceName>
> <CommandText>SELECT GETDATE()</CommandText>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>1in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <rd:ReportID>2549c4c5-9946-4a66-abda-7784310a7b58</rd:ReportID>
> <PageFooter>
> <PrintOnLastPage>true</PrintOnLastPage>
> <PrintOnFirstPage>true</PrintOnFirstPage>
> <Style />
> <Height>0.25in</Height>
> </PageFooter>
> <BottomMargin>1in</BottomMargin>
> <ReportParameters>
> <ReportParameter Name="STARTDATE">
> <DataType>DateTime</DataType>
> <DefaultValue>
> <DataSetReference>
> <DataSetName>DEFAULTDATE</DataSetName>
> <ValueField>ID</ValueField>
> </DataSetReference>
> </DefaultValue>
> <AllowBlank>true</AllowBlank>
> <Prompt>STARTDATE</Prompt>
> </ReportParameter>
> </ReportParameters>
> <Language>en-US</Language>
> </Report>