Parameterized Expression: reporting

Showing posts with label reporting. Show all posts

Friday, March 30, 2012

Part of textbox content in bold, is it possible ?

We're using reporting services 2000 sp2, i'd like to know if it is possible
to set in bold just part of the text in a textbox. We sometimes want ot
highlight a few words out of a sentence in our reports. We're mostly using
table controls to build the reports.
Thanks!That is not possible, would need to split into two or more textboxes.
Steve MunLeeuw
"/dev/null" <devnull@.discussions.microsoft.com> wrote in message
news:3F445BA7-11B8-40C4-B594-E1798A0351CA@.microsoft.com...
> We're using reporting services 2000 sp2, i'd like to know if it is
> possible
> to set in bold just part of the text in a textbox. We sometimes want ot
> highlight a few words out of a sentence in our reports. We're mostly
> using
> table controls to build the reports.
> Thanks!

Wednesday, March 28, 2012

Parsing Text String Field - comma delimited

Can anyone tell me how to parse out a comma-delimited text string field for reporting purposes? What I need is for each new value within the string to create a new record.

Example:

ID STRING

12345 1,2,3

67891 2,4

Becomes

12345 1

12345 2

12345 3

67891 2

67891 4

Thanks for any help!!

Check this page. It has great examples (if you are using 2005 there are two really great ways of handling this)

http://www.aspfaq.com/show.asp?id=2529

Parsing Text String Field

Can anyone tell me how to parse out a comma-delimited text string field for reporting purposes?

Example:

ID STRING

12345 1,2,3

67891 2,4

Becomes

12345 1

12345 2

12345 3

67891 2

67891 4

Thanks for any help!!

You should be handling this in the Dataset not in SSRS.

If you need help with SQL statements to to do this I'm sure posting to the Transact-SQL forum will provide a response on how to get a dataset to come back with the relevant rows. You may have to use functions, sp's, or temporary tables to get the data back in that format.

Craig

Parsing Text String Field

Can anyone tell me how to parse out a comma-delimited text string field for reporting purposes?

Example:

ID STRING

12345 1,2,3

67891 2,4

Becomes

12345 1

12345 2

12345 3

67891 2

67891 4

Thanks for any help!!

You should be handling this in the Dataset not in SSRS.

Craig

Pareto chart in MS Reporting Services

Is MS Reporting Services capable of adding new kinds of charts like a pareto
chart? If so, i'd like to find out if there's one that plugs in to VS .NET
2003 designer and MS Reporting Services.
Dundas does make a pareto chart in their enterprise charting package. They
also designed the charts used MSRS. Does anyone know if this would help?
For those who aren't familiar with a pareto chart, it can be a mix of a bar
and a point (or line) chart that coorespond to two sets of Y axis. One Y
axis can represent an item count and the other Y axis represents the
cumulative percentage.
Thanks,
-ChrisYou may be interested in this related thread:
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/msg/2ccef00744a2f70d
If you want real Pareto charts, then the closest you can get in RS2000 is to
write a custom assembly which takes parameters and generates a chart (e.g.
with the Dundas Enterprise Edition) on-the-fly and returns it as byte array.
The custom assembly would need to retrieve the data for the chart on its own
(unless you can pass them all through parameters to the custom assembly
function). After you have implemented the custom assembly you would perform
these steps in report designer:
* Add a reference to the custom assembly through the VS menu: Report -
Report Properties - References
* Add an image to your report
* Set the image type to Database
* Set the image mimetype to e.g. image/png
* For the image value use an expression like
=MyCustomAssembly.GenerateChart(...)
Notes:
* the custom assembly has to return the image as byte[].
* you should follow the links provided below about custom assemblies and
code access security in RS because e.g. you will need to assert security
permission inside the custom assembly when using certain .NET classes and
methods for generating an image.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_rdl_6d0i.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/dngrfCodeAccessSecurityInSQLServer2000ReportingServices.asp
FYI: Dundas has plans to provide a deeply integrated design-time and
runtime-processing control of the Enterprise Edition chart for RS 2005. You
can still use familiar RS features (like grouping, aggregates, filters,
etc.) and will get full control over charting.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"TUA-Chris" <TUAChris@.discussions.microsoft.com> wrote in message
news:966F0AC6-8CB4-4FBE-AD11-4F709F77B9C7@.microsoft.com...
> Is MS Reporting Services capable of adding new kinds of charts like a
pareto
> chart? If so, i'd like to find out if there's one that plugs in to VS
.NET
> 2003 designer and MS Reporting Services.
> Dundas does make a pareto chart in their enterprise charting package.
They
> also designed the charts used MSRS. Does anyone know if this would help?
> For those who aren't familiar with a pareto chart, it can be a mix of a
bar
> and a point (or line) chart that coorespond to two sets of Y axis. One Y
> axis can represent an item count and the other Y axis represents the
> cumulative percentage.
> Thanks,
> -Chris|||I had to create a Pareto chart manually by generating both series of values within the SQL statement and then manually defining the representation of Series 1 with a bar and Series 2 with a line.
--
Message posted via http://www.sqlmonster.com|||William,
I'm trying to find how to set one series to use a bar chart and another
series to use a line, but can't seem to find anything applicable. It looks
like I can only use one kind of representation. Could you explain how I can
represent two series using different charting techniques (one bar, one line)?
Are there any on-line tutorials that show how this is done?
Thanks in advance!!
-Chris Davis
"William Nichols via SQLMonster.com" wrote:
> I had to create a Pareto chart manually by generating both series of values within the SQL statement and then manually defining the representation of Series 1 with a bar and Series 2 with a line.
> --
> Message posted via http://www.sqlmonster.com
>|||Please read this related posting which also includes a sample:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=c229b25c-b2dc-41ac-923d-decbb253dc6e&sloc=en-us
You may also be interested in this KB How-to article:
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B842422
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"TUA-Chris" <TUAChris@.discussions.microsoft.com> wrote in message
news:D4CEC1F0-4D96-42A6-A46C-25189C5E998C@.microsoft.com...
> William,
> I'm trying to find how to set one series to use a bar chart and another
> series to use a line, but can't seem to find anything applicable. It
looks
> like I can only use one kind of representation. Could you explain how I
can
> represent two series using different charting techniques (one bar, one
line)?
> Are there any on-line tutorials that show how this is done?
> Thanks in advance!!
> -Chris Davis
> "William Nichols via SQLMonster.com" wrote:
> > I had to create a Pareto chart manually by generating both series of
values within the SQL statement and then manually defining the
representation of Series 1 with a bar and Series 2 with a line.
> >
> > --
> > Message posted via http://www.sqlmonster.com
> >|||Hi Robert,
I found a way to do this without going into the RDL itself. Sorry if my
response below looks like a book, but I'm explaining in detail in case anyone
else reading this will find it useful.
I found out that in Report Designer, if a second set of data is added, you
can have it plot in the form of a line. I also found that I can add
functions to the report itself without having to jump through a bunch of
hoops to get there. Using these two features, I was able to accomplish what
I wanted.
Obviously, if I had just plotted the same data with a line, visually, you
would see a line connecting the top parts of each column in my graph. What I
wanted to see was a percentage value that showed what percentage of the
current column plus all previous columns were. This would create a line that
would increase in value up to 100%. Being new to Reporting Services, I
couldn't find a way to count the values of previous columns into a value
which would represent my percentage line point. I dug around and found that
I could create my own functions to calculate a value to graph. This was a
break-through for me, because instead of plotting the value of the field
itself, I could plot the return value of a custom function I could write.
Here's how I did it:
1) Obtain total number of items.
2) Go to Report drop-down meny and select Report Properties, then select the
Code tab
3) Enter the following code:
dim RunningPercentage = 0.0
dim TotalItems = 0;
' TotalItems gets populated with the total number of items elsewhere. I use
this
' to determine percentage.
' This is used when calculating the cumulative percentage of tickets per
category.
function RunningPercentage (accum)
RunningPercentage = RunningPercentage + (accum / TotalItems * 100)
' Trucate decimal values smaller than one-tenths of a percent...
return int (RunningPercentage * 10) / 10
end function
' This just returns RunningPercentage without accumulating...
function ShowRunningPercentage ()
' Trucate decimal values smaller than one-tenths of a percent...
return (int (RunningPercentage * 10) / 10) & "%"
end function
4) In my graph, I drag a duplicate field onto the top of the graph (Drop
data fields here)
5) Right-click the new dulpicate item, select Properties
6) For Series Label, I call it "Cumulative %"
7) For Value, I enter, =Code.RunningPercentage(Count(Fields!Item.Value))
8) Click Appearance tab and check "Plot data as line"
9) Click Point Labels tab. In the Data label field, I enter,
=Code.ShowRunningPercentage()
10) To make the percentage curve look nice, I right click on field in the
Category section of the graph (at bottom) and select Properties. Then I
click on the Sorting tab and in the Expression column, I add
=Count(Fields.Item.Value) then for Direction, I select Descending.
I'm done! Now you will see a graph sorted from largest item on the left to
smallest item on the right with an increasing curve showing the accumulative
percentage.
Thanks for your help!
-Chris
"Robert Bruckner [MSFT]" wrote:
> Please read this related posting which also includes a sample:
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=c229b25c-b2dc-41ac-923d-decbb253dc6e&sloc=en-us
> You may also be interested in this KB How-to article:
> http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B842422
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "TUA-Chris" <TUAChris@.discussions.microsoft.com> wrote in message
> news:D4CEC1F0-4D96-42A6-A46C-25189C5E998C@.microsoft.com...
> > William,
> >
> > I'm trying to find how to set one series to use a bar chart and another
> > series to use a line, but can't seem to find anything applicable. It
> looks
> > like I can only use one kind of representation. Could you explain how I
> can
> > represent two series using different charting techniques (one bar, one
> line)?
> > Are there any on-line tutorials that show how this is done?
> >
> > Thanks in advance!!
> >
> > -Chris Davis
> >
> > "William Nichols via SQLMonster.com" wrote:
> >
> > > I had to create a Pareto chart manually by generating both series of
> values within the SQL statement and then manually defining the
> representation of Series 1 with a bar and Series 2 with a line.
> > >
> > > --
> > > Message posted via http://www.sqlmonster.com
> > >
>
>|||Thanks for sharing your solution! Other people will certainly find it
useful.
Keep in mind, that your approach (calculating the "RunningValue" in code)
has limitations and will only work because you don't have category and
series grouping expressions in the chart. RS 2005 will enable the usage of
the RunningValue() function in charts which solves this for the general
case.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Yes, if you do the sorting and calculations in your dataset (through a
stored procedure)
"TUA-Chris" <TUAChris@.discussions.microsoft.com> wrote in message
news:A3DD2177-842E-40B2-A997-342F95CD4CA9@.microsoft.com...
> Hi Robert,
> I found a way to do this without going into the RDL itself. Sorry if my
> response below looks like a book, but I'm explaining in detail in case
anyone
> else reading this will find it useful.
> I found out that in Report Designer, if a second set of data is added, you
> can have it plot in the form of a line. I also found that I can add
> functions to the report itself without having to jump through a bunch of
> hoops to get there. Using these two features, I was able to accomplish
what
> I wanted.
> Obviously, if I had just plotted the same data with a line, visually, you
> would see a line connecting the top parts of each column in my graph.
What I
> wanted to see was a percentage value that showed what percentage of the
> current column plus all previous columns were. This would create a line
that
> would increase in value up to 100%. Being new to Reporting Services, I
> couldn't find a way to count the values of previous columns into a value
> which would represent my percentage line point. I dug around and found
that
> I could create my own functions to calculate a value to graph. This was a
> break-through for me, because instead of plotting the value of the field
> itself, I could plot the return value of a custom function I could write.
> Here's how I did it:
> 1) Obtain total number of items.
> 2) Go to Report drop-down meny and select Report Properties, then select
the
> Code tab
> 3) Enter the following code:
> dim RunningPercentage = 0.0
> dim TotalItems = 0;
> ' TotalItems gets populated with the total number of items elsewhere. I
use
> this
> ' to determine percentage.
> ' This is used when calculating the cumulative percentage of tickets per
> category.
> function RunningPercentage (accum)
> RunningPercentage = RunningPercentage + (accum / TotalItems * 100)
> ' Trucate decimal values smaller than one-tenths of a percent...
> return int (RunningPercentage * 10) / 10
> end function
> ' This just returns RunningPercentage without accumulating...
> function ShowRunningPercentage ()
> ' Trucate decimal values smaller than one-tenths of a percent...
> return (int (RunningPercentage * 10) / 10) & "%"
> end function
> 4) In my graph, I drag a duplicate field onto the top of the graph (Drop
> data fields here)
> 5) Right-click the new dulpicate item, select Properties
> 6) For Series Label, I call it "Cumulative %"
> 7) For Value, I enter, =Code.RunningPercentage(Count(Fields!Item.Value))
> 8) Click Appearance tab and check "Plot data as line"
> 9) Click Point Labels tab. In the Data label field, I enter,
> =Code.ShowRunningPercentage()
> 10) To make the percentage curve look nice, I right click on field in the
> Category section of the graph (at bottom) and select Properties. Then I
> click on the Sorting tab and in the Expression column, I add
> =Count(Fields.Item.Value) then for Direction, I select Descending.
> I'm done! Now you will see a graph sorted from largest item on the left
to
> smallest item on the right with an increasing curve showing the
accumulative
> percentage.
> Thanks for your help!
> -Chris
>
> "Robert Bruckner [MSFT]" wrote:
> > Please read this related posting which also includes a sample:
> >
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=c229b25c-b2dc-41ac-923d-decbb253dc6e&sloc=en-us
> >
> > You may also be interested in this KB How-to article:
> > http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B842422
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "TUA-Chris" <TUAChris@.discussions.microsoft.com> wrote in message
> > news:D4CEC1F0-4D96-42A6-A46C-25189C5E998C@.microsoft.com...
> > > William,
> > >
> > > I'm trying to find how to set one series to use a bar chart and
another
> > > series to use a line, but can't seem to find anything applicable. It
> > looks
> > > like I can only use one kind of representation. Could you explain how
I
> > can
> > > represent two series using different charting techniques (one bar, one
> > line)?
> > > Are there any on-line tutorials that show how this is done?
> > >
> > > Thanks in advance!!
> > >
> > > -Chris Davis
> > >
> > > "William Nichols via SQLMonster.com" wrote:
> > >
> > > > I had to create a Pareto chart manually by generating both series of
> > values within the SQL statement and then manually defining the
> > representation of Series 1 with a bar and Series 2 with a line.
> > > >
> > > > --
> > > > Message posted via http://www.sqlmonster.com
> > > >
> >
> >
> >

