Parent-child dimension and show empty lines option

I have an issue with a parent-child dimension and ability to show empty lines in browser/client.

Example:

We are putting a parent-child dimension on rows in browser/excel based client and any measures in the data section.

By default the dimension is filtered and only shows dimension members with at least one non-empty measure. However, in certain cases we need to see all dimension members, even when the measures are empty.

To achieve this, there is an option to show empty lines (in cube browser as well as in client we are using for ad hoc data access).

There is however a problem with this option, but only if we use parent-child dimension and we go below first level. Every subsequent level shows all the dimension members and the parent dimension member.

I'll try to illustrate:

before enabling show empty lines:

Lvl1 Lvl2 Measures

A A-A X

A A-B X

A A-C X

B B-A X

B B-C X

After enabling:

Lvl1 Lvl2 Measures

A A X

A A-A X

A A-B X

A A-C X

A A-D

B B

B B-A X

B B-B

B B-C X

In our case this means that every listing of sub-categories withing a category in our product hierarchy, shows all sub-categories and a parent category.

This basically destroys the reporting layout we used since AS2000 and so far I did not find anything that would allow me to change this behavior or anything that would make me believe this is planned behavior in AS2005.

Any help is appreciated

Try setting the nonleafdatahidden property on the parent attribute.

Parameters that depend on each other

I'm developing a report using SQL2000 which has Agent and SubAgent
parameters. The client wants the Agent parameter selection to filter the
SubAgent, which is easy using cascading parameters, but they also want the
SubAgent selection to filter the Agent list to show those Agent's the
selected SubAgent has worked with. Is there any way of referencing parameters
which are higher up the parameter list without getting a "Forward
dependencies are not valid." error?That sounds like a circular reference which would not work, of course...
you might add a third parameter, to list the Agents the subagent has worked
with... continuing down with the cascading parameter...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Swamp Dog" wrote:
> I'm developing a report using SQL2000 which has Agent and SubAgent
> parameters. The client wants the Agent parameter selection to filter the
> SubAgent, which is easy using cascading parameters, but they also want the
> SubAgent selection to filter the Agent list to show those Agent's the
> selected SubAgent has worked with. Is there any way of referencing parameters
> which are higher up the parameter list without getting a "Forward
> dependencies are not valid." error?
>|||Thanks for the response, Wayne.
That sounds like the only way to do it. I'll check with the client to see if
they are happy with having two different Agent parameters, or if they'd
prefer a more usual Agent-SubAgent relationship.
Regards,
Simon.
"Wayne Snyder" wrote:
> That sounds like a circular reference which would not work, of course...
> you might add a third parameter, to list the Agents the subagent has worked
> with... continuing down with the cascading parameter...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Swamp Dog" wrote:
> > I'm developing a report using SQL2000 which has Agent and SubAgent
> > parameters. The client wants the Agent parameter selection to filter the
> > SubAgent, which is easy using cascading parameters, but they also want the
> > SubAgent selection to filter the Agent list to show those Agent's the
> > selected SubAgent has worked with. Is there any way of referencing parameters
> > which are higher up the parameter list without getting a "Forward
> > dependencies are not valid." error?
> >

Parameters in Client Report Builder

Hello,

Is there a way for a regular user of the client report builder to add parameters to their reports? Right now you can only add them by creating a report from visual studio and publishing to the report server, which regular users can't do. Please let me know if I'm missing something here...

Thanks,
Edward

In the Report Builder filter dialog, click the name of the item you want the parameter on and select "Prompt". Example: if you have a condition Last Name = Smith, click on Last Name and select Prompt.
-- Robert||| I've been looking for a way to do that for ages.

Do you know if there is any way to get the prompt to be a drop down box?|||Great! Thanks! |||hey, I am also looking for drop down in report parameter. If you are having any kind of solution for displaying a dropdown list to choose a specific filter criteria on the fly will be a great help!

