Showing posts with label reporting. Show all posts
Showing posts with label reporting. Show all posts

Monday, March 26, 2012

Ragged Hierarchies in RS2005

Hi...

I am connecting to Analysis Sercices 2000 from Reporting Services 2005.

When building a dataset, just in the data pane graphical designer, I have dragged 5 levels of the dimension 'Organisation' into the data pane.

The dimension has members at level 4 that have no children. But the dimenension also has members at level 5 WITH children, meaning that the dimension is ragged. If I only select levels 1-4 on the data pane, I see all level 4 children. However, if I also drag level 5 to the data pane, I loose any level 4 members that do not have children.

This means I loose vallid members that have data. Does anyone know of any way around this?

Thanks

Jeremy

Does anyone have any experience on getting this to work?

I have managed to display a ragged hierarchy by hacking the MDX, with the use of the DrillDownMember function.

Unfortunatlely, altough the ragged hierarchy does now display correctly in the data pane, it does not display in a table or matrix. So I have the situation where by my result set in the data pane does not match my result set in the actual report.

If anyone can offer any assistance it would be appreciated,

Thank you

Jeremy

|||

I have now managed to get this to work.

As I was using an AS2000 cube with the Adomd client, which according to Microsoft is not supported. If you want to use an AS2000 cube, then you have to use the OLE DB data provider.

Unfortunately I still had to use the DillDownMember function, this seems to be the only way I can get ragged hierachies to work correctly.

|||

Sorry I had not seen your post before.

Considering that the returned cellset gets flattened into a result set, if you put your set of members on the ROWS axis of the MDX query, you will actually end up with a separate column per level in you result set.

So if you structure your query like this

SELECT
{[Measures.[x]]} ON COLUMNS,
{Descendants([YourDimension].[All], [YourDimension].[Level 5], SELF_BEFORE_AFTER)} ON ROWS
FROM [YourCube]

The returned dataset should have these columns

Level_1, Level_2, Level_3, Level_4, Level_5, x

This should cater for your ragged hierarchies requirement.

sql

Ragged Dimension

I am creating a picklist in reporting services as below
WITH MEMBER [Measures].[ParameterCaption] AS '[Dim].[Attrib].CURRENTMEMBER.
MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Dim].[Attrib].
CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Dim].
[Attrib].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption],
[Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Dim].
[Attrib].[all].children ON ROWS FROM CUBE
The Value for this parameter in reports is ParameterValue and Label is
caption.
Problem is that this dimension is ragged and when th attrib name is null, the
report shows the uniquename. How can I make the report dropdown show only the
non null attributes? I tried using an IIF to select null when the
currentmember.name is null, else uniquename - but that does not work - the
currentmembwe.name never returns null. I also tried isempty and that did not
work either.
thank you!
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1I have seen people in the group say to try isnothing ... rather than
isnull or isempty
"Angie via SQLMonster.com" wrote:
> I am creating a picklist in reporting services as below
> WITH MEMBER [Measures].[ParameterCaption] AS '[Dim].[Attrib].CURRENTMEMBER.
> MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Dim].[Attrib].
> CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Dim].
> [Attrib].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption],
> [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Dim].
> [Attrib].[all].children ON ROWS FROM CUBE
> The Value for this parameter in reports is ParameterValue and Label is
> caption.
> Problem is that this dimension is ragged and when th attrib name is null, the
> report shows the uniquename. How can I make the report dropdown show only the
> non null attributes? I tried using an IIF to select null when the
> currentmember.name is null, else uniquename - but that does not work - the
> currentmembwe.name never returns null. I also tried isempty and that did not
> work either.
> thank you!
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1
>

radio button in Reporting Services 2000

Hi all,
I am using SQL Reporting Services 2000. In a report I need to have
radio buttons to give the input into query for a report. Does anybody
have idea about how to use the radio buttons into reports.
Thanx
~kHey SSRS doesn't have an option of placing radio buttons. check ut in the
toolbar of SSRS.
Amarnath
"kuls" wrote:
> Hi all,
> I am using SQL Reporting Services 2000. In a report I need to have
> radio buttons to give the input into query for a report. Does anybody
> have idea about how to use the radio buttons into reports.
> Thanx
> ~k
>