Monday, March 26, 2012

Parent-Child Dimension in a Report

Hi,
I saw some threads about using parent-child dimensions in a reporting
services report on an OLAP cube in this newsgroup. I have the same problem
that I didn't get the nesting, I want, because reporting services didn't
render it. I tried with the UniqueName and ParentUniqueName properties, but
I only get a hierarchy of Level2 and no deeper. Does anyone knows a solution
in the meantime?
Thanks
HansI have managed to get round this problem by creating a virtual
dimension of level 3 and then doing a cross join on the 2 dimensions.
This will get you your nesting

Friday, March 23, 2012

partitioning a table question...

Hi,
I have to plan a datawarehouse structure where the primary reporting usage
is to list detailed information about customers.
And also, we have OLAP cube for analysis purpose.
I'll have 100 000 customers and a fact table containing between 35 to
45millions of rows by year. (we also have 2 other fact table with 5 to
6millions of rows by year)
Generally, this fact table is filtered for a particular organizational unit
and for data in 1 year. (for report generation using report server)
So, I plan to partition my fact table by organizational unit and by year.
But I have 180 units.
Does the partitioning will works fine with 180 * 5 years = 900 tables?
on this article:
http://msdn.microsoft.com/library/d...ndw.
htm
there is a note that the maximum number of tables is 256.
But the performance gain can be very high!
The estimated size of the DW is 10gb / year (maybe more with additional
indexes)
My DTS package is ready to support partioning table loading. (the package
automatically create the new table, indexes and update the view for each
detected partition in the staging source table)
My users access my reportserver interactively, scheduling anything is not an
option.
For higher analysis, my olap cubes are ready.
thanks for your feedback.
Jerome.Hi Jerome:
I have some kind of "rule". Just make a partition for each 1 GB of data.
Or doing it for a year, would be great. If you need more granularity, give
it a try to month partitioning.
Tell me if you need some help on this
Sincerely
Alejandro Leguizamo
MVP SQL Server
Colombia
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:uwuxtzxzEHA.3336@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I have to plan a datawarehouse structure where the primary reporting usage
> is to list detailed information about customers.
> And also, we have OLAP cube for analysis purpose.
> I'll have 100 000 customers and a fact table containing between 35 to
> 45millions of rows by year. (we also have 2 other fact table with 5 to
> 6millions of rows by year)
> Generally, this fact table is filtered for a particular organizational
> unit and for data in 1 year. (for report generation using report server)
> So, I plan to partition my fact table by organizational unit and by year.
> But I have 180 units.
> Does the partitioning will works fine with 180 * 5 years = 900 tables?
> on this article:
> http://msdn.microsoft.com/library/d...nd
w.htm
> there is a note that the maximum number of tables is 256.
> But the performance gain can be very high!
> The estimated size of the DW is 10gb / year (maybe more with additional
> indexes)
> My DTS package is ready to support partioning table loading. (the package
> automatically create the new table, indexes and update the view for each
> detected partition in the staging source table)
> My users access my reportserver interactively, scheduling anything is not
> an option.
> For higher analysis, my olap cubes are ready.
> thanks for your feedback.
> Jerome.
>
>|||but does SQL Server support 500 tables in a partitioned view?
I don't need help about this, I just want to plan correctly.
and I don't want month partition but only year + organizational unit
Because I don't do sum or count aggregation in SQL (or just a little sum
compared of what my cube provide).
Generally my reports contains SQL statement like this:
* Last kown value at a specific date (subquery required) (by customer)
* events which start before and end after a specific date (between
statement) (by customer)
Its the reason of not using monthly partitions, because I never know when
these dates appear in the database.
"Alejo Leguizamo (MVP SQL)" <SQL@.sql.sql> a crit dans le message de news:
%23thX4VC0EHA.1204@.TK2MSFTNGP10.phx.gbl...
> Hi Jerome:
> I have some kind of "rule". Just make a partition for each 1 GB of data.
> Or doing it for a year, would be great. If you need more granularity, give
> it a try to month partitioning.
> Tell me if you need some help on this
> Sincerely
>
> --
> Alejandro Leguizamo
> MVP SQL Server
> Colombia
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:uwuxtzxzEHA.3336@.TK2MSFTNGP11.phx.gbl...
>|||Jerome,
Views are based on select statements. A select statement can have a max of
256 tables in the From clause. So the answer to your specific question is
no.
Danny
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:e1ghnJD0EHA.2624@.TK2MSFTNGP11.phx.gbl...
> but does SQL Server support 500 tables in a partitioned view?
> I don't need help about this, I just want to plan correctly.
> and I don't want month partition but only year + organizational unit
> Because I don't do sum or count aggregation in SQL (or just a little sum
> compared of what my cube provide).
> Generally my reports contains SQL statement like this:
> * Last kown value at a specific date (subquery required) (by customer)
> * events which start before and end after a specific date (between
> statement) (by customer)
> Its the reason of not using monthly partitions, because I never know when
> these dates appear in the database.
> "Alejo Leguizamo (MVP SQL)" <SQL@.sql.sql> a crit dans le message de news:
> %23thX4VC0EHA.1204@.TK2MSFTNGP10.phx.gbl...
>|||

