Parameter Mapping format string

Hi,

I have used Data Flow component that refers to named query of data source view. It is a OLE DB source.

The SQL Command property of data flow component shows SELECT * FROM Tablename as I defiend in named query. I have modified the query to accept a parameter as SELECT * FROM Tablename WHERE Status = ?

Now I need to pass the package variable to this parameter. How to I pass using ParameterMapping property?. What is correct way of passing the parameter mapping ? I tried with @.[TestNS:tatus] variable. But it throws error The parameter mapping string is not in the correct format.

Thanks in advance

Hello Prabha!

The best way to set parameter mappings on your OLE DB source is by using the "Parameters" button in the edit dialog for the source component. Once you've clicked that, a pop-up will appear with a grid where you can select variables you want to map to parameters of your SQL statement. If you have problems with this approach, feel free to post a follow up and one of us will be sure to help you from there.

Thanks, -David

|||

Hi David,

I agree that the suggested approch will help in configuing parameterized SQL Command. But my scenario is to use named query as source.

I do not get the 'Parameters' button as I have selected named query option in OLEDB source. However, I am allowed to change the SQL command property to have the parameter place holder '?' under Properties window after configuring the OLEDB connection Manager.

There is also one more property called 'ParameterMapping' under custom properties of OLEDB source. I do not find any reference on the format of the parameter mapping. Can you forward any reference on this ?

Thanks,

Prabha

|||

Sorry, Prabha, I sort of missed that you're using a DSV named query.

Officially, Named Queries don't support parameters, but as you found, there are ways to manually change the SQL in your DSV, such as by changing the xml directly. I wouldn't recommend that because other things using these DSVs are likely to break.

That having been said, I was able to convince the OLE DB source to bind in variables to the parameters I added manually to the Named Query's SQL, by setting the ParameterMapping manually. The format is:

"<param-name>","<variable-guid>";...

But, instead of constructing it manually, which seems kind of error prone, try this:

1) In the OLE DB Source editor, copy the SQL for your named query to the clipboard.

2) Switch to "SQL Command" mode for your OLE DB Source.

3) Paste the query from the clipboard to the SQL editor.

4) Click "Parameters...".

5) For each parameter, choose a variable.

6) Close the parameters screen, close the OLE DB source editor.

7) Find the "ParameterMapping" field in the Properties editor, copy that to the clipboard.

8) Re-open the OLE DB source editor, switch back to the Named Query.

9) Close the OLE DB source editor

10) Paste the ParameterMapping value from the clipboard back into the properties window

A lot of steps, but I think it's better than trying to hand-assemble the thing. Fair warning though, this isn't supported, so it could stop working at any time. From what I know, DSVs don't officially support parameters, so the safe bet would be to switch to SQL Command mode for this.

-David

|||

Thank you so much David, The given work around works well.

- Prabha

Parameter entered has the wrong format.. you must be ....me!

Hi

I have the problem that the below defined paramter gets entered in the database as a interger. the Field in the DB is a nvarchar(5) and the controll that suplies the value is a TextBox

this is the parameter definition:
<asp:ControlParameterControlID="tbComment"Name="Comment"PropertyName="Text"Type="String"/>

Why do I get this error, why does ASP to whant to make an integerfrom this text field? When putting a interger value in the textbox all works well and the data gets posted to the database.

I use aSqlDataSource with automatic generated script.

look forwart to a solution

walter

Do you use a stored procedure or ...? make sure the parameter (comment) is not passed to a parameter of type int.

Parameter Date Formatting

