Friday, March 23, 2012
How to use stored procedures as the dataset for reports?
I want to use a stored procedure as the dataset for a report.
Even though I could execute the stored procedure in the query designer view,
the report designer did not generate the fields list (in the fields window).
Then I specified all the fields explicitly in the fields property of that
dataset. But when I tried to see the report in the preview pane, it gave me
a message that the dataset does not contain the required fields. I verified
the field names and they were exactly matching so no typo out there.
Can anyone tell me how can use a stored procedure as the dataset?
thanks,
NileshThere is a button that looks like the refresh button in IE that if you hover
over it is says refresh fields. Click on that and it will get all the field
information.
Bruce L-C
"Nilesh Oswal" <nilesh_oswal@.persistent.co.in> wrote in message
news:enGR9A3YEHA.3892@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to use a stored procedure as the dataset for a report.
> Even though I could execute the stored procedure in the query designer
view,
> the report designer did not generate the fields list (in the fields
window).
> Then I specified all the fields explicitly in the fields property of that
> dataset. But when I tried to see the report in the preview pane, it gave
me
> a message that the dataset does not contain the required fields. I
verified
> the field names and they were exactly matching so no typo out there.
> Can anyone tell me how can use a stored procedure as the dataset?
> thanks,
> Nilesh
>|||After executing the query from the designer, click the refresh button. Your fields should then populate.
Dave
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
Monday, March 19, 2012
How to use row values from query
render a report from.
For example "select county name from COUNTYTABLE where COUNTYNUMBER IN
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15) ". This query return 1 column and 15
rows of county names. I need them to be in 1 row and 15 columns to make up
the data going accross the report. What is the best way to do this?
Thanks,
ShawnI you create a matrix report with County Name across the top of the matrix,
it will automatically generate the appropriate number of columns...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"sysdesigner" <sysdesigner@.discussions.microsoft.com> wrote in message
news:55F38FB8-CBD1-4C1C-9611-8247A1A5C60E@.microsoft.com...
> Our database is normalized to the point that I need to build a dataset to
> render a report from.
> For example "select county name from COUNTYTABLE where COUNTYNUMBER IN
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15) ". This query return 1 column and
15
> rows of county names. I need them to be in 1 row and 15 columns to make
up
> the data going accross the report. What is the best way to do this?
> Thanks,
> Shawn
>|||I need to make a report that looks like...
Statistic A Statistic B Statistic C Statistic D
----
99 07 102 91
It would be easy but the values are all in one column in the table, like...
KeyValue | StatisticCode | StatisticValue
001| A| 99
002| B| 07
003| D| 91
004| C| 102
Thanks,
Shawn
"Wayne Snyder" wrote:
> I you create a matrix report with County Name across the top of the matrix,
> it will automatically generate the appropriate number of columns...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "sysdesigner" <sysdesigner@.discussions.microsoft.com> wrote in message
> news:55F38FB8-CBD1-4C1C-9611-8247A1A5C60E@.microsoft.com...
> > Our database is normalized to the point that I need to build a dataset to
> > render a report from.
> >
> > For example "select county name from COUNTYTABLE where COUNTYNUMBER IN
> > (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15) ". This query return 1 column and
> 15
> > rows of county names. I need them to be in 1 row and 15 columns to make
> up
> > the data going accross the report. What is the best way to do this?
> >
> > Thanks,
> > Shawn
> >
>
>
Friday, February 24, 2012
how to use cascade parameters with report model
Hi,
I know the use of cascading parameters when you use queries in your dataset.
But could somebody explain to me how to use them when using a reportmodel ?
Vinnie
Ok,
So after long research and testing and testing and test......
i have finaly found the solution.
In order to use cascading parameters with datasets based on models this is how you proceed.
Use the same filtersettings in your datasets that you want to limit to a previous dataset. (ok, i know this sounds like sh...)
but i will explain with an example.
supose you have in your dataset based on an entity with various fields like datetime, userid, username, ...
the goal is to make a report that will let me select a time period (from ... to ...) and will let me select one or more agents that
worked during that period.
This is how i do that :
Create a first dataset based on an entity (eg: UserData). Select the fields you want to show into the report.
Next specify the filters you want to apply to the report.
First we will define your start and end date :
drag your datetime field into the filter area (this will be your start date) and drag the same field back again in the filter area
(this will be your end date). click on the first filter (equals) and change it to 'on or after'. Do this also for the second filter and
change it into 'on or before'. Then click on both of the filters and click on 'prompt'. (this is to indicate to the program that it has
to prompt you to fill in the parameter. Click ok to confirm these filters and go to report parameters menu. Normaly there should
be two parameters created (DateTime and DateTime1 or something like that). Change the type to a datetime of both the
params. You can also change the label if needed.
So you run the report now, you will be first prompted to set a start and an end date.
Now to setup a third parameter (i want a list of the users who worked during that period i indicated with my start and end
date)
Open the first dataset, go to filters and drag and drop the userID (or wathever) entity into the filters. Make it again so it prompts
you for an input. Change "equals" into "in a list".(this is to be able to select more then one user)
Create a second dataset based on the same entity. Select UserId and UserName as fields you want to use in that dataset.
Go to filters and do the same action with the datetime field as you did in dataset1.
"
drag your datetime field into the filter area (this will be your start date) and drag the same field back again in the filter area
(this will be your end date). click on the first filter (equals) and change it to 'on or after'. Do this also for the second filter and
change it into 'on or before'. Then click on both of the filters and click on 'prompt'. (this is to indicate to the program that it has
to prompt you to fill in the parameter. Click ok to confirm these filters
"
Now go again to report parameters menu. A third parameter will be created (Userid). Select this parameter. It will be defined
as a string. check the multi value option. In the available values select from query. select "dataset2" (or whatever you named
it). select userid as value field and username as label field..
Now, when you run the report, and you input the start and end date, your user selection will be limited to your selected time
period (this can be seen because it will take a few moments to populate the user selection listbox).
I know this is a long explanation but i hope it is clear. After all the solution was simple, but i didn't find the answer.
Greetings
Vinnie
how to use cascade parameters with report model
Hi,
I know the use of cascading parameters when you use queries in your dataset.
But could somebody explain to me how to use them when using a reportmodel ?
Vinnie
Ok,
So after long research and testing and testing and test......
i have finaly found the solution.
In order to use cascading parameters with datasets based on models this is how you proceed.
Use the same filtersettings in your datasets that you want to limit to a previous dataset. (ok, i know this sounds like sh...)
but i will explain with an example.
supose you have in your dataset based on an entity with various fields like datetime, userid, username, ...
the goal is to make a report that will let me select a time period (from ... to ...) and will let me select one or more agents that
worked during that period.
This is how i do that :
Create a first dataset based on an entity (eg: UserData). Select the fields you want to show into the report.
Next specify the filters you want to apply to the report.
First we will define your start and end date :
drag your datetime field into the filter area (this will be your start date) and drag the same field back again in the filter area
(this will be your end date). click on the first filter (equals) and change it to 'on or after'. Do this also for the second filter and
change it into 'on or before'. Then click on both of the filters and click on 'prompt'. (this is to indicate to the program that it has
to prompt you to fill in the parameter. Click ok to confirm these filters and go to report parameters menu. Normaly there should
be two parameters created (DateTime and DateTime1 or something like that). Change the type to a datetime of both the
params. You can also change the label if needed.
So you run the report now, you will be first prompted to set a start and an end date.
Now to setup a third parameter (i want a list of the users who worked during that period i indicated with my start and end
date)
Open the first dataset, go to filters and drag and drop the userID (or wathever) entity into the filters. Make it again so it prompts
you for an input. Change "equals" into "in a list".(this is to be able to select more then one user)
Create a second dataset based on the same entity. Select UserId and UserName as fields you want to use in that dataset.
Go to filters and do the same action with the datetime field as you did in dataset1.
"
drag your datetime field into the filter area (this will be your start date) and drag the same field back again in the filter area
(this will be your end date). click on the first filter (equals) and change it to 'on or after'. Do this also for the second filter and
change it into 'on or before'. Then click on both of the filters and click on 'prompt'. (this is to indicate to the program that it has
to prompt you to fill in the parameter. Click ok to confirm these filters
"
Now go again to report parameters menu. A third parameter will be created (Userid). Select this parameter. It will be defined
as a string. check the multi value option. In the available values select from query. select "dataset2" (or whatever you named
it). select userid as value field and username as label field..
Now, when you run the report, and you input the start and end date, your user selection will be limited to your selected time
period (this can be seen because it will take a few moments to populate the user selection listbox).
I know this is a long explanation but i hope it is clear. After all the solution was simple, but i didn't find the answer.
Greetings
Vinnie
How to use aliases in typed dataset
I have a SELECT query with an alias in it.
The intellisense shows all field except the alias one.
What goes wrong?
Intellisense in which application?
|||I found the solution. I just have to add the Alias field to the Typed Dataset
Sunday, February 19, 2012
How to use a table with table name starting "@" symbol in dataset
Hi ,
I have a database with a table name called @.budget_tot.
When I run a query in report designer --dataset, it throws an error thinking this is a variable rather
than a table.
Same query works without error in SQL 2005 query window.
example Query;
Select T1.U_Budget From [@.budet_tot] T1
Sonny
SInce no one answered I'll give you my current workaround.
Create a parameter and either make it hidden (with null) or make it internal.
So the report runs without anyone seeing parameter.
Sonny
|||I am rather new to Reporting Services, but in other places if you will enclose your table name with square brackets the name should be accepted. Try [@.budget_tot] instead of @.budget_tot.|||Hi Kent, Thanks for the reply. This works in SQL qoery analyser but not in report designer.
Sonny
How to use a table with table name starting "@" symbol in dataset
Hi ,
I have a database with a table name called @.budget_tot.
When I run a query in report designer --dataset, it throws an error thinking this is a variable rather
than a table.
Same query works without error in SQL 2005 query window.
example Query;
Select T1.U_Budget From [@.budet_tot] T1
Sonny
SInce no one answered I'll give you my current workaround.
Create a parameter and either make it hidden (with null) or make it internal.
So the report runs without anyone seeing parameter.
Sonny
|||I am rather new to Reporting Services, but in other places if you will enclose your table name with square brackets the name should be accepted. Try [@.budget_tot] instead of @.budget_tot.|||Hi Kent, Thanks for the reply. This works in SQL qoery analyser but not in report designer.
Sonny
How to use a Dataset returned from Web-Service as a report source?
I'm kinda new to the whole reporting services thing - but I'm
wondering if you can bind a report to a dataset returned from a
web-service - maybe using the dataset XSD for schema info and then
setting parameters to pass for the soap call. I am trying to move
away from Crystal and I can do this programmatically using the CR
viewer. I saw a post regarding someone creating a "custom data
extension" but have never heard of that before. Thanks!You can write a custom data processing extension to do this. Here's the doc:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_extend_dataproc_5c2q.asp
--
Rajeev Karunakaran [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sean Dowling" <nyq2000@.hotmail.com> wrote in message
news:606b339b.0408191704.4c9c003@.posting.google.com...
> Hi,
> I'm kinda new to the whole reporting services thing - but I'm
> wondering if you can bind a report to a dataset returned from a
> web-service - maybe using the dataset XSD for schema info and then
> setting parameters to pass for the soap call. I am trying to move
> away from Crystal and I can do this programmatically using the CR
> viewer. I saw a post regarding someone creating a "custom data
> extension" but have never heard of that before. Thanks!
How to use a dataset field value in a report parameter
How can I use a value of a returning dataset to be the default value for a
parameter?
I tried using =Fields!THE_FIELD.value in Non-queried textbox of the
parameter, but this doesn't seem to work.
When I press the fx button, the text "Report item not linked to database" is
shown in Fields node of the treeview. I don't know if this message has
something to do with it.
Thanks
JaimeYou must use the Queried section... Name the dataset and the field...
However I warn you (as does the documentation) that sometimes using the same
data set for report data and parameters cause (I think the word is )
unspecified behaviours... - problems... be careful..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in message
news:526D3602-02A8-40BB-BD1B-D6222D95D730@.microsoft.com...
> Hi all..
> How can I use a value of a returning dataset to be the default value for a
> parameter?
> I tried using =Fields!THE_FIELD.value in Non-queried textbox of the
> parameter, but this doesn't seem to work.
> When I press the fx button, the text "Report item not linked to database"
> is
> shown in Fields node of the treeview. I don't know if this message has
> something to do with it.
> Thanks
> Jaime|||If itis problematic, what is the best solution for this?:
I have a big query that retrieve some data. To make it simpler to explain,
imagine this simple query:
SELECT IDEPOL, POL_NAME
FROM POLIZA
WHERE [some filter]
I have in report layout a data region that shows POLIZA information.
I need to have another data region associated with this other query:
SELECT MOVEMENT
FROM PRIMA
WHERE IDEPOL = :IDEPOL
Where :IDEPOL is the IDEPOL returned by the first query.
Of course, I have grouped by IDEPOL in layout (using a List control)
I repeat that this is only an example, The true main query is very big, and
I need to have several other data regions whose queries are of the form of:
SELECT MOVEMEN
FRIMA
WHERE IDEPOL = :IDEPOL
So the usage of a lot of joins is discarded.
Thanks in advance for any help you can give me.
Jaim
"Wayne Snyder" wrote:
> You must use the Queried section... Name the dataset and the field...
> However I warn you (as does the documentation) that sometimes using the same
> data set for report data and parameters cause (I think the word is )
> unspecified behaviours... - problems... be careful..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in message
> news:526D3602-02A8-40BB-BD1B-D6222D95D730@.microsoft.com...
> > Hi all..
> >
> > How can I use a value of a returning dataset to be the default value for a
> > parameter?
> >
> > I tried using =Fields!THE_FIELD.value in Non-queried textbox of the
> > parameter, but this doesn't seem to work.
> >
> > When I press the fx button, the text "Report item not linked to database"
> > is
> > shown in Fields node of the treeview. I don't know if this message has
> > something to do with it.
> >
> > Thanks
> > Jaime
>
>