sniff
I think I'll do this: partition by organization for the current year and by
year for the history... does this works in a partitioned view?
"Danny" <istdrs@.flash.net> a crit dans le message de news:
7Lkod.23022$Rf1.15232@.newssvr19.news.prodigy.com...
> Jerome,
> Views are based on select statements. A select statement can have a max
> of 256 tables in the From clause. So the answer to your specific question
> is no.
> Danny
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:e1ghnJD0EHA.2624@.TK2MSFTNGP11.phx.gbl...
>|||The primary key of the underlying tables in the view must be the same. For
this to work, you will have to query current and history separately with
each haing its own view.
Danny
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:uKoQZzP0EHA.1932@.TK2MSFTNGP09.phx.gbl...
>

> sniff
> I think I'll do this: partition by organization for the current year and
> by year for the history... does this works in a partitioned view?
> "Danny" <istdrs@.flash.net> a crit dans le message de news:
> 7Lkod.23022$Rf1.15232@.newssvr19.news.prodigy.com...
>

partitioning a table question...

Hi,
I have to plan a datawarehouse structure where the primary reporting usage
is to list detailed information about customers.
And also, we have OLAP cube for analysis purpose.
I'll have 100 000 customers and a fact table containing between 35 to
45millions of rows by year. (we also have 2 other fact table with 5 to
6millions of rows by year)
Generally, this fact table is filtered for a particular organizational unit
and for data in 1 year. (for report generation using report server)
So, I plan to partition my fact table by organizational unit and by year.
But I have 180 units.
Does the partitioning will works fine with 180 * 5 years = 900 tables?
on this article:
http://msdn.microsoft.com/library/de...itionsindw.htm
there is a note that the maximum number of tables is 256.
But the performance gain can be very high!
The estimated size of the DW is 10gb / year (maybe more with additional
indexes)
My DTS package is ready to support partioning table loading. (the package
automatically create the new table, indexes and update the view for each
detected partition in the staging source table)
My users access my reportserver interactively, scheduling anything is not an
option.
For higher analysis, my olap cubes are ready.
thanks for your feedback.
Jerome.
Hi Jerome:
I have some kind of "rule". Just make a partition for each 1 GB of data.
Or doing it for a year, would be great. If you need more granularity, give
it a try to month partitioning.
Tell me if you need some help on this
Sincerely
Alejandro Leguizamo
MVP SQL Server
Colombia
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:uwuxtzxzEHA.3336@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I have to plan a datawarehouse structure where the primary reporting usage
> is to list detailed information about customers.
> And also, we have OLAP cube for analysis purpose.
> I'll have 100 000 customers and a fact table containing between 35 to
> 45millions of rows by year. (we also have 2 other fact table with 5 to
> 6millions of rows by year)
> Generally, this fact table is filtered for a particular organizational
> unit and for data in 1 year. (for report generation using report server)
> So, I plan to partition my fact table by organizational unit and by year.
> But I have 180 units.
> Does the partitioning will works fine with 180 * 5 years = 900 tables?
> on this article:
> http://msdn.microsoft.com/library/de...itionsindw.htm
> there is a note that the maximum number of tables is 256.
> But the performance gain can be very high!
> The estimated size of the DW is 10gb / year (maybe more with additional
> indexes)
> My DTS package is ready to support partioning table loading. (the package
> automatically create the new table, indexes and update the view for each
> detected partition in the staging source table)
> My users access my reportserver interactively, scheduling anything is not
> an option.
> For higher analysis, my olap cubes are ready.
> thanks for your feedback.
> Jerome.
>
>
|||but does SQL Server support 500 tables in a partitioned view?
I don't need help about this, I just want to plan correctly.
and I don't want month partition but only year + organizational unit
Because I don't do sum or count aggregation in SQL (or just a little sum
compared of what my cube provide).
Generally my reports contains SQL statement like this:
* Last kown value at a specific date (subquery required) (by customer)
* events which start before and end after a specific date (between
statement) (by customer)
Its the reason of not using monthly partitions, because I never know when
these dates appear in the database.
"Alejo Leguizamo (MVP SQL)" <SQL@.sql.sql> a crit dans le message de news:
%23thX4VC0EHA.1204@.TK2MSFTNGP10.phx.gbl...
> Hi Jerome:
> I have some kind of "rule". Just make a partition for each 1 GB of data.
> Or doing it for a year, would be great. If you need more granularity, give
> it a try to month partitioning.
> Tell me if you need some help on this
> Sincerely
>
> --
> Alejandro Leguizamo
> MVP SQL Server
> Colombia
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:uwuxtzxzEHA.3336@.TK2MSFTNGP11.phx.gbl...
>
|||Jerome,
Views are based on select statements. A select statement can have a max of
256 tables in the From clause. So the answer to your specific question is
no.
Danny
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:e1ghnJD0EHA.2624@.TK2MSFTNGP11.phx.gbl...
> but does SQL Server support 500 tables in a partitioned view?
> I don't need help about this, I just want to plan correctly.
> and I don't want month partition but only year + organizational unit
> Because I don't do sum or count aggregation in SQL (or just a little sum
> compared of what my cube provide).
> Generally my reports contains SQL statement like this:
> * Last kown value at a specific date (subquery required) (by customer)
> * events which start before and end after a specific date (between
> statement) (by customer)
> Its the reason of not using monthly partitions, because I never know when
> these dates appear in the database.
> "Alejo Leguizamo (MVP SQL)" <SQL@.sql.sql> a crit dans le message de news:
> %23thX4VC0EHA.1204@.TK2MSFTNGP10.phx.gbl...
>
|||