Hey,
I've got a problem - got Start and End date parameters in my report and
on my dev box they show up in Australian format as it should be but on
the production server it's in American format! What do I do? I don't
really want to try setting dates as strings... Installed SP2, it had no
effect...Check the server. It sounds like it's culture settings are not Australian.
-Tim
"masterslave" <ocbka1@.gmail.com> wrote in message
news:1148967379.266799.284360@.u72g2000cwu.googlegroups.com...
> Hey,
> I've got a problem - got Start and End date parameters in my report and
> on my dev box they show up in Australian format as it should be but on
> the production server it's in American format! What do I do? I don't
> really want to try setting dates as strings... Installed SP2, it had no
> effect...
>|||Thanks for your reply Tim, I've checked the server and all settings are
correct! It seems that the date format is actually set on the client
machine in IE language settings... Is that possible? If so, this is
really bizarre...

Parameter Date Format!

Hi,

How to control the Datetime Parameter's format, such as I need the user input Date and Time, but default, the user can only select date, but can not input the time.

How to solve it?

Thank you.

Jeffers,

You could give your users two textbox parameter for date and time entry, combine to 2 strings ( "mm/dd/yyyy" + "00:00:00" ) and use a cdate function convert to the actual datetime field.

Ham

parameter date format - change to dd/mm/yyyy

Hi All,

I'm using report with a date parameter (user enters a date) and all transactions before that date are displayed. I don't know how to set parameter date format on the displayed report to dd/mm/yyyy.

If it's not a parameter I usually use sql " convert(varchar(10),datefield,103)" but don't know how to use this with parameter.

Thanks

Sonny

Pretty much the same whether for a parameter or for a field expression:

Code Snippet

=Format(Fields!last_edit.Value,"d/M/yyyy")

=Format(Parameters!RPDate.value,"d/M/yyyy")

that should do it.... watch the case-sensitivity; a lower case "m" gives you minutes, not months...

One thing, though: if the user enters the date param directly, you might want to do a cast, something like this just in case there is a data type issue somewhere (of course you might have to do this for whatever code actually *uses* the param as well, I don't know, so maybe it's a dumb thought)

Code Snippet

=Format(CDATE(Parameters!RPDate.value),"d/M/yyyy")

>L<

|||

Hi Lisa,

thanks for the answer. this works with date picker but if I enter the date manually it does not. maybe i have to convert date as you said.

basicall current report paramater is such that you must enter date as YYYYMMDD format. I guess this is default SQL server date format.

I'm on RS patch 0 , never been able to use date picker.

Sonny

|||

WHen you enter the date manually, what does it look like (I'm not asking what the result of the expression is -- I'm asking what you actually enter)?

It's not so much that it has to match the "default sql server date format" as it has to match *some* default, which could be the locale on the server, I'm really not sure.

It is not a big deal to write some code to make sure that your manually-entered date is properly parsed into the format that you want. HOWEVER the reason I just suggested the cast, rather than parsing code, is that you run into the problem of other people maybe entering the manually-entered date differently than you do and possibly parsing out for many different cases.

Since, as you say, the server does have an internal idea of what a date should look like, I find it's often best to write something that does a cast and lets the server figure it out if it can. The worst that will happen is that you'll see #ERROR instead of the expected result, but often SQL Server is pretty smart about figuring out dates, and some of the .NET classes at work in RS (which probably aren't even in SQL Server at the point they are doing this work) may be smart about it also.

If the cast doesn't work, we will proceed further <s>.

>L<

Pagination Question

I have a report that groups information by employee. When the report is first
rendered on the server (i.e. HTML format) the data flows over to a 2nd page
for this person. The 2nd page does not have the header defined so there is no
way to tell which employee this is for. One if the solutions is to length the
page, but this will only work to solved this one issue. It is not a good long
term solution.
Question: How do I either a) Make the HTML page length as long as is needed
for a particular page or b) Get the header to really appear on every page?Hope this tidbit helps
a) Make the HTML page length as long as is needed
for a particular page
if your report only has one group then throw your fields inside the list
control and put this formula in the group expression
=Ceiling(Rownumber(Nothing)/20) '20 for example number of pages'
b) Get the header to really appear on every page?
Use the Table Object in which case you can select the option to repeat the
header on every page.
--
Message posted via http://www.sqlmonster.com|||a) My report has multiple groups (I think - Newbie). Information is paginated
by employee. For each employee information is grouped by year.
b) Can you give me some more information about the table object please?
Patrick
"Paul Mauriello via SQLMonster.com" wrote:
> Hope this tidbit helps
>
> a) Make the HTML page length as long as is needed
> for a particular page
> if your report only has one group then throw your fields inside the list
> control and put this formula in the group expression
> =Ceiling(Rownumber(Nothing)/20) '20 for example number of pages'
> b) Get the header to really appear on every page?
> Use the Table Object in which case you can select the option to repeat the
> header on every page.
> --
> Message posted via http://www.sqlmonster.com
>