Friday, March 23, 2012

radar component ...

hi,
It's me again, i'm searching a radar component for my report (for MS SQL
reporting services) but i can't find it; where can i find this kind of
chart ?
Thanks in advance
aVrNormally I don´t like making ads for a company, but perhaps that´ll help
you:
http://www.dundas.com/products/chartrs/index.aspx?Section=ChartRS&Body=screenshots.htm
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"avrama" <avrama@.free.fr> schrieb im Newsbeitrag
news:42ae0ef9$0$2111$626a14ce@.news.free.fr...
> hi,
> It's me again, i'm searching a radar component for my report (for MS SQL
> reporting services) but i can't find it; where can i find this kind of
> chart ?
> Thanks in advance
> aVr

radar chart ?

Hello, it seems that the radar type of chart is not available for Reporting
Services (in opposition, it is available in OWC10). Anyone can confirm that
or anyone has found any way to use radar chart in Reporting Services (without
purchasing commercial chart) ?
Thanks in advanceI forgot to mention : I use SQL Reporting Services with SQL Server 2000 (not
a newer version)
"Tenval Ael" wrote:
> Hello, it seems that the radar type of chart is not available for Reporting
> Services (in opposition, it is available in OWC10). Anyone can confirm that
> or anyone has found any way to use radar chart in Reporting Services (without
> purchasing commercial chart) ?
> Thanks in advance

Wednesday, March 21, 2012

Qury regarding SQl Reporting services 2005(Authentication Problem)