sniff
I think I'll do this: partition by organization for the current year and by
year for the history... does this works in a partitioned view?
"Danny" <istdrs@.flash.net> a crit dans le message de news:
7Lkod.23022$Rf1.15232@.newssvr19.news.prodigy.com.. .
> Jerome,
> Views are based on select statements. A select statement can have a max
> of 256 tables in the From clause. So the answer to your specific question
> is no.
> Danny
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:e1ghnJD0EHA.2624@.TK2MSFTNGP11.phx.gbl...
>
|||The primary key of the underlying tables in the view must be the same. For
this to work, you will have to query current and history separately with
each haing its own view.
Danny
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:uKoQZzP0EHA.1932@.TK2MSFTNGP09.phx.gbl...
>

Tuesday, March 20, 2012

Parent Attribute Hierarchy

Does anyone know how to create a parent-child hierarchy that can be expanded in reporting services as well as analysis services? Currently, I have a dimension with three attributes: parent, child, and key. Everything seems to work fine in Analysis Services. However, reporting services doesn't seem to recognize the parent hierarchy as being a true hierarchy.

Any help? Thank you.

Does anyone have an idea or just need some more clarification?

Thanks

|||

There is only sinlge way to create parent-child hierarchy in Analysis Services. You dont have any choice here.

Not sure about Reporting Services and how it is displaying parent-child hierachy. Moving thread to Reporting Services forum.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Paramters in WITH part of MDX

Hi,

I need a parameter within the WITH part of an MDX Statement in Reporting Services. I tried several types, but all will bring an error. I will do it like:

WITH MEMBER [Measures].[Amount] AS STRTOMEMBER(@.MyMeasure) SELECT { [Measures].[Amount] } on columns, ......

The @.MyMeasures should be a combobox with the values like

Name: Sales Volume (kg) Value: [Measures].[Sales Volume KG]
Name: Sales Volume (m2) Value: [Measures].[Sales Volume KG]
... and so on.

What's my failure?

Thanks
Hans

Whats the error?|||

Hi Adam,

The error is:

The query will not be retrieved from the query builder. Check the query for syntax error.
The syntax of

WITH MEMBER [Measures].[Amount] AS STRTOMEMBER(@.MyMeasure)

is not correct, but I didn't find the correct syntax to make a dynamic selectable measure from a combobox.

Thanks
Hans

|||I've seen a lot of these posts where using a parameter in the WITH section throws an exception. The only solution I've found so far is to revert to connecting via the OLEDB provider and using an expression based MDX query. See this thread for full details http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=726232&SiteID=1|||

Hi @.All,

After a lot of testing, I got it to work. The solution is, you have to mask the Parameter with apostrophes like this:

WITH MEMBER [Measures].[Amount] AS STRTOMEMBER("" + @.SalesFigure + "")
SELECT NON EMPTY .....

The key is, to mask it with double apostophes to escape the "single apostroph". Now this part of my many starting problems works!

Hans

Parametric

Hello,

I'm using SQL 2005 and reporting services.

I have several parameters, the report show two parameters in the same line and I would like to present three parameters in the same line.

how can I do that ?

This is not natively supported.

The closest you can get is to write your own parameter frontend application and then e.g. use the RS report viewer controls to send the parameters to the report server and execute the report in "remote" mode. Other options include to send the parameters via URL-access from your custom frontend application.

-- Robert

Monday, March 12, 2012

Parameters will not Default

Greetings

I am using SQL 2005 Reporting Services and I have a problem with parameters. If I leave 3 of the 4 values as non queried in the Available values section and mark them as a Default Values: of NULL, then the report works fine. However if I set the Available values to a query I have written and leave the Default values as NULL, when I view the report in either preview mode or on the reports server, it wants me to specify a value from the boxes and the tick box for NULL has disappeared. Allow NULL value is ticked inthe first section, Properties.

Anybody got any suggestions?

If you have available values, then the allowable values of the parameter are limited to those returned by your query. If you want to allow the parameter to be null, then null must be one of the available values returned by your query.

-Albert

Parameters to reports that only show is previous parameters filled

I don't know if I can do this with Reporting Services. I have ten parameters to a stored procedure. However, each parameter only shows when the previous one is selected. For sake of this discussion, the queries from which the parameters come are completly unrelated.
Initially, only the pull-down for parameter 1 appears. When the user selects a value for parameter 1 using the initial pull-down, the pull down for parameter 2 should appear. When the user selects a value from this pull down, a pull down from parameter 3 should appear.
At any point, the user can click View Report and pass the parameters he selected and all other parameters null.
Thanks in advance for any help.
JustinYes, RS supports sequenced parameter prompting. You build the valid values
query for a parameter using a query that includes previously selected
parameter values. I believe there is a sample called 'Product Line Sales'
that illustrates this technique.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Justin30519" <Justin30519@.discussions.microsoft.com> wrote in message
news:01FC2CE7-C435-47EC-AD00-E2762510E522@.microsoft.com...
>I don't know if I can do this with Reporting Services. I have ten
>parameters to a stored procedure. However, each parameter only shows when
>the previous one is selected. For sake of this discussion, the queries from
>which the parameters come are completly unrelated.
> Initially, only the pull-down for parameter 1 appears. When the user
> selects a value for parameter 1 using the initial pull-down, the pull down
> for parameter 2 should appear. When the user selects a value from this
> pull down, a pull down from parameter 3 should appear.
> At any point, the user can click View Report and pass the parameters he
> selected and all other parameters null.
> Thanks in advance for any help.
> Justin
>|||> Yes, RS supports sequenced parameter prompting. You build the valid values
> query for a parameter using a query that includes previously selected
> parameter values. I believe there is a sample called 'Product Line Sales'
> that illustrates this technique.
>
Thank you very much for your reply. It was helpful. I can do what my boss asked now. :)
Justin

parameters really only a filter?

