Posted by: Charles Maitland | Wednesday 31 May, 2006

More on Reporting Services 2005 into 2000

Jason left an excellent addition to my first post on this in the comments. Unfortunately the comment editor did not get on with XML so I am promoting Jason’s comment to a post.

He Says"

The XML in the RDL file is different between 2005 and 2000 when you want to pass parameters to a SQL procedure.

In 2000 they pass parameters this way in the RDL file:

<Query>
     <CommandType> StoredProcedure</CommandType>
     <CommandText> =”procGetInvMovement”</CommandText>
     <QueryParameters>
         <QueryParameter Name=”@BegDate”>
             <Value> =Parameters!pBegDate.Value</Value>
         </QueryParameter>
         <QueryParameter Name=”@EndDate”>
             <Value> =Parameters!pEndDate.Value</Value>
         </QueryParameter>
     </QueryParameters>
     <DataSourceName> JLG SQL</DataSourceName>
</Query>

and in 2005 they do it this way:

<Query>
     <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
     <CommandText> =”Execute procGetInvMovement ‘” & Parameters!pBegDate.Value & “‘, ‘” & Parameters!pEndDate.Value & “‘”</CommandText>
     <QueryParameters>
         <QueryParameter Name=”pBegDate”>
             <Value> =Parameters!pBegDate.Value</Value>
         </QueryParameter>
         <QueryParameter Name=”pEndDate”>
             <Value> =Parameters!pEndDate.Value</Value>
         </QueryParameter>
     </QueryParameters>
     <DataSourceName> JLG SQL</DataSourceName>
</Query>

Notice how they don’t include the parameters in the commandtext tag in 2000!!!

It worked for me after I figured that out.

Hopes this saves someone some time!!!

One more addition to the 2000 version of the .RDL file. You will need to add this line:

<CommandType>StoredProcedure</CommandType>

after the first <Query> tag!

"

Thanks for the comment Jason.

PS The same disclaimer applies – This is NOT supported, condoned or anything else. Use at your own risk.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: