Archive

Archive for April, 2007

Working with Dates in Reporting Services

April 10, 2007 45 comments

As with any other technology ( eg. SQL , C# ), you always find people running into problems with processing of dates. I was asked today by a colleague "How to I format dates when using the Date Picker and sending it through to a stored procedure" ? Let’s have a look ….
 
1) The FormatDateTime command
 
This is pretty easy to use, but maybe a bit limiting. You can specify 1 of 4 formats using the command arguments. Let’s say we have selected a date such as 10th April 2007 , our results will be as follows :

Command

Result

FormatDateTime(Parameters!Date.Value,1)

Tuesday, April 10, 2007

FormatDateTime(Parameters!Date.Value,2)

4/10/2007

FormatDateTime(Parameters!Date.Value,3)

12:00:00 AM

FormatDateTime(Parameters!Date.Value,4)

00:00

…but the better way to do it would be to use …

2) The Format command and specify the exact format you require. For example…

Command

Result

Format(Parameters!Date.Value,"dd-MM-yyyy")

10-04-2007

Format(Parameters!Date.Value,"dd/MM/yyyy")

10/04/2007

Format(Parameters!Date.Value,"MMM-dd-yyyy")

Apr-10-2007

Format(Parameters!Date.Value,"MMM-dd-yy")

Apr-10-07

So 3 M’s give you "Apr" ….anyway this is quite useful if you’re looking for Day/Month/Year , since the system will default to MM/DD/YYYY.

Using this you should be able to display the date format you want , or send through a particular format to a Stored Proc.

EDITED 22/08/2007 : If the Format Command doesn’t work , try converting the value to a date , eg.

Format(Cdate(Parameters!Date.Value),"dd-MM-yyyy")
Advertisements
Categories: Reporting Services

Using Report Builder with Analysis Services

April 2, 2007 Leave a comment

Report Builder is taking off , and I know of a few companies now deploying it. I think most people think of it as a Reporting tool for use with a  relational data source, but you can also create a Report Model off a SQL Analysis Services datasource. Here’s how …..
 
You can create the model using SQL Management Studio or Report Manager , but NOT Visual Studio. This means that the generated model cannot be refined further once created. A bit of a limitation at the moment , but I think it will change in the next release. Anyway , open up  Management Studio
 
1) Connect to Reporting Services
 
2) Navigate to your data sources. Right click on a data source that you have already that connects to Analysis Services , and select "Generate Model". I created one that connects to the Adventure Works cube on my local machine.
 

3) A dialog box will pop up as shown below. Set the path to create the model , typically in your "Models" folder, and give it a name.

That should do it. Now when you open up Report Builder , you’ll see the model available on the right hand pane.

Now here’s a few things to remember.

1) Since you generated the model automatically , you cannot edit the entity names etc. afterwards to provide friendly names. Thus you will have to design the cube with user friendly names for the model to be generated accordingly.

2) Your parent-child hierachies are not exposed. Your Dimensions are created as Entities , with Members created as Fields. The good thing is that you still can get a drill down report with the correct heirachy if you create a matrix report and drag the Members in the correct order on either the row group or column group. This is shown below.

3) Calculated Measures and KPI’s that do not have an associated Measure Group will not appear in the Model.

For the full list , go to the following URL :

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

Well , I hope you guys find this useful. Just remember that Report Builder is NOT designed to be a slicing and dicing tool. But it still is great to give your users some functionality in terms of reporting off your OLAP sources.

Categories: Reporting Services