Am I missing a trick here? When i set up my parameter in reporting
services and hit preview, the prompt apears and asks for the specified
column - however the sql generated is not refined any. it brings back
all the data and then filters on the selected column in the report.
By contrast in business objects if i used the @.prompt function the sql
generated would be refined with the input of the end user ...eg
cost_centre_code = 1234 as opposed all cost centres. What reporting
services seems to be doing is bringing back all cost centres then
filtering on 1234.
If im right it has some pretty serious implications in terms of speed
of reporting -as my query is returning all data values rather than
one...
any ideas? any input? Isnt a parameter really just a filter rather
than a condition?
thanks for any ideas
gregIt depends on what you are doing. If you use the parameter in the WHERE
clause of your SQL then you would indeed be doing the 'filtering' on the
Database Server. If you are putting your parameter in a filter expression -
then yes this means that the filtering is done in the Report Server, and all
the data is requested from the Database Server.
You are right it is better to optimise queries to have the data pre-filtered
on the Database Server where advantage can be taken of Indexes.
We cover these issues in depth in Chapter 6 of our book - "Hitchhiker's
Guide to SQL Server 2000 Reporting Services"
Peter Blackburn
Hitchhiker's Guide to SQL Server 2000 Reporting Services
http://www.sqlreportingservices.net
"Greg" <greg_cochrane@.hotmail.com> wrote in message
news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> Am I missing a trick here? When i set up my parameter in reporting
> services and hit preview, the prompt apears and asks for the specified
> column - however the sql generated is not refined any. it brings back
> all the data and then filters on the selected column in the report.
> By contrast in business objects if i used the @.prompt function the sql
> generated would be refined with the input of the end user ...eg
> cost_centre_code = 1234 as opposed all cost centres. What reporting
> services seems to be doing is bringing back all cost centres then
> filtering on 1234.
> If im right it has some pretty serious implications in terms of speed
> of reporting -as my query is returning all data values rather than
> one...
> any ideas? any input? Isnt a parameter really just a filter rather
> than a condition?
>
> thanks for any ideas
> greg|||Greg, I answered this very completely at about 1:30 in your other posting.
Just to reiterate. Filters and query parameters are two different things and
both can use report parameters. You are using filters (which retrieve and
then filter) versus query parameters that are part of the where clause.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg" <greg_cochrane@.hotmail.com> wrote in message
news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> Am I missing a trick here? When i set up my parameter in reporting
> services and hit preview, the prompt apears and asks for the specified
> column - however the sql generated is not refined any. it brings back
> all the data and then filters on the selected column in the report.
> By contrast in business objects if i used the @.prompt function the sql
> generated would be refined with the input of the end user ...eg
> cost_centre_code = 1234 as opposed all cost centres. What reporting
> services seems to be doing is bringing back all cost centres then
> filtering on 1234.
> If im right it has some pretty serious implications in terms of speed
> of reporting -as my query is returning all data values rather than
> one...
> any ideas? any input? Isnt a parameter really just a filter rather
> than a condition?
>
> thanks for any ideas
> greg|||Hi all -
Thanks for the info. I have now a parameter working on the database
server - specified in the where clause as you say - thats great.
However What I want is a prompt for the user - to enter in an
assignment id. When I pass the parameter to the where clause and hit
run in the Data tab I am asked for the Parameter value (on the Data
tab ) here I want a drop down list of values - which I would like
replicated when in the preview screen - so that the user can enter a
value from the drop down list of values box. At the moment I can only
enter in the preview panel what i have specified in the Data tab and
bring back the correct result. How can I get a drop down Lovs
todisplay when the parameter is in the where clause. I tried to point
both label and value to the specific data set but it would not allow -
saying:
"A label expression used for the report parameter ?ApplicationID'
refers to a field. Fields cannot be used in report parameter
expressions."
Any ideas
help is very much appreciated
Greg
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> Greg, I answered this very completely at about 1:30 in your other posting.
> Just to reiterate. Filters and query parameters are two different things and
> both can use report parameters. You are using filters (which retrieve and
> then filter) versus query parameters that are part of the where clause.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Greg" <greg_cochrane@.hotmail.com> wrote in message
> news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > Am I missing a trick here? When i set up my parameter in reporting
> > services and hit preview, the prompt apears and asks for the specified
> > column - however the sql generated is not refined any. it brings back
> > all the data and then filters on the selected column in the report.
> >
> > By contrast in business objects if i used the @.prompt function the sql
> > generated would be refined with the input of the end user ...eg
> > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > services seems to be doing is bringing back all cost centres then
> > filtering on 1234.
> >
> > If im right it has some pretty serious implications in terms of speed
> > of reporting -as my query is returning all data values rather than
> > one...
> >
> > any ideas? any input? Isnt a parameter really just a filter rather
> > than a condition?
> >
> >
> > thanks for any ideas
> >
> > greg|||You are missing a concept here. You only use the data tab to test out your
query. To test your report you use the preview tab. That is where you have
control over the parameters. The parameters can be free form, based on a
list or based on a dataset. In the layout tab go to Report menu, report
parameters and you'll see where you do this. The report parameters have to
be mapped to the query parameters as well (they might be already). You can
check this by clicking on the ... in the data tab and going to the
parameters tab.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg" <greg_cochrane@.hotmail.com> wrote in message
news:2dba7d00.0411100801.51f5bf73@.posting.google.com...
> Hi all -
> Thanks for the info. I have now a parameter working on the database
> server - specified in the where clause as you say - thats great.
> However What I want is a prompt for the user - to enter in an
> assignment id. When I pass the parameter to the where clause and hit
> run in the Data tab I am asked for the Parameter value (on the Data
> tab ) here I want a drop down list of values - which I would like
> replicated when in the preview screen - so that the user can enter a
> value from the drop down list of values box. At the moment I can only
> enter in the preview panel what i have specified in the Data tab and
> bring back the correct result. How can I get a drop down Lovs
> todisplay when the parameter is in the where clause. I tried to point
> both label and value to the specific data set but it would not allow -
> saying:
> "A label expression used for the report parameter 'ApplicationID'
> refers to a field. Fields cannot be used in report parameter
> expressions."
> Any ideas
> help is very much appreciated
> Greg
>
>
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > Greg, I answered this very completely at about 1:30 in your other
posting.
> > Just to reiterate. Filters and query parameters are two different things
and
> > both can use report parameters. You are using filters (which retrieve
and
> > then filter) versus query parameters that are part of the where clause.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > Am I missing a trick here? When i set up my parameter in reporting
> > > services and hit preview, the prompt apears and asks for the specified
> > > column - however the sql generated is not refined any. it brings back
> > > all the data and then filters on the selected column in the report.
> > >
> > > By contrast in business objects if i used the @.prompt function the sql
> > > generated would be refined with the input of the end user ...eg
> > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > services seems to be doing is bringing back all cost centres then
> > > filtering on 1234.
> > >
> > > If im right it has some pretty serious implications in terms of speed
> > > of reporting -as my query is returning all data values rather than
> > > one...
> > >
> > > any ideas? any input? Isnt a parameter really just a filter rather
> > > than a condition?
> > >
> > >
> > > thanks for any ideas
> > >
> > > greg|||If you want the drop-down you need to do the following:
1) Create a second Dataset that returns the list of Valid Assignment ID's
and a second column for the labels you want in the drop-down
2) On the main menu, click on Report -> Report Parameters
3) Click on the Assignment ID parameter
4) In the Available Values area, click on From Query
5) Choose the new Assignment ID DataSet, Value Field, and Label Field
Now try the Preview tab.
Hope this helps
"Greg" wrote:
> Hi all -
> Thanks for the info. I have now a parameter working on the database
> server - specified in the where clause as you say - thats great.
> However What I want is a prompt for the user - to enter in an
> assignment id. When I pass the parameter to the where clause and hit
> run in the Data tab I am asked for the Parameter value (on the Data
> tab ) here I want a drop down list of values - which I would like
> replicated when in the preview screen - so that the user can enter a
> value from the drop down list of values box. At the moment I can only
> enter in the preview panel what i have specified in the Data tab and
> bring back the correct result. How can I get a drop down Lovs
> todisplay when the parameter is in the where clause. I tried to point
> both label and value to the specific data set but it would not allow -
> saying:
> "A label expression used for the report parameter â'ApplicationID'
> refers to a field. Fields cannot be used in report parameter
> expressions."
> Any ideas
> help is very much appreciated
> Greg
>
>
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > Greg, I answered this very completely at about 1:30 in your other posting.
> > Just to reiterate. Filters and query parameters are two different things and
> > both can use report parameters. You are using filters (which retrieve and
> > then filter) versus query parameters that are part of the where clause.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > Am I missing a trick here? When i set up my parameter in reporting
> > > services and hit preview, the prompt apears and asks for the specified
> > > column - however the sql generated is not refined any. it brings back
> > > all the data and then filters on the selected column in the report.
> > >
> > > By contrast in business objects if i used the @.prompt function the sql
> > > generated would be refined with the input of the end user ...eg
> > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > services seems to be doing is bringing back all cost centres then
> > > filtering on 1234.
> > >
> > > If im right it has some pretty serious implications in terms of speed
> > > of reporting -as my query is returning all data values rather than
> > > one...
> > >
> > > any ideas? any input? Isnt a parameter really just a filter rather
> > > than a condition?
> > >
> > >
> > > thanks for any ideas
> > >
> > > greg
>|||Hi there - sorry Im really new to reporting services and this is going
to sound like a simple question! but how do I create a new data set
within a current report' I am trying to build a list of values for a
column in my first report - see below.
Many thanks - help is appreciated.
Greg
"mlapoint" <mlapoint@.discussions.microsoft.com> wrote in message news:<31705145-03B2-45B4-96F9-4D0A2608093A@.microsoft.com>...
> If you want the drop-down you need to do the following:
> 1) Create a second Dataset that returns the list of Valid Assignment ID's
> and a second column for the labels you want in the drop-down
> 2) On the main menu, click on Report -> Report Parameters
> 3) Click on the Assignment ID parameter
> 4) In the Available Values area, click on From Query
> 5) Choose the new Assignment ID DataSet, Value Field, and Label Field
> Now try the Preview tab.
> Hope this helps
> "Greg" wrote:
> > Hi all -
> >
> > Thanks for the info. I have now a parameter working on the database
> > server - specified in the where clause as you say - thats great.
> > However What I want is a prompt for the user - to enter in an
> > assignment id. When I pass the parameter to the where clause and hit
> > run in the Data tab I am asked for the Parameter value (on the Data
> > tab ) here I want a drop down list of values - which I would like
> > replicated when in the preview screen - so that the user can enter a
> > value from the drop down list of values box. At the moment I can only
> > enter in the preview panel what i have specified in the Data tab and
> > bring back the correct result. How can I get a drop down Lovs
> > todisplay when the parameter is in the where clause. I tried to point
> > both label and value to the specific data set but it would not allow -
> > saying:
> > "A label expression used for the report parameter â'ApplicationID'
> > refers to a field. Fields cannot be used in report parameter
> > expressions."
> >
> > Any ideas
> >
> > help is very much appreciated
> >
> > Greg
> >
> >
> >
> >
> >
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > > Greg, I answered this very completely at about 1:30 in your other posting.
> > > Just to reiterate. Filters and query parameters are two different things and
> > > both can use report parameters. You are using filters (which retrieve and
> > > then filter) versus query parameters that are part of the where clause.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > > Am I missing a trick here? When i set up my parameter in reporting
> > > > services and hit preview, the prompt apears and asks for the specified
> > > > column - however the sql generated is not refined any. it brings back
> > > > all the data and then filters on the selected column in the report.
> > > >
> > > > By contrast in business objects if i used the @.prompt function the sql
> > > > generated would be refined with the input of the end user ...eg
> > > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > > services seems to be doing is bringing back all cost centres then
> > > > filtering on 1234.
> > > >
> > > > If im right it has some pretty serious implications in terms of speed
> > > > of reporting -as my query is returning all data values rather than
> > > > one...
> > > >
> > > > any ideas? any input? Isnt a parameter really just a filter rather
> > > > than a condition?
> > > >
> > > >
> > > > thanks for any ideas
> > > >
> > > > greg
> >|||Q Do i "add a new item" and write the sql free hand in there? or do I
create a new report? if so how do I link the result set achieved
through to the parameter on my first report?
Greg
greg_cochrane@.hotmail.com (Greg) wrote in message news:<2dba7d00.0411110257.40fdc0a@.posting.google.com>...
> Hi there - sorry Im really new to reporting services and this is going
> to sound like a simple question! but how do I create a new data set
> within a current report' I am trying to build a list of values for a
> column in my first report - see below.
> Many thanks - help is appreciated.
> Greg
>
>
> "mlapoint" <mlapoint@.discussions.microsoft.com> wrote in message news:<31705145-03B2-45B4-96F9-4D0A2608093A@.microsoft.com>...
> > If you want the drop-down you need to do the following:
> >
> > 1) Create a second Dataset that returns the list of Valid Assignment ID's
> > and a second column for the labels you want in the drop-down
> > 2) On the main menu, click on Report -> Report Parameters
> > 3) Click on the Assignment ID parameter
> > 4) In the Available Values area, click on From Query
> > 5) Choose the new Assignment ID DataSet, Value Field, and Label Field
> >
> > Now try the Preview tab.
> >
> > Hope this helps
> >
> > "Greg" wrote:
> >
> > > Hi all -
> > >
> > > Thanks for the info. I have now a parameter working on the database
> > > server - specified in the where clause as you say - thats great.
> > > However What I want is a prompt for the user - to enter in an
> > > assignment id. When I pass the parameter to the where clause and hit
> > > run in the Data tab I am asked for the Parameter value (on the Data
> > > tab ) here I want a drop down list of values - which I would like
> > > replicated when in the preview screen - so that the user can enter a
> > > value from the drop down list of values box. At the moment I can only
> > > enter in the preview panel what i have specified in the Data tab and
> > > bring back the correct result. How can I get a drop down Lovs
> > > todisplay when the parameter is in the where clause. I tried to point
> > > both label and value to the specific data set but it would not allow -
> > > saying:
> > > "A label expression used for the report parameter â'ApplicationID'
> > > refers to a field. Fields cannot be used in report parameter
> > > expressions."
> > >
> > > Any ideas
> > >
> > > help is very much appreciated
> > >
> > > Greg
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > > > Greg, I answered this very completely at about 1:30 in your other posting.
> > > > Just to reiterate. Filters and query parameters are two different things and
> > > > both can use report parameters. You are using filters (which retrieve and
> > > > then filter) versus query parameters that are part of the where clause.
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > > > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > > > Am I missing a trick here? When i set up my parameter in reporting
> > > > > services and hit preview, the prompt apears and asks for the specified
> > > > > column - however the sql generated is not refined any. it brings back
> > > > > all the data and then filters on the selected column in the report.
> > > > >
> > > > > By contrast in business objects if i used the @.prompt function the sql
> > > > > generated would be refined with the input of the end user ...eg
> > > > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > > > services seems to be doing is bringing back all cost centres then
> > > > > filtering on 1234.
> > > > >
> > > > > If im right it has some pretty serious implications in terms of speed
> > > > > of reporting -as my query is returning all data values rather than
> > > > > one...
> > > > >
> > > > > any ideas? any input? Isnt a parameter really just a filter rather
> > > > > than a condition?
> > > > >
> > > > >
> > > > > thanks for any ideas
> > > > >
> > > > > greg
> > >|||go to the data tab and click on the drop down nesxt to your dataset name.
one of the options will be <new dataset> you will need to code the SQL but
for a parameter list it should be straightforward.
"Greg" wrote:
> Q Do i "add a new item" and write the sql free hand in there? or do I
> create a new report? if so how do I link the result set achieved
> through to the parameter on my first report?
>
> Greg
>
> greg_cochrane@.hotmail.com (Greg) wrote in message news:<2dba7d00.0411110257.40fdc0a@.posting.google.com>...
> > Hi there - sorry Im really new to reporting services and this is going
> > to sound like a simple question! but how do I create a new data set
> > within a current report' I am trying to build a list of values for a
> > column in my first report - see below.
> >
> > Many thanks - help is appreciated.
> >
> > Greg
> >
> >
> >
> >
> > "mlapoint" <mlapoint@.discussions.microsoft.com> wrote in message news:<31705145-03B2-45B4-96F9-4D0A2608093A@.microsoft.com>...
> > > If you want the drop-down you need to do the following:
> > >
> > > 1) Create a second Dataset that returns the list of Valid Assignment ID's
> > > and a second column for the labels you want in the drop-down
> > > 2) On the main menu, click on Report -> Report Parameters
> > > 3) Click on the Assignment ID parameter
> > > 4) In the Available Values area, click on From Query
> > > 5) Choose the new Assignment ID DataSet, Value Field, and Label Field
> > >
> > > Now try the Preview tab.
> > >
> > > Hope this helps
> > >
> > > "Greg" wrote:
> > >
> > > > Hi all -
> > > >
> > > > Thanks for the info. I have now a parameter working on the database
> > > > server - specified in the where clause as you say - thats great.
> > > > However What I want is a prompt for the user - to enter in an
> > > > assignment id. When I pass the parameter to the where clause and hit
> > > > run in the Data tab I am asked for the Parameter value (on the Data
> > > > tab ) here I want a drop down list of values - which I would like
> > > > replicated when in the preview screen - so that the user can enter a
> > > > value from the drop down list of values box. At the moment I can only
> > > > enter in the preview panel what i have specified in the Data tab and
> > > > bring back the correct result. How can I get a drop down Lovs
> > > > todisplay when the parameter is in the where clause. I tried to point
> > > > both label and value to the specific data set but it would not allow -
> > > > saying:
> > > > "A label expression used for the report parameter â'ApplicationID'
> > > > refers to a field. Fields cannot be used in report parameter
> > > > expressions."
> > > >
> > > > Any ideas
> > > >
> > > > help is very much appreciated
> > > >
> > > > Greg
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > > > > Greg, I answered this very completely at about 1:30 in your other posting.
> > > > > Just to reiterate. Filters and query parameters are two different things and
> > > > > both can use report parameters. You are using filters (which retrieve and
> > > > > then filter) versus query parameters that are part of the where clause.
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > >
> > > > > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > > > > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > > > > Am I missing a trick here? When i set up my parameter in reporting
> > > > > > services and hit preview, the prompt apears and asks for the specified
> > > > > > column - however the sql generated is not refined any. it brings back
> > > > > > all the data and then filters on the selected column in the report.
> > > > > >
> > > > > > By contrast in business objects if i used the @.prompt function the sql
> > > > > > generated would be refined with the input of the end user ...eg
> > > > > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > > > > services seems to be doing is bringing back all cost centres then
> > > > > > filtering on 1234.
> > > > > >
> > > > > > If im right it has some pretty serious implications in terms of speed
> > > > > > of reporting -as my query is returning all data values rather than
> > > > > > one...
> > > > > >
> > > > > > any ideas? any input? Isnt a parameter really just a filter rather
> > > > > > than a condition?
> > > > > >
> > > > > >
> > > > > > thanks for any ideas
> > > > > >
> > > > > > greg
> > > >
>|||Hi John - thanks very much!! got it working now!
Greg
"johnE" <johnE@.discussions.microsoft.com> wrote in message news:<70BEB3D8-6A8D-4609-A7D5-D387A4CFE865@.microsoft.com>...
> go to the data tab and click on the drop down nesxt to your dataset name.
> one of the options will be <new dataset> you will need to code the SQL but
> for a parameter list it should be straightforward.
> "Greg" wrote:
> > Q Do i "add a new item" and write the sql free hand in there? or do I
> > create a new report? if so how do I link the result set achieved
> > through to the parameter on my first report?
> >
> >
> > Greg
> >
> >
> > greg_cochrane@.hotmail.com (Greg) wrote in message news:<2dba7d00.0411110257.40fdc0a@.posting.google.com>...
> > > Hi there - sorry Im really new to reporting services and this is going
> > > to sound like a simple question! but how do I create a new data set
> > > within a current report' I am trying to build a list of values for a
> > > column in my first report - see below.
> > >
> > > Many thanks - help is appreciated.
> > >
> > > Greg
> > >
> > >
> > >
> > >
> > > "mlapoint" <mlapoint@.discussions.microsoft.com> wrote in message news:<31705145-03B2-45B4-96F9-4D0A2608093A@.microsoft.com>...
> > > > If you want the drop-down you need to do the following:
> > > >
> > > > 1) Create a second Dataset that returns the list of Valid Assignment ID's
> > > > and a second column for the labels you want in the drop-down
> > > > 2) On the main menu, click on Report -> Report Parameters
> > > > 3) Click on the Assignment ID parameter
> > > > 4) In the Available Values area, click on From Query
> > > > 5) Choose the new Assignment ID DataSet, Value Field, and Label Field
> > > >
> > > > Now try the Preview tab.
> > > >
> > > > Hope this helps
> > > >
> > > > "Greg" wrote:
> > > >
> > > > > Hi all -
> > > > >
> > > > > Thanks for the info. I have now a parameter working on the database
> > > > > server - specified in the where clause as you say - thats great.
> > > > > However What I want is a prompt for the user - to enter in an
> > > > > assignment id. When I pass the parameter to the where clause and hit
> > > > > run in the Data tab I am asked for the Parameter value (on the Data
> > > > > tab ) here I want a drop down list of values - which I would like
> > > > > replicated when in the preview screen - so that the user can enter a
> > > > > value from the drop down list of values box. At the moment I can only
> > > > > enter in the preview panel what i have specified in the Data tab and
> > > > > bring back the correct result. How can I get a drop down Lovs
> > > > > todisplay when the parameter is in the where clause. I tried to point
> > > > > both label and value to the specific data set but it would not allow -
> > > > > saying:
> > > > > "A label expression used for the report parameter â'ApplicationID'
> > > > > refers to a field. Fields cannot be used in report parameter
> > > > > expressions."
> > > > >
> > > > > Any ideas
> > > > >
> > > > > help is very much appreciated
> > > > >
> > > > > Greg
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > > > > > Greg, I answered this very completely at about 1:30 in your other posting.
> > > > > > Just to reiterate. Filters and query parameters are two different things and
> > > > > > both can use report parameters. You are using filters (which retrieve and
> > > > > > then filter) versus query parameters that are part of the where clause.
> > > > > >
> > > > > > --
> > > > > > Bruce Loehle-Conger
> > > > > > MVP SQL Server Reporting Services
> > > > > >
> > > > > > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > > > > > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > > > > > Am I missing a trick here? When i set up my parameter in reporting
> > > > > > > services and hit preview, the prompt apears and asks for the specified
> > > > > > > column - however the sql generated is not refined any. it brings back
> > > > > > > all the data and then filters on the selected column in the report.
> > > > > > >
> > > > > > > By contrast in business objects if i used the @.prompt function the sql
> > > > > > > generated would be refined with the input of the end user ...eg
> > > > > > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > > > > > services seems to be doing is bringing back all cost centres then
> > > > > > > filtering on 1234.
> > > > > > >
> > > > > > > If im right it has some pretty serious implications in terms of speed
> > > > > > > of reporting -as my query is returning all data values rather than
> > > > > > > one...
> > > > > > >
> > > > > > > any ideas? any input? Isnt a parameter really just a filter rather
> > > > > > > than a condition?
> > > > > > >
> > > > > > >
> > > > > > > thanks for any ideas
> > > > > > >
> > > > > > > greg
> > > > >
> >

