Report empty?

I have created a basic report that just shows some columns from a query, I'm using a dataadapter and the reportviewer component.

If I execute the query it calles it returns some rows, but the report is empty.

What's wrong with the reportviewer and the report objects?

I'm thinking about just using gridviews because the reports don't work, altough I'd like to use the reportviewer because it can export to excel/pdf.

Thanks.

HI,onishirox:

I can not debug it without any codes from your scenario, but you can have a check with this following walkround to make your codes work:

Binding Data to the ReportViewer Control Dynamically in ASP.NET 2.0 -

http://www.aspfree.com/c/a/ASP.NET/Binding-Data-to-the-ReportViewer-Control-Dynamically-in-ASPNET-20/1/

If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be

of assistance

|||

HI,onishirox:
We are marking this issue as "Answered". If you have any new findings or concerns, please feel free to unmark the issue.
Thank you for your understanding!

|||

Thanks. I think the problem was that I was using sqlserver2000, seems to work ok now with sql2005.

Report doesnt sortPlease help

Hi everyone,
I created a report where I display informations about travels.

Data are grouped by a unique code (travel code), but they're not sorted chronologically, even though in my ASP page I specify ORDER BY date_beginning.

I also created a new group (for date_beginning) within Crystal Report, and then I moved it at the first position, but nothing changes.

I'm really in trouble, any help is appreciated
Thanks in advanceHi,

the best way to Handle this is to Order the way want in the SQL
Query and bring the Data, then on the Report just set the Group order to
'Original', first set the All the Group order to Original if u have other group as well then try this sql order it will work then one by one set the other gouping order to see which couses the prob... surely another gouping order is effecting the Order.

FaFa

report does not display data

Hi friends
i created a very simple report (VS 2005 standard edition). which works fine.
i added a new column to the underlying table (actually its view) . this column's data is combinations of 2 other varchar columns.
when i added this column to my report, for some reason , report does not display any data when i run the report!! actually there is data as i run that view in query analyzer.
any ideas how to resolve this issue or investigate this problem ?
Thanks for your helplooks like i need to refresh "refesh fields" .after that everything worked nicelysql

Report deployment without VS

Hi!
I created several reports and I want to give them to several persons for the installation on

their SQL Server 2005. Is it possible to create something like an

installation package (similar to the deployment manifest in SSIS)?
Thanks for any help!

Check out the PublishSampleReports.rss script in the C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Script Samples folder.

You can use it with the rs.exe tool to automatically publish an RDL definition to a server. To use it, your user will have to type a command, however.

If you *totally* want to automate deployment of reports you could do so by creating an MSI which uses a custom action: That custom action would essentially call the same commands as in the script above...It seems like a lot of work to me, though.

|||You can use Reporting Services Scripter to do this. Simply select the reports + datasource you want to distribute (and probably the folder too) and generate the scripts. You can then supply the generated scripts/rdl + batch file to them and all they have to do is change the servername and run the batchfile.|||

...and Jasper's reply reminds me that you can also now script this stuff right from SQL Managment Studio just like you could script tables, views, etc. back in 2000.

Launch SSMS, connect to your Report Server, then right-click the object you want to distribute and click Script.

Report Delivery problem with Data Driven Subscription

I created a data driven subscription for a report that will email a copy of the report based on a parameter passed from a table. The report runs successfully as indicated in Report Manager but the email is not delivered. I checked the ReportServerService log file and a message states that the report was sent to the proper email address. There is another log, ReportServerWebApp, that shows the message "The extension Report Server Email does not have a LocalizedNameAttribute" .

I created a regular subscription and it emails the report successfully so it can't be the SMTP server.

Any ideas how I can fix this problem with the Data Driven Subscription? Thank you.

David

I did some further checking. If I send off a second job, it delivers the emailed reports. However, the problem persists with the second job.That is, Reporting Services indicates that the job ran successfully and delivered the reports, but some of the reports are getting stuck in a queue somewhere between the reporting server, the remote SMTP server and Microsoft Exchange.

Is there anyone else having problems with Reporting Services reports not getting fully delivered by a remote SMTP server? Would using a local SMTP server fix the problem?

David

|||Sorry for the delay with replying this. From the SSRS prospective your data driven subscription has been delivered to SMTP server successfully. Then SMTP server failed to deliver e-mal. Any chance that e-mail TO address is build off a data-driven subscription query results and is incorrect? Per every successfully sent e-mail SSRS will write into the service logfile ReportServerService__<Date>.log a line: Email successfully sent to {0}. Can you double check that TO addresses are correct for the cases that you thin are failing?|||

All of the reports are being sent to the same email address. Some get there successfully, others don't make it out of the queue.

David

|||

Then it really looks like a problem with SMTP server. It’s likely not related to SSRS.

-Igor

Report Delivery problem with Data Driven Subscription

I created a data driven subscription for a report that will email a copy of the report based on a parameter passed from a table. The report runs successfully as indicated in Report Manager but the email is not delivered. I checked the ReportServerService log file and a message states that the report was sent to the proper email address. There is another log, ReportServerWebApp, that shows the message "The extension Report Server Email does not have a LocalizedNameAttribute" .

I created a regular subscription and it emails the report successfully so it can't be the SMTP server.

Any ideas how I can fix this problem with the Data Driven Subscription? Thank you.

David

I did some further checking. If I send off a second job, it delivers the emailed reports. However, the problem persists with the second job.That is, Reporting Services indicates that the job ran successfully and delivered the reports, but some of the reports are getting stuck in a queue somewhere between the reporting server, the remote SMTP server and Microsoft Exchange.

Is there anyone else having problems with Reporting Services reports not getting fully delivered by a remote SMTP server? Would using a local SMTP server fix the problem?

David

|||Sorry for the delay with replying this. From the SSRS prospective your data driven subscription has been delivered to SMTP server successfully. Then SMTP server failed to deliver e-mal. Any chance that e-mail TO address is build off a data-driven subscription query results and is incorrect? Per every successfully sent e-mail SSRS will write into the service logfile ReportServerService__<Date>.log a line: Email successfully sent to {0}. Can you double check that TO addresses are correct for the cases that you thin are failing?|||

All of the reports are being sent to the same email address. Some get there successfully, others don't make it out of the queue.

David

|||

Then it really looks like a problem with SMTP server. It’s likely not related to SSRS.

-Igor

REport definitions

hi,

I have created a report in RS 2005 but i want to use in RS2003 how i change the report definitions in the current report to make it compatable in RS 2003

Thanks in advance,

Regards,

Somu

You can start by changing the XML namespaces at the top of the definition. Get a sample RS 2000 report to see how. It will take probably a few cycles of trial and error.|||You can manually convert RS 2005 RDLs so that they can be published to the RS 2000 report server by editing the RDL and:

1. Change the overall RDL namespace
Replace <Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" ... >
With <Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition" ... >
2. Remove the following elements entirely if present in the RDL file:
<InteractiveWidth>21cm</InteractiveWidth>
<InteractiveHeight>29.7cm</InteractiveHeight>

3. Remove all occurences of interactive sort in the RDL file by removing all <UserSort> elements and its inner contents.

These changes are usually sufficient to "downgrade" the RDL so it uploads on a RS 2000 report server.

-- Robert

Report Definition Language

Using VS 2005, I have created a piece of software that creates an RDL file using XML. However, although my data (though a DataView control) is showing as it should, and I can update this to create new data, I cannot get the RDL to display a variable which I change and use as a 'header text' to the report. The variable is shown the first time I display the RDL through a ReportViewer Control but remains static thereafter. The interesting thing is that when I view the XML through a third-party viewer, it shows the correct value stored in the variable.

For information, I am using a child form in a MDI to create the data that is fed into another child form that contains the ReportViewer. The MDI has been programmed so that the child forms are not closed and disposed of, but are hidden from view.

Has anyone any initial ideas of what I could do to flush or clear the RDL or any other relevant object?

Are you viewing the RDL in the report designer when this happens? Report Designer has a functionality to cache the data in preview (http://msdn2.microsoft.com/en-us/library/ms157366.aspx). You should be able to click on the refresh button to get new data.

Report Date Help

I created the below report and it has worked fine all year, however it will
not pull data for Dec 2004. Can anyone tell me what may be causing this?
Thanks!
Glen
SELECT
Customers.CustomerName, DNRM_ItemMaster.ItemCategory4,
SalesDetail.ExtendedPrice,
ISNULL((SELECT
SalesDetailWH.ExtendedPrice
FROM
SalesDetail SalesDetailWH
WHERE
SalesDetailWH.UserDefined5 = 'Warehouse' AND
SalesDetailWH.RowID = SalesDetail.RowID),0) AS WHDollars,
ISNULL((SELECT
SalesDetailFD.ExtendedPrice
FROM
SalesDetail SalesDetailFD
WHERE
SalesDetailFD.UserDefined5 <> 'Warehouse' AND
SalesDetailFD.RowID = SalesDetail.RowID),0) AS FDDollars,
SalesDetail.ExtendedPrice-SalesDetail.TradeDiscountAmount AS Price,
SalesDetail.TradeDiscountAmount,
SalesDetail.ExtendedCost, SalesDetail.GLPostingDate,
SalesDetail.DocumentType,
SalesDetail.CustomerNumber, DNRM_ItemMaster.ItemClassDescription,
SalesDetail.ItemNumber, DNRM_ItemMaster.ClassCode,
DNRM_Stockholder.StockholderNumber, DNRM_Stockholder.StockholderName,
ISNULL((SELECT
1
FROM
DNRM_ItemMaster DNRM_ItemMasterOB
WHERE
(DNRM_ItemMasterOB.ClassCode='DPOS' OR
DNRM_ItemMasterOB.ClassCode='MTYKFD' OR DNRM_ItemMasterOB.ClassCode='NPOS'
OR DNRM_ItemMasterOB.ClassCode='PLTYKFD' OR
DNRM_ItemMasterOB.ClassCode='POS' OR
(DNRM_ItemMasterOB.ItemCategory4='Yokohama' AND
(DNRM_ItemMasterOB.ClassCode NOT IN('LTYKCHPAT',
'LTYKCOMDAS','LTYKPRODAT','LTYKPRODHT','LTYKSIERAS','PSYKDIMESN','PSYKESTEEM','PSYKOVATON','PSYKPR3000',
'PSYKPRODPR','PSYKPRODSB','PSYKPRODTR')))) AND
DNRM_ItemMasterOB.ItemNumber = DNRM_ItemMaster.ItemNumber),0) AS
OverBill
FROM
(DNTIRWH.dbo.SalesDetail SalesDetail INNER JOIN
DNTIRWH.dbo.DNRM_ItemMaster DNRM_ItemMaster ON
SalesDetail.ItemNumber=DNRM_ItemMaster.ItemNumber)
INNER JOIN DNTIRWH.dbo.Customers Customers ON
Customers.CustomerNumber = SalesDetail.CustomerNumber
INNER JOIN DNTIRWH.dbo.DNRM_Stockholder DNRM_Stockholder ON
Left(DNRM_Stockholder.StockholderNumber,
Len(DNRM_Stockholder.StockholderNumber)-2)=Left(SalesDetail.CustomerNumber,Len(SalesDetail.CustomerNumber)-2)
WHERE
-- (LEFT(DNRM_ItemMaster.ClassCode, 2) IN ('AC', 'AT', 'BA', 'BT', 'FM',
'LG', 'MT', 'OR', 'SP', 'TU') AND
-- DNRM_ItemMaster.ClassCode <> 'SPECIAL') AND
DNRM_ItemMaster.ItemType='Sales Inventory' AND
-- Month(SalesDetail.GLPostingDate) < Month(Getdate()) and
Year(SalesDetail.GLPostingDate) >= (Year(Getdate())-1) and
Right(RTRIM(SalesDetail.CustomerNumber), 2) <> '00' AND
Left(SalesDetail.CustomerNumber, 1) IN ('D','N')
ORDER BY
SalesDetail.CustomerNumber,
DNRM_ItemMaster.ItemCategory4Hi Glen,
Are you using a SQL Server database as the Data Source?
To isolate this issue you may want to run the query via
Query Analyzer or OSQL against your database to see if
there is any data returned. If there is any problem at
this point, the SQL Server database may have something
wrong.
If SQL Server returns the data correctly, run the query
in Report Designer->Data tab to verify the result. You
may also want to create a new report and create a new
dataset to test the problem.
Sincerely,
William Wang
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.
=====================================================This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>From: "Glen Tosco" <glen@.delnat.com>
>Subject: Report Date Help
>Date: Thu, 6 Jan 2005 14:49:19 -0600
>Lines: 68
>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: <u8jKiFD9EHA.1264@.TK2MSFTNGP12.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: mail.delnat.com 65.196.130.98
>Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP08.p
hx.gbl!TK2MSFTNGP12.phx.gbl
>Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.reportingsvcs:38876
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>I created the below report and it has worked fine all
year, however it will
>not pull data for Dec 2004. Can anyone tell me what may
be causing this?
>Thanks!
>Glen
>SELECT
> Customers.CustomerName,
DNRM_ItemMaster.ItemCategory4,
>SalesDetail.ExtendedPrice,
> ISNULL((SELECT
> SalesDetailWH.ExtendedPrice
> FROM
> SalesDetail SalesDetailWH
> WHERE
> SalesDetailWH.UserDefined5 = 'Warehouse' AND
> SalesDetailWH.RowID = SalesDetail.RowID),0) AS
WHDollars,
> ISNULL((SELECT
> SalesDetailFD.ExtendedPrice
> FROM
> SalesDetail SalesDetailFD
> WHERE
> SalesDetailFD.UserDefined5 <> 'Warehouse' AND
> SalesDetailFD.RowID = SalesDetail.RowID),0) AS
FDDollars,
>
SalesDetail.ExtendedPrice-SalesDetail.TradeDiscountAmount
AS Price,
>SalesDetail.TradeDiscountAmount,
> SalesDetail.ExtendedCost, SalesDetail.GLPostingDate,
>SalesDetail.DocumentType,
> SalesDetail.CustomerNumber,
DNRM_ItemMaster.ItemClassDescription,
> SalesDetail.ItemNumber, DNRM_ItemMaster.ClassCode,
>DNRM_Stockholder.StockholderNumber,
DNRM_Stockholder.StockholderName,
> ISNULL((SELECT
> 1
> FROM
> DNRM_ItemMaster DNRM_ItemMasterOB
> WHERE
> (DNRM_ItemMasterOB.ClassCode='DPOS' OR
>DNRM_ItemMasterOB.ClassCode='MTYKFD' OR
DNRM_ItemMasterOB.ClassCode='NPOS'
> OR DNRM_ItemMasterOB.ClassCode='PLTYKFD' OR
>DNRM_ItemMasterOB.ClassCode='POS' OR
> (DNRM_ItemMasterOB.ItemCategory4='Yokohama' AND
>(DNRM_ItemMasterOB.ClassCode NOT IN('LTYKCHPAT',
>
'LTYKCOMDAS','LTYKPRODAT','LTYKPRODHT','LTYKSIERAS','PSYK
DIMESN','PSYKESTEEM','PSYKOVATON','PSYKPR3000',
> 'PSYKPRODPR','PSYKPRODSB','PSYKPRODTR')))) AND
> DNRM_ItemMasterOB.ItemNumber =DNRM_ItemMaster.ItemNumber),0) AS
>OverBill
>FROM
> (DNTIRWH.dbo.SalesDetail SalesDetail INNER JOIN
>DNTIRWH.dbo.DNRM_ItemMaster DNRM_ItemMaster ON
> SalesDetail.ItemNumber=DNRM_ItemMaster.ItemNumber)
> INNER JOIN DNTIRWH.dbo.Customers Customers ON
> Customers.CustomerNumber =SalesDetail.CustomerNumber
> INNER JOIN DNTIRWH.dbo.DNRM_Stockholder
DNRM_Stockholder ON
> Left(DNRM_Stockholder.StockholderNumber,
>Len(DNRM_Stockholder.StockholderNumber)-2)=Left(SalesDet
ail.CustomerNumber,Len(SalesDetail.CustomerNumber)-2)
>WHERE
>-- (LEFT(DNRM_ItemMaster.ClassCode, 2) IN ('AC',
'AT', 'BA', 'BT', 'FM',
>'LG', 'MT', 'OR', 'SP', 'TU') AND
>-- DNRM_ItemMaster.ClassCode <> 'SPECIAL') AND
> DNRM_ItemMaster.ItemType='Sales Inventory' AND
>-- Month(SalesDetail.GLPostingDate) <
Month(Getdate()) and
> Year(SalesDetail.GLPostingDate) >=(Year(Getdate())-1) and
> Right(RTRIM(SalesDetail.CustomerNumber), 2) <> '00'
AND
> Left(SalesDetail.CustomerNumber, 1) IN ('D','N')
>ORDER BY
> SalesDetail.CustomerNumber,
> DNRM_ItemMaster.ItemCategory4
>
>|||I created these User-Defined Fuctions to generate the previous month date
range.
For example your syntax would be:
SalesDetail.GLPostingDate >= dbo.fn_ISTCurrentMonth1(GETDATE())
AND
SalesDetail.GLPostingDate <= dbo.fn_ISTCurrentMonth2(GETDATE())
---
CREATE FUNCTION fn_ISTCurrentMonth1 (@.DATE datetime)
RETURNS VarChar(20)
AS
BEGIN
DECLARE @.ISTCurrentMonth1 VarChar(20)
SET @.ISTCurrentMonth1 = convert(varchar,
convert(datetime,(convert(varchar, CASE WHEN MONTH(@.DATE)-1 = 0 THEN 12 ELSE
MONTH(@.DATE)-1 END) + '/' + '1' + '/' + convert(varchar, CASE WHEN
MONTH(@.DATE)-1 = 0 THEN YEAR(@.DATE)-1 ELSE YEAR(@.DATE) END)), 101), 101)
RETURN(@.ISTCurrentMonth1)
END
---
CREATE FUNCTION fn_ISTCurrentMonth2 (@.DATE datetime)
RETURNS VarChar(20)
AS
BEGIN
DECLARE @.ISTCurrentMonth2 VarChar(20)
SET @.ISTCurrentMonth2 = convert(varchar, DATEADD(day, -1,
convert(datetime,(convert(varchar, MONTH(@.DATE)) + '/' + '1' + '/' +
convert(varchar, YEAR(@.DATE))), 101)), 101)
RETURN(@.ISTCurrentMonth2)
END
---
I hope these help.
I also have user-defined functions to calculate Current Year, Last Year,
Last Year Month date ranges. The years are based on a 10/1 fiscal year, but
can be modified to work for any. Let me know if these would be of any
benefit to you.
Thanks.
"William Wang[MSFT]" wrote:
> Hi Glen,
> Are you using a SQL Server database as the Data Source?
> To isolate this issue you may want to run the query via
> Query Analyzer or OSQL against your database to see if
> there is any data returned. If there is any problem at
> this point, the SQL Server database may have something
> wrong.
> If SQL Server returns the data correctly, run the query
> in Report Designer->Data tab to verify the result. You
> may also want to create a new report and create a new
> dataset to test the problem.
> Sincerely,
> William Wang
> 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.
> =====================================================> This posting is provided "AS IS" with no warranties, and
> confers no rights.
> --
> >From: "Glen Tosco" <glen@.delnat.com>
> >Subject: Report Date Help
> >Date: Thu, 6 Jan 2005 14:49:19 -0600
> >Lines: 68
> >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: <u8jKiFD9EHA.1264@.TK2MSFTNGP12.phx.gbl>
> >Newsgroups: microsoft.public.sqlserver.reportingsvcs
> >NNTP-Posting-Host: mail.delnat.com 65.196.130.98
> >Path:
> cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP08.p
> hx.gbl!TK2MSFTNGP12.phx.gbl
> >Xref: cpmsftngxa10.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:38876
> >X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> >
> >I created the below report and it has worked fine all
> year, however it will
> >not pull data for Dec 2004. Can anyone tell me what may
> be causing this?
> >
> >Thanks!
> >Glen
> >
> >SELECT
> > Customers.CustomerName,
> DNRM_ItemMaster.ItemCategory4,
> >SalesDetail.ExtendedPrice,
> > ISNULL((SELECT
> > SalesDetailWH.ExtendedPrice
> > FROM
> > SalesDetail SalesDetailWH
> > WHERE
> > SalesDetailWH.UserDefined5 = 'Warehouse' AND
> > SalesDetailWH.RowID = SalesDetail.RowID),0) AS
> WHDollars,
> > ISNULL((SELECT
> > SalesDetailFD.ExtendedPrice
> > FROM
> > SalesDetail SalesDetailFD
> > WHERE
> > SalesDetailFD.UserDefined5 <> 'Warehouse' AND
> > SalesDetailFD.RowID = SalesDetail.RowID),0) AS
> FDDollars,
> >
> SalesDetail.ExtendedPrice-SalesDetail.TradeDiscountAmount
> AS Price,
> >SalesDetail.TradeDiscountAmount,
> > SalesDetail.ExtendedCost, SalesDetail.GLPostingDate,
> >SalesDetail.DocumentType,
> > SalesDetail.CustomerNumber,
> DNRM_ItemMaster.ItemClassDescription,
> > SalesDetail.ItemNumber, DNRM_ItemMaster.ClassCode,
> >DNRM_Stockholder.StockholderNumber,
> DNRM_Stockholder.StockholderName,
> > ISNULL((SELECT
> > 1
> > FROM
> > DNRM_ItemMaster DNRM_ItemMasterOB
> > WHERE
> > (DNRM_ItemMasterOB.ClassCode='DPOS' OR
> >DNRM_ItemMasterOB.ClassCode='MTYKFD' OR
> DNRM_ItemMasterOB.ClassCode='NPOS'
> > OR DNRM_ItemMasterOB.ClassCode='PLTYKFD' OR
> >DNRM_ItemMasterOB.ClassCode='POS' OR
> > (DNRM_ItemMasterOB.ItemCategory4='Yokohama' AND
> >(DNRM_ItemMasterOB.ClassCode NOT IN('LTYKCHPAT',
> >
> 'LTYKCOMDAS','LTYKPRODAT','LTYKPRODHT','LTYKSIERAS','PSYK
> DIMESN','PSYKESTEEM','PSYKOVATON','PSYKPR3000',
> > 'PSYKPRODPR','PSYKPRODSB','PSYKPRODTR')))) AND
> > DNRM_ItemMasterOB.ItemNumber => DNRM_ItemMaster.ItemNumber),0) AS
> >OverBill
> >FROM
> > (DNTIRWH.dbo.SalesDetail SalesDetail INNER JOIN
> >DNTIRWH.dbo.DNRM_ItemMaster DNRM_ItemMaster ON
> > SalesDetail.ItemNumber=DNRM_ItemMaster.ItemNumber)
> > INNER JOIN DNTIRWH.dbo.Customers Customers ON
> > Customers.CustomerNumber => SalesDetail.CustomerNumber
> > INNER JOIN DNTIRWH.dbo.DNRM_Stockholder
> DNRM_Stockholder ON
> > Left(DNRM_Stockholder.StockholderNumber,
> >Len(DNRM_Stockholder.StockholderNumber)-2)=Left(SalesDet
> ail.CustomerNumber,Len(SalesDetail.CustomerNumber)-2)
> >WHERE
> >-- (LEFT(DNRM_ItemMaster.ClassCode, 2) IN ('AC',
> 'AT', 'BA', 'BT', 'FM',
> >'LG', 'MT', 'OR', 'SP', 'TU') AND
> >-- DNRM_ItemMaster.ClassCode <> 'SPECIAL') AND
> > DNRM_ItemMaster.ItemType='Sales Inventory' AND
> >-- Month(SalesDetail.GLPostingDate) <
> Month(Getdate()) and
> > Year(SalesDetail.GLPostingDate) >=> (Year(Getdate())-1) and
> > Right(RTRIM(SalesDetail.CustomerNumber), 2) <> '00'
> AND
> > Left(SalesDetail.CustomerNumber, 1) IN ('D','N')
> >ORDER BY
> > SalesDetail.CustomerNumber,
> > DNRM_ItemMaster.ItemCategory4
> >
> >
> >
>|||The problem is right here in your code
Month(SalesDetail.GLPostingDate) < Month(Getdate()) and
Year(SalesDetail.GLPostingDate) >= (Year(Getdate())-1) and
The previous month will not always be < current month...when the current
month is January (1), the previous month December will be (12)..
So you need to change your code to take that into account... Try testing
something like
IF you are looking for the previous month
SalesDetail.GLPostingDate between
dateadd(mm,-1,dateadd(dd,-(datediff(dd,getdate(),SalesDetail.GLPostingDate),
getdate()) ) -- first day of prev month..
and
(dateadd(dd,-(datediff(dd,getdate(),SalesDetail.GLPostingDate)) - 1,
getdate()) --lastday of prev month
This is just some on the fly code, with thought and testing you can make
this better I am sure...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Glen Tosco" <glen@.delnat.com> wrote in message
news:u8jKiFD9EHA.1264@.TK2MSFTNGP12.phx.gbl...
> I created the below report and it has worked fine all year, however it
will
> not pull data for Dec 2004. Can anyone tell me what may be causing this?
> Thanks!
> Glen
> SELECT
> Customers.CustomerName, DNRM_ItemMaster.ItemCategory4,
> SalesDetail.ExtendedPrice,
> ISNULL((SELECT
> SalesDetailWH.ExtendedPrice
> FROM
> SalesDetail SalesDetailWH
> WHERE
> SalesDetailWH.UserDefined5 = 'Warehouse' AND
> SalesDetailWH.RowID = SalesDetail.RowID),0) AS WHDollars,
> ISNULL((SELECT
> SalesDetailFD.ExtendedPrice
> FROM
> SalesDetail SalesDetailFD
> WHERE
> SalesDetailFD.UserDefined5 <> 'Warehouse' AND
> SalesDetailFD.RowID = SalesDetail.RowID),0) AS FDDollars,
> SalesDetail.ExtendedPrice-SalesDetail.TradeDiscountAmount AS Price,
> SalesDetail.TradeDiscountAmount,
> SalesDetail.ExtendedCost, SalesDetail.GLPostingDate,
> SalesDetail.DocumentType,
> SalesDetail.CustomerNumber, DNRM_ItemMaster.ItemClassDescription,
> SalesDetail.ItemNumber, DNRM_ItemMaster.ClassCode,
> DNRM_Stockholder.StockholderNumber, DNRM_Stockholder.StockholderName,
> ISNULL((SELECT
> 1
> FROM
> DNRM_ItemMaster DNRM_ItemMasterOB
> WHERE
> (DNRM_ItemMasterOB.ClassCode='DPOS' OR
> DNRM_ItemMasterOB.ClassCode='MTYKFD' OR DNRM_ItemMasterOB.ClassCode='NPOS'
> OR DNRM_ItemMasterOB.ClassCode='PLTYKFD' OR
> DNRM_ItemMasterOB.ClassCode='POS' OR
> (DNRM_ItemMasterOB.ItemCategory4='Yokohama' AND
> (DNRM_ItemMasterOB.ClassCode NOT IN('LTYKCHPAT',
>
'LTYKCOMDAS','LTYKPRODAT','LTYKPRODHT','LTYKSIERAS','PSYKDIMESN','PSYKESTEEM
','PSYKOVATON','PSYKPR3000',
> 'PSYKPRODPR','PSYKPRODSB','PSYKPRODTR')))) AND
> DNRM_ItemMasterOB.ItemNumber = DNRM_ItemMaster.ItemNumber),0) AS
> OverBill
> FROM
> (DNTIRWH.dbo.SalesDetail SalesDetail INNER JOIN
> DNTIRWH.dbo.DNRM_ItemMaster DNRM_ItemMaster ON
> SalesDetail.ItemNumber=DNRM_ItemMaster.ItemNumber)
> INNER JOIN DNTIRWH.dbo.Customers Customers ON
> Customers.CustomerNumber = SalesDetail.CustomerNumber
> INNER JOIN DNTIRWH.dbo.DNRM_Stockholder DNRM_Stockholder ON
> Left(DNRM_Stockholder.StockholderNumber,
>
Len(DNRM_Stockholder.StockholderNumber)-2)=Left(SalesDetail.CustomerNumber,L
en(SalesDetail.CustomerNumber)-2)
> WHERE
> -- (LEFT(DNRM_ItemMaster.ClassCode, 2) IN ('AC', 'AT', 'BA', 'BT', 'FM',
> 'LG', 'MT', 'OR', 'SP', 'TU') AND
> -- DNRM_ItemMaster.ClassCode <> 'SPECIAL') AND
> DNRM_ItemMaster.ItemType='Sales Inventory' AND
> -- Month(SalesDetail.GLPostingDate) < Month(Getdate()) and
> Year(SalesDetail.GLPostingDate) >= (Year(Getdate())-1) and
> Right(RTRIM(SalesDetail.CustomerNumber), 2) <> '00' AND
> Left(SalesDetail.CustomerNumber, 1) IN ('D','N')
> ORDER BY
> SalesDetail.CustomerNumber,
> DNRM_ItemMaster.ItemCategory4
>|||If works fine for all months except December...I am sure it is in the coding
but I cannot find a way to get it to work!
"William Wang[MSFT]" <v-rxwang@.online.microsoft.com> wrote in message
news:Bh4U2CG9EHA.764@.cpmsftngxa10.phx.gbl...
> Hi Glen,
> Are you using a SQL Server database as the Data Source?
> To isolate this issue you may want to run the query via
> Query Analyzer or OSQL against your database to see if
> there is any data returned. If there is any problem at
> this point, the SQL Server database may have something
> wrong.
> If SQL Server returns the data correctly, run the query
> in Report Designer->Data tab to verify the result. You
> may also want to create a new report and create a new
> dataset to test the problem.
> Sincerely,
> William Wang
> 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.
> =====================================================> This posting is provided "AS IS" with no warranties, and
> confers no rights.
> --
>>From: "Glen Tosco" <glen@.delnat.com>
>>Subject: Report Date Help
>>Date: Thu, 6 Jan 2005 14:49:19 -0600
>>Lines: 68
>>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: <u8jKiFD9EHA.1264@.TK2MSFTNGP12.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>>NNTP-Posting-Host: mail.delnat.com 65.196.130.98
>>Path:
> cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP08.p
> hx.gbl!TK2MSFTNGP12.phx.gbl
>>Xref: cpmsftngxa10.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:38876
>>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>>I created the below report and it has worked fine all
> year, however it will
>>not pull data for Dec 2004. Can anyone tell me what may
> be causing this?
>>Thanks!
>>Glen
>>SELECT
>> Customers.CustomerName,
> DNRM_ItemMaster.ItemCategory4,
>>SalesDetail.ExtendedPrice,
>> ISNULL((SELECT
>> SalesDetailWH.ExtendedPrice
>> FROM
>> SalesDetail SalesDetailWH
>> WHERE
>> SalesDetailWH.UserDefined5 = 'Warehouse' AND
>> SalesDetailWH.RowID = SalesDetail.RowID),0) AS
> WHDollars,
>> ISNULL((SELECT
>> SalesDetailFD.ExtendedPrice
>> FROM
>> SalesDetail SalesDetailFD
>> WHERE
>> SalesDetailFD.UserDefined5 <> 'Warehouse' AND
>> SalesDetailFD.RowID = SalesDetail.RowID),0) AS
> FDDollars,
> SalesDetail.ExtendedPrice-SalesDetail.TradeDiscountAmount
> AS Price,
>>SalesDetail.TradeDiscountAmount,
>> SalesDetail.ExtendedCost, SalesDetail.GLPostingDate,
>>SalesDetail.DocumentType,
>> SalesDetail.CustomerNumber,
> DNRM_ItemMaster.ItemClassDescription,
>> SalesDetail.ItemNumber, DNRM_ItemMaster.ClassCode,
>>DNRM_Stockholder.StockholderNumber,
> DNRM_Stockholder.StockholderName,
>> ISNULL((SELECT
>> 1
>> FROM
>> DNRM_ItemMaster DNRM_ItemMasterOB
>> WHERE
>> (DNRM_ItemMasterOB.ClassCode='DPOS' OR
>>DNRM_ItemMasterOB.ClassCode='MTYKFD' OR
> DNRM_ItemMasterOB.ClassCode='NPOS'
>> OR DNRM_ItemMasterOB.ClassCode='PLTYKFD' OR
>>DNRM_ItemMasterOB.ClassCode='POS' OR
>> (DNRM_ItemMasterOB.ItemCategory4='Yokohama' AND
>>(DNRM_ItemMasterOB.ClassCode NOT IN('LTYKCHPAT',
> 'LTYKCOMDAS','LTYKPRODAT','LTYKPRODHT','LTYKSIERAS','PSYK
> DIMESN','PSYKESTEEM','PSYKOVATON','PSYKPR3000',
>> 'PSYKPRODPR','PSYKPRODSB','PSYKPRODTR')))) AND
>> DNRM_ItemMasterOB.ItemNumber => DNRM_ItemMaster.ItemNumber),0) AS
>>OverBill
>>FROM
>> (DNTIRWH.dbo.SalesDetail SalesDetail INNER JOIN
>>DNTIRWH.dbo.DNRM_ItemMaster DNRM_ItemMaster ON
>> SalesDetail.ItemNumber=DNRM_ItemMaster.ItemNumber)
>> INNER JOIN DNTIRWH.dbo.Customers Customers ON
>> Customers.CustomerNumber => SalesDetail.CustomerNumber
>> INNER JOIN DNTIRWH.dbo.DNRM_Stockholder
> DNRM_Stockholder ON
>> Left(DNRM_Stockholder.StockholderNumber,
>>Len(DNRM_Stockholder.StockholderNumber)-2)=Left(SalesDet
> ail.CustomerNumber,Len(SalesDetail.CustomerNumber)-2)
>>WHERE
>>-- (LEFT(DNRM_ItemMaster.ClassCode, 2) IN ('AC',
> 'AT', 'BA', 'BT', 'FM',
>>'LG', 'MT', 'OR', 'SP', 'TU') AND
>>-- DNRM_ItemMaster.ClassCode <> 'SPECIAL') AND
>> DNRM_ItemMaster.ItemType='Sales Inventory' AND
>>-- Month(SalesDetail.GLPostingDate) <
> Month(Getdate()) and
>> Year(SalesDetail.GLPostingDate) >=> (Year(Getdate())-1) and
>> Right(RTRIM(SalesDetail.CustomerNumber), 2) <> '00'
> AND
>> Left(SalesDetail.CustomerNumber, 1) IN ('D','N')
>>ORDER BY
>> SalesDetail.CustomerNumber,
>> DNRM_ItemMaster.ItemCategory4
>>
>|||Kind of a mini-Y2K every year, eh? Unless you switch around your SQL to use
date arithmetic, you're going to have to add some fancy stuff to solve the
wraparound logic. You're treating months and years like separate integers.
Put them together in a single date and let SQL do the work for you: you've
already been given the answer: DateAdd
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Glen Tosco" <glen@.delnat.com> wrote in message
news:OJJsmxT9EHA.3416@.TK2MSFTNGP09.phx.gbl...
> If works fine for all months except December...I am sure it is in the
> coding but I cannot find a way to get it to work!
> "William Wang[MSFT]" <v-rxwang@.online.microsoft.com> wrote in message
> news:Bh4U2CG9EHA.764@.cpmsftngxa10.phx.gbl...
>> Hi Glen,
>> Are you using a SQL Server database as the Data Source?
>> To isolate this issue you may want to run the query via
>> Query Analyzer or OSQL against your database to see if
>> there is any data returned. If there is any problem at
>> this point, the SQL Server database may have something
>> wrong.
>> If SQL Server returns the data correctly, run the query
>> in Report Designer->Data tab to verify the result. You
>> may also want to create a new report and create a new
>> dataset to test the problem.
>> Sincerely,
>> William Wang
>> 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.
>> =====================================================>> This posting is provided "AS IS" with no warranties, and
>> confers no rights.
>> --
>>From: "Glen Tosco" <glen@.delnat.com>
>>Subject: Report Date Help
>>Date: Thu, 6 Jan 2005 14:49:19 -0600
>>Lines: 68
>>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: <u8jKiFD9EHA.1264@.TK2MSFTNGP12.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>>NNTP-Posting-Host: mail.delnat.com 65.196.130.98
>>Path:
>> cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP08.p
>> hx.gbl!TK2MSFTNGP12.phx.gbl
>>Xref: cpmsftngxa10.phx.gbl
>> microsoft.public.sqlserver.reportingsvcs:38876
>>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>>I created the below report and it has worked fine all
>> year, however it will
>>not pull data for Dec 2004. Can anyone tell me what may
>> be causing this?
>>Thanks!
>>Glen
>>SELECT
>> Customers.CustomerName,
>> DNRM_ItemMaster.ItemCategory4,
>>SalesDetail.ExtendedPrice,
>> ISNULL((SELECT
>> SalesDetailWH.ExtendedPrice
>> FROM
>> SalesDetail SalesDetailWH
>> WHERE
>> SalesDetailWH.UserDefined5 = 'Warehouse' AND
>> SalesDetailWH.RowID = SalesDetail.RowID),0) AS
>> WHDollars,
>> ISNULL((SELECT
>> SalesDetailFD.ExtendedPrice
>> FROM
>> SalesDetail SalesDetailFD
>> WHERE
>> SalesDetailFD.UserDefined5 <> 'Warehouse' AND
>> SalesDetailFD.RowID = SalesDetail.RowID),0) AS
>> FDDollars,
>> SalesDetail.ExtendedPrice-SalesDetail.TradeDiscountAmount
>> AS Price,
>>SalesDetail.TradeDiscountAmount,
>> SalesDetail.ExtendedCost, SalesDetail.GLPostingDate,
>>SalesDetail.DocumentType,
>> SalesDetail.CustomerNumber,
>> DNRM_ItemMaster.ItemClassDescription,
>> SalesDetail.ItemNumber, DNRM_ItemMaster.ClassCode,
>>DNRM_Stockholder.StockholderNumber,
>> DNRM_Stockholder.StockholderName,
>> ISNULL((SELECT
>> 1
>> FROM
>> DNRM_ItemMaster DNRM_ItemMasterOB
>> WHERE
>> (DNRM_ItemMasterOB.ClassCode='DPOS' OR
>>DNRM_ItemMasterOB.ClassCode='MTYKFD' OR
>> DNRM_ItemMasterOB.ClassCode='NPOS'
>> OR DNRM_ItemMasterOB.ClassCode='PLTYKFD' OR
>>DNRM_ItemMasterOB.ClassCode='POS' OR
>> (DNRM_ItemMasterOB.ItemCategory4='Yokohama' AND
>>(DNRM_ItemMasterOB.ClassCode NOT IN('LTYKCHPAT',
>> 'LTYKCOMDAS','LTYKPRODAT','LTYKPRODHT','LTYKSIERAS','PSYK
>> DIMESN','PSYKESTEEM','PSYKOVATON','PSYKPR3000',
>> 'PSYKPRODPR','PSYKPRODSB','PSYKPRODTR')))) AND
>> DNRM_ItemMasterOB.ItemNumber =>> DNRM_ItemMaster.ItemNumber),0) AS
>>OverBill
>>FROM
>> (DNTIRWH.dbo.SalesDetail SalesDetail INNER JOIN
>>DNTIRWH.dbo.DNRM_ItemMaster DNRM_ItemMaster ON
>> SalesDetail.ItemNumber=DNRM_ItemMaster.ItemNumber)
>> INNER JOIN DNTIRWH.dbo.Customers Customers ON
>> Customers.CustomerNumber =>> SalesDetail.CustomerNumber
>> INNER JOIN DNTIRWH.dbo.DNRM_Stockholder
>> DNRM_Stockholder ON
>> Left(DNRM_Stockholder.StockholderNumber,
>>Len(DNRM_Stockholder.StockholderNumber)-2)=Left(SalesDet
>> ail.CustomerNumber,Len(SalesDetail.CustomerNumber)-2)
>>WHERE
>>-- (LEFT(DNRM_ItemMaster.ClassCode, 2) IN ('AC',
>> 'AT', 'BA', 'BT', 'FM',
>>'LG', 'MT', 'OR', 'SP', 'TU') AND
>>-- DNRM_ItemMaster.ClassCode <> 'SPECIAL') AND
>> DNRM_ItemMaster.ItemType='Sales Inventory' AND
>>-- Month(SalesDetail.GLPostingDate) <
>> Month(Getdate()) and
>> Year(SalesDetail.GLPostingDate) >=>> (Year(Getdate())-1) and
>> Right(RTRIM(SalesDetail.CustomerNumber), 2) <> '00'
>> AND
>> Left(SalesDetail.CustomerNumber, 1) IN ('D','N')
>>ORDER BY
>> SalesDetail.CustomerNumber,
>> DNRM_ItemMaster.ItemCategory4
>>
>>
>

Report Dataset is lost once the Data Tab is clicked

1. Created a New Report Server Wizard Project using OLAP as datasource
2. Gone through the wizard steps to generate a matrix report.
3. All is fine and the report gets previewed. I build and deploy the report.
4. Now , if I need to add a new column I go to data tab of the report. Realize that the query is lost and now the report throws exception like the following when rebuild:

" [rsFieldReference] The Value expression for the textbox ‘Base_Premium’ refers to the field ‘Base_Premium’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."
5. No idea why the query given at the query builder stage of the wizard is lost

Steps to Reproduce:

1. Create a new Report Server Wizard Project
2. Create a new datasource using OLAP as datasource
3. Add the report data fields (query builder)
4. Select matrix report format and any style
5. Select the row, col and data fields.
6. complete the wizard.
7. Save, build and deploy the project
8. Preview the report.
9. Now try to add a new column to the report by clicking the data tab.

Actual Results:

The sql prepared from the query builder is lost and all the columns previously selected disappear. The report throws exception in preview mode something like :

" [rsFieldReference] The Value expression for the textbox ‘Base_Premium’ refers to the field ‘Base_Premium’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."

Expected Results:

Should be able to modify the report any number of times after it is initially generated.

Actual Results:

The sql prepared from the query builder is lost and all the columns previously selected disappear. The report throws exception in preview mode something like :

" [rsFieldReference] The Value expression for the textbox ‘Base_Premium’ refers to the field ‘Base_Premium’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."

Expected Results:

Should be able to modify the report any number of times after it is initially generated.

Please check this KB article: http://support.microsoft.com/kb/909535/en-us

Report Credentials

Hi i have created serveral reports in sql and uploaded it to report manager. We have an issue here. We have 5 departments at our place.

Each of the department has 5 reports. We want the department for instance Administration to open/view only their reports and no others. How is this done

"Credentials supplied by the user running the report"

Basically if i give sa and sa as username and password, i will be able to enter any department reports but i want something like sa say 'admin' this user will only open his reports and no one else.

there is also security for the folder. I could clump all the reports instead of type do it by department. So all admin reports in one folder and only admin should have access and no one else say if there is another user manager he cant access it.

Do you have any idea on how to go abt this situation?

Sheldon

Hey anyone can help me please?|||

Hi,

Each of the department has 5 reports. We want the department for instance Administration to open/view only their reports and no others

From your description, it seems that you want to grant different permission against the your users from different department, right?

In your post, you have mentioned such accounts like 'sa', I think this is the account which is for accessing the database. But in your case, we don't need to care the database security layer. What you need is to use Administrator account to logon your report site, and create different repot folders for different departments. After that, Just as you said, go into the folder and open the property tab, click on "security", here, you can grant the permission to specific users for the current folder.

there is also security for the folder. I could clump all the reports instead of type do it by department. So all admin reports in one folder and only admin should have access and no one else say if there is another user manager he cant access it.

For this part, I'm not quite sure if I've misunderstood your words. Do you mean you have multiple users with administrator permissions? If so, there's no way to prevent him from accessing folders on this report site.

Thanks.

|||

Okay All Reports Divided Into Departments

How to create a user for each department so that the user will access only their repective folder.

Dont know how to do this

|||

Hi,

You can open your IE and visit your report site, go into site settings, and conifig the site's security, create a new role assign, type the name, select the corresponding role, and submit your action.

Thanks.

Report Builder: how to create a static report?

hi,
I have created a report based on a report builder model using Visual Studio.
This report will replace the default drill report.
so after I have published my report in report server, I'll modify my model
(model properties) to setup the drill through report of my customer entity.
When I save the new config I receive this error:
rsInvalidModelDrillthroughReport
/Models/DrillReports/Report1 is not a valid drill report.
so the question is, how to create a valid drill report'
there is some particular parameters to setup?
the help don't describe this.
any sample anywhere?
thanks.
Jerome.ok
I have missed some properties.
Now all works fine!!!!
"Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
news:ezZS2LuFGHA.140@.TK2MSFTNGP12.phx.gbl...
> hi,
> I have created a report based on a report builder model using Visual
> Studio.
> This report will replace the default drill report.
> so after I have published my report in report server, I'll modify my model
> (model properties) to setup the drill through report of my customer
> entity.
> When I save the new config I receive this error:
> rsInvalidModelDrillthroughReport
> /Models/DrillReports/Report1 is not a valid drill report.
> so the question is, how to create a valid drill report'
> there is some particular parameters to setup?
> the help don't describe this.
> any sample anywhere?
> thanks.
> Jerome.
>

Report Builder, limiting the Dimensions

Hi,

I created a report model based over the cube. The problme is, it retrieves all dimensions from the cube regardless those are checked or not even in the prespective.

It does work with Measure but not with Dimensions.

Appreciate if some one can drop a line here and help me out.

Thank you

Imran

You need to make manual changes to the model for this to happen.

1. Create a new Report Builder project.

2. Save the auto-generated SMDL file from Report Manager.

3. Add it to the Report Builder project.

4. Open the model in the Model Designer and select the root Model node.

5. On the right pane, right-click on the perspective desired and choose Edit.

6. In the Edit Perspective dialog box, unselect all dimensions that you don't need.

7. When you are done, go back to the Report Manager and update the model (click the Update button).

HTH

|||

The model is generated from the cube... It is not over the relational database where I can create and edit prepectives.

Imran

|||

I understand your scenario. You can still make manual changes to a model generated from UDM by following the steps in my previous post.

|||

Ok...

I limit the Dimension as you mentioned but now I cann't generate the report... The reason is, somehow all the Dimensions need to be selected in the Model...else it is not going to work.... Updating the Model back to original state rectify the problem.

Any help?

Thank You

Imran

|||

Can you provide repro steps with the AdventureWorks cube and include a report definition of a Report Builder report that cannot be generated?

|||Report Builder models generated against an Analysis Services cube cannot be edited using Model Designer. Also, modifying the report-model (.smdl) file directly is not supported.

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

Even if try to modify the report builder model using other way, we canot get the achieved results.

--Imran

|||

I am well aware of the Report Builder-SSAS limitations and the fact that the modifying the report-model is not supported. However, when nothing else helps you don't have much choice unless you take the unsupported road, do you? You should be able to open the smdl file in a Report Builder project, make the changes I mentioned, and update the model back successfully. If something doesn't work, please let us know what it is and provide repro steps if possible.

Report Builder, limiting the Dimensions

Hi,

I created a report model based over the cube. The problme is, it retrieves all dimensions from the cube regardless those are checked or not even in the prespective.

It does work with Measure but not with Dimensions.

Appreciate if some one can drop a line here and help me out.

Thank you

Imran

You need to make manual changes to the model for this to happen.

1. Create a new Report Builder project.

2. Save the auto-generated SMDL file from Report Manager.

3. Add it to the Report Builder project.

4. Open the model in the Model Designer and select the root Model node.

5. On the right pane, right-click on the perspective desired and choose Edit.

6. In the Edit Perspective dialog box, unselect all dimensions that you don't need.

7. When you are done, go back to the Report Manager and update the model (click the Update button).

HTH

|||

The model is generated from the cube... It is not over the relational database where I can create and edit prepectives.

Imran

|||

I understand your scenario. You can still make manual changes to a model generated from UDM by following the steps in my previous post.

|||

Ok...

I limit the Dimension as you mentioned but now I cann't generate the report... The reason is, somehow all the Dimensions need to be selected in the Model...else it is not going to work.... Updating the Model back to original state rectify the problem.

Any help?

Thank You

Imran

|||

Can you provide repro steps with the AdventureWorks cube and include a report definition of a Report Builder report that cannot be generated?

|||Report Builder models generated against an Analysis Services cube cannot be edited using Model Designer. Also, modifying the report-model (.smdl) file directly is not supported.

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

Even if try to modify the report builder model using other way, we canot get the achieved results.

--Imran

|||

I am well aware of the Report Builder-SSAS limitations and the fact that the modifying the report-model is not supported. However, when nothing else helps you don't have much choice unless you take the unsupported road, do you? You should be able to open the smdl file in a Report Builder project, make the changes I mentioned, and update the model back successfully. If something doesn't work, please let us know what it is and provide repro steps if possible.

Report Builder, limiting the Dimensions

Hi,

I created a report model based over the cube. The problme is, it retrieves all dimensions from the cube regardless those are checked or not even in the prespective.

It does work with Measure but not with Dimensions.

Appreciate if some one can drop a line here and help me out.

Thank you

Imran

You need to make manual changes to the model for this to happen.

1. Create a new Report Builder project.

2. Save the auto-generated SMDL file from Report Manager.

3. Add it to the Report Builder project.

4. Open the model in the Model Designer and select the root Model node.

5. On the right pane, right-click on the perspective desired and choose Edit.

6. In the Edit Perspective dialog box, unselect all dimensions that you don't need.

7. When you are done, go back to the Report Manager and update the model (click the Update button).

HTH

|||

The model is generated from the cube... It is not over the relational database where I can create and edit prepectives.

Imran

|||

I understand your scenario. You can still make manual changes to a model generated from UDM by following the steps in my previous post.

|||

Ok...

I limit the Dimension as you mentioned but now I cann't generate the report... The reason is, somehow all the Dimensions need to be selected in the Model...else it is not going to work.... Updating the Model back to original state rectify the problem.

Any help?

Thank You

Imran

|||

Can you provide repro steps with the AdventureWorks cube and include a report definition of a Report Builder report that cannot be generated?

|||Report Builder models generated against an Analysis Services cube cannot be edited using Model Designer. Also, modifying the report-model (.smdl) file directly is not supported.

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

Even if try to modify the report builder model using other way, we canot get the achieved results.

--Imran

|||

I am well aware of the Report Builder-SSAS limitations and the fact that the modifying the report-model is not supported. However, when nothing else helps you don't have much choice unless you take the unsupported road, do you? You should be able to open the smdl file in a Report Builder project, make the changes I mentioned, and update the model back successfully. If something doesn't work, please let us know what it is and provide repro steps if possible.

sql

Report Builder, limiting the Dimensions

Hi,

I created a report model based over the cube. The problme is, it retrieves all dimensions from the cube regardless those are checked or not even in the prespective.

It does work with Measure but not with Dimensions.

Appreciate if some one can drop a line here and help me out.

Thank you

Imran

You need to make manual changes to the model for this to happen.

1. Create a new Report Builder project.

2. Save the auto-generated SMDL file from Report Manager.

3. Add it to the Report Builder project.

4. Open the model in the Model Designer and select the root Model node.

5. On the right pane, right-click on the perspective desired and choose Edit.

6. In the Edit Perspective dialog box, unselect all dimensions that you don't need.

7. When you are done, go back to the Report Manager and update the model (click the Update button).

HTH

|||

The model is generated from the cube... It is not over the relational database where I can create and edit prepectives.

Imran

|||

I understand your scenario. You can still make manual changes to a model generated from UDM by following the steps in my previous post.

|||

Ok...

I limit the Dimension as you mentioned but now I cann't generate the report... The reason is, somehow all the Dimensions need to be selected in the Model...else it is not going to work.... Updating the Model back to original state rectify the problem.

Any help?

Thank You

Imran

|||

Can you provide repro steps with the AdventureWorks cube and include a report definition of a Report Builder report that cannot be generated?

|||Report Builder models generated against an Analysis Services cube cannot be edited using Model Designer. Also, modifying the report-model (.smdl) file directly is not supported.

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

Even if try to modify the report builder model using other way, we canot get the achieved results.

--Imran

|||

I am well aware of the Report Builder-SSAS limitations and the fact that the modifying the report-model is not supported. However, when nothing else helps you don't have much choice unless you take the unsupported road, do you? You should be able to open the smdl file in a Report Builder project, make the changes I mentioned, and update the model back successfully. If something doesn't work, please let us know what it is and provide repro steps if possible.

Report Builder, Getting Started Pane: OK greyed out

Hi all,

I'm working on a new SSRS implementation, and have created a Report Model against an SSAS database. I launced Report Builder and can see and select the model in the list, but the OK button at the bottom of the pane is greyed out so I'm unable to actually start building a report against it. Any ideas on what I need to change to make the button work? I have already verified that I have full access to the data source as well as the model, in addition to the underlying SSAS DB, so I don't believe it is a permissions issue... But I'm certainly open to any suggestions.

Thanks!

You are probably running into this (from http://support.microsoft.com/kb/899825):

In Report Builder, when you select a report model that was generated from an Analysis Services cube, you must select a perspective that is contained within the model. You must not select the top-level model itself. If your report contains items that are not within a single perspective, an error may occur when you try to render the report.

Brian

Report Builder Report Possibility?

This may not be possible, but I wanted to see what some of the experts' opinions are here.

One of our users has created a report in Report Builder. I want to take this report, post it to a folder and allow her employees to run the report.

I've put it in a folder called "Reference Line Reports" and gave browser permission to a list of users. These users are able to run the report (without having access to Report Builder or the report model).

When they try to utilize the "infinite clickthrough" functionality, I get the following error.

The permissions granted to user '<domain>\<username>' are insufficient for performing this operation. (rsAccessDenied)

If I give one of these users access to the "report builder" they no longer get the error. The problem is that we don't want them to run the report builder (giving unrestricted access). I just want to be able to give them a report builder report with the clickthrough ability.

I would like to disclaim that we have not upgraded to SP1 (or CTP SP2 yet). Has anyone seen a similar problem or know if this was something fixed in sp1?

Thank you,

Dan

The only tasks that the ReportBrowser role doesn't have compared to Report Builder are Consume reports and Manage individual subscriptions. Try creating a custom role that doesn't have Consume Reports task.|||

Hi Teo,

By the way... kudos on the "Applied Microsoft Analysis Services 2005" book.

I don't know if I was clear enough with my problem. I've removed myself from the "System User" system-level role (so I no longer have ability to see Report Builder button). I've given myself every single role/task on the "Reference Line Reports" folder. I've also given myself every single task on the Report Model.

I am still able to run the Report Builder report (posted in "Reference Line Reports"). However, when I try to utilize the "infinite drill-through" I get the error.

Once I give myself the System User system-level role (with Execute Report Definitions task) and all tasks on the report model, I am able to view the report & it's infite drillthrough. This wouldn't necessarilly work for us, however, because this also gives me the Report Builder button.

I would be ok with everyone having the report builder button, but I do not want to give everyone access to see the report model. If i have the "view models" task on the report model, users can drill through (but this allows them to create reports through report builder). If I remove the "view models" task, the permissions error appears.

Maybe my problem is trying to post a Report Builder report to the Report Manager for all users to view and drill through?

Regards,

Dan

|||Thank you. You need the Execute Report Definitions task (which System User give you by default), to be able to run Report Builder reports because the Report Builder uploads the report definitions on the fly. For your situation, why don't you secure the model itself?|||

Yep, I could probably do that. At this point, however, it is probably just easier to write the report in BIDS and post it for them.

I guess I'm just not sure why they could execute the Report Builder report, but didn't have permission to drill through.

Thanks for looking into, Teo.

Regards,

Dan

|||

The Clickthrough feature is essentially ad-hoc reporting, because the user can start with any report and continue drilling "infinitely"; hence the requirement for permission to the Execute Report Definitions task.

This is an interesting scenario, however. We will consider addressing this issue in a future release. At the very least the report author should be able to remove the Clickthrough links so users don't inadvertently click them and get an intimidating error message.

Thanks for the feedback.

|||

Bob,

I appreciate the response. I'm glad to see it's more of an unforseen scenario in as opposed to something we have done incorrectly. I think it would be helpful to be able to allow users to clickthrough a report builder report without giving them access to execute report definitions.

In this scenario, we have a user who can author reports via report builder. Once she authors them, she will either keep them in her My Reports folder or we will post them for her immediate employees. We don't want to give the immediate employees "unlimited" access to the data - just restricted to the high-level summary view the report builder report gives.

We can also avoid having to re-create reports (summary and detail level views) in BIDS that have to be manually coded to drill through.

I do understand why this scenario might not be natively supported. Thank you for at least considering this scenario (or like you stated, at least considering the suppression of the intimidating error message).

Regards,
Dan

Report Builder Processing Error

Hi all,

Am having a problem processing reports using Report Builder. Have created the Data Source, DSV and Report Model and deployed them (following Report Builder walkthrough instructions in BOL). Building the report through the Report Builder app is also no problem. However when I try and process the report I always get the same error:

Report Execution Error
The Report might not be valid or the server could not process the data

Keyword not supported: 'provider'.
-
Cannot create a connection to data source 'dataSource'. (rsErrorOpeningConnection)
-
An error has occurred during report processing. (rsProcessingAborted)

Have tried building reports using several different models off several different DBs with the same results. Has anyone else seen the same?

I know that in the April CTP Report Builder was not fully functional. Just want to know if this is just a CTP glitch or something I am missing. Either way I am very impressed with what RB is going to offer the ad-hoc reporting world. Really happy that MS will be including it with Standard as well as Enterprise.

Cheers,Please ignore - my fault! Error was being thrown because I used '.' as the server name (lazy way of saying localhost) in my data source.

Everything worked as soon as I typed the full name of the server and redeployed all objects.

Apologies for time wasting.

Cheers,sql

Report Builder Permissions

Hi there,

I created a System Role: Report Builder User (checked "Execute Report Definitions")

Created a folder Home/FolderName/ReportModels

Created a Report Model, deployed it to the above folder.

The are a few users we would like them to run the report model, they belong to a windows group.

I assigned The "Report Builder User" to this Group.

The issue I have that when they run the Report Builder they get a login window (which then doesn't let them to continue).

I managed to overcome this by assiging to this group a "Browser" role at the Home level.

If I remove this assignment and set it in one level underneath the Home folder they can't run the Report Builder.

We don't want them to be able to view the folders at this level, Is there any other option besides setting the Browser role at the Home folder?

Thank you,

Itzhak

I believe users need to have at least Browser permission on all the parent folders of a given folder in addition to the folder itself. So yes, they will need to have permission to the Home folder. However, this will not (by itself) allow them to see subfolders of the Home folder to which they do not have permission. So you should still be able to hide the folders you don't want them to see.

Hope this helps!

|||

That's what I thought.

Yes, it did help.

Thank you,

Itzhak

sql