SSRS issues: LastPeriods resulting in different number of columns being returned based on date level passed in

Question

Post reply

SSRS issues: LastPeriods resulting in different number of columns being returned based on date level passed in

mailbox.jon

SSC Enthusiast

Points: 133
More actions
September 17, 2014 at 7:43 am

#150775

SELECT
([Measures].[Fact TOL Activity Count]) ON COLUMNS,
TopCount(
Except( {[ADVISER].[ID Key].[ID Key].Members}
, [ADVISER].[ID Key].[Unknown])
, 5
, ([Measures].[Fact TOL Activity Count], [Date].[Fiscal].[Fiscal Month].&[FY 13/14]&[3]&[8],
[Dim EVENTTYPE].[IASEVENTTYPEKEY].&[ET00000040] , [Dim User Type].[User Type Key].&[2])
)
* LastPeriods(2, [Date].[Fiscal].[Fiscal Month].&[FY 13/14]&[3]&[8])
ON ROWS
FROM [TOL Metrics Usage]
where ( [Dim EVENTTYPE].[IASEVENTTYPEKEY].&[ET00000040], [Dim User Type].[User Type Key].&[2] )
The date is parameterised, and can be [Fiscal Year] , [Fiscal Year].[Fiscal Quarter] or [Fiscal Year].[Fiscal Quarter].[Fiscal Month]
Unfortunately it is also returning a different resultset for each which means it wont work as the fields are set at design time and it will fall over at runtime if user passes in Quarter or Year.
I'm at a loss at what to do.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

PB_BI SSCoach Points: 17463 More actions · Answer 1

Using LastPeriods with a parameter for different levels will give you different result sets depending on the level.

Consider the following two queries:

SELECT

[Measures].[Reseller Sales Amount] ON 0,

LASTPERIODS(2,[Date].[Fiscal].[Fiscal Quarter].&[2008]&[4]) ON 1

FROM

[Adventure Works]

SELECT

[Measures].[Reseller Sales Amount] ON 0,

LASTPERIODS(2,[Date].[Fiscal].[Fiscal Year].&[2008]) ON 1

FROM

[Adventure Works]

The first one will give you fiscal quarters 3 and 4 in 2008. The second one will give you fiscal years 2008 and 2007. The level of the hierarchy is the driver.

What is it you are trying to achieve with your query? It could be that you need something other than last periods...

I'm on LinkedIn

mailbox.jon SSC Enthusiast Points: 133 More actions · Answer 2

This is the result within SSRS

This is the query run in SMSS.

The way it works is the user can view a chart in Year, Quarter or Month format.

When they click on the chart, it will take note of the date requested and go back a period.

I did not anticipate the SRSS query to start adding columns, so now I don't quite know what to do. If only i could specify the final result column as Period or something...

Thanks for taking the time to read my query.

Cheers

Jon

PB_BI SSCoach Points: 17463 More actions · Answer 3

Yeah SSRS is a bit tricky in that respect.

I suppose what you could do is have three charts based on the different columns and then show/hide them based on what the user puts into the parameter?

I'm on LinkedIn

mailbox.jon SSC Enthusiast Points: 133 More actions · Answer 4

Seriously? Duplicate codes galore... maintenance is going to be nightmare.

SSRS is coming across really handicapped when dealing with mdx queries. How did MS manage to accomplish this?

Not only am I trying to learn mdx, I also then need to learn hacks and workarounds to get it into SSRS. :rolleyes:

Thanks for confirming my fears.

PB_BI SSCoach Points: 17463 More actions · Answer 5

You can have one dataset and multiple charts, no duplicate code "galore" there. It's just a suggestion for a workaround, I have no idea of your individual situation so don't take is as gospel. MDX will work just fine in SSRS but you have to bear in mind that it is not SQL and will not act in the same way, something that Microsoft may not have had at the forefront of their minds when they integrated the two!

I'm on LinkedIn

mailbox.jon SSC Enthusiast Points: 133 More actions · Answer 6

The problem is the dataset defines the fields at design time. So if design time query date parameter is the month, it will define 3 fields.

When it runs and the user passes in quarter, it will fall over saying the dataset is expecting a month field and is not present.

I do not have enough know how to think of a way round this other then 3 datasets with different resultset defined. Queries are the same.

PB_BI SSCoach Points: 17463 More actions · Answer 7

You could achieve this if you used an expression in your dataset and returned the appropriate columns based on the parameter. There's more info on an implimentation of that sort of thing here: http://social.msdn.microsoft.com/forums/sqlserver/en-US/d38929c5-7261-452a-8ea3-839695189f24/mdx-dynamic-column

I'm on LinkedIn

mailbox.jon SSC Enthusiast Points: 133 More actions · Answer 8

PB_BI (9/17/2014)
You could achieve this if you used an expression in your dataset and returned the appropriate columns based on the parameter. There's more info on an implimentation of that sort of thing here: http://social.msdn.microsoft.com/forums/sqlserver/en-US/d38929c5-7261-452a-8ea3-839695189f24/mdx-dynamic-column

That sort of sounds like it might help, but complexity level went way up.

I've got my fields defined and the query comes back missing a field, this is causing it to fall over, if it did not I woulndt have a problem.

From what I have been reading it should just be a warning, is there a setting somewhere I can set to relax this?

I've tried adding IsMissing to anything referencing the potential missing field but its still complaining the field is missing and falling over.

mailbox.jon SSC Enthusiast Points: 133 More actions · Answer 9

Ah, the error is caused by the IIF checking both sides anyway. So if I remove the reference altogether it works!

Getting there...