Parameters on Reporting Services 2005 REPOST

Hi, I experiencing some problems to access the Parameters collection inside
a custom code to build a sql statement.
Below is my code (very simple):
DataSet:
=Code.SQL(Parameters)
Custom Code:
Public Function SQL(ByRef pars As Object) As String
Dim stmt as String
stmt = "SELECT * FROM customers WHERE ID = " & pars!ID.Value
return stmt
End Function
This code works just fine on the Preview(Designer) but if I test the report
on the
browser, it doesnt work and returns the following error:
a.. An error has occurred during report processing.
a.. Cannot set the command text for data set 'ExpoMedios'.
a.. Error during processing of the CommandText expression of dataset
'ExpoMedios'.
Doing some debugging the error message inside the function is:
Attempt to access the method failed.
Can anyone pleae explain why this is happening. Your help will be
appreciated.
Regards,
FabianHi,
have you tried to declare pars As Parameter and not as Object ?
"Fabian von Romberg" wrote:
> Hi, I experiencing some problems to access the Parameters collection inside
> a custom code to build a sql statement.
> Below is my code (very simple):
> DataSet:
> =Code.SQL(Parameters)
> Custom Code:
> Public Function SQL(ByRef pars As Object) As String
> Dim stmt as String
> stmt = "SELECT * FROM customers WHERE ID = " & pars!ID.Value
> return stmt
> End Function
>
> This code works just fine on the Preview(Designer) but if I test the report
> on the
> browser, it doesnt work and returns the following error:
> a.. An error has occurred during report processing.
> a.. Cannot set the command text for data set 'ExpoMedios'.
> a.. Error during processing of the CommandText expression of dataset
> 'ExpoMedios'.
> Doing some debugging the error message inside the function is:
> Attempt to access the method failed.
>
> Can anyone pleae explain why this is happening. Your help will be
> appreciated.
> Regards,
> Fabian
>
>|||Whatt!!!!!!!!!!!!,
I should have tried that before. It did it. I used this code on the older
version of Reporting Services and never got that error message, actually I
think I was not able to set a function parameter as type of Parameters but
Object. On 2005's seems to be the correct way to do it.
Thanks Cedric, I appreciated it.
Regards,
Fabian von Romberg
"Cedric" <Cedric@.discussions.microsoft.com> wrote in message
news:C8A38357-8A82-473A-8208-6DEEBF40DC9F@.microsoft.com...
> Hi,
> have you tried to declare pars As Parameter and not as Object ?
>
> "Fabian von Romberg" wrote:
> > Hi, I experiencing some problems to access the Parameters collection
inside
> > a custom code to build a sql statement.
> >
> > Below is my code (very simple):
> >
> > DataSet:
> > =Code.SQL(Parameters)
> >
> > Custom Code:
> >
> > Public Function SQL(ByRef pars As Object) As String
> > Dim stmt as String
> > stmt = "SELECT * FROM customers WHERE ID = " & pars!ID.Value
> > return stmt
> > End Function
> >
> >
> > This code works just fine on the Preview(Designer) but if I test the
report
> > on the
> > browser, it doesnt work and returns the following error:
> > a.. An error has occurred during report processing.
> > a.. Cannot set the command text for data set 'ExpoMedios'.
> > a.. Error during processing of the CommandText expression of
dataset
> > 'ExpoMedios'.
> >
> > Doing some debugging the error message inside the function is:
> >
> > Attempt to access the method failed.
> >
> >
> > Can anyone pleae explain why this is happening. Your help will be
> > appreciated.
> >
> > Regards,
> > Fabian
> >
> >
> >
> >

