Help on understanding generated MDX WHERE clause

When your reporting services datasource is a cube, MDX is generated when you use the design view. In the MDX editor the generated MDX can be viewed. Using parameters I always get a where clause with code like the following:

IIF( STRTOSET(@.OrgLevelname, CONSTRAINED).Count = 1, STRTOSET(@.OrgLevelname, CONSTRAINED), [Organisation].[Level 2 name].currentmember )

I like to understand what is generated. Is there something I can read on the generated WHERE clause (I do understand the generated SELECT and FROM clauses)? Or can someone shed a light on it?

Why does the MDX need to branch on 'Count = 1' In what way does the result slice my data when Count = 1 or when Count <> 1?

Thanks,
Henk

I think this is what Reed Jacobson is talking about here:

http://sqljunkies.com/WebLog/hitachiconsulting/archive/2006/11/06/25176.aspx

To find out more about how subcubes and the Where clause work, see:

http://www.sqljunkies.com/WebLog/mosha/archive/2006/11/23/subselects_sp2.aspx

HTH,

Chris

sql

Help on SQL Reporting Services

Can someone please explain what is required to install this Software ? I mean , anything special needed at all or I can just slap it on to any Win2K server . I am assuming it doesn't have to be on the same server as SQLIn case you are not concerned about the cost, you don't have to install MS Reporting Services and MS SQL Server on the same machine (you will need extra SQL Server license though). In case you'd like to create web-based reports only, you may fiind LGX Report interesting (it is free): www.FreeReporting.com.

Help on setting up Reporting Service...

I am new to Reporting Services. I set up the infrastruicture with two
computers to learn the Reporting Service, a stand alone server (No DNS and
AD and not a domain server) and a workstation, and both computers are in the
same workgroup. On the server computer I have a SQL 2000 SP4, mixed mode,
running on a Windows 2003 Server SP1. The second computer is installed with
the Reporting Service SP2 and is running on Windows XP Professional SP2 with
IIS enabled, also I have Visual Studio 2003 installed on this box to create
test reports. I can create and deploy reports using the Visual Studio 2003
and also view the report by typing the something like http://<IIS Server
Name>/ReportServer on the address bar in the Internet Explorer on the
workstation within the LAN. It opens the Report Manager UI and I can see all
the reports I deployed using the VS 2003. However if I try to access the
Report Manager from a remote location which I asked my friend try it from
his home computer by typing http://<My IIS public IP address>/ReportServer
and it did not work. I know that it is something to do with the security
setting with the IIS and the SQL 2000. How can I change the setting to allow
remote computer to access the reports directly or using the Report Manager
UI? Also how can I implment security to stop people access the reports or
Report Manager UI? Should I implement the security at the IIS server or to
the SQL 2000 or on individual database? Or should I implement the security
on both IIS and SQL 2000 and database? Thanks.I had a similar issue with the Firewall on XP SP2. You can add exclusions
to the firewall using the firewall administrator.
--
| From: "Paul" <paul_mak@.hotmail.com>
| Subject: Help on setting up Reporting Service...
| Date: Thu, 25 Aug 2005 10:38:13 -0700
| Lines: 23
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| Message-ID: <#UqJ3vZqFHA.272@.TK2MSFTNGP15.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 64.114.25.49
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP15.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:51138
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I am new to Reporting Services. I set up the infrastruicture with two
| computers to learn the Reporting Service, a stand alone server (No DNS
and
| AD and not a domain server) and a workstation, and both computers are in
the
| same workgroup. On the server computer I have a SQL 2000 SP4, mixed mode,
| running on a Windows 2003 Server SP1. The second computer is installed
with
| the Reporting Service SP2 and is running on Windows XP Professional SP2
with
| IIS enabled, also I have Visual Studio 2003 installed on this box to
create
| test reports. I can create and deploy reports using the Visual Studio
2003
| and also view the report by typing the something like http://<IIS Server
| Name>/ReportServer on the address bar in the Internet Explorer on the
| workstation within the LAN. It opens the Report Manager UI and I can see
all
| the reports I deployed using the VS 2003. However if I try to access the
| Report Manager from a remote location which I asked my friend try it from
| his home computer by typing http://<My IIS public IP
address>/ReportServer
| and it did not work. I know that it is something to do with the security
| setting with the IIS and the SQL 2000. How can I change the setting to
allow
| remote computer to access the reports directly or using the Report
Manager
| UI? Also how can I implment security to stop people access the reports or
| Report Manager UI? Should I implement the security at the IIS server or
to
| the SQL 2000 or on individual database? Or should I implement the
security
| on both IIS and SQL 2000 and database? Thanks.
|
|
|

Help on Reporting services

Hi, I am new to reporting services 2005 express. Is there a website, SDK, or any other source for help (online and offline) on reporting services so that it can be used for help while creating reports or for learning purposes?

Regards, Sandy

When you install reporting services it asks you whether to install books online, samples etc.

If you mark that option, you will have installed some sample reports which some of them are deployed on the sql server - they will appear on the 'Microsoft Sql Server 2005' tab as a new sub-tab 'Reporting services'

There is also some source code illustrating how to access deployed reports.

Cheers,

Yani

Help on creating letter in RS

Hi,
I am trying to create report using Reporting Services 2000. My dataset returns 2 records. I want to generate two reports (one for each record) in a letter format. Can anybody tell me how do I design the report that to generate seperate letter for each record? Thanks.

Am I correct in assuming that you want a seperate page for each record?

Help on Calculated Members

Hi Gurus,

I am new to Microsoft Analysis Services. I wanted to create a new Average Calculation.

The Syntax is

Avg(?Set[, ?Numeric Expression]). I am using the Foodmart cube.

I have tried using many things but have not achieved any success.

These are the following things that I have tried.

1. Avg([Product].[Product Family].&[Drink].&[Dairy].&[Dairy].&[Milk] , 'Avergare of Milk')

In this 'Avergare of Milk' is some sort of Calculation name I suppose.

2. Avg([Product Family].&[Drink].&[Dairy].&[Dairy].&[Milk])

In this ‘6’ I have mentioned assuming there are only 6 members in the hierarchy.

Please tell me What shall I write in place of Set and Numeric Expression.

I also came across an article and found this

“If you want to create an average, what you need to do is create one
measure
called SUM, and another measure based on COUNT and then create a
calculated
member called AVG which would just be SUM/COUNT. I believe there are
examples of this in the Foodmart 2000 cubes.”

But to my Surprise I also found the Syntax of SUM and Count to be similar.

Please help me out in this.

I also have a request Could anyone give me some examples and any link from where I could learn all the available calculation with examples.

Thanks in advance

Here is an example that should help you:

with member [Measures].[Av1] as
'Avg({descendants([Product].[All Products].[Drink],[Product Name])}, [Measures].[Profit])'
member [Measures].[Av2] as 'Avg({descendants([Product].[All Products].[Drink],[Product Name])}, coalesceempty([Measures].[Profit],0))'
member [Measures].[Num] as 'count({descendants([Product].[All Products].[Drink],[Product Name])})'
member [Measures].[Sum] as 'sum({descendants([Product].[All Products].[Drink],[Product Name])}, [Measures].[Profit])'
member [Measures].[Av3] as '[Measures].[Sum]/[Measures].[Num]'

select

{[Measures].[Av1],[Measures].[Av2],[Measures].[Av3],[Measures].[Num],[Measures].[Sum]} on columns,

{[Store].[Store Name].members} on rows
from sales

As you can see, you Avg1 is different from Av2 because it doesn't consider empty cells, so the average is slightly bigger.

Hope this help,

Santiago

|||

Hi,

Thanks for the help. But I think I was not clear what I wanted. I am using Analysis manager (Microsoft SQL Server -> Analysis Services -> Analysis Manger).

In that I am drilling down the Analysis Servers to list the Available servers and then drilling down the database present to get the list of the available cubes.Now I am right clicking on a cube and choosing edit to open the Cube Editor.In Cube editor I am trying to create a new Calculated Members. (In the Calculated member Builder I am unable to write the query for the Average.) There I am not able to make the calculation. And am unable to sort out the problem.

I hope this information help in giving my problem the perfect picture.

Please let me know if u need anything else

Thanks in advance

|||

In Calculated Member builder, select Measures as the Parent Dimension, write the name of the measure (eg. MyAverage) and type the appropriate mdx expression. For example:

Avg({descendants([Product].[All Products].[Drink],[Product Name])}, [Measures].[Profit])

You don′t need then to reprocess the cube. Just browse it and you should be able to see the new Calculated Member.

|||

HI,

Many many thanks for the help. It worked.

Can I get some link from where I can get some examples of the calculation.

Thanks

Help needed with KERBROS and Native XML Web Services