Hi experts,
I have some questions on Sql server reporting services 2005.
First one is that on my report server there are some reports for which
i want to remove authentication
i.e. Any body can run those reports without giving any user name and
password, How i can do this please tell me.
Second thing is
For multiple selection, how to implement it because previously i was
working on SSRS 2000
I can show now multipple selection check list boxes now but in quert
how i can implement that
in query i wrote like this
Select * from Table where
City like @.Dataset2
But when i select multiple parameters for city it is giving error
if i select one parameter then it is running fine
So please tell me how to do these.
Thanks & Regards
DineshOn Mar 18, 2:51 pm, "Dinesh" <dinesh...@.gmail.com> wrote:
> Hi experts,
> I have some questions on Sql server reporting services 2005.
> First one is that on my report server there are some reports for which
> i want to remove authentication
> i.e. Any body can run those reports without giving any user name and
> password, How i can do this please tell me.
> Second thing is
> For multiple selection, how to implement it because previously i was
> working on SSRS 2000
> I can show now multipple selection check list boxes now but in quert
> how i can implement that
> in query i wrote like this
> Select * from Table where
> City like @.Dataset2
> But when i select multiple parameters for city it is giving error
> if i select one parameter then it is running fine
> So please tell me how to do these.
> Thanks & Regards
> Dinesh
To remove report authentication (on a report-by-report basis), select
a report in the Report Manager, then select the Properties tab and
select the very bottom radio button that says 'Credentials are not
required' and then 'Apply.'
Something like the below query should work for the multi-select
parameter:
--CREATE PROC SomeProc
DECLARE
@.CityList VARCHAR(MAX)
--AS
DECLARE @.CityListBuffer VARCHAR(MAX),
@.END_POSITION INT;
--TEST DATA--
SET @.CityList = 'Chicago, New York, Miami, San Diego, Los Angeles,
Nashville';
--TEST DATA--
CREATE TABLE #CityList (City INT);
SET @.CityListBuffer = @.CityList;
WHILE (LEN(@.CityListBuffer) > 0)
BEGIN
IF (CHARINDEX(',', @.CityListBuffer) > 0)
BEGIN
SET @.END_POSITION = CHARINDEX(',', @.CityListBuffer);
INSERT INTO #CityList VALUES (SUBSTRING(@.CityListBuffer, 1,
(@.END_POSITION - 1)));
END
IF (CHARINDEX(',', @.CityListBuffer) = 0)
BEGIN
SET @.END_POSITION = LEN(@.CityListBuffer);
INSERT INTO #CityList VALUES (SUBSTRING(@.CityListBuffer, 1,
(@.END_POSITION + 1)));
END
SET @.CityListBuffer = RIGHT(@.CityListBuffer, (LEN(@.CityListBuffer)
- @.END_POSITION));
END
SELECT * FROM TABLE WHERE CITY IN (SELECT City FROM #CityList)
DROP TABLE #CityList;
You may have to change the charindexes above for a different delimiter
(i.e., ''',''' for strings, etc); but it should work.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||On Mar 19, 4:25 pm, "EMartinez" <emartinez...@.gmail.com> wrote:
> On Mar 18, 2:51 pm, "Dinesh" <dinesh...@.gmail.com> wrote:
>
>
> > Hi experts,
> > I have some questions on Sql server reporting services 2005.
> > First one is that on my report server there are some reports for which
> > i want to remove authentication
> > i.e. Any body can run those reports without giving any user name and
> > password, How i can do this please tell me.
> > Second thing is
> > For multiple selection, how to implement it because previously i was
> > working on SSRS 2000
> > I can show now multipple selection check list boxes now but in quert
> > how i can implement that
> > in query i wrote like this
> > Select * from Table where
> > City like @.Dataset2
> > But when i select multiple parameters for city it is giving error
> > if i select one parameter then it is running fine
> > So please tell me how to do these.
> > Thanks & Regards
> > Dinesh
> To remove report authentication (on a report-by-report basis), select
> a report in the Report Manager, then select the Properties tab and
> select the very bottom radio button that says 'Credentials are not
> required' and then 'Apply.'
> Something like the below query should work for the multi-select
> parameter:
> --CREATE PROC SomeProc
> DECLARE
> @.CityList VARCHAR(MAX)
> --AS
> DECLARE @.CityListBuffer VARCHAR(MAX),
> @.END_POSITION INT;
> --TEST DATA--
> SET @.CityList = 'Chicago, New York, Miami, San Diego, Los Angeles,
> Nashville';
> --TEST DATA--
> CREATE TABLE #CityList (City INT);
> SET @.CityListBuffer = @.CityList;
> WHILE (LEN(@.CityListBuffer) > 0)
> BEGIN
> IF (CHARINDEX(',', @.CityListBuffer) > 0)
> BEGIN
> SET @.END_POSITION = CHARINDEX(',', @.CityListBuffer);
> INSERT INTO #CityList VALUES (SUBSTRING(@.CityListBuffer, 1,
> (@.END_POSITION - 1)));
> END
> IF (CHARINDEX(',', @.CityListBuffer) = 0)
> BEGIN
> SET @.END_POSITION = LEN(@.CityListBuffer);
> INSERT INTO #CityList VALUES (SUBSTRING(@.CityListBuffer, 1,
> (@.END_POSITION + 1)));
> END
> SET @.CityListBuffer = RIGHT(@.CityListBuffer, (LEN(@.CityListBuffer)
> - @.END_POSITION));
> END
> SELECT * FROM TABLE WHERE CITY IN (SELECT City FROM #CityList)
> DROP TABLE #CityList;
> You may have to change the charindexes above for a different delimiter
> (i.e., ''',''' for strings, etc); but it should work.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer- Hide quoted text -
> - Show quoted text -
Hi Enrique,
I have tried what you suggested for removing the authentication but
after doing like that report is not rendring it is showng error that
credential is not set.
So please tell what to do... This i done for a single report only
suppoese if i want to remove authentication from my report server it
self then what to do, how i can implement that.
Regards
Dinesh

Tuesday, March 20, 2012

quote in csv

I'm using the reporting services web service to export reports to csv files.
When I export to a csv, a quote mark is appended at the beginning and end of
each line. I'm using the default device info settings. This is not the
Qualifier, because I tried changing that from ["] to other characters in the
devinfo settings, and the ["] is still inserted.
How can I prevent the ["] from being inserted? It causes problems when I
open the file in Excel, because Excel thinks it means the whole row is one
field, and it gets truncated after 256 characters.
Thanks!
BillTwo different solutions depending on RS 2000 or RS 2005. Note, either you
need to be concerned with a merged cells problem when exporting CSV.
Solution 1:
Depending on how you design your reports you can do the following to export
to Excel. Or, what I do sometimes is make a copy of the report and clean it
up for data export and then hide it in list view. If you export from Report
Manager it puts CSV data in unicode which Excel puts all in one column. If
you export in ASCII then Excel does just as you want. To prevent a problem
with cells (Excel will object to sorting the data) you need to remove any
textboxes you have (for instance with a title, showing the parameters run
etc) and instead add additional header rows, merge the cells and put your
text in there instead. I add a link at the top of the report that says
Export Data. With RS 2005 you will be able to configure it to use ASCII
instead of Unicode.
Here is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
If you don't want to have it appear in a new window then do this in jump to
URL:
=Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
Solution 2:
Do the above design change to avoid the merged cell problem with sorting in
Excel. Then modify rsreportserver.config. Reboot after the change. The below
shows commenting out the existing entry and putting in the needed change to
have CSV export as ASCII
. <!--
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
-->
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
</DeviceInfo>
</Configuration>
</Extension>
Very nice and very fast.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bill" <belgie@.datamti.com> wrote in message
news:%235tVqK7ZGHA.3684@.TK2MSFTNGP05.phx.gbl...
> I'm using the reporting services web service to export reports to csv
> files.
> When I export to a csv, a quote mark is appended at the beginning and end
> of each line. I'm using the default device info settings. This is not
> the Qualifier, because I tried changing that from ["] to other characters
> in the devinfo settings, and the ["] is still inserted.
> How can I prevent the ["] from being inserted? It causes problems when I
> open the file in Excel, because Excel thinks it means the whole row is one
> field, and it gets truncated after 256 characters.
> Thanks!
> Bill
>
>
>|||I just realized that the quotes are being added when I save the csv, and
aren't there when it is generated by the reporting service.
"bill" <belgie@.datamti.com> wrote in message
news:%235tVqK7ZGHA.3684@.TK2MSFTNGP05.phx.gbl...
> I'm using the reporting services web service to export reports to csv
> files.
> When I export to a csv, a quote mark is appended at the beginning and end
> of each line. I'm using the default device info settings. This is not
> the Qualifier, because I tried changing that from ["] to other characters
> in the devinfo settings, and the ["] is still inserted.
> How can I prevent the ["] from being inserted? It causes problems when I
> open the file in Excel, because Excel thinks it means the whole row is one
> field, and it gets truncated after 256 characters.
> Thanks!
> Bill
>
>
>

Wednesday, March 7, 2012

Quick Expression Question

Hello,
I've done quite a bit of reporting services programming... and I know this
is possible... I'd like to simply hide two textboxes based ont he value of a
selected parameter value.
I've set expression value for visibility and tried various different
versions of this:Hi
This is supposed to be easy. If you select the textbox in layout view,
choose visibility in Properties, then you can see that "Hidden" has a default
value of false.
There you can write a nestet if expression:
=IIF(Parameters!YourParameter.Value = 1,True,False)
This means that if the value of your parameter returns 1 then the textbox
will be hidden, if not, it will be visible.
"Weston Weems" wrote:
> Hello,
> I've done quite a bit of reporting services programming... and I know this
> is possible... I'd like to simply hide two textboxes based ont he value of a
> selected parameter value.
> I've set expression value for visibility and tried various different
> versions of this:
>
>

Monday, February 20, 2012

Questions with Reporting Service Development by C#.

I am going to develop a project with Reporting Service Delivery Extesions by c# (schedule,subscribe and send mail), but i did not have much know about these, does someone can help me or give me some pieces of advise?

Thanks so much. :)

Go to the MSDN web site at http://msdn2.microsoft.com/en-gb/sql/aa336316.aspx and work through the training material there.

|||

thanks, that's quite useful

Questions regarding Reporting Service SOAP

Hi,
I using reporting service SOAP in my application. I would like to know..

- what is the method for delete a report
- there is list children method for listing reports from certain folder but how to get list of catalog or list of folders from reporting service, but method to use.

Thanks and best regards!

Delete a report: DeleteItem( itempath )

List folders: Call ListChildren(parentpath) and filter the resulting CatalogItem collection for folders. There is no way to only return folders from ListChildren.

List catalog: This is not meaningful because any given Report Server has one and only one catalog.