Friday, March 9, 2012

Parameters in WITH part of MDX

Hi,

I need a parameter within the WITH part of an MDX Statement in Reporting Services. I tried several types, but all will bring an error. I will do it like:

WITH MEMBER [Measures].[Amount] AS STRTOMEMBER(@.MyMeasure) SELECT { [Measures].[Amount] } on columns, ......

The @.MyMeasures should be a combobox with the values like

Name: Sales Volume (kg) Value: [Measures].[Sales Volume KG]
Name: Sales Volume (m2) Value: [Measures].[Sales Volume KG]
... and so on.

What's my failure?

Thanks
Hans

Whats the error?|||

Hi Adam,

The error is:

The query will not be retrieved from the query builder. Check the query for syntax error.
The syntax of

WITH MEMBER [Measures].[Amount] AS STRTOMEMBER(@.MyMeasure)

is not correct, but I didn't find the correct syntax to make a dynamic selectable measure from a combobox.

Thanks
Hans

Hi @.All,

After a lot of testing, I got it to work. The solution is, you have to mask the Parameter with apostrophes like this:

WITH MEMBER [Measures].[Amount] AS STRTOMEMBER("" + @.SalesFigure + "")
SELECT NON EMPTY .....

The key is, to mask it with double apostophes to escape the "single apostroph". Now this part of my many starting problems works!

