Showing posts with label dataset. Show all posts
Showing posts with label dataset. Show all posts

Friday, March 23, 2012

RS2005 storedprocegere parameters

Hi All,
I use a dataset from storedprocedure with 2 parameters:
GetItemsList (@.ItemCount as int , @.CategoryId as int)
In the dataset I write : exec GetItemsList
In the dataset parameter I connect the repotr parameter to the
storedprocedure parmeters.
If I set default value to the storedprocedure parameters:
GetItemsList (@.ItemCount as int = 0, @.CategoryId as int = 0)
the report work fine and pass the report parameters to the storedprocedure
(not 0 )
If I don't set default value to the storedprocedure parameters:
GetItemsList (@.ItemCount as int , @.CategoryId as int)
I get an error that the no value set to the storedprocedure parameters .
What is the problem ?
10x.There are two types of parameters: query parameters and report parameters.
RS tries to automatically create the report parameters for you and map it to
the query parameter. Note that you can change this mapping. For instance you
can map to an expression, you can use the same report parameter to several
query parameters, etc.
Anyway, it looks like RS did not automatically create the parameter for you.
Go to your dataset, click on the ..., change the command type to
storedprocedure, then put in GetItemsList as the query string (leave off the
exec). Now, go to layout and save. Then go to the menu Reports-> Report
Parameters. You should see two report parameters have been created. If you
want to see where the mapping occured go back to the dataset, click on the
..., parameters tab.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Liz Matyas" <lizmts@.mail.com> wrote in message
news:%23WT0Z57rFHA.4044@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I use a dataset from storedprocedure with 2 parameters:
> GetItemsList (@.ItemCount as int , @.CategoryId as int)
> In the dataset I write : exec GetItemsList
> In the dataset parameter I connect the repotr parameter to the
> storedprocedure parmeters.
> If I set default value to the storedprocedure parameters:
> GetItemsList (@.ItemCount as int = 0, @.CategoryId as int = 0)
> the report work fine and pass the report parameters to the storedprocedure
> (not 0 )
> If I don't set default value to the storedprocedure parameters:
> GetItemsList (@.ItemCount as int , @.CategoryId as int)
> I get an error that the no value set to the storedprocedure parameters .
> What is the problem ?
> 10x.
>

RS2005 Multi-Value Problem, urgent!

Dose RS 2005 Multi-Value parameter function can work?
Trying to use multi-value function of RS2005,
dataset named as "AdventureWorks and syntax as following:
SELECT Name, ProductCategoryID
FROM Production.ProductCategory
WHERE (ProductCategoryID = @.ID)
Properties: multi-value
Available values: From query
Dataset: dataset1
Value field: ProductCategoryID
Label field: Name
Dataset1's syntax:
SELECT Name, ProductCategoryID
FROM Production.ProductCategory
Preview report and choice Bikes, Clothing has error msg:
An error occurred during local report processing.
An error has occurred during report processing.
Query execution failed for data set 'AdventureWorks'.
Incorrect syntax near ','.
Dose anyone has the same problem?
In Beta the Multi-Value function is ok,
but in released enterprise version I have this problem?
What's happened?
Thanks for any advice!
AngiYou have to write
... WHERE ProductCategoryID IN (@.ID)
"Angi" <enchiw@.msn.com> schrieb im Newsbeitrag
news:uIw261SAGHA.3968@.TK2MSFTNGP10.phx.gbl...
> Dose RS 2005 Multi-Value parameter function can work?
> Trying to use multi-value function of RS2005,
> dataset named as "AdventureWorks and syntax as following:
> SELECT Name, ProductCategoryID
> FROM Production.ProductCategory
> WHERE (ProductCategoryID = @.ID)
> Properties: multi-value
> Available values: From query
> Dataset: dataset1
> Value field: ProductCategoryID
> Label field: Name
> Dataset1's syntax:
> SELECT Name, ProductCategoryID
> FROM Production.ProductCategory
> Preview report and choice Bikes, Clothing has error msg:
> An error occurred during local report processing.
> An error has occurred during report processing.
> Query execution failed for data set 'AdventureWorks'.
> Incorrect syntax near ','.
> Dose anyone has the same problem?
> In Beta the Multi-Value function is ok,
> but in released enterprise version I have this problem?
> What's happened?
> Thanks for any advice!
> Angi
>|||uh... Thanks Ralph...
Don't know what I think... ><
Make the funny mistake..
"Ralph Watermann" <We.Want@.NoSpam.de> ¼¶¼g©ó¶l¥ó·s»D:uizY49VAGHA.3928@.tk2msftngp13.phx.gbl...
> You have to write
> ... WHERE ProductCategoryID IN (@.ID)
>
> "Angi" <enchiw@.msn.com> schrieb im Newsbeitrag
> news:uIw261SAGHA.3968@.TK2MSFTNGP10.phx.gbl...
>> Dose RS 2005 Multi-Value parameter function can work?
>> Trying to use multi-value function of RS2005,
>> dataset named as "AdventureWorks and syntax as following:
>> SELECT Name, ProductCategoryID
>> FROM Production.ProductCategory
>> WHERE (ProductCategoryID = @.ID)
>> Properties: multi-value
>> Available values: From query
>> Dataset: dataset1
>> Value field: ProductCategoryID
>> Label field: Name
>> Dataset1's syntax:
>> SELECT Name, ProductCategoryID
>> FROM Production.ProductCategory
>> Preview report and choice Bikes, Clothing has error msg:
>> An error occurred during local report processing.
>> An error has occurred during report processing.
>> Query execution failed for data set 'AdventureWorks'.
>> Incorrect syntax near ','.
>> Dose anyone has the same problem?
>> In Beta the Multi-Value function is ok,
>> but in released enterprise version I have this problem?
>> What's happened?
>> Thanks for any advice!
>> Angi
>

Tuesday, March 20, 2012

RS2000 dataset doubt

Hi,
I have 2 datasets. The query in the first dataset is executed and it gives the values to the report parameters. The 2nd dataset uses these parameters to execute the query and then get the data.

The problem is if the first query returns no data, then the report parameters are blank. When I see the preview of the report, it gives me an error saying
"One or more parameters required to run the report have not been specified".
What I want is, if the first query doesn't return any data, then the 2nd query should not be executed.

Can someone please guide me in achieving this?

Regards,
Asim.Rather than not executing the second query, perhaps you could try returning a default value if no rows are returned from the stored procedure?

RS:Help

Hello

Does anybody has idea...

my dataset

Field: item_number, amount

data:

item_number, amount

1, 10

2,10

3,10

4,40

5.10

6,20

My report seems like following

item_number, amount

1, 10

2,10

3,10

4,40

subtotal(1-4),70

5.10

6,20

total(1-6),100

Is it possible to get this kind of report.

thanks!

What kind of datasource are you using? SQL Server, Analysis Services, or something else?

Is this grouping defined somewhere in your datasource or do you have to do this manually?

|||

thank you for reply

my datasource is sql server

I get the dataset by the following sql

