Hi:
Thanks first.Did anyone know how to send the query statement for dynamic
MDX Parameter query in sql server reporting service 2005.For example in
2000,we could send the statement like this:
= " select {[Measures].[aaa]} on axis(0),
{" + Parameters@.Parm_time.value + "} on axis(1)
from [Cube] "
,but it failes in 2005.Anyone know why it failes?
--
zhouhuituanI'm pretty sure you need to write the parameters like this:
Parameters!Parm_time.Value in 2005.
Also, make sure everything is on the same line (there will be page breaks,
but don't add them yourself.)
Does your syntax work without the parameters? Are you sure your parameter
value looks like the code you test your query with?
Also, are you using the graphic query builder at all? In my opinion, the
query syntax that appears when using the query builder is very different
from the MDX queries used with AS 2000, and takes a while to get used to.
The filtering in the query works differently than before, especially when
you mess around with the parameters.
What is the error message?
Kaisa M. Lindahl Lervik
"zhouhuituan" <zhouhuituan@.discussions.microsoft.com> wrote in message
news:D231360E-7B36-41B2-A476-15FE606435BB@.microsoft.com...
> Hi:
> Thanks first.Did anyone know how to send the query statement for dynamic
> MDX Parameter query in sql server reporting service 2005.For example in
> 2000,we could send the statement like this:
> = " select {[Measures].[aaa]} on axis(0),
> {" + Parameters@.Parm_time.value + "} on axis(1)
> from [Cube] "
> ,but it failes in 2005.Anyone know why it failes?
> --
> zhouhuituan
Showing posts with label service. Show all posts
Showing posts with label service. Show all posts
Wednesday, March 21, 2012
Monday, March 19, 2012
Dynamic grouping problem
Hi all,
I need a certain functionality in my Reporting service report.
I need a report with 4 levels of grouping above the detail level. The
initial visibility of all the lower levels should be hidden expect the top
most level. For all the levels toggle item is set in the group properties.
There should also be a boolean parameter called "expand all" which will
affect the initial visibility of the report.
This report should also have dynamic grouping facility with the help of 4
parameters one for each level. Thus if the value passed to one of these
groupby parameters is the word "none" then that group header row should not
be shown. In all cases the detail row should be visible either by drilling
down to that level or as a top level if all other levels are "none".
I am partially successful in the above scenario by making the top level a
mandatory one. i.e the top level can never be "none". However my problem is
that if in the database a particular column supplied to report thorough one
of the groupby parameters has all nulls then also the group header row should
not show up. But when I try to put expression to control this using
"isnothing" function, the detail row never shows up.
Thus there are 3 boolean values to be controlled along with toggle in each
group and detail section. How to achieve this?
Please help me solve this issue.
Thanks.Hi,
Welcome to MSDN Managed NewsGroup. This is Justin from Microsoft.
I do not quite understand the following:
"if in the database a particular column supplied to report thorough one
of the groupby parameters has all nulls then also the group header row
should
not show up. But when I try to put expression to control this using
"isnothing" function, the detail row never shows up. "
Could you elaborate your issue?
Please aslo let me know the expression you set for the visiblity of the
group so that I could better understand your issue.
If you have any question, please feel free to let me know.
Thanks & Regards,
Justin Shen
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--
| Thread-Topic: Dynamic grouping problem
| thread-index: AcYmdoUqBM3uTevuTUye6qEq3EKGnQ==| X-WBNR-Posting-Host: 38.113.18.195
| From: "=?Utf-8?B?bXNkbnVzZXI=?=" <ringt@.nospam.nospam>
| Subject: Dynamic grouping problem
| Date: Tue, 31 Jan 2006 06:56:30 -0800
| Lines: 29
| Message-ID: <AE4B3F3C-C7DF-431E-9F62-5FCE1D4B82CB@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:67863
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi all,
|
| I need a certain functionality in my Reporting service report.
|
| I need a report with 4 levels of grouping above the detail level. The
| initial visibility of all the lower levels should be hidden expect the
top
| most level. For all the levels toggle item is set in the group
properties.
| There should also be a boolean parameter called "expand all" which will
| affect the initial visibility of the report.
|
| This report should also have dynamic grouping facility with the help of 4
| parameters one for each level. Thus if the value passed to one of these
| groupby parameters is the word "none" then that group header row should
not
| be shown. In all cases the detail row should be visible either by
drilling
| down to that level or as a top level if all other levels are "none".
|
| I am partially successful in the above scenario by making the top level a
| mandatory one. i.e the top level can never be "none". However my problem
is
| that if in the database a particular column supplied to report thorough
one
| of the groupby parameters has all nulls then also the group header row
should
| not show up. But when I try to put expression to control this using
| "isnothing" function, the detail row never shows up.
|
| Thus there are 3 boolean values to be controlled along with toggle in
each
| group and detail section. How to achieve this?
|
| Please help me solve this issue.
|
| Thanks.
||||Hi,
I have 3 parameters by name groupby2,groupby3,groupby4 which helps to create
3 dynamic groups. Also I have the top most non-dynamic group. Thus 4 group
headers above my details row and no group footers. I pass the word "NONE"
through one or many of the above parameters,if I dont want to see any or all
of the group header. I use a parameter called expand_all to set the initial
toggle state of all the groups to be expanded or collapsed.
Here are the values I set for each group:
1st group header:
(I reach visibility tab as follows: right click group header--edit
group-visibility tab)
There I set the initial visibility to be visible and no toggle item. close
dialog boxes.
Then I select the first group header row and in the properties window, I set
the visibility(hidden) to be visible.
2nd Group Header:
I reach visibility tab. Set initial visibility to be an expression ="(not
parameters!expand_all.value)" and toggle item to be the name of first textbox
in the 1st group header.
Then I select the 2nd group header row and in the properties window, I set
the visibility(hidden) to be
="IIF(parameters!groupby2.value="none",true,false)
3rd Group Header:
I reach the visibility tab. Set initial visibility to be ="(not
parameters!expand_all.value) and
iif(parameters!groupby2.value="none",false,true) and toggle item to be name
of the first text box in the 2nd group header row.
Then I select the 3nd group header row and in the properties window, I set
the visibility(hidden) to be
="IIF(parameters!groupby3.value="none",true,false)
4th Group Header:
I reach the visibility tab. Set initial visibility to be ="(not
parameters!expand_all.value) and
iif(parameters!groupby3.value="none",false,true) and the toggle item to be
name of the first textbox in the 3rd group header row.
Then I select the 4th group header row and in the properties window, I set
the visibility(hidden) to be
="IIF(parameters!groupby4.value="none",true,false)
Details row:
initial visibility: =(NOT Parameters!EXPAND_ALL.Value) AND
IIF(PARAMETERS!GROUPBY4.VALUE="NONE",FALSE,TRUE) and the toggle item to be
the name of the first text box in the 4th group header row.
Then I select the details row and in the properties window, I set the
visibility(hidden) to be =false.
With the above set up, the report works good. But my problem is say we pass
a particular database field name to groupby2 parameter. (These 4 groupby
parameters are attached in the select list of the dataset of report). Now in
the table if the sent column has no data just nulls, then the group header
first column has just the + toggle sign or - toggle sign based on the state
of toggle and no value. So I tried to suppress the group header when the
field passed for that group header has null values in the database using the
a condition like this in the group header row properties.
=iif(parameters!groupby2.value=none or
fields!groupby2databasefieldname.value is nothing,true,false)
now this trick does work good when the initial visibillity is expanded. But
when the initial visibility is collapsed, then the detials row always remains
hidden. Never shows up.
To put the problem precisely, I want to suppress the group header rows
conditionally; but details row should show up under all circumstances, even
if the initial state of details row is hidden.
Thanks|||Hi,
Is it possible for you to generate a sample rdl file using AdventureWorks
database to describe the issue more?
I understand the information may be sensitive to you, my direct email
address is v-mingqc@.ONLINEmicrosoft.com (Please make sure you have removed
ONLINE before you click SEND), you may send the file to me directly and I
will keep secure.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Quick question Michael
One of my developers wanted the .rdls for AdventureWorks from SSRS 2000 SP1
(interested in one of the features demonstrated).
How does one go about getting these (other than the chunking sprocs in the
default Report Server database)?
rob
"Michael Cheng [MSFT]" wrote:
> Hi,
> Is it possible for you to generate a sample rdl file using AdventureWorks
> database to describe the issue more?
> I understand the information may be sensitive to you, my direct email
> address is v-mingqc@.ONLINEmicrosoft.com (Please make sure you have removed
> ONLINE before you click SEND), you may send the file to me directly and I
> will keep secure.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||all set (helped my developer...)
just 'edit' from the http interface and copy the .rdl into a VB.NET project.
thanks anyways
rob
"tutor" wrote:
> Quick question Michael
> One of my developers wanted the .rdls for AdventureWorks from SSRS 2000 SP1
> (interested in one of the features demonstrated).
> How does one go about getting these (other than the chunking sprocs in the
> default Report Server database)?
> rob
> "Michael Cheng [MSFT]" wrote:
> > Hi,
> >
> > Is it possible for you to generate a sample rdl file using AdventureWorks
> > database to describe the issue more?
> >
> > I understand the information may be sensitive to you, my direct email
> > address is v-mingqc@.ONLINEmicrosoft.com (Please make sure you have removed
> > ONLINE before you click SEND), you may send the file to me directly and I
> > will keep secure.
> >
> > Thank you for your patience and cooperation. If you have any questions or
> > concerns, don't hesitate to let me know. We are always here to be of
> > assistance!
> >
> >
> > Sincerely yours,
> >
> > Michael Cheng
> > Microsoft Online Partner Support
> > ======================================================> > When responding to posts, please "Reply to Group" via your newsreader so
> > that others may learn and benefit from your issue.
> > =====================================================> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >|||Michael,
Do you have the rdl based on AdventureWorks for this reporting issue? I have
a similar need.
Thanks
John
"Michael Cheng [MSFT]" wrote:
> Hi,
> Is it possible for you to generate a sample rdl file using AdventureWorks
> database to describe the issue more?
> I understand the information may be sensitive to you, my direct email
> address is v-mingqc@.ONLINEmicrosoft.com (Please make sure you have removed
> ONLINE before you click SEND), you may send the file to me directly and I
> will keep secure.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Did you resolve this problem? I have the exact same issue and would
appreciate any help.
"msdnuser" wrote:
> Hi all,
> I need a certain functionality in my Reporting service report.
> I need a report with 4 levels of grouping above the detail level. The
> initial visibility of all the lower levels should be hidden expect the top
> most level. For all the levels toggle item is set in the group properties.
> There should also be a boolean parameter called "expand all" which will
> affect the initial visibility of the report.
> This report should also have dynamic grouping facility with the help of 4
> parameters one for each level. Thus if the value passed to one of these
> groupby parameters is the word "none" then that group header row should not
> be shown. In all cases the detail row should be visible either by drilling
> down to that level or as a top level if all other levels are "none".
> I am partially successful in the above scenario by making the top level a
> mandatory one. i.e the top level can never be "none". However my problem is
> that if in the database a particular column supplied to report thorough one
> of the groupby parameters has all nulls then also the group header row should
> not show up. But when I try to put expression to control this using
> "isnothing" function, the detail row never shows up.
> Thus there are 3 boolean values to be controlled along with toggle in each
> group and detail section. How to achieve this?
> Please help me solve this issue.
> Thanks.
I need a certain functionality in my Reporting service report.
I need a report with 4 levels of grouping above the detail level. The
initial visibility of all the lower levels should be hidden expect the top
most level. For all the levels toggle item is set in the group properties.
There should also be a boolean parameter called "expand all" which will
affect the initial visibility of the report.
This report should also have dynamic grouping facility with the help of 4
parameters one for each level. Thus if the value passed to one of these
groupby parameters is the word "none" then that group header row should not
be shown. In all cases the detail row should be visible either by drilling
down to that level or as a top level if all other levels are "none".
I am partially successful in the above scenario by making the top level a
mandatory one. i.e the top level can never be "none". However my problem is
that if in the database a particular column supplied to report thorough one
of the groupby parameters has all nulls then also the group header row should
not show up. But when I try to put expression to control this using
"isnothing" function, the detail row never shows up.
Thus there are 3 boolean values to be controlled along with toggle in each
group and detail section. How to achieve this?
Please help me solve this issue.
Thanks.Hi,
Welcome to MSDN Managed NewsGroup. This is Justin from Microsoft.
I do not quite understand the following:
"if in the database a particular column supplied to report thorough one
of the groupby parameters has all nulls then also the group header row
should
not show up. But when I try to put expression to control this using
"isnothing" function, the detail row never shows up. "
Could you elaborate your issue?
Please aslo let me know the expression you set for the visiblity of the
group so that I could better understand your issue.
If you have any question, please feel free to let me know.
Thanks & Regards,
Justin Shen
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--
| Thread-Topic: Dynamic grouping problem
| thread-index: AcYmdoUqBM3uTevuTUye6qEq3EKGnQ==| X-WBNR-Posting-Host: 38.113.18.195
| From: "=?Utf-8?B?bXNkbnVzZXI=?=" <ringt@.nospam.nospam>
| Subject: Dynamic grouping problem
| Date: Tue, 31 Jan 2006 06:56:30 -0800
| Lines: 29
| Message-ID: <AE4B3F3C-C7DF-431E-9F62-5FCE1D4B82CB@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:67863
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi all,
|
| I need a certain functionality in my Reporting service report.
|
| I need a report with 4 levels of grouping above the detail level. The
| initial visibility of all the lower levels should be hidden expect the
top
| most level. For all the levels toggle item is set in the group
properties.
| There should also be a boolean parameter called "expand all" which will
| affect the initial visibility of the report.
|
| This report should also have dynamic grouping facility with the help of 4
| parameters one for each level. Thus if the value passed to one of these
| groupby parameters is the word "none" then that group header row should
not
| be shown. In all cases the detail row should be visible either by
drilling
| down to that level or as a top level if all other levels are "none".
|
| I am partially successful in the above scenario by making the top level a
| mandatory one. i.e the top level can never be "none". However my problem
is
| that if in the database a particular column supplied to report thorough
one
| of the groupby parameters has all nulls then also the group header row
should
| not show up. But when I try to put expression to control this using
| "isnothing" function, the detail row never shows up.
|
| Thus there are 3 boolean values to be controlled along with toggle in
each
| group and detail section. How to achieve this?
|
| Please help me solve this issue.
|
| Thanks.
||||Hi,
I have 3 parameters by name groupby2,groupby3,groupby4 which helps to create
3 dynamic groups. Also I have the top most non-dynamic group. Thus 4 group
headers above my details row and no group footers. I pass the word "NONE"
through one or many of the above parameters,if I dont want to see any or all
of the group header. I use a parameter called expand_all to set the initial
toggle state of all the groups to be expanded or collapsed.
Here are the values I set for each group:
1st group header:
(I reach visibility tab as follows: right click group header--edit
group-visibility tab)
There I set the initial visibility to be visible and no toggle item. close
dialog boxes.
Then I select the first group header row and in the properties window, I set
the visibility(hidden) to be visible.
2nd Group Header:
I reach visibility tab. Set initial visibility to be an expression ="(not
parameters!expand_all.value)" and toggle item to be the name of first textbox
in the 1st group header.
Then I select the 2nd group header row and in the properties window, I set
the visibility(hidden) to be
="IIF(parameters!groupby2.value="none",true,false)
3rd Group Header:
I reach the visibility tab. Set initial visibility to be ="(not
parameters!expand_all.value) and
iif(parameters!groupby2.value="none",false,true) and toggle item to be name
of the first text box in the 2nd group header row.
Then I select the 3nd group header row and in the properties window, I set
the visibility(hidden) to be
="IIF(parameters!groupby3.value="none",true,false)
4th Group Header:
I reach the visibility tab. Set initial visibility to be ="(not
parameters!expand_all.value) and
iif(parameters!groupby3.value="none",false,true) and the toggle item to be
name of the first textbox in the 3rd group header row.
Then I select the 4th group header row and in the properties window, I set
the visibility(hidden) to be
="IIF(parameters!groupby4.value="none",true,false)
Details row:
initial visibility: =(NOT Parameters!EXPAND_ALL.Value) AND
IIF(PARAMETERS!GROUPBY4.VALUE="NONE",FALSE,TRUE) and the toggle item to be
the name of the first text box in the 4th group header row.
Then I select the details row and in the properties window, I set the
visibility(hidden) to be =false.
With the above set up, the report works good. But my problem is say we pass
a particular database field name to groupby2 parameter. (These 4 groupby
parameters are attached in the select list of the dataset of report). Now in
the table if the sent column has no data just nulls, then the group header
first column has just the + toggle sign or - toggle sign based on the state
of toggle and no value. So I tried to suppress the group header when the
field passed for that group header has null values in the database using the
a condition like this in the group header row properties.
=iif(parameters!groupby2.value=none or
fields!groupby2databasefieldname.value is nothing,true,false)
now this trick does work good when the initial visibillity is expanded. But
when the initial visibility is collapsed, then the detials row always remains
hidden. Never shows up.
To put the problem precisely, I want to suppress the group header rows
conditionally; but details row should show up under all circumstances, even
if the initial state of details row is hidden.
Thanks|||Hi,
Is it possible for you to generate a sample rdl file using AdventureWorks
database to describe the issue more?
I understand the information may be sensitive to you, my direct email
address is v-mingqc@.ONLINEmicrosoft.com (Please make sure you have removed
ONLINE before you click SEND), you may send the file to me directly and I
will keep secure.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Quick question Michael
One of my developers wanted the .rdls for AdventureWorks from SSRS 2000 SP1
(interested in one of the features demonstrated).
How does one go about getting these (other than the chunking sprocs in the
default Report Server database)?
rob
"Michael Cheng [MSFT]" wrote:
> Hi,
> Is it possible for you to generate a sample rdl file using AdventureWorks
> database to describe the issue more?
> I understand the information may be sensitive to you, my direct email
> address is v-mingqc@.ONLINEmicrosoft.com (Please make sure you have removed
> ONLINE before you click SEND), you may send the file to me directly and I
> will keep secure.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||all set (helped my developer...)
just 'edit' from the http interface and copy the .rdl into a VB.NET project.
thanks anyways
rob
"tutor" wrote:
> Quick question Michael
> One of my developers wanted the .rdls for AdventureWorks from SSRS 2000 SP1
> (interested in one of the features demonstrated).
> How does one go about getting these (other than the chunking sprocs in the
> default Report Server database)?
> rob
> "Michael Cheng [MSFT]" wrote:
> > Hi,
> >
> > Is it possible for you to generate a sample rdl file using AdventureWorks
> > database to describe the issue more?
> >
> > I understand the information may be sensitive to you, my direct email
> > address is v-mingqc@.ONLINEmicrosoft.com (Please make sure you have removed
> > ONLINE before you click SEND), you may send the file to me directly and I
> > will keep secure.
> >
> > Thank you for your patience and cooperation. If you have any questions or
> > concerns, don't hesitate to let me know. We are always here to be of
> > assistance!
> >
> >
> > Sincerely yours,
> >
> > Michael Cheng
> > Microsoft Online Partner Support
> > ======================================================> > When responding to posts, please "Reply to Group" via your newsreader so
> > that others may learn and benefit from your issue.
> > =====================================================> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >|||Michael,
Do you have the rdl based on AdventureWorks for this reporting issue? I have
a similar need.
Thanks
John
"Michael Cheng [MSFT]" wrote:
> Hi,
> Is it possible for you to generate a sample rdl file using AdventureWorks
> database to describe the issue more?
> I understand the information may be sensitive to you, my direct email
> address is v-mingqc@.ONLINEmicrosoft.com (Please make sure you have removed
> ONLINE before you click SEND), you may send the file to me directly and I
> will keep secure.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Did you resolve this problem? I have the exact same issue and would
appreciate any help.
"msdnuser" wrote:
> Hi all,
> I need a certain functionality in my Reporting service report.
> I need a report with 4 levels of grouping above the detail level. The
> initial visibility of all the lower levels should be hidden expect the top
> most level. For all the levels toggle item is set in the group properties.
> There should also be a boolean parameter called "expand all" which will
> affect the initial visibility of the report.
> This report should also have dynamic grouping facility with the help of 4
> parameters one for each level. Thus if the value passed to one of these
> groupby parameters is the word "none" then that group header row should not
> be shown. In all cases the detail row should be visible either by drilling
> down to that level or as a top level if all other levels are "none".
> I am partially successful in the above scenario by making the top level a
> mandatory one. i.e the top level can never be "none". However my problem is
> that if in the database a particular column supplied to report thorough one
> of the groupby parameters has all nulls then also the group header row should
> not show up. But when I try to put expression to control this using
> "isnothing" function, the detail row never shows up.
> Thus there are 3 boolean values to be controlled along with toggle in each
> group and detail section. How to achieve this?
> Please help me solve this issue.
> Thanks.
Wednesday, March 7, 2012
Dynamic database in Web Service
I have a question about changing the database source dynamically in a Web Service. I have created a Web Service for a Crystal Report using VS .NET 2003. I use Crystal Reports Web Viewer to display the report in an ASP.NET page. My database is an Access database and I have a copy of the report source database for each unique user to the Web page. I read the post on changing the database dynamically but it seems to be changed on the Crystal Reports viewer and not in the Crystal report which would be done in the Web Service. I have tried setting debug stops in the code behind module in the Web Service but the debugger does not seem to reach the breakpoints. Can you offer any advice on modifying the database connections dynamically in the Web Service?I have a similar problem. I can set the viewer db properties when i declare a web service but i cant use report engine object model. Can you tell me how you have accomplished that|||I found it fairly easy to bind to a single database. I merely opened the Crystal Report in Visual Studio .NET. In the Report Design view, I right click on the Database Fields in the Field Explorer and Select "Set Location". I defined an ODBC connection to the database and bind the report to that. Turning the report into a Web Services was easy as well. I added the report to a project that I was using to define Web Services. Then right click on the project and select Publish the report as a Web Service. Visual Studio does all the rest. Unforunately all of this is a design/build time and I haven't figured out how to change the database during runtime.
Hope this helps you.|||thanks for the reply. However that is not my problem. I want to be able to export these reports as well. Exporting code only works from report engine object model and you cant use it from client application if your reports are published as web service. That is my complication. I can set parameters, db changes at runtime when i do web services. Still thanks for the reply|||I'm sorry. I guess I did not understand the question about the the report engine object model. I also am not sure what you are referring to on exporting the reports. I haven't done anything beyond the defaults that you get when Visual Studio creates the Web Service except to connect it to the Crystal Reports Web Viewer in the ASPX pages. What did you mean when you said you can set parameters, db changes at runtime through web services? My main question is how to specify a different database file(Access database) at runtime.|||This is how u do it with web services and a strored procedure as your data source. If you use tables as data source. loop through all the tables and set the propetries of the tables(uid,pwd,dbname, servername)
Hope this helps.
Exporting questions is i have to export the reports in crystal to excel, pdf, word format.That can rbe done using viewer object model and i have to do it in web services but i dont know how to modify the engine in the web service code. I have the same problem you have. U cant debug any code in web services
Dim mytablelogoninfos As New CrystalDecisions.Shared.TableLogOnInfos
Dim mytablelogoninfo As New CrystalDecisions.Shared.TableLogOnInfo
Dim myconnectioninfo As New CrystalDecisions.Shared.ConnectionInfo
With myconnectioninfo
.UserID = "uid"
.ServerName = "servername"
.Password = "pwd"
.DatabaseName = "dbname"
End With
mytablelogoninfo.TableName = "cspAppointmentsReport;1"
With mytablelogoninfo.ConnectionInfo
.UserID = "uid"
.Password = "pwd"
End With
mytablelogoninfo.ConnectionInfo = myconnectioninfo
mytablelogoninfos.Add(mytablelogoninfo)
CrystalReportViewer1.LogOnInfo = mytablelogoninfos
CrystalReportViewer1.ReportSource = New localhost.AppointmentSubReportService|||Now I understand what you mean by exporting and no I haven't tried anything like that yet. Sorry I don't think I can help you.
Thanks for the sample code. I will see if I can make it work for what I'm trying to do. I appreciate the help.
Good luck solving your problem.
Hope this helps you.|||thanks for the reply. However that is not my problem. I want to be able to export these reports as well. Exporting code only works from report engine object model and you cant use it from client application if your reports are published as web service. That is my complication. I can set parameters, db changes at runtime when i do web services. Still thanks for the reply|||I'm sorry. I guess I did not understand the question about the the report engine object model. I also am not sure what you are referring to on exporting the reports. I haven't done anything beyond the defaults that you get when Visual Studio creates the Web Service except to connect it to the Crystal Reports Web Viewer in the ASPX pages. What did you mean when you said you can set parameters, db changes at runtime through web services? My main question is how to specify a different database file(Access database) at runtime.|||This is how u do it with web services and a strored procedure as your data source. If you use tables as data source. loop through all the tables and set the propetries of the tables(uid,pwd,dbname, servername)
Hope this helps.
Exporting questions is i have to export the reports in crystal to excel, pdf, word format.That can rbe done using viewer object model and i have to do it in web services but i dont know how to modify the engine in the web service code. I have the same problem you have. U cant debug any code in web services
Dim mytablelogoninfos As New CrystalDecisions.Shared.TableLogOnInfos
Dim mytablelogoninfo As New CrystalDecisions.Shared.TableLogOnInfo
Dim myconnectioninfo As New CrystalDecisions.Shared.ConnectionInfo
With myconnectioninfo
.UserID = "uid"
.ServerName = "servername"
.Password = "pwd"
.DatabaseName = "dbname"
End With
mytablelogoninfo.TableName = "cspAppointmentsReport;1"
With mytablelogoninfo.ConnectionInfo
.UserID = "uid"
.Password = "pwd"
End With
mytablelogoninfo.ConnectionInfo = myconnectioninfo
mytablelogoninfos.Add(mytablelogoninfo)
CrystalReportViewer1.LogOnInfo = mytablelogoninfos
CrystalReportViewer1.ReportSource = New localhost.AppointmentSubReportService|||Now I understand what you mean by exporting and no I haven't tried anything like that yet. Sorry I don't think I can help you.
Thanks for the sample code. I will see if I can make it work for what I'm trying to do. I appreciate the help.
Good luck solving your problem.
Friday, February 17, 2012
Dynamic capabilities with Parameters
Hello all,
I have a winform application, where in we get the parameters from the reporting service and we display them in a panel for user's to input their criteria. So far everything worked good, but now we have a requirement wherein dynamic functionality is needed.
For Example:
Based on a selection of a combobox value, we want the other control to be enabled or disabled. Also we want default value of a control to be calculated based on another control's value like .. if user enters value "1" in textbox1 then texbox 2 should have default value of "6". (Textbox1 + 5).........and mix and match of such capabilities.
The Question is........IS IT POSSIBLE? and if yes...HOW!!!
VDeepak,
You can not enable and disable control, but you can provide default value to parameters by using "Cascade Parameters". I customary write a stored procedure to provide my values.
Ham
|||Thanks for the reply Cris,
Getting the default values from strored proceduers was never a problem. The problem here is to get the dynamic behaviour of the controls based on the retrieved parameters from the reporting services.
any help would be appriciated.
|||Oops.. typo Carl to cris....sorry
|||Not a problem,
Okay the approach could be textbox1 is parameter pMyFirst, Textbox2 is parameter mysecond. On your second parameter fire by your default stored procedure "sp_mysecond" witht akes a parameter of "pMyFirst" inside my sp_second goes like:
If @.pMyFirst = 1
begin
return 5+1
end
If @.pMyFirst = 2
begin
return 6+2
end
Now your second textbox should have the require value based on your business rule.
Ham
|||
Thanks for the response Carl.
The validation which you detailed can be done in reporting service . But , as I explained earlier I am brining all the parameters to win-forms application which is our data entry point for the end users. Here when the user edits any default value then this change should reflect in concurrent parameters with the appropriate values in the win-form app. This is a dynamic change in all parameters.
Hope I explained it clear...
|||
VDeepak,
My bad, I missed the operative word "Win-Forms Application" - Sorry I have not yet done any Reporting Service in a Win-Form application so my experience is limited in this area.
Ham
Subscribe to:
Posts (Atom)