Page Size and Optimum Cluster Size

I would like to know the Page Size of SQL Server 2005.
If we implement SQL Server 2005 on SAN, what is the optimum size of the
cluster in NTFS format ? What is the relationship between the Page Size and
the cluster (Allocation Unit) size ?
Thanks"Jason" <Jason@.discussions.microsoft.com> wrote in
news:OmuTiWB8FHA.3388@.TK2MSFTNGP11.phx.gbl:
> If we implement SQL Server 2005 on SAN, what is the optimum size of
> the cluster in NTFS format ? What is the relationship between the
> Page Size and the cluster (Allocation Unit) size ?
On SQL Server 2000 the recommendation I've seen is 64k (One extent), and I
do think it is the same in SQL Server 2005.
One extent contains 8 pages. By using 64k allocation units, (in combination
with the same block size in the raid set/san) you have only one IO
operation on one single disk to read/write whole extent, thus reducing the
number of IOs when performing IO-intensive operations like a table scan.
--
Ole Kristian Bangås
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging

Page Size and Optimum Cluster Size

I would like to know the Page Size of SQL Server 2005.
If we implement SQL Server 2005 on SAN, what is the optimum size of the
cluster in NTFS format ? What is the relationship between the Page Size and
the cluster (Allocation Unit) size ?
Thanks"Jason" <Jason@.discussions.microsoft.com> wrote in
news:OmuTiWB8FHA.3388@.TK2MSFTNGP11.phx.gbl:

> If we implement SQL Server 2005 on SAN, what is the optimum size of
> the cluster in NTFS format ? What is the relationship between the
> Page Size and the cluster (Allocation Unit) size ?
On SQL Server 2000 the recommendation I've seen is 64k (One extent), and I
do think it is the same in SQL Server 2005.
One extent contains 8 pages. By using 64k allocation units, (in combination
with the same block size in the raid set/san) you have only one IO
operation on one single disk to read/write whole extent, thus reducing the
number of IOs when performing IO-intensive operations like a table scan.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging

Page Size and Optimum Cluster Size

I would like to know the Page Size of SQL Server 2005.
If we implement SQL Server 2005 on SAN, what is the optimum size of the
cluster in NTFS format ? What is the relationship between the Page Size and
the cluster (Allocation Unit) size ?
Thanks
"Jason" <Jason@.discussions.microsoft.com> wrote in
news:OmuTiWB8FHA.3388@.TK2MSFTNGP11.phx.gbl:

> If we implement SQL Server 2005 on SAN, what is the optimum size of
> the cluster in NTFS format ? What is the relationship between the
> Page Size and the cluster (Allocation Unit) size ?
On SQL Server 2000 the recommendation I've seen is 64k (One extent), and I
do think it is the same in SQL Server 2005.
One extent contains 8 pages. By using 64k allocation units, (in combination
with the same block size in the raid set/san) you have only one IO
operation on one single disk to read/write whole extent, thus reducing the
number of IOs when performing IO-intensive operations like a table scan.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging

Page problem when exporting to pdf format.

I have a main report that contains charts and sub reports. When i see in preview mode it shows ok but when i export it in pdf format it does not show the way i want one relevant remains on previous page and other are gone to next page etc...