select item_number, sum(amount) as amount from table1 grouy by item_number

but I must output a subtotal in the middle of report.

|||

if you don't have any groups in your datasource you could use something like

Code Snippet

select

(CASE WHEN item_number BETWEEN 1 AND 4 THEN '1-4' ELSE 'rest' END) as myGroup,

item_number, sum(amount) as amount from table1

grouy by

(CASE WHEN item_number BETWEEN 1 AND 4 THEN '1-4' ELSE 'rest' END),

item_number

but if you don't have 6 items but thousands, this won't do it. Then could also try to use modulo or something like this to create your groups.

After this you can use the regular grouping within reporting services to group on 'myGroup'.

hope this helps

Markus

|||

you are right

it means I can't output a subtotal in the report, I must get a dataset as I hope,

thank you very much

rs:about the Scope parameter of a aggregate function

A valid scope is the name of a grouping, dataset, or data region. Only groupings or data regions valid scope is the name of a grouping, dataset, or data region. Only groupings or data regions .
the meaning of data region? for example?See my response on the public RS newsgroup:

A data region is a list, matrix, table, or chart.
Please check Reporting Services BOL for more information, e.g.:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_structure_objects_v1_8epf.asp

-- Robert

rs:about the Scope parameter of a aggregate function

A valid scope is the name of a grouping, dataset, or data region. Only
groupings or data regions valid scope is the name of a grouping, dataset, or
data region. Only groupings or data regions .
the meaning of data region? for example?A data region is a list, matrix, table, or chart.
Please check Reporting Services BOL for more information, e.g.:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_structure_objects_v1_8epf.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"aoeabc" <aoeabc@.discussions.microsoft.com> wrote in message
news:72ABECDD-41BB-427E-8CD8-AEC0776BF9F6@.microsoft.com...
>A valid scope is the name of a grouping, dataset, or data region. Only
> groupings or data regions valid scope is the name of a grouping, dataset,
> or
> data region. Only groupings or data regions .
> the meaning of data region? for example?|||Tell me an idea how to do this:
I have three records like this:
Type: 1
Amount: 100
Type 2
Amount: 200
Type 2
Amount: 100
The types of 1 must be negative and the 2 positive, so when I sum must be
-100+300 = 200 positive. I want to make a conditional sum. Now I made it
making the type 1 * -1 and then when I want to print the value amount *
iif(type = 1, -1, 1), so its very tired.
Any idea?
"Robert Bruckner [MSFT]" wrote:
> A data region is a list, matrix, table, or chart.
> Please check Reporting Services BOL for more information, e.g.:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_structure_objects_v1_8epf.asp
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "aoeabc" <aoeabc@.discussions.microsoft.com> wrote in message
> news:72ABECDD-41BB-427E-8CD8-AEC0776BF9F6@.microsoft.com...
> >A valid scope is the name of a grouping, dataset, or data region. Only
> > groupings or data regions valid scope is the name of a grouping, dataset,
> > or
> > data region. Only groupings or data regions .
> > the meaning of data region? for example?
>
>

RS/MDX - Column Parameter / Field name in dataset problem