Hans

Parameters in URL - Problem

Hi,

I am new to Reporting services and have managed to create some reports. I want to pass some parameters in the URL to the report. I know how to do this but some parameters are not passing to the parameter boxes. They are clearly displayed in the URL but are not populating the boxes. I have one report done before i came and this used to work but now it doesnt work.

Any ideas?

Matt

Hi Matt,

i am unable to understand ur prob completely. But still, if the parameter field is a dropdown list, then the value for that parameter passed in the URL should exists in the dropdown list or else the report will throw an error.

Cheers

Chakri.

|||

Try the sample reports available with your installation of SQL Server. They should show you how to do this:

http://msdn2.microsoft.com/en-us/library/ms161542.aspx

Hope that helps,

-Lukasz

|||

Hi All,

I have the similar kind of problem where i can't see the parameters and their values.

I have a simple report where i am passing employeeid as parameter. When i run it on the server it prompt me for employeeid which after giving displays the report but in the url of browser , i cannot see the parameter and it's value .

Anybody has any idea why this is happenning.

Thanks,

Nagul Shaik

|||

We do not show the parameter in the URL when you're using the report viewer. It does not matter, the report is receiving the parameter.

To see what value the report is receiving you can add a textbox to the report and set it's value to:

=ReportParameters!ParameterName.Value

Hope that helps,

-Lukasz

|||

thanks for the reply but i am not using any report viewer. I am trying access the report thru browser giving the parameters in query string instead of in the prompt.

My requirement is to create hyperlink in my aspx page for that report and when the logged in user clicks on it , i have to pass userid to that report to show the contents of the report.

My sql2005 reporting service is running on WIN2K3.

Please help me with it.

|||

This topic in books online provides you the starting point for how to construct URLs understood by the report server.

http://msdn2.microsoft.com/de-de/library/ms155362.aspx

to pass a particular parameter you defined in the report, you can just append <parametername>=<value> to the end of the query string.

The User!UserId global variable is not a parameter - it is determined by who is logged into the report server. You cannot control it on the URL itself.

Hope that helps,

-Lukasz

|||

http://msdn2.microsoft.com/en-us/library/ms153586(SQL.90).aspx

Parameters in URL - Problem

Hi,

Any ideas?

Matt

Hi Matt,

Cheers

Chakri.

|||

Try the sample reports available with your installation of SQL Server. They should show you how to do this:

http://msdn2.microsoft.com/en-us/library/ms161542.aspx

Hope that helps,

-Lukasz

|||

Hi All,

I have the similar kind of problem where i can't see the parameters and their values.

Anybody has any idea why this is happenning.

Thanks,

Nagul Shaik

|||

We do not show the parameter in the URL when you're using the report viewer. It does not matter, the report is receiving the parameter.

To see what value the report is receiving you can add a textbox to the report and set it's value to:

=ReportParameters!ParameterName.Value

Hope that helps,

-Lukasz

|||

thanks for the reply but i am not using any report viewer. I am trying access the report thru browser giving the parameters in query string instead of in the prompt.

My requirement is to create hyperlink in my aspx page for that report and when the logged in user clicks on it , i have to pass userid to that report to show the contents of the report.

My sql2005 reporting service is running on WIN2K3.

Please help me with it.

|||

This topic in books online provides you the starting point for how to construct URLs understood by the report server.

http://msdn2.microsoft.com/de-de/library/ms155362.aspx

to pass a particular parameter you defined in the report, you can just append <parametername>=<value> to the end of the query string.

The User!UserId global variable is not a parameter - it is determined by who is logged into the report server. You cannot control it on the URL itself.

Hope that helps,

-Lukasz

|||

http://msdn2.microsoft.com/en-us/library/ms153586(SQL.90).aspx

Parameters in Reporting Services...

Is it possible to control a parameter type as being single vs. multi-valued
based on the value selected in the previous parameter?
Thank you
Ramdaskeep it multi - but only show one value to select if previous parameter
selection so indicates.
"Ram" wrote:
> Is it possible to control a parameter type as being single vs. multi-valued
> based on the value selected in the previous parameter?
> Thank you
> Ramdas|||Hi,
Thanks for the tip. How would i show only one value based on the previous
parameter selection.
Thank you
Ramdas
"Jimbo" wrote:
> keep it multi - but only show one value to select if previous parameter
> selection so indicates.
>
>
> "Ram" wrote:
> > Is it possible to control a parameter type as being single vs. multi-valued
> > based on the value selected in the previous parameter?
> >
> > Thank you
> > Ramdas|||use a stored procedure to populate your select list - one of the parameters
for this stored procedure would indicate whether the return list will be
multiple records or a single record
this parameter would be set by user selection before being passed to the
stored procedure
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:60367FB5-4886-40A2-85F8-A08AF9A938E2@.microsoft.com...
> Hi,
> Thanks for the tip. How would i show only one value based on the previous
> parameter selection.
> Thank you
> Ramdas
> "Jimbo" wrote:
>> keep it multi - but only show one value to select if previous parameter
>> selection so indicates.
>>
>>
>> "Ram" wrote:
>> > Is it possible to control a parameter type as being single vs.
>> > multi-valued
>> > based on the value selected in the previous parameter?
>> >
>> > Thank you
>> > Ramdas|||Is it possible to modify the XML code at runtime? I want to control the
report parameter properties multi-value setting of True/False during runtime
in the XML behind the RDL file. Set it to True if I want the parameters to be
multi-value or False for single-value. This is determined based on the value
selected in parameter one. Parameter one and two are City,State.
If City is selected in parameter one then I want Parameter two to be a
single-valued list, if State is chosen in Parameter One then I want the list
in Parameter two to be a multi-valued select list.
Any ideas or guidance would be appreciated.
"Jim" wrote:
> use a stored procedure to populate your select list - one of the parameters
> for this stored procedure would indicate whether the return list will be
> multiple records or a single record
> this parameter would be set by user selection before being passed to the
> stored procedure
>
>
>
>
> "Ram" <Ram@.discussions.microsoft.com> wrote in message
> news:60367FB5-4886-40A2-85F8-A08AF9A938E2@.microsoft.com...
> > Hi,
> > Thanks for the tip. How would i show only one value based on the previous
> > parameter selection.
> >
> > Thank you
> > Ramdas
> >
> > "Jimbo" wrote:
> >
> >> keep it multi - but only show one value to select if previous parameter
> >> selection so indicates.
> >>
> >>
> >>
> >>
> >> "Ram" wrote:
> >>
> >> > Is it possible to control a parameter type as being single vs.
> >> > multi-valued
> >> > based on the value selected in the previous parameter?
> >> >
> >> > Thank you
> >> > Ramdas
>
>

Parameters in Reporting Services - C# Syntax

I'm using code from microsoft's website and I'm getting the following error:
"The type or namespace name 'ParameterValue' could not be found (are you missing a using directive or an assembly reference?)"
What am I doing wrong here? I want to pass parameters to the .render method.
// Prepare report parameter.
ParameterValue[] parameters = new ParameterValue[3]; <-- error occurs on this line
parameters[0] = new ParameterValue();
parameters[0].Name = "EmpID";
parameters[0].Value = "38";
parameters[1] = new ParameterValue();
parameters[1].Name = "ReportMonth";
parameters[1].Value = "6"; // June
parameters[2] = new ParameterValue();
parameters[2].Name = "ReportYear";
parameters[2].Value = "2004";
i figured it out
i had to add ReportServer before ParameterValue
i.e. ReportServer.ParameterValue[] parameters = new ReportServer.ParameterValue[3];

Friday, March 30, 2012

Part of textbox content in bold, is it possible ?

Wednesday, March 28, 2012

Parsing Text String Field - comma delimited

Parsing Text String Field

Parsing Text String Field

Pareto chart in MS Reporting Services

Monday, March 26, 2012

Parent-Child Dimension in a Report

Friday, March 23, 2012

partitioning a table question...

partitioning a table question...

Tuesday, March 20, 2012

Parent Attribute Hierarchy

Paramters in WITH part of MDX

Parametric

Monday, March 12, 2012

Parameters will not Default

Parameters to reports that only show is previous parameters filled

parameters really only a filter?

Parameters on Reporting Services 2005 REPOST

Friday, March 9, 2012

Parameters in WITH part of MDX

Parameters in URL - Problem

Parameters in URL - Problem

Parameters in Reporting Services...

Parameters in Reporting Services - C# Syntax

Parameterized Expression

Blog Archive

About Me