At present, I am using report parameter with free text which is not the exact solution need to be provided.

By selection of any list item, report will be displayed for specific list item.

e.g. Project name is my report parameter should have dropdown list with data list like "Project1", "Project2" etc. selection of "Project1" will display only Project1 related information in report.

Parameters for report based on MDX

Hi
Can you pl. help me with something that is supposed to be really simple
however it is frustrating us no end. A client needs a reporting services
report based on an SSAS 2005 cube. He wants to have only two date filters
for the report that are shown in the form of calendars (default of RS report
parameters that are datetime type.) One is a 'From Date' and second is 'To
date'. The report data will be filtered on these two dates. Now to try this
out, I created a very simple report based on a cube. The MDX for the report
is as follows.
/<
SELECT NON EMPTY { [Measures].[Number of copies sold] } ON COLUMNS, NON
EMPTY { ([Sales].[All Products].[Product Number].ALLMEMBERS ) } DIMENSION
PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [Stat Can Sales]
Where (
{filter(Dates.[Sales Date].Members,datevalue(Dates.Currentmember.Name) >= datevalue('2006/01/01')
and datevalue(Dates.Currentmember.Name) <= datevalue('2007/01/01'))}
)
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
>/
AS you notice, I harded coded two dates in this MDX just to get the report
working with dataset and layout. Then I added two report parameters,
FromDate and ToDate of type datetime and ran the report again. Now of course
two calendar controls are shown when the report is previewed though the
parameters are not yet tied with the MDX query. Then I got to the dataset of
this MDX query, selected properties by clicking on the '.' on the toolbar
and in the parameters tab for the dataset, I created two parameters as
follows.
Name Value
pFromDate =Parameters!FromDate.Value
pToDate =Parameters!ToDate.Value
Finally I tried to modify the MDX by replacing the hardcoded date fields
with these parameters as
/<
SELECT NON EMPTY { [Measures].[Number of copies sold] } ON COLUMNS, NON
EMPTY { ([Sales].[All Products].[Product Number].ALLMEMBERS ) } DIMENSION
PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [Stat Can Sales]
Where (
{filter(Dates.[Sales Date].Members,datevalue(Dates.Currentmember.Name) >= @.
pFromDate
and datevalue(Dates.Currentmember.Name) <= @. pToDate)}
)
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
>/
Looks like straightforward thing to do. Right? However I cannot save this
parameterized MDX and get an error. So my question is, how to create and
pass parameters like this to MDX?
I also tried something I found on the net where it was mentioned that one
can start the MDX or a SQL query for that matter with "=" and then have the
rest of it is in double quotes. That way, the processing engine treats it as
an expression and then evaluates it at runtime. So the updated query would
be
/<
= "SELECT NON EMPTY { [Measures].[Number of copies sold] } ON COLUMNS, NON
EMPTY { ([Sales].[All Products].[Product Number].ALLMEMBERS ) } DIMENSION
PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [Stat Can Sales]
Where (
{filter(Dates.[Sales Date].Members,datevalue(Dates.Currentmember.Name) >= " + Parameters!FromDate.Value +
"and datevalue(Dates.Currentmember.Name) <= " + Paramters!ToDate.Value
+")}
)
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"
>/
This does not work either. Basically query editor does not like the starting
"=" itself.
Am I missing something?
ThanksHi Anant
You're second approach is the right one.
First, bin the parameters you defined on the paramter tab of the Dataset
properties dialog.
Then click the query tab. The value of you Querystring text area should be
something along the lines of:
"=Select
Non Empty { [Measures].[Number of copies sold] } ON COLUMNS,
Non Empty { ([Sales].[All Products].[Product Number].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
From
[Stat Can Sales]
Where
(
{
Filter(Dates.[Sales Date].Members, DateValue(Dates.Currentmember.Name) >=" + Parameters!FromDate.Value + " And DateValue(Dates.Currentmember.Name) <=" + Paramters!ToDate.Value +")
}
)
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"
Bear in mind, you'll always need to run a hard coded version of your query
first to prepare your query.
Joe
"Anant Rege" wrote:
> Hi
>
> Can you pl. help me with something that is supposed to be really simple
> however it is frustrating us no end. A client needs a reporting services
> report based on an SSAS 2005 cube. He wants to have only two date filters
> for the report that are shown in the form of calendars (default of RS report
> parameters that are datetime type.) One is a 'From Date' and second is 'To
> date'. The report data will be filtered on these two dates. Now to try this
> out, I created a very simple report based on a cube. The MDX for the report
> is as follows.
>
> /<
>
> SELECT NON EMPTY { [Measures].[Number of copies sold] } ON COLUMNS, NON
> EMPTY { ([Sales].[All Products].[Product Number].ALLMEMBERS ) } DIMENSION
> PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
>
> FROM [Stat Can Sales]
>
> Where (
> {filter(Dates.[Sales Date].Members,datevalue(Dates.Currentmember.Name) >=> datevalue('2006/01/01')
> and datevalue(Dates.Currentmember.Name) <= datevalue('2007/01/01'))}
> )
> CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
> FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
>
> >/
>
> AS you notice, I harded coded two dates in this MDX just to get the report
> working with dataset and layout. Then I added two report parameters,
> FromDate and ToDate of type datetime and ran the report again. Now of course
> two calendar controls are shown when the report is previewed though the
> parameters are not yet tied with the MDX query. Then I got to the dataset of
> this MDX query, selected properties by clicking on the '.' on the toolbar
> and in the parameters tab for the dataset, I created two parameters as
> follows.
>
> Name Value
> pFromDate =Parameters!FromDate.Value
> pToDate =Parameters!ToDate.Value
>
> Finally I tried to modify the MDX by replacing the hardcoded date fields
> with these parameters as
>
> /<
>
> SELECT NON EMPTY { [Measures].[Number of copies sold] } ON COLUMNS, NON
> EMPTY { ([Sales].[All Products].[Product Number].ALLMEMBERS ) } DIMENSION
> PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
>
> FROM [Stat Can Sales]
>
> Where (
> {filter(Dates.[Sales Date].Members,datevalue(Dates.Currentmember.Name) >= @.
> pFromDate
> and datevalue(Dates.Currentmember.Name) <= @. pToDate)}
> )
> CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
> FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
>
> >/
>
> Looks like straightforward thing to do. Right? However I cannot save this
> parameterized MDX and get an error. So my question is, how to create and
> pass parameters like this to MDX?
>
> I also tried something I found on the net where it was mentioned that one
> can start the MDX or a SQL query for that matter with "=" and then have the
> rest of it is in double quotes. That way, the processing engine treats it as
> an expression and then evaluates it at runtime. So the updated query would
> be
>
> /<
>
> = "SELECT NON EMPTY { [Measures].[Number of copies sold] } ON COLUMNS, NON
> EMPTY { ([Sales].[All Products].[Product Number].ALLMEMBERS ) } DIMENSION
> PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
>
> FROM [Stat Can Sales]
>
> Where (
> {filter(Dates.[Sales Date].Members,datevalue(Dates.Currentmember.Name) >=> " + Parameters!FromDate.Value +
> "and datevalue(Dates.Currentmember.Name) <= " + Paramters!ToDate.Value
> +")}
> )
> CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
> FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"
>
>
> >/
>
> This does not work either. Basically query editor does not like the starting
> "=" itself.
>
> Am I missing something?
>
> Thanks
>
>
>
>|||Anant,
If you found a working solution, would you please email me? I have the same problem in trying to pass the parameter to MDX. I am using SSRS on SSAS 2005.
I tried filter and "=" method and have been getting an error on both methods whenever I insert the parameter.
Thanks
John
EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com