In creating a dataset with MDX, the field names (column captions) for
my dataset always resolve to the actual member name in the AS cube.
For example, if i use the following query as the datasource
With Set [GenericMeasureName] as '{[Measures].[Quantity]}'
SELECT {[GenericMeasureName]} ON COLUMNS, .... etc
The field name in the dataset still appears as [Measures].[Quantity]
instead of [GenericMeasureName] .
Why is this a problem? I need to parameterize Measures... If I build
the report with a field name of [Measures].[Quantity] and the user
selects [Measures].[Revenue] then the report no-worky.
I cannot put measures as rows, or page, or a slicer dimension or
anything like that...those are parameterized also. The users need to
filter on all dimensions for this (and many other) report.
I also tried to "Edit Data Set" and change the field name on the
"Fields" tab (edit dataset dialog) and same behavior.
I've been searching BOL, etc for a day now with no luck. It seems like
the answer should be a quick click on a setting somewhere... what am I
missing? thanks in advance.
JoelNot sure if this is exactly what you need, but I'll tell you how I've solved
a similar problem, based on cascading and dynamic parameters.
My report has 2 sets of parameters:
1) What dimension to slice on
2) Values in that dimension
My report is built so that I can put any of my parameters as my WHERE clause
in my MDX statement. The trick is to use the Uniquename of a member.
The first parameter is called Selection. I've hard coded a list of
dimensions, with a sort of Friendly name as the Label and the actual name of
the dimension as Value.
The next parameter is called SelectionSelected, and is based on an mdx query
building a list of parametrs based on the dimension selected previously.
Dataset is called ListDimensions and the mdx looks like this:
="WITH set [Listrows] as 'descendants([" & Parameters!Selection.Value &
"].defaultmember,1,self)'
MEMBER [MEASURES].[List_Qualified_Name] AS '[" & Parameters!Selection.Value
& "].CurrentMember.UniqueName'
MEMBER [MEASURES].[List_Report_Name] AS '[" & Parameters!Selection.Value &
"].CurrentMember.Name'
SELECT non empty {[MEASURES].[List_Report_Name],
[MEASURES].[List_Qualified_Name]} ON AXIS(0), non empty {[Listrows]} ON
AXIS(1) FROM [MyCube]"
The set will still keep the name of whatever dimensionname I used to get the
fields. But you don't need to use the name of this set for anything. You
need to use the member fields, and these will still be called
Measures_List_Qualified_Name and Measures_List_Report_Name.
In your report parameters, the parameter SelectionSelected should be tied to
your ListDimensions dataset, with Measures_List_Qualified_Name as your Value
field and Measures_List_Report_Name as the Label field. The unique name
(Measures_List_Qualified_Name) will be in the form of
[Dimension].[MemberLevel1].[MemberLevel2], while the
Report_Name will be the usual name (Like Store1).
So, in your report dataset, you use your basic mdx query, but in the WHERE
clause, you add Parameters!SelectionSelected.Value.
="Select {[Measures].[Totalt amount} on columns,
non empty descendants([Clients].defaultmember, 0, self_and_after) on rows
from [MyCube]
where (" & Parameters!SelectionSelected.Value & ")"
Now, the trick is that you can't really use the same dimension on more than
one axis, but that would be a problem anyway. You may have to tweak your mdx
a bit to make it work, but using Dimension.Currentmember.Uniquename should
be usefull for you.
Kaisa M. Lindahl
"JoelBarish" <stacijon@.gmail.com> wrote in message
news:1136572195.653007.143290@.z14g2000cwz.googlegroups.com...
> In creating a dataset with MDX, the field names (column captions) for
> my dataset always resolve to the actual member name in the AS cube.
> For example, if i use the following query as the datasource
> With Set [GenericMeasureName] as '{[Measures].[Quantity]}'
> SELECT {[GenericMeasureName]} ON COLUMNS, .... etc
> The field name in the dataset still appears as [Measures].[Quantity]
> instead of [GenericMeasureName] .
> Why is this a problem? I need to parameterize Measures... If I build
> the report with a field name of [Measures].[Quantity] and the user
> selects [Measures].[Revenue] then the report no-worky.
> I cannot put measures as rows, or page, or a slicer dimension or
> anything like that...those are parameterized also. The users need to
> filter on all dimensions for this (and many other) report.
> I also tried to "Edit Data Set" and change the field name on the
> "Fields" tab (edit dataset dialog) and same behavior.
> I've been searching BOL, etc for a day now with no luck. It seems like
> the answer should be a quick click on a setting somewhere... what am I
> missing? thanks in advance.
> Joel
>|||Kaisa -
This is wonderful. I have used a version of this technique before, but
your solution is even more dynamic with the introduction of the
parameter to select the dimension. Many Thanks and I know this will be
useful to me many many times.
I still can't figure out a way to make this work for the measures
dimension however, though it makes great sense for all other
dimensions.
The trick is using measure names the rows AND the columns (as you
mentioned) and I can't figure out how to twist the axis's correctly for
this result.
I'm thinking of creating a fake dimension with one member and use that
for the column header. Then couple this with your solution. Not sure
if this will work...keeping my fingers crossed
thanks again for the code!
Kaisa M. Lindahl wrote:
> Not sure if this is exactly what you need, but I'll tell you how I've solved
> a similar problem, based on cascading and dynamic parameters.
> My report has 2 sets of parameters:
> 1) What dimension to slice on
> 2) Values in that dimension
> My report is built so that I can put any of my parameters as my WHERE clause
> in my MDX statement. The trick is to use the Uniquename of a member.
> The first parameter is called Selection. I've hard coded a list of
> dimensions, with a sort of Friendly name as the Label and the actual name of
> the dimension as Value.
> The next parameter is called SelectionSelected, and is based on an mdx query
> building a list of parametrs based on the dimension selected previously.
> Dataset is called ListDimensions and the mdx looks like this:
> ="WITH set [Listrows] as 'descendants([" & Parameters!Selection.Value &
> "].defaultmember,1,self)'
> MEMBER [MEASURES].[List_Qualified_Name] AS '[" & Parameters!Selection.Value
> & "].CurrentMember.UniqueName'
> MEMBER [MEASURES].[List_Report_Name] AS '[" & Parameters!Selection.Value &
> "].CurrentMember.Name'
> SELECT non empty {[MEASURES].[List_Report_Name],
> [MEASURES].[List_Qualified_Name]} ON AXIS(0), non empty {[Listrows]} ON
> AXIS(1) FROM [MyCube]"
> The set will still keep the name of whatever dimensionname I used to get the
> fields. But you don't need to use the name of this set for anything. You
> need to use the member fields, and these will still be called
> Measures_List_Qualified_Name and Measures_List_Report_Name.
> In your report parameters, the parameter SelectionSelected should be tied to
> your ListDimensions dataset, with Measures_List_Qualified_Name as your Value
> field and Measures_List_Report_Name as the Label field. The unique name
> (Measures_List_Qualified_Name) will be in the form of
> [Dimension].[MemberLevel1].[MemberLevel2], while the
> Report_Name will be the usual name (Like Store1).
> So, in your report dataset, you use your basic mdx query, but in the WHERE
> clause, you add Parameters!SelectionSelected.Value.
> ="Select {[Measures].[Totalt amount} on columns,
> non empty descendants([Clients].defaultmember, 0, self_and_after) on rows
> from [MyCube]
> where (" & Parameters!SelectionSelected.Value & ")"
> Now, the trick is that you can't really use the same dimension on more than
> one axis, but that would be a problem anyway. You may have to tweak your mdx
> a bit to make it work, but using Dimension.Currentmember.Uniquename should
> be usefull for you.
> Kaisa M. Lindahl
> "JoelBarish" <stacijon@.gmail.com> wrote in message
> news:1136572195.653007.143290@.z14g2000cwz.googlegroups.com...
> > In creating a dataset with MDX, the field names (column captions) for
> > my dataset always resolve to the actual member name in the AS cube.
> >
> > For example, if i use the following query as the datasource
> > With Set [GenericMeasureName] as '{[Measures].[Quantity]}'
> > SELECT {[GenericMeasureName]} ON COLUMNS, .... etc
> >
> > The field name in the dataset still appears as [Measures].[Quantity]
> > instead of [GenericMeasureName] .
> >
> > Why is this a problem? I need to parameterize Measures... If I build
> > the report with a field name of [Measures].[Quantity] and the user
> > selects [Measures].[Revenue] then the report no-worky.
> >
> > I cannot put measures as rows, or page, or a slicer dimension or
> > anything like that...those are parameterized also. The users need to
> > filter on all dimensions for this (and many other) report.
> >
> > I also tried to "Edit Data Set" and change the field name on the
> > "Fields" tab (edit dataset dialog) and same behavior.
> >
> > I've been searching BOL, etc for a day now with no luck. It seems like
> > the answer should be a quick click on a setting somewhere... what am I
> > missing? thanks in advance.
> >
> > Joel
> >|||Glad to help, I've been pretty satisfied with that solution myself. ;)
Anyway, what are you trying to do with the measures? Creating a query "where
[Measures].[Totalamount] > 0" or something?
You could try asking for help in the Olap-newsgroup,
microsoft.public.sqlserver.olap, as you may get around the problem by
rewriting your mdx somewhat.
Kaisa
"JoelBarish" <stacijon@.gmail.com> wrote in message
news:1136821283.237700.90330@.g47g2000cwa.googlegroups.com...
> Kaisa -
> This is wonderful. I have used a version of this technique before, but
> your solution is even more dynamic with the introduction of the
> parameter to select the dimension. Many Thanks and I know this will be
> useful to me many many times.
> I still can't figure out a way to make this work for the measures
> dimension however, though it makes great sense for all other
> dimensions.
> The trick is using measure names the rows AND the columns (as you
> mentioned) and I can't figure out how to twist the axis's correctly for
> this result.
> I'm thinking of creating a fake dimension with one member and use that
> for the column header. Then couple this with your solution. Not sure
> if this will work...keeping my fingers crossed
> thanks again for the code!
>
>
> Kaisa M. Lindahl wrote:
>> Not sure if this is exactly what you need, but I'll tell you how I've
>> solved
>> a similar problem, based on cascading and dynamic parameters.
>> My report has 2 sets of parameters:
>> 1) What dimension to slice on
>> 2) Values in that dimension
>> My report is built so that I can put any of my parameters as my WHERE
>> clause
>> in my MDX statement. The trick is to use the Uniquename of a member.
>> The first parameter is called Selection. I've hard coded a list of
>> dimensions, with a sort of Friendly name as the Label and the actual name
>> of
>> the dimension as Value.
>> The next parameter is called SelectionSelected, and is based on an mdx
>> query
>> building a list of parametrs based on the dimension selected previously.
>> Dataset is called ListDimensions and the mdx looks like this:
>> ="WITH set [Listrows] as 'descendants([" & Parameters!Selection.Value &
>> "].defaultmember,1,self)'
>> MEMBER [MEASURES].[List_Qualified_Name] AS '[" &
>> Parameters!Selection.Value
>> & "].CurrentMember.UniqueName'
>> MEMBER [MEASURES].[List_Report_Name] AS '[" & Parameters!Selection.Value
>> &
>> "].CurrentMember.Name'
>> SELECT non empty {[MEASURES].[List_Report_Name],
>> [MEASURES].[List_Qualified_Name]} ON AXIS(0), non empty {[Listrows]} ON
>> AXIS(1) FROM [MyCube]"
>> The set will still keep the name of whatever dimensionname I used to get
>> the
>> fields. But you don't need to use the name of this set for anything. You
>> need to use the member fields, and these will still be called
>> Measures_List_Qualified_Name and Measures_List_Report_Name.
>> In your report parameters, the parameter SelectionSelected should be tied
>> to
>> your ListDimensions dataset, with Measures_List_Qualified_Name as your
>> Value
>> field and Measures_List_Report_Name as the Label field. The unique name
>> (Measures_List_Qualified_Name) will be in the form of
>> [Dimension].[MemberLevel1].[MemberLevel2], while the
>> Report_Name will be the usual name (Like Store1).
>> So, in your report dataset, you use your basic mdx query, but in the
>> WHERE
>> clause, you add Parameters!SelectionSelected.Value.
>> ="Select {[Measures].[Totalt amount} on columns,
>> non empty descendants([Clients].defaultmember, 0, self_and_after) on rows
>> from [MyCube]
>> where (" & Parameters!SelectionSelected.Value & ")"
>> Now, the trick is that you can't really use the same dimension on more
>> than
>> one axis, but that would be a problem anyway. You may have to tweak your
>> mdx
>> a bit to make it work, but using Dimension.Currentmember.Uniquename
>> should
>> be usefull for you.
>> Kaisa M. Lindahl
>> "JoelBarish" <stacijon@.gmail.com> wrote in message
>> news:1136572195.653007.143290@.z14g2000cwz.googlegroups.com...
>> > In creating a dataset with MDX, the field names (column captions) for
>> > my dataset always resolve to the actual member name in the AS cube.
>> >
>> > For example, if i use the following query as the datasource
>> > With Set [GenericMeasureName] as '{[Measures].[Quantity]}'
>> > SELECT {[GenericMeasureName]} ON COLUMNS, .... etc
>> >
>> > The field name in the dataset still appears as [Measures].[Quantity]
>> > instead of [GenericMeasureName] .
>> >
>> > Why is this a problem? I need to parameterize Measures... If I build
>> > the report with a field name of [Measures].[Quantity] and the user
>> > selects [Measures].[Revenue] then the report no-worky.
>> >
>> > I cannot put measures as rows, or page, or a slicer dimension or
>> > anything like that...those are parameterized also. The users need to
>> > filter on all dimensions for this (and many other) report.
>> >
>> > I also tried to "Edit Data Set" and change the field name on the
>> > "Fields" tab (edit dataset dialog) and same behavior.
>> >
>> > I've been searching BOL, etc for a day now with no luck. It seems like
>> > the answer should be a quick click on a setting somewhere... what am I
>> > missing? thanks in advance.
>> >
>> > Joel
>> >
>|||Hi Kaisa -
The users want a report that includes only one measure and they want to
choose the measure. They want EITHER Revenue OR Quantity OR Cost OR
Discount on the report, but not all of these. I tried to convince them
to have all these on the report and only look at the one they want, but
they won't accept that... they have some downstream procedures that
will be impacted or similar...
This isn't necessarily a problem, I could put the measure in the WHERE
if I had to. However, I then encounter the same problem with whatever
dimension I move to the columns. For example, I can't use the Time
dimension in the column because users want to select Time from a
parameter (result: changing column name problem for Time dimension).
Users also want a parameter for Line Of Business, etc. (all fields are
parameters therefore all fields have changing column/field names).
What I have done for now is create a calculated field in the dataset:
(Psuedo code): Iif(parameter=revenue,
revenue.value,(iif(parameter=quantity,quantity.value,(iif(parameter=cost,
...etc.) ... then reference the calculated field in the report
definition.
(I've read on this forum that CASE statements are not allowed in
expressions.)
I don't like this solution because I don't like hardcoding this much
into a report. And... there are a very large number of reports that
require this same type of coding.
I will post the mdx question on the OLAP forum. meanwhile, if you
happen to think of a different/better way to skin this cat, let me
know... you seem to have some creative solutions up your sleeve.
gracias, joel b
--i like MSAS a lot (i've worked with three other major OLAP vendors
and the development environment in MSAS is tops). I am new to
Reporting Services but so far it seems it could be enhanced to interact
with MSAS better. I continue to run into problems that don't fit well
into MSRS and they are all specific to sourcing from a cube.|||As Reporting Services isn't all that dynamic, I guess some hard coding has
to be done for each report. But you still might be able to do some tricks.
Even if the users only want to see one measure, you can still make the
report query find numbers for all the measures they can choose from. And
then do some visibility checks on the actual columns in your report. You
probably shouldn't have too much of a difference in the query time, even if
you get numbers for both Revenue AND quantity.
And if you want a super generic report that can't be grouped too much, you
can do something like this:
with member [Measures].[MyMeasure] as '[Store].currentmember.name'
member [Measures].[MyMeasure2] as '[Store].currentmember.Level.Name'
member [Measures].[MyMeasure3] as '[Store].currentmember.UniqueName'
select
{[Measures].[Unit Sales],[Measures].[Store Cost],[Measures].[Store
Sales],
[Measures].[MyMeasure],[Measures].[MyMeasure2],[Measures].[MyMeasure3]} on
columns,
{filter([Store].members,[Measures].[Unit Sales] > 100) } on rows
from [Sales]
The first calculated member will give you the store names in a column, the
second will give you that member's level, the third the unique name in the
form of [Store].[All Stores].[USA] etc. You can then group on Measure3, but
you might want to do some visibility checks to not dump out all the rows a
lot of times. Set all columns and rows to Visibility - Hidden = True, and
make it visible if the data you want to show equals ... something.
All calculated members will be called by the name you give it, contrary to
sets. So you can change the query to something like this, and all your
column names will controllable:
with member [Measures].[MyMeasure] as '[Store].currentmember.name'
member [Measures].[MyMeasure2] as '[Store].currentmember.Level.Name'
member [Measures].[MyMeasure3] as '[Store].currentmember.UniqueName'
member [Measures].[MyMeasure4] as '[Measures].[Unit Sales]'
member [Measures].[MyMeasure5] as '[Measures].[Store Cost]'
member [Measures].[MyMeasure5] as '[Measures].[Store Sales]'
select
{[Measures].[MyMeasure],[Measures].[MyMeasure2],[Measures].[MyMeasure3],[Measures].[MyMeasure4],[Measures].[MyMeasure5],[Measures].[MyMeasure6]}
on columns,
{filter([Store].members,[Measures].[Unit Sales] > 100) } on rows
from [Sales]
Your dimension used on rows and the first three measures needs to be the
same. And you need to filter on what ever measure you want to filter on, but
you can use [MyMeasure4] instead of [Unit Sales]. But you don't use the
row-based dimensions on your report rows, you use the measure. On the other
hand, there will be some differences from report to report, or else you
could create one huge generic report and just leave work for the day. ;)
Kaisa M. Lindahl
"JoelBarish" <stacijon@.gmail.com> wrote in message
news:1136829966.359151.155080@.g14g2000cwa.googlegroups.com...
> Hi Kaisa -
> The users want a report that includes only one measure and they want to
> choose the measure. They want EITHER Revenue OR Quantity OR Cost OR
> Discount on the report, but not all of these. I tried to convince them
> to have all these on the report and only look at the one they want, but
> they won't accept that... they have some downstream procedures that
> will be impacted or similar...
> This isn't necessarily a problem, I could put the measure in the WHERE
> if I had to. However, I then encounter the same problem with whatever
> dimension I move to the columns. For example, I can't use the Time
> dimension in the column because users want to select Time from a
> parameter (result: changing column name problem for Time dimension).
> Users also want a parameter for Line Of Business, etc. (all fields are
> parameters therefore all fields have changing column/field names).
> What I have done for now is create a calculated field in the dataset:
> (Psuedo code): Iif(parameter=revenue,
> revenue.value,(iif(parameter=quantity,quantity.value,(iif(parameter=cost,
> ...etc.) ... then reference the calculated field in the report
> definition.
> (I've read on this forum that CASE statements are not allowed in
> expressions.)
> I don't like this solution because I don't like hardcoding this much
> into a report. And... there are a very large number of reports that
> require this same type of coding.
> I will post the mdx question on the OLAP forum. meanwhile, if you
> happen to think of a different/better way to skin this cat, let me
> know... you seem to have some creative solutions up your sleeve.
> gracias, joel b
>
> --i like MSAS a lot (i've worked with three other major OLAP vendors
> and the development environment in MSAS is tops). I am new to
> Reporting Services but so far it seems it could be enhanced to interact
> with MSAS better. I continue to run into problems that don't fit well
> into MSRS and they are all specific to sourcing from a cube.
>|||I created this report, based on data from the Foodmart 2000- cube.
Most of it can be edited from Notepad, if you want to add a dimension or
change what a column is showing. If you want to add more columns, then you
have to do it in VS.
Kaisa
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<RightMargin>2.5cm</RightMargin>
<Body>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<Top>0.25cm</Top>
<rd:DefaultName>textbox4</rd:DefaultName>
<Height>0.63492cm</Height>
<Width>13.25cm</Width>
<CanGrow>true</CanGrow>
<Value>="Generic report based on " & Parameters!Dimension.Value</Value>
</Textbox>
<Table Name="table1">
<Height>1.90476cm</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.63492cm</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
<Left>Solid</Left>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>= Parameters!Dimension.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
<Right>Solid</Right>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>10</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Unit sales</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
<Right>Solid</Right>
</BorderStyle>
<TextAlign>Right</extAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>9</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Store Cost</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
<Right>Solid</Right>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FntWeight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox10</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Store Sales</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.63492cm</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Measures_MyMeasure">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=IIF(Fields!Measures_MyMeasure2.Value = "0", "LightBlue",
IIF(Fields!Measures_MyMeasure2.Value = "2", "LightGreen",
IIF(Fields!Measures_MyMeasure2.Value = "3", "LightYellow",
"White")))</BackgroundColor>
<BorderStyle>
<Left>Solid</Left>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>Measures_MyMeasure</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Measures_MyMeasure.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Measures_MyMeasure4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>N0</Format>
<BackgroundColor>=IIF(Fields!Measures_MyMeasure2.Value = "0", "LightBlue",
IIF(Fields!Measures_MyMeasure2.Value = "2", "LightGreen",
IIF(Fields!Measures_MyMeasure2.Value = "3", "LightYellow",
"White")))</BackgroundColor>
<BorderStyle>
<Right>Solid</Right>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>Measures_MyMeasure4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Measures_MyMeasure4.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Measures_MyMeasure5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>N0</Format>
<BackgroundColor>=IIF(Fields!Measures_MyMeasure2.Value = "0", "LightBlue",
IIF(Fields!Measures_MyMeasure2.Value = "2", "LightGreen",
IIF(Fields!Measures_MyMeasure2.Value = "3", "LightYellow",
"White")))</BackgroundColor>
<BorderStyle>
<Right>Solid</Right>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>Measures_MyMeasure5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Measures_MyMeasure5.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Measures_MyMeasure6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>N0</Format>
<BackgroundColor>=IIF(Fields!Measures_MyMeasure2.Value = "0", "LightBlue",
IIF(Fields!Measures_MyMeasure2.Value = "2", "LightGreen",
IIF(Fields!Measures_MyMeasure2.Value = "3", "LightYellow",
"White")))</BackgroundColor>
<BorderStyle>
<Right>Solid</Right>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>Measures_MyMeasure6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Measures_MyMeasure6.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<Grouping Name="table1_Details_Group">
<GroupExpressions>
<GroupExpression>=Fields!Measures_MyMeasure3.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</Details>
<DataSetName>DataSet1</DataSetName>
<Top>1.25cm</Top>
<Width>13.63624cm</Width>
<Footer>
<TableRows>
<TableRow>
<Height>0.63492cm</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Top>Solid</Top>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Top>Solid</Top>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Top>Solid</Top>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Top>Solid</Top>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Footer>
<TableColumns>
<TableColumn>
<Width>5.33333cm</Width>
</TableColumn>
<TableColumn>
<Width>2.75cm</Width>
<Visibility>
<Hidden>=IIF( Parameters!ShowAll.Value = False, IIF(Parameters!Measure.Label
= "Unit Sales", False, True), False)</Hidden>
</Visibility>
</TableColumn>
<TableColumn>
<Width>2.77645cm</Width>
<Visibility>
<Hidden>=IIF( Parameters!ShowAll.Value = False, IIF(Parameters!Measure.Label
= "Store Cost", False, True), False)</Hidden>
</Visibility>
</TableColumn>
<TableColumn>
<Width>2.77646cm</Width>
<Visibility>
<Hidden>=IIF( Parameters!ShowAll.Value = False, IIF(Parameters!Measure.Label
= "Store Sales", False, True), False)</Hidden>
</Visibility>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>15cm</Height>
<ColumnSpacing>1cm</ColumnSpacing>
</Body>
<TopMargin>2.5cm</TopMargin>
<DataSources>
<DataSource Name="Foodmart">
<rd:DataSourceID>124dab9d-708f-4c42-88b0-429857870478</rd:DataSourceID>
<DataSourceReference>Foodmart</DataSourceReference>
</DataSource>
</DataSources>
<Width>16cm</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="Store_Store_Country">
<DataField>[Store].[Store Country].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Store_Store_State">
<DataField>[Store].[Store State].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Store_Store_City">
<DataField>[Store].[Store City].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Store_Store_Name">
<DataField>[Store].[Store Name].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Measures_MyMeasure">
<DataField>[Measures].[MyMeasure]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
<Field Name="Measures_MyMeasure2">
<DataField>[Measures].[MyMeasure2]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
<Field Name="Measures_MyMeasure3">
<DataField>[Measures].[MyMeasure3]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
<Field Name="Measures_MyMeasure4">
<DataField>[Measures].[MyMeasure4]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
<Field Name="Measures_MyMeasure5">
<DataField>[Measures].[MyMeasure5]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
<Field Name="Measures_MyMeasure6">
<DataField>[Measures].[MyMeasure6]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Foodmart</DataSourceName>
<CommandText>="with member [Measures].[MyMeasure] as '[" &
Parameters!Dimension.Value & "].currentmember.name'member
[Measures].[MyMeasure2] as '[" & Parameters!Dimension.Value &
"].currentmember.Level.Ordinal' member [Measures].[MyMeasure3] as '[" &
Parameters!Dimension.Value & "].currentmember.UniqueName' member
[Measures].[MyMeasure4] as '[Measures].[Unit Sales]' member
[Measures].[MyMeasure5] as '[Measures].[Store Cost]' member
[Measures].[MyMeasure6] as '[Measures].[Store Sales]' select
{[Measures].[MyMeasure], [Measures].[MyMeasure2], [Measures].[MyMeasure3],
[Measures].[MyMeasure4], [Measures].[MyMeasure5], [Measures].[MyMeasure6]}
on columns, {filter([" & Parameters!Dimension.Value & "].members,"
& Parameters!Measure.Value & " > " & Parameters!Amount.Value
& ") } on rows from [Sales]"</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>2.5cm</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<PageHeight>29.7cm</PageHeight>
<rd:DrawGrid>true</rd:DrawGrid>
<PageWidth>21cm</PageWidth>
<rd:ReportID>247133a5-6ff9-4041-b398-bb2f76911f3e</rd:ReportID>
<BottomMargin>2.5cm</BottomMargin>
<ReportParameters>
<ReportParameter Name="Dimension">
<DataType>String</DataType>
<Prompt>Choose dimension</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>Store</Value>
</ParameterValue>
<ParameterValue>
<Value>Store Type</Value>
</ParameterValue>
<ParameterValue>
<Value>Store Size in SQFT</Value>
</ParameterValue>
<ParameterValue>
<Value>Time</Value>
</ParameterValue>
<ParameterValue>
<Value>Product</Value>
</ParameterValue>
<ParameterValue>
<Value>Gender</Value>
</ParameterValue>
<ParameterValue>
<Value>Promotions</Value>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
<ReportParameter Name="Measure">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>="[Measures].[Unit Sales]"</Value>
</Values>
</DefaultValue>
<Prompt>Choose measure</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>[Measures].[Unit Sales]</Value>
<Label>Unit Sales</Label>
</ParameterValue>
<ParameterValue>
<Value>[Measures].[Store Cost]</Value>
<Label>Store Cost</Label>
</ParameterValue>
<ParameterValue>
<Value>[Measures].[Store Sales]</Value>
<Label>Store Sales</Label>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
<ReportParameter Name="Amount">
<DataType>Integer</DataType>
<DefaultValue>
<Values>
<Value>0</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>More than</Prompt>
</ReportParameter>
<ReportParameter Name="ShowAll">
<DataType>Boolean</DataType>
<DefaultValue>
<Values>
<Value>true</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Show all</Prompt>
</ReportParameter>
</ReportParameters>
<Language>nb-NO</Language>
</Report>
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
news:%23vOCcGpFGHA.2708@.TK2MSFTNGP11.phx.gbl...
> As Reporting Services isn't all that dynamic, I guess some hard coding has
> to be done for each report. But you still might be able to do some tricks.
> Even if the users only want to see one measure, you can still make the
> report query find numbers for all the measures they can choose from. And
> then do some visibility checks on the actual columns in your report. You
> probably shouldn't have too much of a difference in the query time, even
> if you get numbers for both Revenue AND quantity.
> And if you want a super generic report that can't be grouped too much, you
> can do something like this:
> with member [Measures].[MyMeasure] as '[Store].currentmember.name'
> member [Measures].[MyMeasure2] as '[Store].currentmember.Level.Name'
> member [Measures].[MyMeasure3] as '[Store].currentmember.UniqueName'
> select
> {[Measures].[Unit Sales],[Measures].[Store Cost],[Measures].[Store
> Sales],
> [Measures].[MyMeasure],[Measures].[MyMeasure2],[Measures].[MyMeasure3]} on
> columns,
> {filter([Store].members,[Measures].[Unit Sales] > 100) } on rows
> from [Sales]
> The first calculated member will give you the store names in a column, the
> second will give you that member's level, the third the unique name in the
> form of [Store].[All Stores].[USA] etc. You can then group on Measure3,
> but you might want to do some visibility checks to not dump out all the
> rows a lot of times. Set all columns and rows to Visibility - Hidden => True, and make it visible if the data you want to show equals ...
> something.
> All calculated members will be called by the name you give it, contrary to
> sets. So you can change the query to something like this, and all your
> column names will controllable:
> with member [Measures].[MyMeasure] as '[Store].currentmember.name'
> member [Measures].[MyMeasure2] as '[Store].currentmember.Level.Name'
> member [Measures].[MyMeasure3] as '[Store].currentmember.UniqueName'
> member [Measures].[MyMeasure4] as '[Measures].[Unit Sales]'
> member [Measures].[MyMeasure5] as '[Measures].[Store Cost]'
> member [Measures].[MyMeasure5] as '[Measures].[Store Sales]'
> select
> {[Measures].[MyMeasure],[Measures].[MyMeasure2],[Measures].[MyMeasure3],[Measures].[MyMeasure4],[Measures].[MyMeasure5],[Measures].[MyMeasure6]}
> on columns,
> {filter([Store].members,[Measures].[Unit Sales] > 100) } on rows
> from [Sales]
> Your dimension used on rows and the first three measures needs to be the
> same. And you need to filter on what ever measure you want to filter on,
> but you can use [MyMeasure4] instead of [Unit Sales]. But you don't use
> the row-based dimensions on your report rows, you use the measure. On the
> other hand, there will be some differences from report to report, or else
> you could create one huge generic report and just leave work for the day.
> ;)
> Kaisa M. Lindahl
>

Friday, March 9, 2012

RS PARAMETERS

I have a report with a Dataset that has a query string:
exec sp_ReporteFlashVentasXRutaYDiario @.Usuario, 1, @.Mercado, @.Agencia,@.Ruta
The report is working, but I want to delete the parameter @.Rute and the
follow error are displaying:
An error has occurred during report processing.
Query execution failed for dataset "resumen"
Must declare the variable @.Usuario.
Plese help me.
--
MauryqTwo things to try. Click on the refresh fields button (to the right of the
...). The other thing is to click on the ... and go to the parameters tab
and make sure all the expected parameters are there.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mauryq" <mauryq@.hotmail.com> wrote in message
news:3F2DFA2A-C1B9-4219-8EB7-7C71487DF6EF@.microsoft.com...
> I have a report with a Dataset that has a query string:
> exec sp_ReporteFlashVentasXRutaYDiario @.Usuario, 1, @.Mercado,
@.Agencia,@.Ruta
> The report is working, but I want to delete the parameter @.Rute and the
> follow error are displaying:
> An error has occurred during report processing.
> Query execution failed for dataset "resumen"
> Must declare the variable @.Usuario.
> Plese help me.
> --
> Mauryq|||When you create a dataset with a SP with parameters, automatically the
parameters are created at the report, but in this case the parameters can't
be created automatically, and have that created it manually, and when I eject
the preview is like the parameter cant be recognized.
exec sp_ReporteFlashVentasXRutaYDiario @.Usuario, 1, @.Mercado,
@.Agencia,@.Ruta
"Bruce L-C [MVP]" escribió:
> Two things to try. Click on the refresh fields button (to the right of the
> ...). The other thing is to click on the ... and go to the parameters tab
> and make sure all the expected parameters are there.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Mauryq" <mauryq@.hotmail.com> wrote in message
> news:3F2DFA2A-C1B9-4219-8EB7-7C71487DF6EF@.microsoft.com...
> > I have a report with a Dataset that has a query string:
> >
> > exec sp_ReporteFlashVentasXRutaYDiario @.Usuario, 1, @.Mercado,
> @.Agencia,@.Ruta
> >
> > The report is working, but I want to delete the parameter @.Rute and the
> > follow error are displaying:
> >
> > An error has occurred during report processing.
> > Query execution failed for dataset "resumen"
> > Must declare the variable @.Usuario.
> >
> > Plese help me.
> >
> > --
> > Mauryq
>
>|||I have had mixed results with RS automatically creating the parameters.
Sometimes it messes up. Did you try either of the two things I mentioned?
What happens when you click on the ... and go to the parameters tab?
I have had some stored procedures in SQL Server (I work with both Sybase and
SQL Server) that worked flawlessly with RS creating the Report Parameter.
When you have an @.Paramname in your SQL what happens is that RS looks for a
matching Report Parameter. If it doesn't see it then it assumes that it is a
T-SQL variable. You can have a multi-line T-SQL code with declare
@.SOMEVARIABLE. So what is happening to you is that the wiring between your
query parameters and your report parameters has gotten messed up. It is
important to remember that they are two different things (which gets
confusing because RS is being helpful behind the scenes and creating Report
Parameters).
You need to do two things. You need to check the parameter mapping ( click
on the ... and go to the parameter tab and make sure the query parameters
you expect are all there and are mapped to the appropriate Report
Parameter). You can also from layout view go to the Report Menu, Parameters.
You should see
You say you want to get rid of @.Ruta query parameter. Then you should delete
it from your query calling code, make sure it is deleted from the query
parameter to report parameter mapping and finally that it is not still a
report parameter.
exec sp_ReporteFlashVentasXRutaYDiario @.Usuario, 1, @.Mercado, @.Agencia
Note that you can also be in generic query designer and leave off the exec:
sp_ReporteFlashVentasXRutaYDiario @.Usuario, 1, @.Mercado, @.Agencia
I never use the exec. See if that helps at all.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mauryq" <mauryq@.hotmail.com> wrote in message
news:7DF27C6B-F720-46D7-A24F-54641DD96461@.microsoft.com...
> When you create a dataset with a SP with parameters, automatically the
> parameters are created at the report, but in this case the parameters
> can't
> be created automatically, and have that created it manually, and when I
> eject
> the preview is like the parameter cant be recognized.
> exec sp_ReporteFlashVentasXRutaYDiario @.Usuario, 1, @.Mercado,
> @.Agencia,@.Ruta
>
> "Bruce L-C [MVP]" escribió:
>> Two things to try. Click on the refresh fields button (to the right of
>> the
>> ...). The other thing is to click on the ... and go to the parameters
>> tab
>> and make sure all the expected parameters are there.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Mauryq" <mauryq@.hotmail.com> wrote in message
>> news:3F2DFA2A-C1B9-4219-8EB7-7C71487DF6EF@.microsoft.com...
>> > I have a report with a Dataset that has a query string:
>> >
>> > exec sp_ReporteFlashVentasXRutaYDiario @.Usuario, 1, @.Mercado,
>> @.Agencia,@.Ruta
>> >
>> > The report is working, but I want to delete the parameter @.Rute and the
>> > follow error are displaying:
>> >
>> > An error has occurred during report processing.
>> > Query execution failed for dataset "resumen"
>> > Must declare the variable @.Usuario.
>> >
>> > Plese help me.
>> >
>> > --
>> > Mauryq
>>|||Mauryq,
whenever get this type of error, I go back to the source and debug from
there.
You say you want to remove a param. Did you already do this on the SProc
itself? Try exec from QA on the SQL instance to ensure the problem isn't at
the source, and then work forwards onto the RS.
Tony
"Bruce L-C [MVP]" wrote:
> I have had mixed results with RS automatically creating the parameters.
> Sometimes it messes up. Did you try either of the two things I mentioned?
> What happens when you click on the ... and go to the parameters tab?
> I have had some stored procedures in SQL Server (I work with both Sybase and
> SQL Server) that worked flawlessly with RS creating the Report Parameter.
> When you have an @.Paramname in your SQL what happens is that RS looks for a
> matching Report Parameter. If it doesn't see it then it assumes that it is a
> T-SQL variable. You can have a multi-line T-SQL code with declare
> @.SOMEVARIABLE. So what is happening to you is that the wiring between your
> query parameters and your report parameters has gotten messed up. It is
> important to remember that they are two different things (which gets
> confusing because RS is being helpful behind the scenes and creating Report
> Parameters).
> You need to do two things. You need to check the parameter mapping ( click
> on the ... and go to the parameter tab and make sure the query parameters
> you expect are all there and are mapped to the appropriate Report
> Parameter). You can also from layout view go to the Report Menu, Parameters.
> You should see
> You say you want to get rid of @.Ruta query parameter. Then you should delete
> it from your query calling code, make sure it is deleted from the query
> parameter to report parameter mapping and finally that it is not still a
> report parameter.
> exec sp_ReporteFlashVentasXRutaYDiario @.Usuario, 1, @.Mercado, @.Agencia
> Note that you can also be in generic query designer and leave off the exec:
> sp_ReporteFlashVentasXRutaYDiario @.Usuario, 1, @.Mercado, @.Agencia
> I never use the exec. See if that helps at all.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Mauryq" <mauryq@.hotmail.com> wrote in message
> news:7DF27C6B-F720-46D7-A24F-54641DD96461@.microsoft.com...
> > When you create a dataset with a SP with parameters, automatically the
> > parameters are created at the report, but in this case the parameters
> > can't
> > be created automatically, and have that created it manually, and when I
> > eject
> > the preview is like the parameter cant be recognized.
> >
> > exec sp_ReporteFlashVentasXRutaYDiario @.Usuario, 1, @.Mercado,
> > @.Agencia,@.Ruta
> >
> >
> > "Bruce L-C [MVP]" escribió:
> >
> >> Two things to try. Click on the refresh fields button (to the right of
> >> the
> >> ...). The other thing is to click on the ... and go to the parameters
> >> tab
> >> and make sure all the expected parameters are there.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Mauryq" <mauryq@.hotmail.com> wrote in message
> >> news:3F2DFA2A-C1B9-4219-8EB7-7C71487DF6EF@.microsoft.com...
> >> > I have a report with a Dataset that has a query string:
> >> >
> >> > exec sp_ReporteFlashVentasXRutaYDiario @.Usuario, 1, @.Mercado,
> >> @.Agencia,@.Ruta
> >> >
> >> > The report is working, but I want to delete the parameter @.Rute and the
> >> > follow error are displaying:
> >> >
> >> > An error has occurred during report processing.
> >> > Query execution failed for dataset "resumen"
> >> > Must declare the variable @.Usuario.
> >> >
> >> > Plese help me.
> >> >
> >> > --
> >> > Mauryq
> >>
> >>
> >>
>
>

Saturday, February 25, 2012

RS by SQL Express

Dear Friends

I installed RS by SQL Express, but i have a problem, RS was installed in W2003 server and i try to connect by Dataset to other DB, this is a SQL2000 in W2000, when i disigned the report and i see the view the report show me the result, but when i deploy teh report and i try to see by web the report dont respond and show the error "The dataset don;t connect or this function isn't disponible in this version", why is the problem, and who i resolve this problem.

Tahnk you

Hi,

RS Express could only run report against the local data source. To use remote data source, you will need standard/enterprise edition.

RS 2005: Use a Custom Class as DataSource?

Hi,
Is it possible to use a Custom Class/Object as a DataSource/DataSet for a
report?
I have now for instance my object MyCompany, and it woudl be nice to just
use a MyCompany-instance as the DataSource...
Any hints/help woudl be really appreciated,
Thanks a lot in advance,
PieterI thought that the objectDatasource control was only used by ASP.NET
applications.
A SSRS report is not an ASP.NET application unless you use the new
reportViewer component and there you can use an ObjectDataSource
Med bouchenafa
"Pieter" <pietercoucke@.hotmail.com> a écrit dans le message de news:
%23w1cPSlIGHA.668@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Is it possible to use a Custom Class/Object as a DataSource/DataSet for a
> report?
> I have now for instance my object MyCompany, and it woudl be nice to just
> use a MyCompany-instance as the DataSource...
> Any hints/help woudl be really appreciated,
> Thanks a lot in advance,
> Pieter
>|||Hi,
You can bind windows applications to objects also.
Ken
--
"Med Bouchenafa" <com.hotmail@.bouchenafa> wrote in message
news:OhXZe$lIGHA.2896@.TK2MSFTNGP09.phx.gbl...
>I thought that the objectDatasource control was only used by ASP.NET
>applications.
> A SSRS report is not an ASP.NET application unless you use the new
> reportViewer component and there you can use an ObjectDataSource
> Med bouchenafa
> "Pieter" <pietercoucke@.hotmail.com> a écrit dans le message de news:
> %23w1cPSlIGHA.668@.TK2MSFTNGP11.phx.gbl...
>> Hi,
>> Is it possible to use a Custom Class/Object as a DataSource/DataSet for a
>> report?
>> I have now for instance my object MyCompany, and it woudl be nice to just
>> use a MyCompany-instance as the DataSource...
>> Any hints/help woudl be really appreciated,
>> Thanks a lot in advance,
>> Pieter
>|||That's right, my mistake
I should have said applications and not only ASP.NET applications
I meant a SSRS report is a standalone xml file
Thanks for correcting
Med bouchenafa
"Ken Tucker [MVP]" <vb2ae@.bellsouth.net> a écrit dans le message de news:
OPOUq3mIGHA.1180@.TK2MSFTNGP09.phx.gbl...
> Hi,
> You can bind windows applications to objects also.
> Ken
> --
> "Med Bouchenafa" <com.hotmail@.bouchenafa> wrote in message
> news:OhXZe$lIGHA.2896@.TK2MSFTNGP09.phx.gbl...
>>I thought that the objectDatasource control was only used by ASP.NET
>>applications.
>> A SSRS report is not an ASP.NET application unless you use the new
>> reportViewer component and there you can use an ObjectDataSource
>> Med bouchenafa
>> "Pieter" <pietercoucke@.hotmail.com> a écrit dans le message de news:
>> %23w1cPSlIGHA.668@.TK2MSFTNGP11.phx.gbl...
>> Hi,
>> Is it possible to use a Custom Class/Object as a DataSource/DataSet for
>> a report?
>> I have now for instance my object MyCompany, and it woudl be nice to
>> just use a MyCompany-instance as the DataSource...
>> Any hints/help woudl be really appreciated,
>> Thanks a lot in advance,
>> Pieter
>>
>|||Ok thanks, but how do I have to do it? I can't find any information about
it?
How do I tell my report (when designing it) that it has to use property's of
a certain class? and how can I give an instance of that class as a parameter
before refreshing the reportviewer?
"Med Bouchenafa" <com.hotmail@.bouchenafa> wrote in message
news:ubgLk$oIGHA.3492@.TK2MSFTNGP09.phx.gbl...
> That's right, my mistake
> I should have said applications and not only ASP.NET applications
> I meant a SSRS report is a standalone xml file
> Thanks for correcting
> Med bouchenafa
> "Ken Tucker [MVP]" <vb2ae@.bellsouth.net> a écrit dans le message de news:
> OPOUq3mIGHA.1180@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> You can bind windows applications to objects also.
>> Ken
>> --
>> "Med Bouchenafa" <com.hotmail@.bouchenafa> wrote in message
>> news:OhXZe$lIGHA.2896@.TK2MSFTNGP09.phx.gbl...
>>I thought that the objectDatasource control was only used by ASP.NET
>>applications.
>> A SSRS report is not an ASP.NET application unless you use the new
>> reportViewer component and there you can use an ObjectDataSource
>> Med bouchenafa
>> "Pieter" <pietercoucke@.hotmail.com> a écrit dans le message de news:
>> %23w1cPSlIGHA.668@.TK2MSFTNGP11.phx.gbl...
>> Hi,
>> Is it possible to use a Custom Class/Object as a DataSource/DataSet for
>> a report?
>> I have now for instance my object MyCompany, and it woudl be nice to
>> just use a MyCompany-instance as the DataSource...
>> Any hints/help woudl be really appreciated,
>> Thanks a lot in advance,
>> Pieter
>>
>>
>