Trying to get Native XML Web Services setup in our test enviroment, and I've
hit a problem.
When the HTTP EndPoint is set to use Integrated Authentication, I can browse
to the endpoint (using IE7 from a seperate PC) and get the WSDL back, but
when I switch the EndPoint to use KERBEROS authentication, I get nothing
returned, and only see a blank page.
All machines are in the same Active Directory domain. Using SQL Server 2005
SP2 on Win2003 Std SP1 on the server, and XP SP2 and IE7 on the PC.
The SQL Server is running under a local domain account, and this account has
been registered for both the MSSQLSvc and HTTP services, as below (the names
have been changed to protect the guilty).
MSSQLSvc/Server1.test.local:1433
MSSQLSvc/Server1:1433
HTTP/Server1.test.local
HTTP/Server1
The EndPoint name has been reserved using sp_reserve_http_namespace, and is
owned by SA. I'll be changing the auditting to log all authentication event.
So, anyone has any ideas or guidance'
Thanks in advance,
AlHi Al
Have you tried AUTHENTICATION=KERBEROS? It sounds like you have run
SetSPN.exe (http://msdn2.microsoft.com/en-us/library/ms178119.aspx)
John
"Al" wrote:
> Trying to get Native XML Web Services setup in our test enviroment, and I've
> hit a problem.
> When the HTTP EndPoint is set to use Integrated Authentication, I can browse
> to the endpoint (using IE7 from a seperate PC) and get the WSDL back, but
> when I switch the EndPoint to use KERBEROS authentication, I get nothing
> returned, and only see a blank page.
> All machines are in the same Active Directory domain. Using SQL Server 2005
> SP2 on Win2003 Std SP1 on the server, and XP SP2 and IE7 on the PC.
> The SQL Server is running under a local domain account, and this account has
> been registered for both the MSSQLSvc and HTTP services, as below (the names
> have been changed to protect the guilty).
> MSSQLSvc/Server1.test.local:1433
> MSSQLSvc/Server1:1433
> HTTP/Server1.test.local
> HTTP/Server1
> The EndPoint name has been reserved using sp_reserve_http_namespace, and is
> owned by SA. I'll be changing the auditting to log all authentication event.
> So, anyone has any ideas or guidance'
> Thanks in advance,
> Al|||When I do the
ALTER ENDPOINT <endpoint> AS HTTP (AUTHENTICATION=(KERBEROS)),
I don't get the WSDL. But I have discovered that the endpoint still accepts
and processes calls to the web services on the EndPoint.
When I do
ALTER ENDPOINT <endpoint> AS HTTP (AUTHENTICATION=(INTEGRATED)), I do get
the WSDL.
Could it be that using KERBEROS authentication disables the WSDL discovery?
"John Bell" wrote:
> Hi Al
> Have you tried AUTHENTICATION=KERBEROS? It sounds like you have run
> SetSPN.exe (http://msdn2.microsoft.com/en-us/library/ms178119.aspx)
> John
>|||Hi
I am not sure if this is the case and can't find any documentation to say
so. Have you tried AUTHENTICATION=KERBEROS,NTLM and AUTHENTICATION=NTLM,
KERBEROS to see if there are any differences?
John
"Al" wrote:
> When I do the
> ALTER ENDPOINT <endpoint> AS HTTP (AUTHENTICATION=(KERBEROS)),
> I don't get the WSDL. But I have discovered that the endpoint still accepts
> and processes calls to the web services on the EndPoint.
> When I do
> ALTER ENDPOINT <endpoint> AS HTTP (AUTHENTICATION=(INTEGRATED)), I do get
> the WSDL.
> Could it be that using KERBEROS authentication disables the WSDL discovery?
> "John Bell" wrote:
> > Hi Al
> >
> > Have you tried AUTHENTICATION=KERBEROS? It sounds like you have run
> > SetSPN.exe (http://msdn2.microsoft.com/en-us/library/ms178119.aspx)
> >
> > John
> >|||Hi John,
I changed the WS to return some details from sys.dm_exec_connections as
well, so I could see a little more of what was going on when calling the WS.
When I have specified NTLM as an Authentication method (position doesn't
appear to make a difference), then I can get a WSDL back (with IE7).
If I have both NTLM and KERBEROS, or INTEGRATED by itself, then the
connection is made as NEGOTIATE.
NTLM by itself gets the WSDL back, and is made as NTLM
KERBEROS by itself doesn't return a WSDL and is made as KERBEROS.
But what I have now seen (because I did a refresh instead of using a new IE
tab), if IE7 has displayed the WSDL, and then I switch the EndPoint to
KERBEROS only, then it displays the following instead of the blank page I've
usually had.
The XML page cannot be displayed
Cannot view XML input using style sheet. Please correct the error and then
click the Refresh button, or try again later.
----
Access is denied. Error processing resource 'http://apollo/SQLTestEP?wsdl'.
I've checked, and IE7 thinks the web site is in the "Local Intranet", so I'm
assuming that the Windows credentials are passed straight through. And it
seems odd that I can call the WS from C#, but get an "Access is denied" from
IE7.
"John Bell" wrote:
> Hi
> I am not sure if this is the case and can't find any documentation to say
> so. Have you tried AUTHENTICATION=KERBEROS,NTLM and AUTHENTICATION=NTLM,
> KERBEROS to see if there are any differences?
> John
> "Al" wrote:
> > When I do the
> > ALTER ENDPOINT <endpoint> AS HTTP (AUTHENTICATION=(KERBEROS)),
> > I don't get the WSDL. But I have discovered that the endpoint still accepts
> > and processes calls to the web services on the EndPoint.
> >
> > When I do
> > ALTER ENDPOINT <endpoint> AS HTTP (AUTHENTICATION=(INTEGRATED)), I do get
> > the WSDL.
> >
> > Could it be that using KERBEROS authentication disables the WSDL discovery?
> >
> > "John Bell" wrote:
> >
> > > Hi Al
> > >
> > > Have you tried AUTHENTICATION=KERBEROS? It sounds like you have run
> > > SetSPN.exe (http://msdn2.microsoft.com/en-us/library/ms178119.aspx)
> > >
> > > John
> > >|||Hi
Does this mean you are using custom WDSL, does default change the behavior?
John
"Al" wrote:
> Hi John,
> I changed the WS to return some details from sys.dm_exec_connections as
> well, so I could see a little more of what was going on when calling the WS.
> When I have specified NTLM as an Authentication method (position doesn't
> appear to make a difference), then I can get a WSDL back (with IE7).
> If I have both NTLM and KERBEROS, or INTEGRATED by itself, then the
> connection is made as NEGOTIATE.
> NTLM by itself gets the WSDL back, and is made as NTLM
> KERBEROS by itself doesn't return a WSDL and is made as KERBEROS.
> But what I have now seen (because I did a refresh instead of using a new IE
> tab), if IE7 has displayed the WSDL, and then I switch the EndPoint to
> KERBEROS only, then it displays the following instead of the blank page I've
> usually had.
> The XML page cannot be displayed
> Cannot view XML input using style sheet. Please correct the error and then
> click the Refresh button, or try again later.
> ----
> Access is denied. Error processing resource 'http://apollo/SQLTestEP?wsdl'.
> I've checked, and IE7 thinks the web site is in the "Local Intranet", so I'm
> assuming that the Windows credentials are passed straight through. And it
> seems odd that I can call the WS from C#, but get an "Access is denied" from
> IE7.
> "John Bell" wrote:
> > Hi
> >
> > I am not sure if this is the case and can't find any documentation to say
> > so. Have you tried AUTHENTICATION=KERBEROS,NTLM and AUTHENTICATION=NTLM,
> > KERBEROS to see if there are any differences?
> >
> > John
> >
> > "Al" wrote:
> >
> > > When I do the
> > > ALTER ENDPOINT <endpoint> AS HTTP (AUTHENTICATION=(KERBEROS)),
> > > I don't get the WSDL. But I have discovered that the endpoint still accepts
> > > and processes calls to the web services on the EndPoint.
> > >
> > > When I do
> > > ALTER ENDPOINT <endpoint> AS HTTP (AUTHENTICATION=(INTEGRATED)), I do get
> > > the WSDL.
> > >
> > > Could it be that using KERBEROS authentication disables the WSDL discovery?
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi Al
> > > >
> > > > Have you tried AUTHENTICATION=KERBEROS? It sounds like you have run
> > > > SetSPN.exe (http://msdn2.microsoft.com/en-us/library/ms178119.aspx)
> > > >
> > > > John
> > > >|||Hi
The HTTP EndPoint has been created with WSDL = STANDARD (i.e.
WSDL=N'[master].[sys].[sp_http_generate_wsdl_defaultcomplexorsimple]').
"John Bell" wrote:
> Hi
> Does this mean you are using custom WDSL, does default change the behavior?
> John
> "Al" wrote:
> > Hi John,
> >
> > I changed the WS to return some details from sys.dm_exec_connections as
> > well, so I could see a little more of what was going on when calling the WS.
> >
> > When I have specified NTLM as an Authentication method (position doesn't
> > appear to make a difference), then I can get a WSDL back (with IE7).
> > If I have both NTLM and KERBEROS, or INTEGRATED by itself, then the
> > connection is made as NEGOTIATE.
> > NTLM by itself gets the WSDL back, and is made as NTLM
> > KERBEROS by itself doesn't return a WSDL and is made as KERBEROS.
> >
> > But what I have now seen (because I did a refresh instead of using a new IE
> > tab), if IE7 has displayed the WSDL, and then I switch the EndPoint to
> > KERBEROS only, then it displays the following instead of the blank page I've
> > usually had.
> >
> > The XML page cannot be displayed
> > Cannot view XML input using style sheet. Please correct the error and then
> > click the Refresh button, or try again later.
> > ----
> > Access is denied. Error processing resource 'http://apollo/SQLTestEP?wsdl'.
> >
> > I've checked, and IE7 thinks the web site is in the "Local Intranet", so I'm
> > assuming that the Windows credentials are passed straight through. And it
> > seems odd that I can call the WS from C#, but get an "Access is denied" from
> > IE7.
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > I am not sure if this is the case and can't find any documentation to say
> > > so. Have you tried AUTHENTICATION=KERBEROS,NTLM and AUTHENTICATION=NTLM,
> > > KERBEROS to see if there are any differences?
> > >
> > > John
> > >
> > > "Al" wrote:
> > >
> > > > When I do the
> > > > ALTER ENDPOINT <endpoint> AS HTTP (AUTHENTICATION=(KERBEROS)),
> > > > I don't get the WSDL. But I have discovered that the endpoint still accepts
> > > > and processes calls to the web services on the EndPoint.
> > > >
> > > > When I do
> > > > ALTER ENDPOINT <endpoint> AS HTTP (AUTHENTICATION=(INTEGRATED)), I do get
> > > > the WSDL.
> > > >
> > > > Could it be that using KERBEROS authentication disables the WSDL discovery?
> > > >
> > > > "John Bell" wrote:
> > > >
> > > > > Hi Al
> > > > >
> > > > > Have you tried AUTHENTICATION=KERBEROS? It sounds like you have run
> > > > > SetSPN.exe (http://msdn2.microsoft.com/en-us/library/ms178119.aspx)
> > > > >
> > > > > John
> > > > >

Help needed on reprting services

Hi, Can anyone help me to create a report with all drill down features
to be expanded at the time of display itself( without clicking the
expand button)
Thank youMake all the texbox visible ofcourse with toggle items and check in the
texbox->properties->visibility last option
ie "initial Appearence" select "expanded (-)"
Amarnath
"georgerijo@.gmail.com" wrote:
> Hi, Can anyone help me to create a report with all drill down features
> to be expanded at the time of display itself( without clicking the
> expand button)
> Thank you
>

Help Needed For reporting service error(An internal error occurred on the report server)

Hi experts
I am working on sql server reporting services 2005. And now a days i
am facing a problem.
Some times when i want to run a report the at that time i am geting
the following error msg
"An internal error occurred on the report server. See the error log
for more details."
This eroor not every time come when i run the reports and if i agian
pres the view report button then in 2nd or 3rd attemt
same report run and shows the correct data.
But it is not good to see any error msg during running the report. I
have checked the log file but there no eoor msg is
listed.
So if any one have any idea abt this how to solve this problem Please
help me.
Becuase now a days it is comming more freqently.
Any help will be gratefull.
Regards
DineshIt sounds like you have the PerfCounters issue on an AMD Processor server.
If you check the SRS error logs in C:\Program Files\Microsoft SQL
Server\MSSQL.#\Reporting Services\LogFiles you will probably find an
"ASSERT" message in there.
Edit your boot.ini file and add the following switch to your boot.ini and
see if that fixes the issue /usepmtimer
More information regarding this can be found here:
http://support.microsoft.com/kb/895980/en-us
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> From: Dinesh <dinesht15@.gmail.com>
> Newsgroups: microsoft.public.sqlserver.reportingsvcs
> Subject: Help Needed For reporting service error(An internal error
occurred on the report server)
> Date: Thu, 04 Oct 2007 02:23:20 -0700
> Hi experts
> I am working on sql server reporting services 2005. And now a days i
> am facing a problem.
> Some times when i want to run a report the at that time i am geting
> the following error msg
> "An internal error occurred on the report server. See the error log
> for more details."
> This eroor not every time come when i run the reports and if i agian
> pres the view report button then in 2nd or 3rd attemt
> same report run and shows the correct data.
> But it is not good to see any error msg during running the report. I
> have checked the log file but there no eoor msg is
> listed.
> So if any one have any idea abt this how to solve this problem Please
> help me.
> Becuase now a days it is comming more freqently.
> Any help will be gratefull.
> Regards
> Dinesh
>

Help Needed For reporting service 2005(Rendering in PDF)

Hi experts
I am working on sql server reporting services 2005.
Now a days i m developing a text report. Which shows text for selected
parameters.
I have added more table headers in my layout. And i am using one row
for heading and just below row for description.
This description is comming from database so it is variable, somtimes
it is having ony 100 chars and some times it is
having more than 20000 chars. Problem starts here when the number of
charcters are less then report is looking good in PDF
But when it is more then the description row moves on next page. I
have tried this by selecting the table property "Fit table in one page
if possible"
I have once cheked this property and once unchecked this but result is
same. Report looks good in report viewer but not looks good in PDF
format.
MY another question is it possible that i can bind two rows in the
table that if second rows goes in the second page then
the row above also goes on next page. RIght now heading row appears on
first page in pdf and then second row starts on new page after leaving
all the available space on first page.
So please if any body have any idea about my problem, Please help me.
Any help will be gratefull.
Regards
DineshTry to use group.
"Dinesh" wrote:
> Hi experts
> I am working on sql server reporting services 2005.
> Now a days i m developing a text report. Which shows text for selected
> parameters.
> I have added more table headers in my layout. And i am using one row
> for heading and just below row for description.
> This description is comming from database so it is variable, somtimes
> it is having ony 100 chars and some times it is
> having more than 20000 chars. Problem starts here when the number of
> charcters are less then report is looking good in PDF
> But when it is more then the description row moves on next page. I
> have tried this by selecting the table property "Fit table in one page
> if possible"
> I have once cheked this property and once unchecked this but result is
> same. Report looks good in report viewer but not looks good in PDF
> format.
> MY another question is it possible that i can bind two rows in the
> table that if second rows goes in the second page then
> the row above also goes on next page. RIght now heading row appears on
> first page in pdf and then second row starts on new page after leaving
> all the available space on first page.
> So please if any body have any idea about my problem, Please help me.
> Any help will be gratefull.
> Regards
> Dinesh
>

Help Needed For reporting service 2005(Regarding deployment)

Hi experts
I am working on sql server reporting services 2005.
And i want to know one thing. I have one solution in SSRS 2005 report
designer and currently i m dploying each and every report to their
respective folders.
But now i have to deploy all the reports again on some new server. So
is there any way by which i dont need to deploy each and every report
seprately. IS there any script available by which i can deploy all the
reports on my server.
And second thing i wish to know is it possible to make instalable file
for this solution so that where ever i want i can install these
reports. If yes then how can i make instalable file of my this
solution.
Any help will be gratefull.
Regards
DineshThe easiest way to deploy your reports is to have a solution with multiple
report projects in them. Each Report Project would represent a folder on
your report server. In the project properties you would have your
"TargetReportFolder" set to the correct deployment folder name for each of
the projects.
Then when you wanted to deploy your reports to a different server all you
would need to do is modify the "TargetServerURL" property to point to the
new server name and then redeploy your reports.
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> From: Dinesh <dinesht15@.gmail.com>
> Newsgroups: microsoft.public.sqlserver.reportingsvcs
> Subject: Help Needed For reporting service 2005(Regarding deployment)
> Date: Tue, 16 Oct 2007 03:39:27 -0700
> Organization: http://groups.google.com
> Hi experts
> I am working on sql server reporting services 2005.
> And i want to know one thing. I have one solution in SSRS 2005 report
> designer and currently i m dploying each and every report to their
> respective folders.
> But now i have to deploy all the reports again on some new server. So
> is there any way by which i dont need to deploy each and every report
> seprately. IS there any script available by which i can deploy all the
> reports on my server.
> And second thing i wish to know is it possible to make instalable file
> for this solution so that where ever i want i can install these
> reports. If yes then how can i make instalable file of my this
> solution.
> Any help will be gratefull.
> Regards
> Dinesh
>

Help Needed For reporting service 2005(Regarding default date value for report parameter)

Hi experts
I am working on sql server reporting services 2005.
I am using Date time control for my report parameter. And default
value i given =Today(). This is working fine.
But i want some days previous date for default value and when I am
writting Today()-1, Report is giving error.
So can any body tell me how i can do this for defalut value. I want to
give one month previous date as default value.
And second thing i wish to know is it possible to make instalable file
for this solution so that where ever i want i can
install these reports. If yes then how can i make instalable file of
my this solution.
Any help will be gratefull.
Regards
DineshOn Oct 23, 9:38 am, Dinesh <dinesh...@.gmail.com> wrote:
> Hi experts
> I am working on sql server reporting services 2005.
> I am using Date time control for my report parameter. And default
> value i given =Today(). This is working fine.
> But i want some days previous date for default value and when I am
> writting Today()-1, Report is giving error.
> So can any body tell me how i can do this for defalut value. I want to
> give one month previous date as default value.
> And second thing i wish to know is it possible to make instalable file
> for this solution so that where ever i want i can
> install these reports. If yes then how can i make instalable file of
> my this solution.
> Any help will be gratefull.
> Regards
> Dinesh
Hi!
You can try it;
(Date = DATEADD(Day, - 1, GetDate())
Hope this helps
Regards
Shima

Help Needed For Date format in SSRS 2005

Hi experts,
I am working on SQL server 2005 reporting services and i am getting a
problem.
I am developing a report in which i am taking two parameters
one is FromDate and second is ToDate and i have changed thier Data
type as Date Time.
So it is giving callender control in reports. And default values for
both parameter is todays system date.
Now I want these dates in dd/mm/yyyy format so i changed the setting
of my system for the required date format.
In parameter selection box date format is correct it is comming in dd/
mm/yyyy format.
But again I am using a text box in the report body which tell us a
message that this report is contains the data between these dates.
For this I am using the expression
="The following data is for the period between " & Parameters!
FromDate.Value & " and " & Parameters!ToDate.Value
So here i am getting these dates in the mm/dd/yyyy format.
I have tried this also
="The following data is for the period between " & Parameters!
FromDate.Label & " and " & Parameters!ToDate.Label
The no date is comming in the text box.
I have tried Cdate function and other functions in DateTime function
which are available in the reporting services property box, But i am
not finding the solution for this problem.
So if any body is having any idea about this then please help me.
Any help wil be appriciated.
Regards
DineshHi Dinesh,
Make sure the report language is set to Australia (or any other language
that supports this format by default) , you find this under the properties
dialog box when you only select the form (not any controls on it).
Cheers
Matt
"Dinesh" wrote:
> Hi experts,
> I am working on SQL server 2005 reporting services and i am getting a
> problem.
> I am developing a report in which i am taking two parameters
> one is FromDate and second is ToDate and i have changed thier Data
> type as Date Time.
> So it is giving callender control in reports. And default values for
> both parameter is todays system date.
> Now I want these dates in dd/mm/yyyy format so i changed the setting
> of my system for the required date format.
> In parameter selection box date format is correct it is comming in dd/
> mm/yyyy format.
> But again I am using a text box in the report body which tell us a
> message that this report is contains the data between these dates.
> For this I am using the expression
> ="The following data is for the period between " & Parameters!
> FromDate.Value & " and " & Parameters!ToDate.Value
> So here i am getting these dates in the mm/dd/yyyy format.
> I have tried this also
> ="The following data is for the period between " & Parameters!
> FromDate.Label & " and " & Parameters!ToDate.Label
> The no date is comming in the text box.
> I have tried Cdate function and other functions in DateTime function
> which are available in the reporting services property box, But i am
> not finding the solution for this problem.
> So if any body is having any idea about this then please help me.
> Any help wil be appriciated.
> Regards
> Dinesh
>|||try something like:
Parameters!ToDate.Value.ToString("dd/mm/yyyy")
or
DateTime.Parse(Parameters!ToDate.Value).ToString("dd/mm/yyyy")
or
CDate(Parameters!ToDate.Value).ToString("dd/mm/yyyy")
I don't remember which one works or not
good luck!
"Dinesh" <dinesht15@.gmail.com> wrote in message
news:1189587299.387328.305020@.50g2000hsm.googlegroups.com...
> Hi experts,
> I am working on SQL server 2005 reporting services and i am getting a
> problem.
> I am developing a report in which i am taking two parameters
> one is FromDate and second is ToDate and i have changed thier Data
> type as Date Time.
> So it is giving callender control in reports. And default values for
> both parameter is todays system date.
> Now I want these dates in dd/mm/yyyy format so i changed the setting
> of my system for the required date format.
> In parameter selection box date format is correct it is comming in dd/
> mm/yyyy format.
> But again I am using a text box in the report body which tell us a
> message that this report is contains the data between these dates.
> For this I am using the expression
> ="The following data is for the period between " & Parameters!
> FromDate.Value & " and " & Parameters!ToDate.Value
> So here i am getting these dates in the mm/dd/yyyy format.
> I have tried this also
> ="The following data is for the period between " & Parameters!
> FromDate.Label & " and " & Parameters!ToDate.Label
> The no date is comming in the text box.
> I have tried Cdate function and other functions in DateTime function
> which are available in the reporting services property box, But i am
> not finding the solution for this problem.
> So if any body is having any idea about this then please help me.
> Any help wil be appriciated.
> Regards
> Dinesh
>

Help needed !!!! the colors used by a bar chart in SQL Server 2005 Reporting Services.

I have created a linked report in which I have two screen:

First Screen: Clicking on the bar of particular country the details of that particular country should be visible (the second screen)

Second Screen: When I clicked on a bar. All the count values are very well correct. In first chart Count of values under one bar "Pending decision"(yellow) was 337 for a country and in second screen its 337 again(shown in light green bar).

<!--[if !vml]--><!--[endif]-->

Problem: My problem is to make it sure that the colors that the first screen is having for particular status (as shown in the legend) should remain same in second screen also. For example for a status say "pending decision" the chart is using yellow color, so in screen two as well it must be shown with yellow color and not with lany other color. Can anyone help me in this context. Is there any way to customize colors used by bar chart. Please note that the question is not about using appropriate color scheme(palette) the question is how to "capture/ Re-use/ customize/ pass as a parameter" the colors used by a bar chart in SQL Server 2005 Reporting Services.

This may help you.

http://www.cubido.at/Default.aspx?tabid=176&EntryID=29

cheers,

Andrew

Help needed

Hello geeks,
I am new to database thing, wanted to know that does MS reporting services 2005 works with SQL 2000..? if yes than can any one please give me reference link.
I appreciate for the efforts to be taken by you people.

Thanks,
Pranav

SQL Server 2000 had its own version of Reporting Services, so I think you'd want to use that instead.

http://www.microsoft.com/sql/prodinfo/previousversions/default.mspx

However, I moved this thread over to the SSRS forum in case there's a more authoritative answer. TFS forums aren't the right place for a question like this.

|||

Pranav,

1.) You can use SQL 2000 as the data source for your SQL RS 2005 reports.

2.)You can also set up your report server to use SQL 2000 as it's reportserver database.
This will not, however, get around the need to license SQL 2005 for Reporting Services.

For #1 you will simply point your report datasource to the SQL 2000 instance.

For #2 you will need to install SQL 2005 Reporting Services with the configure later option. You will then go into the Reporting Services Configuration Manager and point to an existing SQL 2000 ReportServer database.

Hope this helps!

help nedded for MS reporting service 2000

Hi,
I am working on microsoft SQl server 2000 reporting services. I am
developing an application in c#.net in VS2003 which
will call reports in windowds forms and ask for user to select the
parameters for them.
Till now i had finished . Now I want to select multiple values for
parameters. and wants that for each combination a seprate report will
be drawn and it the end all the reports are merged in to one.
i.e.
suppose there are two parameters in a report and there are 10 available
values for first one and 20 for second parameter.
now i have selected 2 values for first parameter and 4 for second
parameter.
how this i can do in my program.
Please help me. because i am not finding any way to do it.
Thanks And Regards
DineshYou are not finding it because you cannot do this in RS 2000. RS 2000 does
not support multi-select parameters. RS 2005 does.
If it is possible to go to RS 2005 I would. For one thing, VS 2005 comes
will new controls that make integrating with RS much much easier. Plus RS
2005 has many good improvements: multi-select parameters, end user sorting,
performance improvements, etcs.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dinesh" <dinesht15@.gmail.com> wrote in message
news:1164259279.407811.135710@.l12g2000cwl.googlegroups.com...
> Hi,
> I am working on microsoft SQl server 2000 reporting services. I am
> developing an application in c#.net in VS2003 which
> will call reports in windowds forms and ask for user to select the
> parameters for them.
> Till now i had finished . Now I want to select multiple values for
> parameters. and wants that for each combination a seprate report will
> be drawn and it the end all the reports are merged in to one.
> i.e.
> suppose there are two parameters in a report and there are 10 available
> values for first one and 20 for second parameter.
> now i have selected 2 values for first parameter and 4 for second
> parameter.
> how this i can do in my program.
> Please help me. because i am not finding any way to do it.
> Thanks And Regards
> Dinesh
>|||hi.......
i think u could not understand my problem properly.....
i know it is possible........
I had done the rendering and pring part of this for RS2000.
I had developed an application which will show all the reports on
report Server in a combobox.
here user will select a report if it contains parameters then it will
draw that many checklist boxes on the form if it does not have any
parameter then it will ask from user to select a format in which he
wantthe report.
now user will select a value for each parameter from checklistbox by
checking a single value in each check list box.
now those valuse will be passed in the rendor method to draw the
report.
after that report is drawn and printed.
till now i had finished.
now i wan to selct multiple values in checklistboxes.
suppose there are 2 parameters and user has selected 3 values in first
one and 2 values in second one
then i will get 6 parameters to pass in render method.
then after passing one set i will get one report i will store it in a
string varible and append others after it. ie. i will got total 6
reports which will be merged one after others.
if it is always fixed then no problem i can do it in for loop.
but since number of parameters are not fixed so i want to know how can
get the parameters set dynamically. for any combimation of parameters.
i.e.
i want each combination of parameter and want to pass it to render
method whioch i wrote in a seprate function.
Hope i have explained my problem completely.
if any body have any idea please tell me.
Regards
Dinesh
Bruce L-C [MVP] wrote:
> You are not finding it because you cannot do this in RS 2000. RS 2000 does
> not support multi-select parameters. RS 2005 does.
> If it is possible to go to RS 2005 I would. For one thing, VS 2005 comes
> will new controls that make integrating with RS much much easier. Plus RS
> 2005 has many good improvements: multi-select parameters, end user sorting,
> performance improvements, etcs.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Dinesh" <dinesht15@.gmail.com> wrote in message
> news:1164259279.407811.135710@.l12g2000cwl.googlegroups.com...
> > Hi,
> >
> > I am working on microsoft SQl server 2000 reporting services. I am
> > developing an application in c#.net in VS2003 which
> > will call reports in windowds forms and ask for user to select the
> > parameters for them.
> > Till now i had finished . Now I want to select multiple values for
> > parameters. and wants that for each combination a seprate report will
> > be drawn and it the end all the reports are merged in to one.
> >
> > i.e.
> > suppose there are two parameters in a report and there are 10 available
> > values for first one and 20 for second parameter.
> > now i have selected 2 values for first parameter and 4 for second
> > parameter.
> > how this i can do in my program.
> > Please help me. because i am not finding any way to do it.
> >
> > Thanks And Regards
> >
> > Dinesh
> >|||Hello,
I am trying to have both comma delimited and tab delimited output
option from sql 2000 reporting services. I have updated the config file
to have 2 output option in the render section of the config file. But I
always get 2 options listed on the dropdown as CSV - Comma Delimited, I
didn't get the tab-delimited option. Why my code in the config file
didn't get affected? Anything else I have to do, or is it all possible
in RS 2000.
Any help would be really appreciated.
Thanks,
Ravi

Help me please

Hi,
this is my first post..Please help me out!
I am using a dataset from Analysis services and i built a report in tabular
fashion with drilldown ( I used report wizard ). I also created a simple bar
chart...
Now my problem is:
Is it possible to make a drilldown in the graph as well...?
i.e.for example: if the graph loads initally with country (x-axis) and
sales(y-axis)
i need the graph to give me the sales data with individual states in the
country and split sales data..am i making sense?..
Thanks for your valuable time.
P.S: I am using Sql server 2005 Beta 2.
Sincerely,
SankarI answered your question on the MSDN Forum:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=22371
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"sankar" <sankar_s_07@.hotmail.com> wrote in message
news:%23m8gjEDeFHA.3376@.TK2MSFTNGP10.phx.gbl...
> Hi,
> this is my first post..Please help me out!
> I am using a dataset from Analysis services and i built a report in
> tabular
> fashion with drilldown ( I used report wizard ). I also created a simple
> bar
> chart...
> Now my problem is:
> Is it possible to make a drilldown in the graph as well...?
> i.e.for example: if the graph loads initally with country (x-axis) and
> sales(y-axis)
> i need the graph to give me the sales data with individual states in the
> country and split sales data..am i making sense?..
> Thanks for your valuable time.
> P.S: I am using Sql server 2005 Beta 2.
> Sincerely,
> Sankar
>|||Thank you Robert..
I shall download the latest version.
Sincerely,
Sankar
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:e$PYQPHeFHA.544@.TK2MSFTNGP12.phx.gbl...
> I answered your question on the MSDN Forum:
> http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=22371
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "sankar" <sankar_s_07@.hotmail.com> wrote in message
> news:%23m8gjEDeFHA.3376@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > this is my first post..Please help me out!
> >
> > I am using a dataset from Analysis services and i built a report in
> > tabular
> > fashion with drilldown ( I used report wizard ). I also created a simple
> > bar
> > chart...
> > Now my problem is:
> >
> > Is it possible to make a drilldown in the graph as well...?
> >
> > i.e.for example: if the graph loads initally with country (x-axis) and
> > sales(y-axis)
> > i need the graph to give me the sales data with individual states in the
> > country and split sales data..am i making sense?..
> >
> > Thanks for your valuable time.
> > P.S: I am using Sql server 2005 Beta 2.
> >
> > Sincerely,
> > Sankar
> >
> >
>

Help me Deploying rdl Report

Hi

I am new to SQL Reporting Services. I have developed an rdl report in SQL Server Reporting Serices 2005. It works very fine in development environment. But now i want to include this report in an exisitng project running at our client in some remote location. This report 'll be accessed by several clients from different offices.

To view reports locally I have added myself as an administrator on the local Report Server. But how would i authecticate specific users that i want to grant access to the report. I have a database table that stores users that have access to that report.

Also how do I upload these reports with all these users settings on the client's Report Server.

Looking forward for ur good response.

Regards

Asif Hameed

This isn't a complete answer, but here are some things you need to know.

Do the clients who will be accessing reports have windows accounts on the new server in the remote location (or can they have windows accounts)? If so, you can use windows groups to give authority to different folders and reports on the Report Server.

If not, you may be looking at a more custom solution which could include custom security extensions and a front end application.

Hope this points you in the right direction.

|||

Hi

thanx for your solution. I think I must go for some custom security extensions. But could you please explain a little bit more about custom security extensions. I mean how to create and use these in my project.

Another problem is deployment of reports. I am creating reports on my local server. How do I create the setup to copy these reports to client's server, or just manual copy would solve the problem.

Regards,

Asif Hameed

|||

Regarding custom security extensions, I'm not a good resource because I haven't written one, but I'm sure someone on the board has.

Regarding deployment, you can either change your properties on your solution in visual studio to point to your client's server, and deploy or you can browse to the client's http://domain/reports interface and upload the rdl file. If you don't have remote access, you can always email the file to the client or carry it on disk to upload loacally.

Hope that helps.

|||

Well, thats a great idea to email or send the rdl files to user, but the problem is that the user is not so much technical. How could I deploy the reports to his server, while I have no remote access.

Is there any possible way that I can make a setup, like PUBLISH in rdlc reports, that the user will only need to run the setup and the report will be deployed automatically to his server?

|||

I haven't had the need to do so in the past, so I'm just speculating. I believe that you can build deployment packages if you have the Pro version of Visual Studio. I'm assuming that this would include Reporting Services projects.

In addition, there is a reporting services scripting capability so you might be able to put together a zip of files for the client to extract into a designated folder such as c:\temp and then run the script, or double-click a batch file that runs the script.

Again sorry I don't have more. I'm just telling you the routes I would explore in your situation. Someone may come along with a good answer from their experience.

Good luck.

|||Well, thanx for your effort, I ll try your solution. I think it ll work. Thanx once again.|||

I have found an other solution for deploying rdl reports using MSI. Here is the link if anyone else is facing the same problem.

http://blogs.msdn.com/bimusings/archive/2006/03/01/541599.aspx

Hope this will work