Another problem i am facing is if i make the page size greater than 7.5 it exports to one page blank and one page data.

One more thing i want to show table data vertically not horizontally due to restriction of 7.5 inches width.

Thanks in Advance

Muzaffar Ali

Hi Muzaffar,

Regarding report items getting shifted to next page in pdf and getting a blank page check this post -

http://blogs.msdn.com/chrisbal/archive/2006/08/10/694892.aspx

and what exactly you mean by showing data vertically, having header as rows and data as columns(then the number of columns will depend on the numbers of rows in dataset). I think you can try matrix to do that -http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx

Page numbers does not appear while exporting to PDF format

Hi there,
Page numbers does not appear while exporting to PDF format.
I have a main report containing 6 subreports each within a rectagle to
enable page breaks after each subreport. Each subreport is 7.5in wide and 10
in long. I have included same measurements for Page width and Page Height in
report properties-> layout tab also for all the 6 subreports. Here all margin
size is set to 0in.
Main report has report body (8in wide and 9.325in long) and report footer
area (8in wide and 0.175in long) with page number textbox. I have set Page
width and Page height of main report in report properties to be 8.5in wide
and 11 in long.
Spacing is set to 0in. Top and bottom margins are 0.15in each and left
margin is 0.35in and right margin is 0in. I need these marginal settings to
make my data appear good in PDF export.
With these settings, the page numbers appear at the bottom in HTML format,
but it does not appear in PDF format. What is the issue? Where am I going
wrong?
Please help me with the issue. I am using SQL Server Reporting Services 2005
RTM Version on Windows XP Service pack 2.
Thanks.Hi ringt,
Thank you for using Microsoft Partner Managed Newsgroup Support.
From your description, my understanding of this issue is: You want to show
the Page Number in the PDF render format. If I misunderstood your concern,
please feel free to let me know.
I have tested on my side, I setup a report include a subreport and set the
report representation properties like you posted. I export this report to
the PDF format, it shows the Page Number.
Would you please check does this issue occur on all the report or only for
this specified report?
If this only appeared on a specified report, would you please send the rdl
file to me? I understand the information may be sensitive to you, my direct
email address is weilu@.ONLINE.microsoft.com (Please remove the ONLINE. when
you send the email), you may send the file to me directly and I will keep
secure.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Page numbering - X of Y format

I have a reporting services application which generate reports for given IDs. You can pass any number of IDs.
e.g.. If you pass 2 IDs it will generate a single report for those 2 IDs..
Page numbering would look like
1,2,3,1
for ID 1 it has generated 3 pages while for ID 2 it has genereted only 1 page.

But I want the page numbering to return 1 of 3,2 of 3, 3 of 3, 1 of 2, 2 of 2.

Parameters i pass to generate the report is only the ID. It will be in a string format & the SP I use to get data split the string & get the relevant IDs...

Any help to achieve this will be great.

thanks

If you don't need the "of y Pages"-Part look at:
http://blogs.msdn.com/bwelcker/archive/2005/05/19/420046.aspx
|||Hi thanks for the reply; but i doo want of y pages :(

Page numbering - X of Y format

I have a reporting services application which generate reports for given IDs. You can pass any number of IDs.
e.g.. If you pass 2 IDs it will generate a single report for those 2 IDs..
Page numbering would look like
1,2,3,1
for ID 1 it has generated 3 pages while for ID 2 it has genereted only 1 page.

But I want the page numbering to return 1 of 3,2 of 3, 3 of 3, 1 of 2, 2 of 2.

Parameters i pass to generate the report is only the ID. It will be in a string format & the SP I use to get data split the string & get the relevant IDs...

Any help to achieve this will be great.

thanks

If you don't need the "of y Pages"-Part look at:
http://blogs.msdn.com/bwelcker/archive/2005/05/19/420046.aspx
|||Hi thanks for the reply; but i doo want of y pages :(